Common mistakes in solving SELECT statement exercises

The company receiving recycled and has several points of reception. Each point receives money to pay to suppliers for raw materials. In fact, the diagram shows two databases. In each task under this scheme uses only one pair of tables (with the suffix «_o», or without it).

In tables Income_o and Outcome_o the primary key is a pair of attributes (point, date) - point number and date of reception. This key must simulate a situation when information about receiving money at the receiving point, and their extradition to suppliers recorded in the database no more than once a day.

Fig. 2.1.The scheme of «Recycled materials company» database.

Note

The data values in the column date does not contain the time, for example, 2001-03-22 00:00:00.000. Unfortunately, the use for the datetime data type of column may cause confusion, since it is obvious that taking into account the time does not allow to limit multiple input values with the same date (and the item number) and different time of day. This deficiency associated with the lack of specific data types for date and time have been overcome in  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server 2008. To ensure the correct input in SQL Server 2000 the CK_Income_o restriction can be used:

ALTER TABLE Income_o ADD 
CONSTRAINT PK_Income_o PRIMARY KEY 
 (
 [point],
 [date]
 ),
CONSTRAINT CK_Income_o CHECK 
 (
 DATEPART(hour,[date]) + DATEPART(minute,[date]) + 
 DATEPART(second,[date]) + DATEPART(millisecond,[date]) = 0
 )

This restriction (the amount of hours, minutes, seconds and milliseconds is equal to zero) would not set any time other than 00:00:00.000. With such restriction the primary key will actually ensure that there is only one row per day for each point of reception.

The Income_o (point, date, inc) table contains information on the income (inc) per point of reception (point). Similar table — Outcome_o (point, date, out) — is used to control the expenses (out).

The second pair of tables — Income (code, point, date, inc) and Outcome (code, point, date, out) — simulates the situation where the income and expenses of money can be recorded several times a day. It should be noted that if the record in the last table only date without time (that occurs), then no natural combination of attributes can be used as the primary key, since the amount of money can be the same. Therefore, we must take into account the time, or add an artificial key. We used the second option, adding an integer column code only in order to ensure uniqueness of records in the table.

Content:

(2.1) Exercise #30
(2.2) Exercise #59
(2.3) Exercise #60
(2.4) Exercise 128