Basic SQL

1 .Chapter 4 6e & 8 5e: Basic SQL Prof. Steven A. Demurjian, Sr. Computer Science & Engineering Department The University of Connecticut 191 Auditorium Road, Box U-155 Storrs, CT 06269-3155 steve@engr.uconn.edu http://www.engr.uconn.edu/~steve (860) 486 - 4818 About one third of these slides are being used with the permission of Dr. Ling Lui, Associate Professor, College of Computing, Georgia Tech. About one-half of these slides have been adapted from the AWL web site for the textbook.

2 .Relational Languages A Relational Language Defines Operations to Manipulate Relations Used to Specify Retrieval Requests (Queries) Query Result is Expressed in the Form of a Relation Classification Relational Algebra (We’ve Discussed) Relational Calculus To be Briefly Explored as the Basis of ... Structured Query Language (SQL)

3 .Based on First-Order Predicate Logic Symbol Alphabet Logic Symbols (e.g.,  ¬  a Set of Constants a Set of Variables a Set of n-ary Predicates a Set of n-ary Functions Parentheses Expressions (called well-formed formulae (wff)) Built from this Symbol Alphabet Classification: Tuple Relational Calculus Domain Relational Calculus Relational Calculus

4 .Tuple Relational Calculus The Primitive Variable is a Tuple Variable Which Specifies a Tuple of a Relation Ranges Over the Tuples of a Relation In Tuple Relational Calculus, Queries are Specified as {t | F(t)} where t is a Tuple Variable and F is a Formula Consisting of the Atoms and Operators

5 .EMP(ENO, ENAME, AGE) PROJ(PNO, PNAME, LOC, MGR) WORKS(ENO, PNO, DUR) Find the Names of Employees under age 30 { t [ENAME] | t  EMP  t [AGE] <30} Find the Names of Employees working on the CAD/CAM Project { t [ENAME] | t  EMP   u ( u  WORKS  t [ENO]  u   v ( v  PROJ  u [PNO]= v [PNO]  v [PNAME]=‘CAD/CAM’))  Example  ENAME (  AGE < 30 (EMP))  ENAME (  PNAME=‘CAD/CAM” (PROJ) PNO (WORKS ENO EMP))

6 .A formula F is Composed of Atoms Boolean operators  ¬ Existential Qualifier  Universal Qualifier  Formation rules: Each Atom is a Formula If F and G are Formulae, so are F  G , F  G , ¬ F, and ¬ G If F is a Formula, so is ( F ) If F is a Formula and t is a Free variable in F , then  t ( F ) and  t ( F ) are also Formulae Nothing else is a Formula Tuple Relational Calculus

7 .The Atoms are the Following : Tuple variables R . t or R ( t ) Conditions s [ A ]  t    where s and t are Tuple Variables and A and B are Components of s and t , Respectively, and  <>  <= >= specifies that Component A of s stands in relation  to component B of t (e.g., s [SALARY] > t [SALARY]) s [ A ]  c where s , A and  are as Defined above and c is a Constant e.g., s [NAME] = Smith. Tuple Relational Calculus

8 .Find the names of Employees who have Worked for a Project for more than Two Years { t [ENAME] | t  EMP   u ( u  WORKS  t [ENO]  u   u [DUR]=‘24’))  Find all Managers who are less than 40 years old? { t [MGR] | t  PROG   u ( u  WORKS  t [PNO]  u  P N  v ( v  EMP  u [ENO]= v [ENO]  v [AGE]<40))  Example  MGR (  AGE<40 EMP ENO (WORKS PNO PROJ))  ENAME (  DUR=24 (WORKS) ENO EMP)

9 .SQL is a Partial Example of a Tuple Relational Language Simple Queries are all Declarative More Complex Queries are both Declarative and Procedural (e.g., joins, nested queries) Find the names of employees working on the CAD/CAM project SELECT EMP.ENAME FROM EMP, WORKS, PROJ WHERE (EMP.ENO= WORKS.ENO) AND (WORKS.PNO = PROJ.PNO) AND (PROJ.PNAME = “CAD/CAM”) Tuple Variables are Implicit SQL: Tuple Relational Calculus-Based

10 . PNAME, PNAME1 (PROJ LOC = LOC1 PROJ1) Let Proj1(PNAME1, LOC1) =  PNAME, LOC (PROJ) Explicit Tuple Variables Find the Pairs of all Project Names for those Projects that are Located at the same Place SELECT (P1.PNAME, P2.PNAME) FROM PROJ P1, PROJ P2 WHERE P1.LOC = P2.LOC

11 .SQL Components Data Definition Language (DDL) For External and Conceptual Schemas Views - DDL for External Schemas Data Manipulation Language (DML) Interactive DML Against External and Conceptual Schemas Embedded DML in Host PLs (EQL, JDBC, etc.) Others Integrity (Allowable Values/Referential) Catalog and Dictionary Facilities Transaction Control (Long-Duration and Batch) Authorization (Who can Do What When)

12 .SQL DDL and DML Data Definition Language (DDL) Defining the Relational Schema - Relations, Attributes, Domains - The Meta-Data CREATE TABLE Student: Name(CHAR(30 )), SSN(CHAR(9)), GPA(FLOAT(2 )) CREATE TABLE Courses: Course#(CHAR(6)), Title(CHAR(20)), Descrip (CHAR(100)), PCourse #(CHAR(6)) Data Manipulation Language (DML) Defining the Queries Against the Schema SELECT Name, SSN From Student Where GPA > 3.00

13 .History of SQL SQL is based on the Relational Tuple Calculus Evolved from SEQUEL: S tructured E nglish QUE ry L anguage - part of IBM’s SYSTEM R, 1974 SQL2 Supported by ORACLE, SYBASE, INFORMIX, IBM DB2, SQL SERVER, … MS Access, MySQL, … SQL2 also called SQL/92 is evolved from SQL/86, SQL/89, all were ANSI & ISO standard Ongoing work on SQL3 with OO Extensions

14 .Data Definition Language - DDL A Pre-Defined set of Primitive Types Numeric Character-string Bit-string Additional Types Defining Domains Defining Schema Defining Tables Defining Views Note: Each DBMS May have their Own DBMS Specific Data Types - Is this Good or Bad?

15 .DDL - Primitive Types Numeric INTEGER (or INT), SMALLINT REAL, DOUBLE PRECISION FLOAT(N) Floating Point with at Least N Digits DECIMAL(P,D) (DEC(P,D) or NUMERIC(P,D)) have P Total Digits with D to Right of Decimal Note that INTs and REALs are Machine Dependent (Based on Hardware/OS Platform)

16 .DDL - Primitive Types Character-String CHAR(N) or CHARACTER(N) - Fixed VARCHAR(N), CHAR VARYING(N), or CHARACTER VARYING(N) Variable with at Most N Characters Bit-Strings BIT(N) Fixed VARBIT(N) or BIT VARYING(N) Variable with at Most N Bits

17 .DDL - Primitive Types These Specialized Primitive Types are Used to: Simplify Modeling Process Include “Popular” Types Reduce Composite Attributes/Programming DATE : YYYY-MM-DD TIME: HH-MM-SS TIME(I): HH-MM-SS-F....F - I Fraction Seconds TIME WITH TIME ZONE: HH-MM-SS-HH-MM TIME-STAMP: YYYY-MM-DD-HH-MM-SS-F...F{-HH-MM } NOTE: Different DBMS have Different Date/Time

18 .DDL - What are Domains? Domains are Similar in Concepts to Programming Language Type Definitions A Domain can be Defined as Follows: CREATE DOMAIN CITY CHAR(15) DEFAULT ‘<Storrs>’; CREATE DOMAIN SSNFORMAT CHAR(9); Advantage of Using Domains Changing a Domain Definition in One Place Changes it Consistently Everywhere it is Used Default Values Can Be Defined for Domains Constraints Can Be Defined for Domains

19 .DDL - Dropping a Domain A Domain is Dropped As Follows: DROP DOMAIN CITY RESTRICT; DROP DOMAIN SSNFORMAT CASCADE; Restrict: Drop Operation Fails If the Domain is Used in Column Definitions Cascade: Drop Operation Causes Columns to be Defined Directly on the Underlying Data Type

20 .What is a SQL Schema? A Schema in SQL is the Major Meta-Data Construct Create a DB to Contain Multiple Tables Supports the Definition of: Relation - Table with Name Attributes - Columns and their Types Identification - Primary Key Constraints - Referential Integrity (FK) Two Part Definition CREATE Schema - Named Database or Conceptually Related Tables CREATE Table - Individual Tables of the Schema

21 .DDL-Create/Drop a Schema Creating a Schema: CREATE SCHEMA MY_COMPANY AUTHORIZATION Demurjian ; Schema MY_COMPANY bas Been Created and is Owner by the User “ Demurjian ” Tables can now be Created and Added to Schema Dropping a Schema: DROP SCHEMA MY_COMPANY RESTRICT; DROP SCHEMA MY_COMPANY CASCADE ; Restrict: Drop Operation Fails If Schema is Not Empty Cascade: Drop Operation Removes Everything in the Schema

22 .DDL - Create Tables CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL , MINIT CHAR , LNAME VARCHAR(15) NOT NULL , SSN CHAR(9) NOT NULL , BDATE DATE ADDRESS VARCHAR(30) , SEX CHAR , SALARY DECIMAL(10,2) , SUPERSSN CHAR(9) , DNO INT NOT NULL , PRIMARY KEY (SSN) , FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN) , FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ) ;

23 .DDL - Create Tables (continued) CREATE 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) ) ; CREATE TABLE DEPT_LOCATIONS (DNUMBER INT NOT NULL , DLOCATION VARCHAR(15) NOT NULL , PRIMARY KEY (DNUMBER, DLOCATION) , FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER) ) ;

