SQL the relational database standard

1
SQL - the relational database standard

• Data definition
• Basic queries
• Views
• Additional features

Dr. Reuven Bakalash
2
Data definition, constraints, and schema changes

• Creating a schema
• CREATE SCHEMA COMPANY AUTHORIZATION JSMITH
• A catalog contains INFORMATION_SCHEMA, which
  provides information on all the element
  descriptors of all the schemas in the catalog to
  authorized users.
• Creating a table ( see Fig. 8.1(a))
• CREATE TABLE EMPLOYEE or CREATE TABLE
  COMPANY.EMPLOYEE
• Data types
• Numeric (INT, SMALLINT), (FLOAT, REAL, DOUBLE
  PRECISION), formatted numbers (DECIMAL(i,j))
• Character string (CHAR(n), VARCHAR(n))
• Bit string (BIT(n), VARBIT(n))
• DATE in the form YYYY-MM-DD
• TIME in the form HHMMSS
• Timestamp (TIMESTAMP) includes DATE, TIME 6
  positions for fraction of seconds, and an
  optional WITH TIME ZONE qualifier
• Interval (INTERVAL) is a relative value to
  increment or decrement time. Intervals are
  qualified either as YEAR/MONTH or DAY/TIME
  intervals.

Dr. Reuven Bakalash
3

• Domains
• CREATE DOMAIN SSN_TYPE AS CHAR(9)
• Then SSN_TYPE can be used for the attributes
  SSN, SUPERSSN, MGRSSN, and ESSN.
• Constraints and default values (Fig. 8.1a,b)
• NOT NULL when NULL is not permitted
• Default values can be defined
• Following attribute specs table constraints can
  be specified
• Referential triggered action specifying the
  action taken if referential integrity constraint
  is violated. The options are SET NULL, CASCADE,
  SET DEFAULT (Fig. 8.1b). The action for CASCADE
  ON DELETE is to delete all the referencing
  tuples, whereas for CASCADE ON UPDATE is to
  change the foreign key to the updated primary key
  value for all referencing tuples. In general the
  CASCADE is suitable for relationship tables
  (WORKS_ON), and weak entity tables (DEPENDENT).
• Constraints may be given names, in case they must
  be dropped or replaced later.

Dr. Reuven Bakalash
4

• The DROP SCHEMA and DROP TABLE commands
• There are two options CASCADE and RESTRICT.
• DROP SCHEMA COMPANY CASCADE
• Removes the database schema and all its tables,
  domains, and other elements.
• If the RESTRICT option is chosen the schema is
  dropped only if it has no elements in it
  otherwise it is not executed.
• DROP TABLE DEPENDENT CASCADE
• All constraints and views that reference the
  table are dropped automatically from the schema,
  along with the table itself.
• If the RESTRICT option is chosen the table is
  dropped only if it is not referenced in any
  constraints (e.g. by foreign key in another
  table) or views.

Dr. Reuven Bakalash
5

• The ALTER TABLE command
• A schema evolution command adding or dropping a
  column, changing a column definition, and adding
  or dropping table constraints.
• ALTER TABLE COMPANY.EMPLOYEE ADD JOB
  VARCHAR(12)
• If no default is specified, the new attribute
  will have NULLs in all the tuples. To drop a
  column CASCADE or RESTRICT must be chosen.
• ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS
  CASCADE
• Column definition can be altered by dropping
  existing default or by defining a new default
• ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN
  DROP DEFAULT
• ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN SET
  DEFAULT 333445555
• Constraints can be added or dropped
• ALTER TABLE COMPANY.EMPLOYEE
• DROP CONSTRAINT EMPSUPERFK CASCADE

Dr. Reuven Bakalash
6
Basic queries in SQL

• The SELECT-FROM-WHERE structure
• SELECT ltattribute listgt
• FROM lttable listgt
• WHERE ltconditiongt
• Query 0
• Retrieve the birth date and address of the
  employees whose name is John B. Smith
