Structured Query Language

1
Structured Query Language

• CS 54 Database Systems
• Lecture 7

2
What is SQL

• Structured Query Language.
• A declarative query language.
• Its prototype, SEQUEL, was developed at IBM
  Research.
• Standards in 1986 (SQL1 or SQL-86) and 1992 (SQL2
  or SQL-92).
• Both a DDL and a DML.

3
What is SQL (contd.)

• Basic elements -- statements/commands.
• Many other functions defining views, indexes,
  embedded in a host language, etc.
• Available in almost all commercial DBMSs.
• SQL is case insensitive.

4
Data Definition in SQL

• Schema in SQL.
• An SQL schema has a name, an owner (user
  account), and descriptions for each elements in
  the schema (tables, views, domains, etc.).
• CREATE SCHEMA COMPANY AUTHORIZATION JSMITH
  
• Schema elements can be defined later.

5
Data Definition in SQL (contd.)

• CREATE TABLE command define a new table
  - Table name. -
  Attributes name and type/format
  - Constraints.
  domain,
  key,entity, integrity, referential integrity
• The schema where the table belongs is implicitly
  specified in the environment where command is
  executed.
• To explicitly specify the schema, attach the
  schema name before the table name.
  CREATE TABLE COMPANY.EMPLOYEE

6
Data Definition in SQL (contd.)

• Example 1
• 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))

7
Data Types in SQL

• Numeric.
  integer, float, double,
  etc. formatted decimal(i,j) or
  numeric(i,j)
• Character-string. fixed length
  char(n) varying length varchar(n) default
  n 1
• Bit-string. fixed length bit(n)
  varying length bit varying(n)
  default n 1

8
Data Types in SQL (contd.)

• DATE YYYY-MM-DD
• TIME HHMMSS
• TIME(i) fractions of seconds up to i-the digit.
• TIMESTAMP Includes both DATE and TIME.
• INTERVAL Specifies relative value for DATE,
  TIME, and TIMESTAMP.

9
Data Definition in SQL (Cont.)

• We can specify the data types of attributes
  directly as in Example 1, or we can specify a
  domain and then use the domain.
• create domain SSN_TYPE as char(9)
  create table EMPLOYEE(,SSN
  SSN_TYPE,)
• Domain can be used for many attributes which
  makes it easy to change the schema and improve
  the readability.

10
Data Definition in SQL (Cont.)

• An attribute can have a default value via a
  DEFAULT clause.
• A constraint NOT NULL is specified if NULL is not
  allowed for that attribute. For the primary key,
  this must be specified.
• Table constraints.
• - PRIMARY KEY. -
  UNIQUE -- alternative key.
  - FOREIGN KEY --
  referential integrity constraints.

11
Data Definition in SQL (Cont.)

• Referential triggered action. an action taken
  when a referential integrity constraint is
  violated.
• - Attaching one of the options to the
  foreign key constraintsSET
  NULL, CASCADE, SET DEFAULT. - Must be
  qualified with ON DELETE or ON UPDATE.
• Constraints can be given names for referencing
  purposes.

12
Data Definition in SQL (Cont.)

• DROP SCHEMA and DROP TABLE command.
• To remove COMPANY database schema and all its
  tables, domains, etc.
  DROP SCHEMA COMPANY CASCADE
• To remove the schema only if it is empty
  (otherwise, ignore the command)
  DROP SCHEMA COMPANY RESTRICT
• Similarly, DROP TABLE EMPLOYEE CASCADE
  DROP TABLE EMPLOYEE RESTRICT

13
Data Definition in SQL (Cont.)

• ALTER TABLE command. to add/drop a column,
  change a column definition, add/drop table
  constraints.
• Either CASCADE or RESTRICT must be chosen for
  dropping.
• Examples.
• ALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12)

14
Data Definition in SQL (Cont.)
ALTER TABLE COMPANY.EMPLOYEE
DROP ADDRESS CASCADE ALTER TABLE
COMPANY.DEPARTMENT ALTER MGRSSN DROP DEFAULT
ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN SET
DEFAULT 333445555'' ALTER TABLE
COMPANY.EMPLOYEE DROP CONSTRAINT EMPSUPERFK
CASCADE
15
Data Manipulation in SQL

