SQL: Structured Query Language

1
Lecture 20 (10/27/2005)

• SQL Structured Query Language

2
Quick Revision

• Forms of nested queries so far
• IN
• SOME
• ALL
• Attribute OP (r) where r is a query that selects
  a matching attribute
• What is the difference between a correlated a
  non-correlated query?
• MySQL
• Select now() ? know the date format
• Select 59
• Select 55

3
Practice nested queries

• Sailors (sid, sname, rating, age)
• Boats (bid, bname, color)
• Reserves (sid, bid, date)
• Find the average age of sailors for each rating
  level that has at least two sailors or a rating
  equal to that of Tim Allen s
• SELECT rating, AVG(age)FROM SailorsGROUP BY
  ratingHAVING COUNT() 1 OR rating IN (
  SELECT rating FROM Sailors WHERE sname
  Like Tim Allen )
• What conditions can we specify in the Having VS
  the Where clause?

4
The EXISTS Function

• EXISTS is used to check whether the result of a
  correlated nested query is empty (contains no
  tuples)
• Query 12 Retrieve the name of each employee who
  has a dependent with the same first name as the
  employee
• Do it using an IN?
• SELECT FNAME, LNAMEFROM EMPLOYEEWHERE
  EXISTS (SELECT FROM DEPENDENT
  WHERE SSNESSN AND
  FNAMEDEPENDENT_NAME)

5
The EXISTS Function

• Query 6 Retrieve the names of employees who have
  no dependents
• SELECT FNAME, LNAMEFROM EMPLOYEEWHERE NOT
  EXISTS(SELECT FROM DEPENDENT
  WHERE SSNESSN)
• The correlated nested query retrieves all
  DEPENDENT tuples related to an EMPLOYEE tuple
• ? If none exist , the EMPLOYEE tuple is selected
• EXISTS is necessary for the expressive power of
  SQL
• Using this operator we can implement the division
  operator

6
The EXISTS Function

• Query 3 Retrieve the name of each employee who
  works on all the projects controlled by
  department number 5
• Logic Select all EMPLOYEEs such that there does
  not exist a PROJECT in DEPARTMENT 5 that they do
  not work on (or that there does not exist a
  corresponding WORKS_ON tuple)
• SELECT FNAME, LNAMEFROM EMPLOYEEWHERE NOT
  EXISTS (SELECT FROM PROJECT WHERE
  DNUM5 AND NOT EXISTS (SELECT
  FROM WORKS_ON C
  WHERE SSNC.ESSN AND C.PNOPNUMBER))

7
The EXISTS Function

• Or (Using Not Exists and Except)
• SELECT FNAME, LNAMEFROM EMPLOYEEWHERE NOT
  EXISTS