• Q0 SELECT BDATE, ADDRESS
• FROM EMPLOYEE
• WHERE FNAMEJohn AND MINITB AND
  LNAMESmith
• It is similar to the following relational algebra
  expression except that duplicates, if any,
  would not be eliminated
• pBDATE,ADDRESS(sFNAMEJohn AND MINITB AND
  LNAMESmith (EMPLOYEE))

Dr. Reuven Bakalash
7
Query 1 Retrieve the name and address of all
employees who work for the Research
department. Q1 SELECT FNAME, LNAME,
ADDRESS FROM EMPLOYEE, DEPARTMENT
WHERE DNAMEResearch AND DNUMBERDNO This
query is similar to a SELECT-PROJECT-JOIN
sequence. DNAMEResearch is a selection
condition, and DNUMBERDNO is a join
condition. Query 2 For every project located in
Stafford, list the project number, the
controlling department number, and the department
managers last name, address, and birth date. Q2
SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE
FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM
DNUMBER AND MGRSSNSSN AND PLOCATIONStafford

Dr. Reuven Bakalash
8

• Ambiguous attribute names and renaming (aliasing)
• In case that in two separate tables attributes
  have the same name, we have to specify the
  attributes table, or rename the attributes.
• Lets suppose that the DNO and LNAME of the
  EMPLOYEE where called DNUMBER and NAME, and the
  DNAME attribute of DEPARTMENT was also called
  NAME
• Q1ASELECT FNAME, EMPLOYEE.NAME, ADDRESS
• FROM EMPLOYEE, DEPARTMENT
• WHERE DEPARTMENT.NAMEResearch AND
  DEPARTMENT.NUMBER EMPLOYEE.DNUMBER
• Ambiguity arises also in the case of queries that
  refer to the same table twice
• QUERY 8
• For each employee, retrieve the employees first
  and last name and the first and last name of his
  immediate supervisor.
• Q8 SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
• FROM EMPLOYEE AS E, EMPLOYEE AS S
• WHERE E.SUPERSSNS.SSN
• (See the result in 8.2d). E, S are called
  aliases. It is possible to rename attributes as
  well in the FROM clause
• EMPLOYEE AS E(FN,MI,LN,SSN,BD,ADDR,SEX,SAL,SSSN,D
  NO)

Dr. Reuven Bakalash
9

• Unspecified WHERE-clause and use of asterisk ()
• A missing WHERE clause indicates no condition on
  tuple selection.
• Query 9. Select all EMPLOYEE SSNs in the
  database.
• Q9 SELECT SSN
• FROM EMPLOYEE
• If more than one table is specified then the
  CROSS PRODUCT of these relations is selected (all
  possible tuple combinations)
• Query 10. Select all combinations of EMPLOYEE SSN
  and DEPARTMENT DNAME in the database.
• Q10 SELECT SSN, DNAME
• FROM EMPLOYEE, DEPARTMENT
• Asterisk () stands for all the attributes. E.g.
  retrieving all the attributes of tuples who work
  in department number 5
• Query 1c SELECT
• FROM EMPLOYEE
• WHERE DNO5
• Two additional examples
• Query 1d SELECT
• FROM EMPLOYEE, DEPARTMENT
• WHERE DNAMEResearch AND DNODNUMBER
• Query 10a SELECT

Dr. Reuven Bakalash
10

• Tables as sets
• SQL treats a table not as a set but rather as a
  multiset. Duplicate tuples can appear more than
  once in a table. SQL does not eliminate
  duplicates for the following reasons
• Duplicate elimination is an expensive operation
  (eliminate by sorting).
• The user may want to see duplicate tuples in the
  result query.
• When aggregate function is applied we do not want
  to eliminate duplicates.
• SELECT ALL (equivalent to SELECT) does not
  eliminates duplicates, whereas SELECT DISTINCT
  eliminates duplicates.
• Query 11
• Retrieve the salary of every employee (Q11) and
  all distinct salary values (Q11a).
