CSC 742 Database Management Systems Topic 10 SQL Spring 2002 CSC 742 DBMS by Dr Peng Ning 1 Part A Data Definition Language DDL Spring 2002 CSC 742 DBMS by Dr Peng Ning 2 Schema and Catalog Schema A collection of relations tables Identified by a schema name Include tables constraints views domains and others CREATE SCHEMA COMPANY AUTHORIZATION Bob Catalog A named collection of schemas Integrity constraints can be defined between relations only if they exist in the same catalog Spring 2002 CSC 742 DBMS by Dr Peng Ning 3 CREAT TABLE Define a new relation Specify name and attributes Specify constraints Attribute constraints Relation constraints Primary key Other keys Referential integrity constraint Spring 2002 CSC 742 DBMS by Dr Peng Ning 4 CREAT TABLE EMPLOYEE FNAME VARCHAR 15 NOT NULL LNAME VARCHAR 15 NOT NULL SSN CHAR 9 NOT NULL SUPERSSN CHAR 9 DNO INT NOT NULL PRIMARY KEY SSN FOREIGN KEY SUPERSSN REFERENCE EMPLOYEE SSN FOREIGN KEY DNO REFERENCE DEPARTMENT DNUMBER CREAT TABLE DEPARTMENT DNAME VARCHAR 15 NOT NULL DNUMBER INT NOT NULL MGRSSN CHAR 9 NOT NULL MGRSTARTDATE DATE PRIMARY KEY DNUMBER UNIQUE DNAME FOREIGN KEY MGRSSN REFERENCES EMPLOYEE SSN Spring 2002 CSC 742 DBMS by Dr Peng Ning 5 Three options Two events ON DELETE ON UPDATE Referential Triggered Actions SET NULL SET DEFAULT CASCADE CREAT TABLE DEPARTMENT DNAME VARCHAR 15 NOT NULL DNUMBER INT NOT NULL MGRSSN CHAR 9 NOT NULL MGRSTARTDATE DATE PRIMARY KEY DNUMBER UNIQUE DNAME FOREIGN KEY MGRSSN REFERENCES EMPLOYEE SSN ON DELETE SET DEFAULT ON UPDATE CASCADE Spring 2002 CSC 742 DBMS by Dr Peng Ning 6 Self Study DROP SCHEMA DROP TABLE ALTER TABLE Alter attributes Alter constraints Spring 2002 CSC 742 DBMS by Dr Peng Ning 7 Part B Data Manipulation Language DML Spring 2002 CSC 742 DBMS by Dr Peng Ning 8 DML Our focus is how to formulate queries Self study INSERT DELETE UPDATE Spring 2002 CSC 742 DBMS by Dr Peng Ning 9 SELECT Used for retrieval Used to specify subqueries for retrieval and for the other operations Not quite the or select operator of the relational algebra SELECT tuple queries aggregate queries Spring 2002 CSC 742 DBMS by Dr Peng Ning 10 SELECT Cont d Basic paradigm SELECT column1 columnn FROM table1 tablem WHERE condition The WHERE condition can be a boolean combination of other conditions involving the tables table1 through tablem Spring 2002 CSC 742 DBMS by Dr Peng Ning 11 Employee SSN Lname Fname Salary 111 22 3333 Smith John 30000 121 23 3333 Wong Frank 45000 153 32 1342 Wallace Jennifer 43000 154 33 3333 Borg James 56000 555 44 5555 English Joyce 53000 List the names of the employees whose salary is more than 50 000 Relational Algebra SELECT Lname Fname Select Project FROM Employee WHERE Salary 50000 Spring 2002 CSC 742 DBMS by Dr Peng Ning 12 Employee Dependent Fname Lnane SSN Fname Lnane ESSN Alice Zelaya 999 88 7777 Eric Zelaya 999 88 7777 Jennifer Wallace 111 22 3333 Alex Wallace 111 22 3333 Joyce White 222 33 4444 List the names of the dependents of Alice Zelaya SELECT Dependent Fname Dependent Lname FROM Employee Dependent WHERE Employee Fname Alice AND Employee Lname Zelaya AND Employee SSN Dependent ESSN Relational Algebra Select Project Join Spring 2002 CSC 742 DBMS by Dr Peng Ning 13 Exercise 1 Find names of employees in the research dept Employee Fname Lname SSN Bdate Address Sex Salary SuperSSN Dno Department Dname Dnumber MgrSSN MgrStartDate SELECT FROM WHERE AND Spring 2002 CSC 742 DBMS by Dr Peng Ning 14 Exercise 2 For every project in Stafford list the controlling dept number and the dept manager s last name Employee Fname Lname SSN Bdate Address Sex Salary SuperSSN Dno Department Dname Dnumber MgrSSN MgrStartDate Project Pname Pnumber Plocation Dnum SELECT FROM WHERE AND AND Spring 2002 CSC 742 DBMS by Dr Peng Ning 15 SQL Variants Employee SSN Lname Fname Salary 111 22 3333 Smith John 30000 121 23 3333 Wong Frank 45000 153 32 1342 Wallace Jennifer 43000 154 33 3333 Borg James 56000 555 44 5555 English Joyce 53000 SELECT FROM Employee WHERE Salary 50000 Spring 2002 CSC 742 DBMS by Dr Peng Ning 16 SQL Variants ALL and DISTINCT Employee SSN Lname Fname Salary 111 22 3333 Smith John 30000 121 23 3333 Wong Frank 30000 153 32 1342 Wallace Jennifer 43000 154 33 3333 Borg James 53000 555 44 5555 English Joyce 53000 SELECT ALL Salary FROM Employee Spring 2002 SELECT DISTINCT Salary FROM Employee CSC 742 DBMS by Dr Peng Ning 17 SQL Variants Renaming Employee Fname Lname SSN Bdate Address Sex Salary SuperSSN Dno Retrieve the employee s name and the names of their immediate supervisor SELECT E Fname E Lname S Fname S Lname FROM Employee AS E Employee AS S WHERE E SuperSSN S SSN Spring 2002 CSC 742 DBMS by Dr Peng Ning 18 SQL Variants IS NOT NULL Employee SSN Lname Fname Salary 111 22 3333 Smith John 30000 121 23 3333 Wong Frank 30000 153 32 1342 Wallace Jennifer 43000 154 33 3333 Borg James NULL 555 44 5555 English Joyce 53000 SELECT Lname Fname FROM Employee WHERE Salary IS NULL Spring 2002 SELECT Lname Fname FROM Employee WHERE Salary IS NOT NULL CSC 742 DBMS by Dr Peng Ning 19 SQL Variants ORDER BY Employee SSN Lname Fname Salary 111 22 3333 Smith John 30000 121 23 3333 Wong Frank 30000 153 32 1342 Wallace Jennifer 43000 154 33 3333 Borg James NULL 555 44 5555 English Joyce 53000 SELECT Lname Fname FROM Employee WHERE Salary IS NOT NULL ORDER BY Salary DESC Spring 2002 CSC 742 DBMS by Dr Peng Ning 20 Nested Queries A nested query subquery a query in the WHERE clause of another query Some paradigms of subqueries are column NOT IN subquery column op subquery column op ANY ALL subquery NOT EXISTS subquery subquery CONTAINS subquery Spring 2002 CSC 742 DBMS by Dr Peng Ning 21 Nested Queries Example 1 Find all the dept 6 projects located where a dept 5 project is located Project Pname Pnumber Plocation Dnum SELECT Project Pnumber FROM Project WHERE AND Project Plocation IN SELECT Project Plocation FROM Project WHERE Spring 2002 CSC 742 DBMS by Dr Peng Ning 22 Nested Queries Example 2 List the name of the employee who has the highest salary Employee SSN Lname Fname Salary 111 22 3333 Smith John 30000 121 23 3333 Wong Frank 30000 153 32 1342 Wallace Jennifer 43000 154 33 3333 Borg James 56000 555 44 5555 English Joyce 53000 SELECT E Lname E Fname FROM Employee WHERE SELECT Salary FROM Employee Spring 2002 CSC 742 DBMS by Dr Peng Ning 23 Subqueries Subqueries can t modify tables For column op subquery subquery can return exactly one value from one column Correlated subquery When the subquery includes a