• ((SELECT PNUMBER FROM PROJECT
  WHERE DNUM5
• EXCEPT (SELECT PNO FROM WORKS_ON
  WHERE SSNESSN))

8
Practice nested queries

• Sailors (sid, sname, rating, age)
• Boats (bid, bname, color)
• Reserves (sid, bid, date)
• Find the sids of sailors who have reserved a red
  boat (Use IN)
• SELECT R.sid FROM Reserves R WHERE R.bid IN
  (SELECT B.bid FROM Boats B WHERE B.color
  red )
• Using Exists
• SELECT R.sid FROM Reserves R WHERE EXISTS
  (SELECT FROM Boats B B.bidR.Bid AND B.Color
  Red)
• Theoretically, which is more efficient?

9
Practice nested queries

• Sailors (sid, sname, rating, age)
• Boats (bid, bname, color)
• Reserves (sid, bid, date)
• Find names of sailors who have reserved a red
  boat (Use IN without JOINS)
• SELECT S.sname FROM Sailors S
  WHERE S.sid IN ( SELECT R.sid FROM
  Reserves R WHERE R.bid IN (SELECT
  B.bid FROM Boats B WHERE
  B.colorred ))
• Find the sailors with the highest rating (Use
  ALL)
• SELECT S.sid FROM Sailors S WHERE S.rating
  ALL (SELECT S2.rating FROM Sailors S2 )

10
Practice nested queries

• Sailors (sid, sname, rating, age)
• Boats (bid, bname, color)
• Reserves (sid, bid, date)
• Find sailors whose rating is better than Johns.
  (Use Any)
• SELECT  S.sid FROM Sailors S WHERE
   S.rating SOME (SELECT S2.rating FROM Sailors
  S2 WHERE S2.sname Like John )
• Find the names of sailors who have reserved both
  a red and a green boat. (Use In)
• SELECT S.sname FROM Sailors S, Boats B, Reserves
  R WHERE S.sidR.sid AND R.bidB.bid AND
  B.colorred AND S.sid IN
• (SELECT S2.sid FROM Sailors S2, Boats B2,
  Rerserves R2
• WHERE S2.sidR2.sid AND R2.bidB2.bid AND
  B2.colorgreen)

11
Practice nested queries

• Sailors (sid, sname, rating, age)
• Boats (bid, bname, color)
• Reserves (sid, bid, date)
• Find the names of sailors who have reserved all
  boats.
• SELECT S.sname FROM Sailors S WHERE NOT EXISTS
  (SELECT B.bid FROM Boats B WHERE NOT
  EXISTS (SELECT R.bid FROM Reserves R
  WHERE R.bidB.bid AND R.sidS.sid))
• Using difference
• SELECT S.sname FROM Sailors S WHERE NOT EXISTS
  ((SELECT B.bid FROM Boats B) EXCEPT
  (SELECT R.bid FROM Reserves R WHERE
  R.sidS.sid))

12
Practice nested queries

• Sailors (sid, sname, rating, age)
• Boats (bid, bname, color)
• Reserves (sid, bid, date)
• Find the average age of sailors for each rating
  level that has at least two sailors.
• SELECT S.rating, AVG (S.age) AS average FROM
  Sailors S GROUP BY S.rating HAVING
  (SELECT COUNT () FROM Sailors S2 WHERE
  S.rating S2.rating) 1
• Find the names of sailors who are older than the
  oldest sailor with a rating of 10.
• SELECT S.sname FROM Sailors S WHERE S.age
  (SELECT MAX (S2.age) FROM Sailors S2 WHERE
  S2.rating 10)
• SELECT S.sname FROM Sailors S WHERE S.age ALL
  (SELECT S2.age FROM Sailors S2 WHERE S2.rating
  10)

13
Joined Relations Feature

• Can specify a "joined relation" in the
  FROM-clause
• Allows the user to specify different join types
• INNER JOIN
• OUTER JOINs (RIGHT, LEFT, FULL)
• Join condition can be
• Natural (all attributes with the same names are
  used to join the two relations)
• Equi (the operator used for joining is most
  popular)
• Regular "theta (any other condition)

14
Joined Relations Feature

• R NATURAL RIGHT/LEFT/FULL OUTER/INNER S ON
  condition
• R JOIN S ON ???
• R INNER JOIN S ON ???
• R RIGHT OUTER JOIN S ON ???
• R LEFT OUTER JOIN S ON ???
• R FULL OUTER JOIN S ON ???
• Add the word NATURAL before the join type ? no
  need to for ON ????

15
Joined Relations Feature

• Select the names of all employees with their
  supervisors
• SELECT E.FNAME, E.LNAME, S.FNAME,
  S.LNAMEFROM EMPLOYEE E, EMPLOYEE
  SWHERE E.SUPERSSNS.SSN
• SELECT E.FNAME, E.LNAME, S.FNAME,
  S.LNAMEFROM (EMPLOYEE E JOIN EMPLOYEES S ON
  E.SUPERSSNS.SSN)

16
Joined Relations Feature

• Select the names and addresses of all employees
  in the Research department
• SELECT FNAME, LNAME, ADDRESSFROM EMPLOYEE,
  DEPARTMENTWHERE DNAME'Research' AND DNUMBERDNO
• SELECT FNAME, LNAME, ADDRESSFROM (EMPLOYEE JOIN
  DEPARTMENT ON DNUMBERDNO)WHERE DNAME'Resea
  rch

17
Joined Relations Feature

• Relation LOAN

L J B L LOJ B L ROJ B L FOJ B L NJ B L NROJ B

• Relation BORROWER

Borrower information missing for L-260 and loan
information missing for L-155
18
Joined Relations Feature

• SELECT FROM (LOAN JOIN BORROWER
  ONLOAN.LOAN-NUMBER BORROWER.LOAN-NUMBER)
• SELECT FROM (LOAN LEFT OUTER JOIN BORROWER
  ONLOAN.LOAN-NUMBER BORROWER.LOAN-NUMBER)

19
Joined Relations Feature

• SELECT FROM (LOAN RIGHT OUT JOIN BORROWER
  ONLOAN.LOAN-NUMBER BORROWER.LOAN-NUMBER)
• SELECT FROM (LOAN FULL OUTER JOIN BORROWER
  ONLOAN.LOAN-NUMBER BORROWER.LOAN-NUMBER)

20
Joined Relations Feature

• SELECT FROM (LOAN NATURAL JOIN BORROWER)

• SELECT FROM (LOAN NATURAL RIGHT OUTER JOIN
  BORROWER)

21
INSERT

• There are three SQL commands to modify the
  database INSERT, DELETE, and UPDATE
• In its simplest form, it is used to add one or
  more tuples to a relation
• Attribute values should be listed in the same
  order as the attributes were specified in the
  CREATE TABLE command
• INSERT INTO EMPLOYEE VALUES ('Richard', 'K',
  'Marini', '653298653', '30-DEC-52', '98 Oak
  Forest,Katy,TX', 'M', 37000, '987654321', 4)

22
INSERT

• An alternate form of INSERT specifies explicitly
  the attribute names that correspond to the values
  in the new tuple
• Attributes with NULL values can be left out
• Example Insert a tuple for a new EMPLOYEE for
  whom we only know the FNAME, LNAME, and SSN
  attributes
• INSERT INTO EMPLOYEE (FNAME, LNAME, SSN) VALUES
  ('Richard', 'Marini', '653298653')

23
INSERT

• Missed values will the specified DEFAULT value
  (or NULL if no default is specified)
• NB Values for NOT NULL attributes must be
  specified
• Another variation of INSERT allows insertion of
  multiple tuples resulting from a query
• Bulk Insert
• Suppose we want to create a table that has the
  name, number of employees, and total salaries for
  each department. A table DEPTS_INFO is created
  and is loaded with the summary information
  retrieved from the database by the query

24
INSERT

• CREATE TABLE DEPTS_INFO (DEPT_NAME VARCHAR(10),
  NO_OF_EMPS INTEGER, TOTAL_SAL INTEGER)IN
  SERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS,
  TOTAL_SAL)(SELECT DNAME, COUNT (), SUM
  (SALARY) FROM DEPARTMENT, EMPLOYEE
  WHERE DNUMBERDNO GROUP BY DNAME )

25
DELETE

• Removes tuples from a relation
• Includes a WHERE-clause to select the tuples to
  be deleted
• Tuples are deleted from only one table at a time
  (unless CASCADE is specified on a referential
  integrity constraint)
• A missing WHERE-clause specifies that all tuples
  in the relation are to be deleted
• ? the table then becomes an empty table
• DELETE FROM EMPLOYEE
• DELETE FROM EMPLOYEEWHERE LNAME'Brown
• DELETE FROM EMPLOYEEWHERE DNO
  IN (SELECT DNUMBER FROM DEPARTMENT
  WHERE DNAME'Research')

26
UPDATE

• Used to modify attribute values of one or more
  selected tuples in a single table
• A WHERE-clause selects the tuples to be modified
• An additional SET-clause specifies the attributes
  to be modified and their new values
• Change the location and controlling department
  number of project number 10 to 'Bellaire' and 5,
  respectively
• UPDATE PROJECTSET PLOCATION 'Bellaire', DNUM
  5WHERE PNUMBER10

27
UPDATE

• Give all employees in the 'Research' department a
  10 raise in salary(using nested queries)
• UPDATE EMPLOYEESET SALARY SALARY
  1.1WHERE DNO IN (SELECT DNUMBER
  FROM DEPARTMENT WHERE DNAME'Research')
• In this request, the modified SALARY value
  depends on the original SALARY value in each
  tuple
• The reference to the SALARY attribute on the
  right of refers to the old SALARY value before
  modification
• The reference to the SALARY attribute on the left
  of refers to the new SALARY value after
  modification

28
Constraints as Assertions - SKIP

• General constraints constraints that do not fit
  in the basic SQL categories
• Domain-level
• Attribute-level
• Tuple-level
• Mechanism CREATE ASSERTION
• components include a constraint name, followed
  by CHECK, followed by a condition which must be
  true on every database state
• The name can be used later on to drop or alter
  the assertion

29
Assertions An Example - SKIP

• The salary of an employee must not be greater
  than the salary of the manager of the department
  that the employee works for
• CREAT ASSERTION SALARY_CONSTRAINTCHECK (NOT
  EXISTS(SELECT FROM EMPLOYEE E, EMPLOYEE M,
  DEPARTMENT DWHERE E.SALARY M.SALARY AND
  E.DNOD.NUMBER AND D.MGRSSNM.SSN ))
• Specify a query that violates the condition
  include inside a NOT EXISTS clause
• ? Query result must be empty for assertion to be
  valid
• if the query result is not empty, the assertion
  has been violated

30
Various CHECK Constraints - SKIP

• Most constraints are specified using the CHECK
  clause
• Domain-level CREATE DOMAIN
• Attribute-level On the same line where an
  attributes definition is provided within CREATE
  TABLE
• Involves only one attribute
• Tuple-level After defining the attributes in the
  CREATE TABLE
• Involves more than one attribute from the same
  table
• Table-level Through assertions
• Involves attributes from different tables
• The first three are checked ONLY when tuples are
  inserted or updated. Assertions are always true

31
SQL Triggers - SKIP

• Objective to monitor a database and take action
  when a condition occurs
• Triggers are expressed in a syntax similar to
  assertions and include the following
• event (e.g., an update operation)
• condition
• action (to be taken when the condition is
  satisfied)

32
Views in SQL

• Reflect the different external schema(s)
• A view is a virtual table that is derived from
  other tables referred to as the defining tables
  of the view
• Virtual VS base tables which are physically
  stored on disk
• Allows full query operations (SELECT operations)
• No difference between a table and a view
• Allows for a limited update operations (since
  most of the time, the view table is not
  physically stored)
• Visible differences between tables and views

33
Advantages of Views

• Simplify the specification of some frequently
  posed queries
• A view is similar in many ways to a subroutine in
  standard programming
• Can be used in multiple queries
• Access Control and security Give groups of users
  access to just the information they are allowed
  to see
• E.g. Registrar and Business office need student
  information but the former cares about grades and
  GPAs while the latter about balances owed
• View allows owner to provide SELECT access to a
  subset of columns
• Customization Users need not see full complexity
  of database
• View creates the illusion of a simpler database
  customized to the needs of a particular category
  of users

34
Specification of Views

• SQL command CREATE VIEW view_name AS
• a query to specify the table contents
• Specify a WORKS_ON1 view that shows, for every
  project, the full name of every employee along
  with the number of hours they work on the project
• CREATE VIEW WORKS_ON1 ASSELECT FNAME,LNAME,PNAME
  ,HOURSFROM EMPLOYEE,PROJECT,WORKS_ONWHERE
  SSNESSN AND PNOPNUMBERORDER BY PNAME

35
SQL Views An Example

• Specify a view that shows, for every department,
  the name of the department and the total number
  of employees working in that department along
  with the sum of their salaries
• CREATE VIEW DEPT_INFO ASSELECT DNAME,COUNT()
  AS NO_OF_EMPS, SUM(SALARY)AS TOTAL_SALFROM
  EMPLOYEE,DEPARTMENTWHERE DNODNUMBER GROUP
  BY DNUMBER, DNAME

36
SQL Views An Example
37
Using Views

• Specify any SQL query on a newly created view
• SELECT FNAME, LNAME FROM WORKS_ON1
• WHERE PNAMESeena
• Much simpler than original query
• Limits information to given attributes only
  (security)
• When no longer needed, a view can be dropped
• DROP WORKS_ON1

38
View Implementation

• Views must always be up-to-date two ways to
  achieve this
• (1) Query modification whenever a query is posed
  on a view, present the view query in terms of a
  query defined on the defining base tables
• SELECT FNAME, LNAME FROM WORKS_ON1
• WHERE PNAMESeena
• ?SELECT FNAME, LNAMEFROM EMPLOYEE, PROJECT,
  WORKS_ONWHERE SSNESSN AND PNOPNUMBER AND
  PNAMESeena

39
View Implementation

• Steps in mapping the view query to a query on the
  defining tables
• SELECT clause Get the projection attribute list
  from the query and map each attribute into the
  original attribute in the defining tables
• FROM clause Get the tables form the FROM clause
  in the view definition and append to them all
  tables that appear in the FROM clause of the view
  query
• WHERE clause Get the conditions specified in the
  WHERE clause of the view definition and add to
  them (using ANDs) the conditions that appear in
  the WHERE clause of the view query
• Disadvantage inefficient for views defined via
  complex queries especially if the view is
  accessed frequently

40
View Implementation

• (2) View materialization involves physically
  creating and keeping a temporary table containing
  the data of the view
• Assumption view is frequently accessed
• Disadvantages maintaining consistency between
  the base table and the view when the base table
  is updated
• Must update the corresponding view table
• incremental update
• Determines when tuples should be inserted to,
  delete from or update in the view table when any
  of the defining tables are modified

41
View Updates

• A view is updateable if the update can be mapped
  into a single update on the defining relations
• Has no aggregate operations (NEXT SLIDE)
• Is defined on single tables and contains primary
  key of the base relation and all NOT NULL
  attributes with no Defaults
• E.g. View defined on the DEPARTMENT table that
  includes DNAME, DNUMBER

42
View Updates

• Views defined using groups and aggregate
  functions are not updateable
• Why?
• CREATE VIEW DEPT_INFO ASSELECT DNAME,COUNT() AS
  NO_OF_EMPS, SUM(SALARY)AS TOTAL_SALFROM
  EMPLOYEE,DEPARTMENTWHERE DNODNUMBER GROUP
  BY DNUMBER, DNAME
• UPDATE DEPT_INFO SET NO_OF_EMPS 500 WHERE
  DNAMERESEARCH
• What if we update DNAME?

43
View Updates

• Views defined on multiple tables using joins are
  generally not updateable
• Why?
• CREATE VIEW WORKS_ON1 ASSELECT FNAME,LNAME,PNAME,
  HOURSFROM EMPLOYEE,PROJECT,WORKS_ONWHERE
  SSNESSN AND PNOPNUMBERORDER BY PNAME
• UPDATE WORKS_ON1 SET HOURS 200
  WHERE PNAMEPROJECT X
• Need PNO and ESSN
• Same if we update FNAME? LNAME? PNAME?

44
View Updates - SKIP

• A view for users wishing to view the profiles of
  other employees
• CREATE VIEW EMP_INFO ASSELECT SSN, FNAME,
  LNAME, BDATE, ADDRES, DNO FROM EMPLOYEE
• In SQL, WITH CHECK OPTION clause must be added to
  the end of the definition of a view if the view
  is to be updated
• to allow check for updatability and to plan for
  an execution strategy
• CREATE VIEW EMP_INFO ASSELECT SSN, FNAME,
  LNAME, BDATE, ADDRES, DNO FROM
  EMPLOYEEWITH CHECK OPTION