Database Concepts - Data Warehouse Systems: Design and Implementation - page 34

ShipCountry CHARACTER VARYING (50) NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
FOREIGN KEY (ShippedVia) REFERENCES Shippers(ShipperID),
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
CHECK (OrderDate < = RequiredDate) )

SQL provides a DROP TABLE statement for deleting a table and an ALTER
TABLE statement for modifying the structure of a table.
The DML part of SQL is used to insert, update, and delete tuples from
the database tables. For example, the following INSERT statement

INSERT INTO Shippers(CompanyName, Phone)
VALUES ( ' Federal Express ' , ' 02 752 75 75 ' )

adds a new shipper in the Northwind database. This tuple is modified by the
following UPDATE statement:

UPDATE Shippers
SET CompanyName= ' Fedex '
WHERE CompanyName= ' Federal Express '

Finally, the new shipper is removed in the following DELETE statement:

DELETE FROM Shippers WHERE CompanyName= ' Fedex '

SQL also provides statements for retrieving data from the database. The
basic structure of an SQL expression is:

SELECT list of attributes
FROM list of tables
WHERE condition

where list of attributes indicates the attribute names whose values are to be

retrieved by the query, list of tables is a list of the relation names that will

be included in the query, and is a Boolean expression that must

be satisfied by the tuples in the result. The semantics of an SQL expression

condition

SELECT R.A, S.B
FROM R, S
WHERE R.B = S.A

is given by the relational algebra expression
π R.A,S.B ( σ R.B=S.A ( R × S )),
that is, the SELECT clause is analogous to a projection π ,the WHERE clause
is a selection σ ,andthe FROM clause indicates the Cartesian product

×

between all the tables included in the clause.
It is worth noting that an SQL query, opposite to a relational algebra one,
returns a set with duplicates (or a bag). Therefore, the keyword DISTINCT
must be used to remove duplicates in the result. For example, the query
“Countries of customers” must be written: