---

I. Advantages of having an international standard

• Easier conversion from one relational DBMS to another relational DBMS

• Access to multiple relational DBMSs from a single database application
  

II. SQL, Relational Algebra, and Tuple Relational Calculus

• SQL (Structured Query Language) includes some features from relational algebra , but it is based to a greater extent on the tuple relational calculus (see Section 9.3).

• The SQL syntax is more user-friendly than either of the two formal languages.

• SQL-86 or SQL1

• SQL2 (also referred to as SQL-92)

• SQL3, which will further extend SQL with object-oriented and other recent database concepts.
  

• Table of Contents:

• Section 8.1 describes the SQL2 DDL commands for creating and modifying schemas, tables, and constraints. 
• Section 8.2 describes the basic SQL constructs for specifying retrieval queries.
• Section 8.3 goes over more complex features. 
• Section 8.4 describes the SQL commands for inserting, deleting and updating.
• Section 8.5 discusses the concept of views (virtual tables). 
• Section 8.6 shows how general constraints may be specified as assertions or triggers.
  

Ø Go to the Index

---

8.1  Data Definition, Constraints, and Schema Changes in SQL2

• Schema and Catalog Concepts 

• An SQL schema is identified by a schema name, and includes an authorization identifier to indicate the user or account who owns the schema, as well as descriptors for each element in the schema.

• Schema elements include the tables, constraints, views, domains, and other constructs (such as authorization grants) that describe the schema. 

• A schema is created via the CREATE SCHEMA statement .

• Example: CREATE SCHEMA COMPANY AUTHORIZATION JSMITH;

• A SQL catalog is a named collection of schemas in an SQL environment. 

• A catalog always contains a special schema called INFORMATION_SCHEMA, which provides information on all the element descriptors of all the schemas in the catalog to authorized users. 

• Integrity constraints such as referential integrity can be defined between relations only if they exist in schemas within the same catalog. 

• Schemas within the same catalog can also share certain elements, such as domain definitions.
  
  

• The CREATE TABLE Command + Data Types + Constraints

• The CREATE TABLE command is used to specify a new relation by giving it a name and specifying its attributes and constraints. 

• Example: See Figure 8.1(a).

• The data types available for attributes include numeric, character-string, bit-string, date, and time.

• Numeric data types include integer numbers of various sizes (INTEGER or INT, and SMALLINT), and real numbers of various precision (FLOAT, REAL, DOUBLE PRECISION). Formatted numbers can be declared by using DECIMAL(i,j)—or DEC(i,j) or NUMERIC(i,j)—where i, the precision, is the total number of decimal digits and j, the scale, is the number of digits after the decimal point. 

• Character-string data types are either fixed-length—CHAR(n) or CHARACTER(n), where n is the number of characters—or varying-length—VARCHAR(n) or CHAR VARYING(n) or CHARACTER VARYING(n), where n is the maximum number of characters. 

• Bit-string data types are either of fixed length n—BIT(n)—or varying length—BIT VARYING(n), where n is the maximum number of bits. 

• The DATE data type has ten positions, and its components are YEAR, MONTH, and DAY typically in the form YYYY-MM-DD. 

• The TIME data type has at least eight positions, with the components HOUR, MINUTE, and SECOND, typically in the form HH:MM:SS. 

• TIME ( i )

• The TIME WITH TIME ZONE data type 

• The TIMESTAMP data type includes both the DATE and TIME fields.

• The INTERVAL data type: either YEAR/MONTH intervals or DAY/TIME intervals

• User-defined data types: Domains

• Example: CREATE DOMAIN SSN_TYPE AS CHAR(9);
  

---

• NOT NULL constraint 

• Default value for an attribute

• The PRIMARY KEY clause specifies one or more attributes that make up the primary key of a relation. 

• The UNIQUE clause specifies alternate (or secondary) keys. 

• Referential integrity is specified via the FOREIGN KEY clause. 

• A referential triggered action clause may be added to any foreign key constraint.  The options include SET NULL, CASCADE, and SET DEFAULT.  An option must be qualified with either ON DELETE or ON UPDATE. 

• Example: SET NULL ON DELETE, CASCADE ON UPDATE, CASCADE ON DELETE ( See Figure 8.1(b) )

• As a general rule, the CASCADE option is suitable for "relationship" relations such as WORKS_ON, for relations that represent multivalued attributes such as DEPT_LOCATIONS, and for relations that represent weak entity types such as DEPENDENT.
  

• Why?
  

• Base tables, Virtual tables, Views, Derived tables ?

• The DROP SCHEMA and DROP TABLE Commands

• DROP SCHEMA COMPANY CASCADE;
• DROP SCHEMA COMPANY RESTRICT ;

• DROP TABLE DEPENDENT CASCADE; 
• DROP TABLE DEPENDENT RESTRICT;
  

• The ALTER TABLE Command

• The possible alter table actions include 
• adding or dropping a column (attribute)

ALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12); 

ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS CASCADE; 

• changing a column definition

ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN DROP DEFAULT;

ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN SET DEFAULT "333445555";

• adding or dropping table constraints

ALTER TABLE COMPANY.EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE ;

Ø Go to the Index

---

8.2  Basic Queries in SQL

• The 'relation' concept in SQL and in the formal relational model:  
• A bag of tuples   versus   A set of tuples
  

•  SELECT <attribute list>
   FROM <table list>
   WHERE <condition>; 

SELECT <attribute and function list>
FROM <table list>
[WHERE <condition>]
[GROUP BY <grouping attribute(s)>]
[HAVING <group condition>]
[ORDER BY <attribute list>];

• Q0:
  

 SELECT BDATE, ADDRESS
    FROM EMPLOYEE
    WHERE FNAME=‘John’ AND MINIT=‘B’ AND LNAME=‘Smith’;
 

• Exercise: Express the above query as relational algebra operations.

• Exercise: Express queries Q1 through Q11 in the book as relational algebra operations.

• Substring Comparisons

 Q12:
 SELECT  FNAME, LNAME
  FROM  EMPLOYEE
 WHERE  ADDRESS LIKE ‘%Houston,TX%’;

 
QUERY 12A: Find all employees who were born during the 1950s.
 
 SELECT FNAME, LNAME
 FROM EMPLOYEE
 WHERE BDATE LIKE’_ _ 5 _ _ _ _ _ _ _’;
 

• Arithmetic Operators

QUERY 13: Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10 percent raise.
 
 SELECT FNAME, LNAME, 1.1*SALARY
 FROM EMPLOYEE, WORKS_ON, PROJECT
 WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME=‘ProductX’;
 

• Ordering: The  ORDER BY clause

QUERY 15: Retrieve a list of employees and the projects they are working on, ordered by department and, within each department, ordered alphabetically by last name, first name.

 SELECT DNAME, LNAME, FNAME, PNAME
 FROM  DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT
 WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER
 ORDER BY DNAME DESC, LNAME, FNAME;
 
Ø Go to the Index

---

 8.3 More Complex Queries

• Nested Queries

 Q4A:

 SELECT   DISTINCT PNUMBER
 FROM  PROJECT
 WHERE  PNUMBER IN

 (SELECT  PNUMBER
 FROM PROJECT, DEPARTMENT, EMPLOYEE
 WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME=‘Smith’)

Another nested query:  union-compatible list of attributes

 SELECT  DISTINCT ESSN
 FROM  WORKS_ON
 WHERE  (PNO, HOURS) IN

 (SELECT PNO, HOURS FROM WORKS_ON WHERE SSN=‘123456789’);

 
Another nested query:  > ALL

SELECT  LNAME, FNAME
 FROM EMPLOYEE
 WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEE WHERE DNO=5);

Exercise: Express the above queries as relational algebra operations.  

• The rule for resolving ambiguous reference to attributes in a nested query : 

A reference to an unqualified attribute refers to the relation declared in the innermost nested query.  (See examples above.)

• Question: Then how would you refer to different relations in the outer query?  

Answer: Use qualified attributes.

• Correlated nested queries: Whenever a condition in the WHERE-clause of a nested query references some attribute of a relation declared in the outer query, the two queries are said to be correlated.  Example: See Query 16 above.

• We can understand a correlated query better by considering that the nested query is evaluated once for each tuple (or combination of tuples) in the outer query.
  

• EXISTS and NOT EXISTS functions are usually used in conjunction with a correlated nested query.  Example: Query 16B.
  

• EXISTS(Q) returns TRUE if there is at least one tuple in the result of query Q, and it returns FALSE otherwise.
  

• Exercise: Implement the query "Retrieve the names of employees who have no dependents".
  

Answer: See Q6.

• Exercise: Implement the query "List the names of managers who have at least one dependent".
  

Answer: See Q7.


Ø Go to the Index

---

• Use an explicit set of values in the WHERE-clause
  

 SELECT  DISTINCT ESSN
 FROM  WORKS_ON
 WHERE  PNO IN (1, 2, 3);

• To rename any attribute that appears in the result of a query by adding the qualifier AS 

• The implementation of JOIN operations:

• Q8A:
   SELECT  E.LNAME AS EMPLOYEE_NAME, S.LNAME AS SUPERVISOR_NAME
   FROM  EMPLOYEE AS E, EMPLOYEE AS S
   WHERE  E.SUPERSSN=S.SSN;
   

• Using the JOIN operator:
  

 Q1A:
 SELECT  FNAME, LNAME, ADDRESS
 FROM  (EMPLOYEE JOIN DEPARTMENT ON DNO=DNUMBER)
 WHERE  DNAME=‘Research’;
 

•  Using the NATURAL JOIN operator:

 Q1B:
 SELECT  FNAME, LNAME, ADDRESS
 FROM  (EMPLOYEE NATURAL JOIN (DEPARTMENT AS DEPT (DNAME, DNO, MSSN, MSDATE)))
 WHERE  DNAME=‘Research;

 

NOTE: In a NATURAL JOIN on two relations R and S, no join condition is specified; an implicit equi-join condition for each pair of attributes with the same name from R and S is created. Each such pair of attributes is included only once in the resulting relation

• Nested JOINs:
  

Q2A:
 SELECT  PNUMBER, DNUM, LNAME, ADDRESS, BDATE
 FROM  ((PROJECT JOIN DEPARTMENT ON DNUM= DNUMBER)
                    JOIN EMPLOYEE ON MGRSSN=SSN)
 WHERE  PLOCATION=‘Stafford’;
 

---

• Aggregate Functions
  

Q19:

 SELECT  SUM (SALARY), MAX (SALARY), MIN (SALARY), AVG (SALARY)
FROM  EMPLOYEE;

Q20:
SELECT  
 SUM (SALARY), MAX (SALARY), MIN (SALARY), AVG (SALARY)
 FROM  EMPLOYEE, DEPARTMENT
 WHERE  DNO=DNUMBER AND DNAME=‘Research’;
 

Q23:
 SELECT  COUNT (DISTINCT SALARY)
 FROM  EMPLOYEE;
 

 
 Q5:
 SELECT  LNAME, FNAME
 FROM  EMPLOYEE
 WHERE  (SELECT  COUNT (*)
             FROM  DEPENDENT
             WHERE  SSN=ESSN)
        >= 2;
 

• Grouping and Aggregate Functions
  

 Q24:  See Fig. 8.4 (a)

 SELECT  DNO , COUNT (*), AVG (SALARY)
 FROM  EMPLOYEE
 GROUP BY  DNO ;
 

NOTE: The SELECT-clause includes only the grouping attribute and the functions to be applied on each group of tuples.

• Exercise: How would you implement the following query "For each project, retrieve the project number, the project name, and the number of employees who work on that project. " ?

Answer: See Q25.

• The HAVING Clause:  

NOTE: HAVING provides a condition on the group of tuples associated with each value of the grouping attributes; and only the groups that satisfy the condition are retrieved in the result of the query.

 Q26:
 SELECT  PNUMBER, PNAME, COUNT (*)
 FROM  PROJECT, WORKS_ON
 WHERE  PNUMBER=PNO
 GROUP BY  PNUMBER, PNAME
 HAVING   COUNT (*) > 2;
 

• Exercise: How would you implement the following query " For each project, retrieve the project number, the project name, and the number of employees from department 5 who work on the project. " ?

Answer: See Q27.

Ø Go to the Index

---

8.4 Insert, Delete, and Update Statements in SQL

• Study the examples in this section for yourself.
  

---

8.5 Views (Virtual Tables) in SQL

• A view in SQL terminology is a single table that is derived from other tables (Note 15). These other tables could be base tables or previously defined views.
  

• We can think of a view as a way of specifying a table that we need to reference frequently, even though it may not exist physically.
  

• The 'dynamic' nature of a view:
  

A view is always up to date; if we modify the tuples in the base tables on which the view is defined, the view must automatically reflect these changes. Hence, the view is not realized at the time of view definition but rather at the time we specify a query on the view.

• View Implementation and View Update: Two approaches
• Query modification: modifying the view query into a query on the underlying base tables.
• Problems?

• View materialization: physically creating a temporary view table when the view is first queried and keeping that table on the assumption that other queries on the view will follow. 
• Requirements?