• INSERT, DELETE, and UPDATE.
• Integrity constraints should be enforced.
• INSERT command.
• INSERT INTO EMPLOYEE VALUES
  (Richard', K', Marini', 652349822', 30-DEC-
  62', 98 Oak, Katy, TX',M', 36000,
  987654321', 4)
• INSERT INTO EMPLOYEE (FNAME,LNAME,SSN,DNO)
  VALUES (Richard', Marini', 652349822', 4)

16
Data Manipulation in SQL (contd.)

• INSERT command.
• CREATE TABLE DEPT_INFO (DEPT_NAME
  VARCHAR(15), NO_OF_EMP INTEGER,
  TOTAL_SALARY INTEGER)
• INSERT INTO DEPT_INFO (DEPT_NAME,NO_OF_EMP,TOTAL_
  SALARY) SELECT DNAME, COUNT(), SUM(SALARY)
  FROM DEPARTMENT, EMPLOYEE WHERE
  DNUMBER DNO GROUP
  BY DNAME

17
Data Manipulation in SQL (contd.)

• DELETE command.
  DELETE FROM EMPLOYEE WHERE LNAME
  Brown'
• DELETE FROM EMPLOYEE WHERE SSN 123456789'
  
  DELETE FROM EMPLOYEE WHERE
  DNO IN (SELECT DNUMBER FROM
  DEPARTMENT WHERE DNAME Research')

18
Data Manipulation in SQL (contd.)

• UPDATE command. UPDATE PROJECT SET
  PLOCATION Bellaire DNUM 5 WHERE PNUMBER
  10'
• UPDATE EMPLOYEE SET SALARY SALARY
  1.1 WHERE DNO IN (SELECT DNUMBER
  FROM DEPARTMENT WHERE DNAME
  Research')

19
Basic Queries in SQL

• Retrieve the birth date and address of the
  employ(s) whose name is John B. Smith.
  Q0 SELECT BDATE,
  ADDRES FROM EMPLOYEE WHERE FNAME
  John AND MINT B AND LNAMESmith
• Retrieve the name and address of all employees
  who work for the Research department.
  Q1 SELECT FNAME,LNAME, ADDRES FROM
  EMPLOYEE,DEPARTMENT WHERE DNAME Research
  AND DNUMBERDNO

20
Basic Queries in SQL (Cont.)

• For every project located in Stafford,list the
  project number, the department number, and the
  department managers last name,address, and birth
  date.
• Q2
• SELECT PUMBER,DNUM,LNAME,BDATE, ADDRES
  FROM PROJECT,DEPARTMENT,EMPLOYEE WHERE DNUM
  DNUMBER AND MGRSSNSSN AND PLOCATIONStafford
  

21
Basic Queries in SQL (Cont.)

• To prevent ambiguity, rephrase Q1.
• Q1A SELECT FNAME,EMPLOYEE.NAME,
  ADDRES FROM
  EMPLOYEE,DEPARTMENT WHERE
  DEPARTEMNT.NAMEResearch AND
  DEPARTMENT. DNUMBER
  EMPLOYEE.DNUMBER
• For each employee, retrieve the first and last
  name of the employee and his immediate
  supervisor.
• Q8 SELECT E.FNAME,E.LNAME,S.FNA
  ME,S.LNAME FROM EMPLOYEE AS
  E,EMPLOYEE AS S WHERE
  E.SUPERSSNS.SSN

22
Basic Queries in SQL (Cont.)

• To shorten the relation names that prefix the
  attributes.
• Q1B SELECT E.FNAME, E.NAME, E.ADDRES
  FROM EMPLOYEE E, DEPARTMENT D
  WHERE D.NAMEResearch AND
  D.DNUMBER E.DNUMBER

23
Basic Queries in SQL (Cont.)

• Select all EMPLOYEE SSNS(Q9), and all
  combinations of EMPLOYEE SSN and DEPARTMENT
  (Q10).
• Q9 SELECT SSN FROM
  EMPLOYEE
• Q10 SELECT SSN,DNAME
  FROM EMPLOYEE, DEPARTEMNT.

24
Basic Queries in SQL (Cont.)

• Use of Asterisk ()
• Q1C SELECT FROM EMPLOYEE
  WHERE DNO 5
• Q1D SELECT FROM
  EMPLOYEE ,DEPARTMENT WHERE DNAMEResearch
  AND DNO DNUMBER
• Q10A SELECT FROM
  EMPLOYEE, DEPARTMENT

25
Basic Queries in SQL (Cont.)

• Retrieve the salary of every employee (Q11) and
  all distinct salary values (Q11A)
• Q11 SELECT ALL SALARY
  FROM EMPLOYEE
• Q11A SELECT DISTINCT SALARY
  FROM EMPLOYEE

26
Basic Queries in SQL (Cont.)

• Make a list of all project numbers for projects
  that involve an employee whose last name is
  Smith,either as a worker or a manager of the
  department what controls the project.
• Q4 (SELECT DISTINCT PNUMBER
  FROM PROJECT,DEPARTMENT,EMPLOYEE WHERE
  DNUM DNUMBER AND MGRSSNSSN AND
  LNAMESmith)
• UNION (SELECT DISTINCT PNUMBER
  FROM PROJECT, WORKS_ON,EMPLOYEE WHE
  RE PNUMBER PNO AND ESSNSSN AND
  LNAMESmith)

27
Basic Queries in SQL (Cont.)

• Retrieve all employees whose address is in
  Houston, Texas
• Q12 SELECT FNAME,LNAME
  FROM EMPLOYEE WHERE ADDRESS
  LIKE Houston,TX
• Find all employees who were born during the 1950s
  
• Q12A SELECT FNAME,LNAME
  FROM EMPLOYEE WHERE BDATE LIKE
  __5________

28
Basic Queries in SQL (Cont.)

• Show the resulting salaries if every employee
  working on the Product X project is given a 10
  raise.
• Q13 SELECT FNAME,LNAME,1.1SALARY
  FROM
  EMPLOYEE,WORKS_ON,PROJECT WHERE SSNESSN AND
  PNO PNUMBER AND PNAME Product X
• Retrieve all employees in department 5 whose
  salary is between 30,000 and 40,000.
  Q14 SELECT
  FROM EMPLOYEE
  WHERE (SALARY
  BETWEEN 30000 AND 40000) AND DNO 5

29
Basic Queries in SQL (Cont.)

• 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.
  Q15 SELECT DNAME,LNAME,FNAME,PNAME
  FROM DEPARTMENT,
  EMPLOYEE, WORKS_ON, PROJECT
  WHERE DNUMBERDNO AND SSNESSN AND
  PNOPNUMBER ORDER BY DNAME, LNAME,FNAME

30
Basic Queries in SQL

• SELECT statement
• SQL table is a multi-set, not a set -- two or
  more identical tuples are allowed.
• Basic SQL query. SELECT attribute
  list FROM table list WHERE
  condition

31
Basic Queries in SQL (Contd.)

• A general SQL query. SELECT attribute
  list FROM table list WHERE
  condition GROUP BY grouping
  attribute(s) HAVING group condition ORDE
  R BY attribute list

32
Basic Queries in SQL (Cont.)

• Query 0 retrieve the birth date and address of
  the employee whose name is John B. Smith.
• SELECT BDATE, ADDRESS FROM EMPLOYEE
  WHERE FNAMEJohn' AND MINITB
  AND LNAMESmith'
• Query 1 retrieve the name and address of all
  employees who work for the research
  department. SELECT FNAME, LNAME, ADDRESS
  FROM EMPLOYEE, DEPARTMENT WHERE
  DNAMEResearch' AND DNUMBERDNO

33
Basic Queries in SQL (Cont.)

• Query 2 for every project located in
  Stafford',list the project number, the
  controlling department number,and the department
  manager's l name, address, and birth date.
  
• SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE
• FROM PROJECT, EMPLOYEE, DEPARTMENT
• WHERE DNUMDNUMBER AND MGRSSNSSN AND
  PLOCATIONStafford'

34
Basic Queries in SQL (Cont.)

• Query 0A retrieve the name and birth date of all
  dependents of the employee whose name is John B.
  Smith' SELECT
  DEPENDENT_NAME, DEPENDENT.BDATE
  FROM EMPLOYEE, DEPENDENT WHERE
  ESSNSSN AND FNAMEJohn' AND
  MINITB' AND LNAMESmith'
• Dealing with ambiguous attribute names. prefixing
  with table name.

35
Basic Queries in SQL (Cont.)

• Unspecified WHERE clause. equivalent to WHERE
  TRUE. SELECT SSN FROM EMPLOYEE
• Use of . retrieve all attributes. SELECT
  FROM EMPLOYEE WHERE DNO5
• Remove of duplicate tuples. SELECT SALARY
  FROM EMPLOYEE SELECT DISTINCT
  SALARY FROM EMPLOYEE

36
Basic Queries in SQL (Cont.)

• Tables as sets. - SQL incorporated set union
  (UNION), set difference (EXCEPT), and set
  intersection (INTERSECT).
• The results are sets -- duplicate tuples are
  removed.
• Query 4 make a list of all project numbers for
  projects that involve an employee whose l name is
  Smith', either as a worker or as a manager of
  the department that controls the project.

37
Basic Queries in SQL (Cont.)

• (SELECT PNUMBER FROM PROJECT,
  DEPARTMENT, EMPLOYEE WHERE DNUMDNUMBER AND
  MGRSSNSSN AND LNAMESmith')
• UNION (SELECT PNUMBER
  FROM PROJECT, WORKS_ON, EMPLOYEE WHERE
  PNUMBERPNO AND ESSNSSN AND
  LNAMESmith')