• Query 11 SELECT ALL SALARY
• FROM EMPLOYEE
• Query 11a SELECT DISTINCT SALARY
• FROM EMPLOYEE
• Results SALARY SALARY
• 30000 30000
• 40000 40000
• 25000 25000
• 43000 43000
• 38000 38000
• 25000 55000

Dr. Reuven Bakalash
11
SQL set operations UNION, EXCEPT (set
difference), and INTERSECT.The result is set of
tuples, that is, duplicate tuples are eliminated
from the result. The two relations on which we
apply the operation have the same attributes, and
the attributes appear in the same order in both
relations. Use of UNION Query 4 Make a list of
all project numbers for projects that involve an
employee whose last name is Smith, either as a
worker or as a manager of the department that
controls the project. Q4 (SELECT
DISTINCT PNUMBER FROM PROJECT, DEPARTMENT,
EMPLOYEE WHERE DNUMDNUMBER AND MGRSSSSSN AND
LNAMESmith) UNION (SELECT
DISTINCT PNUMBER FROM PROJECT, WORKS_ON,
EMPLOYEE WHERE PNUMBERPNO AND ESSNSSN AND
LNAMESmith) The first SELECT retrieves
Smith as a manager, the second as a worker.
UNION gives the desired result.
Dr. Reuven Bakalash
12

• Substring comparisons, arithmetic operators, and
  ordering
• LIKE comparison operator allows comparison of
  only part of a character string.
• Query 12
• Retrieve all employees whose address is in
  Houston, Texas.
• Q12 SELECT FNAME, LNAME
• FROM EMPLOYEE
• WHERE ADDRESS LIKE Houston, TX
• To retrieve all employees who were born during
  the 1950s
• Q12a SELECT FNAME, LNAME
• FROM EMPLOYEE
• WHERE BDATE LIKE __5_______
• Use arithmetic in queries, applying standard
  operators ,-,,/.
• Query 13.
• Show the resulting salaries if every employee
  working on the ProductX project is given a 10
  raise.
• Q13 SELECT FNAME, LNAME, 1.1SALARY
• FROM EMPLOYEE, WORKS_ON, PROJECT
• WHERE SSNESSN AND PNOPNUMBER AND
  PNAMEProductX

13

• BETWEEN is used for interval
• Query 14
• 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
  DNO5
• ORDER BY allows to order the tuples in the
  result
• Q15 SELECT DNAME, LNAME, FNAME, PNAME
• FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT
• WHERE DNUMBERDNO AND SSNESSN AND PNOPNUMBER
• ORDER BY DNAME, LNAME, FNAME
• The default is in ascending order of values.
  Descending can be done by
• ORDER BY DNAME DESC, LNAME ASC, FNAME ASC

Dr. Reuven Bakalash
14

• Nested queries
• Nested query, outer query
• Query 4A. Make a list of all project numbers for
  projects that involve an employee whose last name
  is Smith, either as a worker or as a manager of
  the department that controls the project.
• Q4A SELECT DISTINCT PNUMBER
• FROM PROJECT
• WHERE PNUMBER IN (SELECT PNUMBER
• FROM PROJECT, DEPARTMENT, EMPLOYEE
• WHERE DNUMDNUMBER AND MGRSSNSSN AND
  LNAMESmith)
• OR
• PNUMBER IN (SELECT PNO
• FROM WORKS_ON, EMPLOYEE
• WHERE ESSNSSN AND LNAMESmith)
• The first nested query selects projects where
  Smith is a manager, while the second selects
  projects where Smith is a worker.
• The IN operator compares a value v with a set of
  values V. TRUE if v is included in V. Another
  example using IN

Dr. Reuven Bakalash
15
To select the SSN of all employees who work the
same (project, hours) combination on some project
that John Smith (whose SSN is 123456789) works
on. SELECT DISTINCT ESSN FROM WORKS_ON WHERE
(PNO, HOURS) IN (SELECT PNO, HOURS FROM WORK
S_ON WHERE ESSN123456789) In
addition to IN there are other comparison
operators. ANY (or SOME) return TRUE if the
value v is equal to some value in the set V and
is hence equivalent to IN. Other operators
combined with ANY (or SOME) are gt,gt,lt,lt, and
ltgt. ALL can also be combined with the above
operators. Example Retrieve the names of all
employees whose salary is greater than the salary
of all the employees in dept. 5 SELECT LNAME,
FNAME FROM EMPLOYEE WHERE SALARY gt ALL (SELECT
SALARY FROM EMPLOYEE WHERE DNO5)
16

• Aggregate functions and Grouping
• COUNT, SUM, MAX, MIN, AVG built-in functions.
• Query 19. Find the sum of the salaries of all
  employees, the maximum salary, the minimum
  salary, and the average salary.
• Q19 SELECT SUM (SALARY), MAX (SALARY0, MIN
  (SALARY), AVG (SALARY)
• FROM EMPLOYEE
• Query 20. Find the sum of the salaries of all
  employees of the Research department, as well
  as the maximum salary, the minimum salary, and
  the average salary in this department.
• Q20 SELECT SUM (SALARY), MAX (SALARY0, MIN
  (SALARY), AVG (SALARY)
• FROM EMPLOYEE, DEPARTMENT
• WHERE DNODNUMBER AND DNAMEResearch
• Queries 21 and 22. Retrieve the total number of
  employees in the company (Q21) and the number of
  employees in the Research department (Q22).
• Q21 SELECT COUNT ()
• FROM EMPLOYEE
• Asterisk () refers to rows. We can count values
  in a column, rather than tuples by COUNT(SALARY)
• Q22 SELECT COUNT ()
• FROM EMPLOYEE, DEPARTMENT
• WHERE DNODNUMBER AND DNAMEResearch

Dr. Reuven Bakalash
17
COUNT in a column Query 23. Count the number of
distinct salary values in the database. Q23 SELEC
T COUNT (DISTINCT SALARY) FROM EMPLOYEE The
case of COUNT(SALARY) will give the same result
as COUNT () because duplicates will not be
eliminated. Query 5. Retrieve names of employees
that have two or more dependents. Lets compare
with the same query in relational algebra. Q5
T1(SSN, NO_OF_DEPTS) ? ESSN F COUNT
DEPENDENT_NAME (DEPENDENT) T2 ? sNO_OF_DEPTS 2
(T1) RESULT ? pESSN, PNO (T2EMPLOYEE) In SQL
Q5 SELECT LNAME, FNAME FROM EMPLOYEE WHERE (
SELECT COUNT () FROM DEPENDENT WHERE SSNESSN
) gt 2
Dr. Reuven Bakalash
18
Grouping tuples according to the same values of
some attributes. Query 24. For each department,
retrieve the department number, the number of
employees in the department, and their average
salary (for result see Fig. 8.4a). Q24 SELECT DNO
, COUNT(), AVG(SALARY) FROM EMPLOYEE GROUP
BY DNO Using a join condition with conjunction
with GROUP BY. Q25 SELECT PNUMBER, PNAME,
COUNT() FROM PROJECT, WORKS_ON WHERE PNUMBERPN
O GROUP BY PNUMBER, PNAME In order to retrieve
the values of these functions only for groups
that satisfy certain conditions, we use
HAVING. Q26 SELECT PNUMBER, PNAME,
COUNT() FROM PROJECT, WORKS_ON WHERE PNUMBERPN
O GROUP BY PNUMBER, PNAME HAVING COUNT() gt
2 See Fig. 8.4b. WHERE SSNESSN)
gt 2
Dr. Reuven Bakalash
19
Query 27. For each project, retrieve the project
number, the project name, and the number of
employees from department 5 who work on the
project. Q27 SELECT PNUMBER, PNAME,
COUNT() FROM PROJECT, WORKS_ON,
EMPLOYEE WHERE PNUMBERPNO AND SSNESSN AND
DNO5 GROUP BY PNUMBER, PNAME Here we restrict
the tuples in the relation (and hence in each
group) to those that satisfy the condition
specified in the WHERE-clause (work in dept.
5). Query 28. For each department that has more
than five employees, retrieve the department
number and the number of its employees who are
making more than 40,000. SELECT DNAME,
COUNT() FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER
DNO AND SALARYgt40000 GROUP BY DNAME HAVING COUN
T() gt 5 Watch out! The rule is that the WHERE
clause is executed first, the HAVING is applied
later. Is the above correct? SELECT DNUMBER,
COUNT() FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER
DNO AND SALARYgt40000 AND DNO IN (SELECT DNO
FROM EMPLOYEE GROUP BY DNO HAVING COU
NT() gt 5) GROUP BY DNUMBER
20

• Insert, Delete and Update
• Insert
• The values should be listed in the same order of
  CREATE TABLE.
• The simplest form is used to add a single tuple.
• U1 INSERT INTO EMPLOYEE
• VALUES (Richard, K, Marini, 987654321,
  1974-3-27, 98 Oak Forest, Katy, TX, M,
  37000, 987987321, 4)
• The second form allows to insert only few
  attributes (all attributes with NOT NULL specs
  and no default value). Attributes not specified
  are set to default or to null.
• U1A INSERT INTO EMPLOYEE (FNAME, LNAME, SSN,
  DNO)
• VALUES (Richard, Marini, 987654321, 4)

Dr. Reuven Bakalash
21
Delete The following commands, when applied
independently, will delete zero, one, four, and
all tuples, respectively, from the EMPLOYEE
relation (Fig. 7.6). U4A DELETE
FROM EMPLOYEE WHERE LNAMEBrown U4B DELETE
FROM EMPLOYEE WHERE SSN123456789 U4C DELETE
FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT WHERE DNAMEResearch) U4D
DELETE FROM EMPLOYEE
Dr. Reuven Bakalash
22
Update Modifies attribute values of one or more
selected tuples. An example changing the
location and controlling department number of
project 10 to Bellaire and 5, respectively.
U5 UPDATE PROJECT SET PLOCATION Bellaire,
DNUM 5 WHERE PNUMBER 10 Several tuples
can be modified with a single update. An example
10 raise in salary to all employees of the
Research department. U6 UPDATE EMPLOYEE SET
SALARY SALARY1.1 WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT WHERE DNAMEResearch) It
is possible to specify NULL or DEFAULT as the
new attribute value. To modify multiple relations
must issue several UPDATE commands.
Dr. Reuven Bakalash
23

• Views
• A view is a single table that is derived from
  other tables. These other tables could be based
  tables or previously defined views. A view is
  considered a virtual table.
• E.g. For queries with the employee name and
  project name instead of specifying every time the
  join of EMPLOYEE, WORKS_ON and PROJECT tables, we
  define a view which is a result of these joins.
  The above tables are called defining tables of a
  view.
• V1 CREATE VIEW WORKS_ON1
• AS SELECT FNAME, LNAME, PNAME, HOURS
• FROM EMPLOYEE, PROJECT, WORKS_ON
• WHERE SSNESSN AND PNOPNUMBER
• A query on a view
• QV1 SELECT FNAME, LNAME
• FROM WORKS_ON1
• WHERE PNAMEProjectX
• V1 CREATE VIEW DEPT_INFO(DEPT_NAME, NO_OF_EMPS,
  TOTAL_SAL)
• AS SELECT DNAME, COUNT(), SUM(SALARY)
• FROM DEPARTMENT, EMPLOYEE
• WHERE DNUMBERDNO

DEPT_INFO
DEPT_NAME NO_OF_EMPS TOTAL_SAL
Dr. Reuven Bakalash
24

• To dispose of a view
• V1A DROP VIEW WORKS_ON1
• A view is not realized at the time it is defined
  but rather at the time we specify a query on the
  view. Therefore the view automatically reflects
  all the changes in the base tables. A view is
  always up to date.
• View Implementation
• Two approaches to implementing a view for
  querying
• Query modification modifies the view query into a
  query on the underlying tables. It is inefficient
  for complex and multiple queries.
• View materialization physically creates a view
  table when the view is first queried, and keeps
  the table for the following queries. Must have an
  automatic update of the view table when the
  underlying tables are modified (mostly
  incremental update). If the view is not queried
  for a certain period of time, the system removes
  the table, and recompute it from scratch when
  queried again.

Dr. Reuven Bakalash
25

• View update
• Update is complicated and can be ambiguous.
• Update on a view defined on a single table
  (without aggregate functions) can be mapped on
  the underlying base table.
• For a view involving joins, the mapping can be
  done in multiple ways, most likely causing
  ambiguity.
• E.g. In the WORKS_ON view update the PNAME
  attribute of John Smith from ProductX to
  ProductY.
• UV1 UPDATE WORKS_ON1
• SET PNAMEProductY
• WHERE LNAMESmith AND FNAMEJohn AND
  PNAMEProductX
• Consider two possible updates
• (a) UPDATE WORKS_ON
• SET PNO (SELECT PNUMBER FROM PROJECT WHERE
  PNAMEProductY)
• WHERE ESSN IN (SELECT SSN FROM EMPLOYEE WHERE
  LNAMESmith AND FNAMEJohn)
• AND
• PNO IN (SELECT PNUMBER FROM PROJECT WHERE
  PNAMEProductX)
• (b) UPDATE PROJECT
• SET PNAMEProductY
• WHERE PNAMEProductX
• The (b) interpretation accomplishes as well the
  desired view update, however it doesnt reflect
  the users intention.

Dr. Reuven Bakalash
26

• Some view updates do not make sense, for example
• UV2 UPDATE DEPT_INFO
• SET TOTAL_SAL100000
• WHERE DNAMEResearch
• A large number of different updates on the
  underlying base tables can satisfy this view
  update.
• In summary
• A view with a single defining table is
  updateable if the view attributes contain the
  primary key of the basis relation because this
  maps each virtual view tuple to a single base
  tuple
• Views defined on multiple tables using joins are
  generally no updateable
• Views defined using grouping and aggregate
  functions are not updateable
• Adding WITH CHECK OPTION clause at the end of the
  view definition (if the view is to be updated)
  allows the system for updateability and to plan a
  strategy for view updates.

Dr. Reuven Bakalash
27

• Additional features of SQL
• Specifying general constrains by declarative
  assertions
• The CREATE ASSERTION statement
• CREATE ASSERTION SALARY_CONSTRAINT
• CHECK (NOT EXISTS (SELECT FROM EMPLOYEE E,
  EMPLOYEE M, DEPARTMENT D
• WHERE E.SALARYgtM.SALARY AND E.DNOD.DNUMBER
  AND D.MGRSSNM.SSN) )
• The DBMS is responsible for ensuring that the
  condition is not violated.
• The CHECK clause and constraint condition can
  also be used in conjunction with the CREATE
  DOMAIN statement
• CREATE DOMAIN D_NUM AS INTEGER
• CHECK (D_NUM gt 0 AND D_NUM lt 21)
• Granting and revoking of privileges to users
• Each table is assigned an owner, and either the
  owner or the DBA can grant to use statements such
  as SELECT, INSERT, DELETE, or UPDATE.
• In addition, the DBA can grant the privileges to
  create schema, tables, or views to user.
• The commands are GRANT and REVOKE.

Dr. Reuven Bakalash
28

• Embedding SQL statements in programming
  languages
• These are language bindings to various languages
  such as C, C, COBOL, or PASCAL.SQL.
• Transaction control commands
• These are functionalities for concurrency control
  and recovery control. Such as shared (read)and
  exclusive (write) locks, dealing with a deadlock
  problem by timeouts and deadlock detection for
  concurrency. UNDO and REDO recovery operations,
  checkpointing, caching, in-place updating and
  shadowing controls.
• Storage Definition Language commands
• These are set of commands for specifying physical
  database design parameters, file structures for
  tables, and access paths such as indexes.

Dr. Reuven Bakalash