24 .DDL - Create Tables (continued) CREATE TABLE PROJECT (PNAME VARCHAR(15) NOT NULL , PNUMBER INT NOT NULL , PLOCATION VARCHAR(15) , DNUM INT NOT NULL , PRIMARY KEY (PNUMBER) , UNIQUE (PNAME) , FOREIGN KEY (DNUM) REFERENCES DEPARTMENT(DNUMBER) ) ; CREATE TABLE WORKS_ON (ESSN CHAR(9) NOT NULL , PNO INT NOT NULL , HOURS DECIMAL(3,1) NOT NULL , PRIMARY KEY (ESSN, PNO) , FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN) , FOREIGN KEY (PNO) REFERENCES PROJECT(PNUMBER) ) ;

25 .DDL - Create Tables with Constraints CREATE TABLE EMPLOYEE ( . . . , DNO INT NOT NULL DEFAULT 1, CONSTRAINT EMPPK PRIMARY KEY (SSN) , CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL ON UPDATE CASCADE , CONSTRAINT EMPDEPTFK FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET DEFAULT ON UPDATE CASCADE );

26 .DDL - Create Tables with Constraints CREATE TABLE DEPARTMENT ( . . . , MGRSSN CHAR(9) NOT NULL DEFAULT 888665555 , . . . , CONSTRAINT DEPTPK PRIMARY KEY (DNUMBER) , CONSTRAINT DEPTSK UNIQUE (DNAME), CONSTRAINT DEPTMGRFK FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET DEFAULT ON UPDATE CASCADE );

27 .CREATE TABLE Supplier_Parts ( S# CHAR(5) NOT NULL, P# CHAR(6) NOT NULL, QTY INTEGER, PRIMARY KEY (S# P#) FOREIGN KEY S# REFERENCE SUPPLIER ON DELETE CASCADE, REFERENCE PART ON DELETE RESTRICT); Create and Drop: Summary Defines Types and Order of Attributes in a Relation May Specify Which Attribute Are Keys and Which Cannot Be Null Create a New Empty Table May Define DELETION Effect by Cascade/Restricted

28 .SQL Data Definition Drop Table A Relation Can Be Dropped at Any Time Drop Will Delete Both Definition and Data All Views, Indexes, and FKs are Dropped DROP TABLE SUPPLIER; Alter Table Add New Attributes or PK and FK to the Table All Existing Records are Expanded With Nulls, but Not Physically Changed. ALTER TABLE SUPPLIER ADD DISCOUNT SMALLINT;

29 .DDL - Drop Tables Command: DROP TABLE EMPLOYEE RESTRICT; DROP TABLE DEPARTMENT CASCADE; Restrict: Drop Operation fails if the Table is Referenced by View and/or Constraint Definitions Cascade: Drop Operation Removes Referencing View and Constraint Definitions