Download - DatabaseIM ISU1 Fundamentals of Database Systems Chapter 8 SQL99 - The Relational Database Standard
IM ISU 1Database
Fundamentals of
Database Systems
Chapter 8
SQL99 - The Relational Database Standard
IM ISU 2Database
Data Definition in SQL Schema and Catalog Concepts in SQL2
An SQL schema is identified by » A schema name» An authorization identifier to indicate the user or
account who owns the schema » Descriptors for each element in the schema
Schema elements include the tables, constraints, views, domains, and other constructs (such as authorization grants) that describe the schema
IM ISU 3Database
Data Definition in SQL (cont.) A schema is created via the CREATE
SCHEMA statement» Example
Create a schema called COMPANY, owned by the user with authorization identifier JSMITH:
CREATE SCHEMA COMPANY AUTHORIZATION JSMITH;
IM ISU 4Database
Data Definition in SQL (cont.) Catalog
» A named collection of schemas in an SQL environment
» A catalog always contains a special schema called INFORMATION_SCHEMA, which provides information on all the element descriptors of all the schemas in the catalog to authorized users
IM ISU 5Database
Data Definition in SQL (cont.) Purpose
Used to CREATE, DROP, and ALTER the descriptions of the tables (relations) of a database
CREATE TABLE Specifies a new base relation by giving it a
name, and specifying each of its attributes and their data types
IM ISU 6Database
Data Definition in SQL (cont.) Data types in SQL2
» Numeric– INT or INTEGER– SMALLINT– FLOAT– REAL– DOUBLE PRECISION– DEC(i, j) or DECIMAL(i,j) or NUMERIC(i,j)
» Character-string– CHAR(n) or CHARACTER(n)
– VARCHAR(n) or CHAR VARYING(n) or CHARACTER VARYING(n)
IM ISU 7Database
Data Definition in SQL (cont.)» Bit-string
– BIT(n)
– BIT VARYING(n)
» Date – In the form YYYY-MM-DD
» Time – In the form HH:MM:SS
» TIME(i)– Made up of hour:minute:second plus i additional digits specifying fractions of a second– format is hh:mm:ss:ii...i
IM ISU 8Database
Data Definition in SQL (cont.)» TIMESTAMP
– Has both DATE and TIME components
» INTERVAL– Specifies a relative value rather than an absolute
value
– Can be DAY/TIME or YEAR/MONTH intervals
– Can be positive or negative
– when added to or subtracted from an absolute value, the result is an absolute value
IM ISU 9Database
Data Definition in SQL (cont.) In SQL2, it is possible to specify the domain
of each attribute and declare the domain name, e.g.,
CREATE DOMAIN SSN_TYPE AS CHAR(9);
» We can use SSN_TYPE in place of CHAR(9)
IM ISU 10Database
Data Definition in SQL (cont.) A constraint NOT NULL may be specified on an attribute
CREATE TABLE DEPARTMENT( DNAME VARCHAR(10) NOT NULL,
DNUMBER INTEGER NOT NULL,MGRSSN CHAR(9),MGRSTARTDATE CHAR(9) );
In SQL2, the CREATE TABLE can be used to specify the primary key, secondary keys, and referential integrity constraints (foreign keys)
IM ISU 11Database
Data Definition in SQL (cont.)» Primary key attributes can be specified via the
PRIMARY KEY » Key attributes can be specified via UNIQUE» Referential integrity is specified via the
FOREIGN KEY clauseCREATE TABLE DEPT(DNAME VARCHAR(10) NOT NULL,DNUMBER INTEGER NOT NULL,MGRSSN CHAR(9),MGRSTARTDATE CHAR(9),PRIMARY KEY (DNUMBER),UNIQUE (DNAME),FOREIGN KEY (MGRSSN) REFERENCES EMP);
IM ISU 12Database
Data Definition in SQL (cont.) It is also possible to define a default value for
an attribute by appending the clause DEFAULT <value> to an attribute definition
IM ISU 14Database
Data Definition in SQL (cont.) In SQL2, action can be specified if a referenti
al integrity constraint is violated upon deletion of a referenced tuple or upon modification of a referenced primary key » Attaching a referential triggered action clause to
any foreign key constraint » The options include SET NULL, CASCADE, an
d SET DEFAULT» An option must be qualified with either ON DEL
ETE or ON UPDATE
IM ISU 15Database
Data Definition in SQL (cont.)» SET NULL (DEFAULT) ON DELETE: the valu
e of the deleted referencing attributes is changed to NULL or the specified default value
» SET NULL (DEFAULT) ON UPDATE: the value of the updated referencing attributes is changed to NULL or the specified default value
» CASCADE ON DELETE: delete all the referencing tuples
» CASCADE ON UPDATE: change the value of the foreign key to the updated (new) primary key value for all referencing tuples
IM ISU 16Database
Data Definition in SQL (cont.)» A constraint may be given a name, following the
keyword CONSTRAINT» The names of all constraints within a particular
schema must be unique
IM ISU 17Database
Data Definition in SQL (cont.) Base tables (or base relations)
» The relations declared through CREATE TABLE statements are called
» The relation and its tuples are actually created and stored as a file by the DBMS
Virtual relations» The relations created through the CREATE VIE
W statement » The relations may or may not correspond to an ac
tual physical file
IM ISU 18Database
Data Definition in SQL (cont.) DROP SCHEMA
Used to a whole schema There are two drop behavior options
» CASCADE: remove the database schema and all its tables, domains, and other elements, e.g.,
DROP SCHEMA COMPANY CASCADE; » RESTRICT: the schema is dropped only if it has
no elements
IM ISU 19Database
Data Definition in SQL (cont.) DROP TABLE
Used to remove a relation (base table) and its definition
The relation can no longer be used in queries, updates, or any other commands since its
description no longer exists
DROP TABLE DEPENDENT;
IM ISU 20Database
Data Definition in SQL (cont.) Two options
» CASCADE: all constraints and views that reference the table are dropped automatically along with the table itself
DROP TABLE DEPENDENT CASCADE;
» RESTRICT: a table is dropped only if it is not referenced in any constraints or views
IM ISU 21Database
Data Definition in SQL (cont.) ALTER TABLE
Used to change the base relation definition Possible actions include
» adding or dropping a column (attribute) » changing a column definition» adding or dropping table constraints
ExampleALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12);
IM ISU 22Database
Characteristics of Relations
» The new attribute will have NULLs in all the tuples of the relation» Hence, the NOT NULL constraint is not allowed for such an attribute
» The database users must still enter a value for the new attribute JOB for each EMPLOYEE tuple
» This can be done using the UPDATE command
IM ISU 23Database
Characteristics of Relations
To drop a column, we must choose either CASCADE or RESTRICT for drop behavior» CASCADE: all constraints and views that
reference the column are dropped automatically along with the column
» RESTRICT: the column is drop only if no views or constraints reference the column
» Example
ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS CASCADE;
IM ISU 24Database
Characteristics of Relations
It is also possible to alter a column definition by dropping an existing default clause or by defining a new default clause» Example 1
ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN DROP DEFAULT;
» Example 2
ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN SET DEFAULT "333445555";
IM ISU 25Database
Retrieval Queries in SQL
Preliminary SQL has one basic statement for retrieving
information from a database; the SELECT statement
This is not the same as the SELECT operation of the relational algebra
IM ISU 26Database
Retrieval Queries in SQL (cont.)
Important distinction between SQL and the formal relational model » SQL allows a table (relation) to have two or more
identical tuples» Hence, an SQL relation (table) is a multi-set (so
metimes called a bag) of tuples; it is not a set of tuples
» SQL relations can be constrained to be sets by specifying PRIMARY KEY or UNIQUE attributes, or by using the DISTINCT option in a query
IM ISU 27Database
Retrieval Queries in SQL (cont.)
Basic form of the SQL SELECT statementSELECT <attribute list>FROM <table list>WHERE <condition>» <attribute list> is a list of attribute names whose
values are to be retrieved by the query» <table list> is a list of the relation names required
to process the query» <condition> is a conditional (Boolean) expressio
n that identifies the tuples to be retrieved by the query
IM ISU 28Database
Retrieval Queries in SQL (cont.)
Simple SQL Queries Basic SQL queries correspond to using the
SELECT, PROJECT, and JOIN operations of the relational algebra
All subsequent examples use the COMPANY database
Example of a simple query on one relation
IM ISU 31Database
Retrieval Queries in SQL (cont.) Query 0: Retrieve the birthdate and address of t
he employee whose name is 'John B. Smith'Q0: SELECT BDATE, ADDRESS
FROM EMPLOYEEWHERE FNAME='John' AND
MINIT='B' AND LNAME='Smith’;
» Similar to a SELECT-PROJECT pair of relational algebra operations
– the SELECT-clause specifies the projection attributes – the WHERE-clause specifies the selection condition
IM ISU 32Database
Retrieval Queries in SQL (cont.)
BDATE,ADDRESS (FNAME=‘John’ AND MINIT=‘B’ AND LNAME=‘Sm
ith’ (EMPLOYEE))
» However, the result of the query may contain duplicate tuples
» Result of Q0
BDATE ADDRESS
1965-01-09 731 Fondren, Houston, TX
IM ISU 33Database
Retrieval Queries in SQL (cont.) Query 1: Retrieve the name and address of all
employees who work for the 'Research' departmentQ1: SELECT FNAME, LNAME,
ADDRESSFROM EMPLOYEE,
DEPARTMENTWHERE DNAME='Research' AND
DNUMBER=DNO;» Similar to a SELECT-PROJECT-JOIN sequence of
relational algebra operations– (DNAME='Research') is a selection condition– (DNUMBER=DNO) is a join condition
IM ISU 34Database
Retrieval 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 last name, address, and birthdate.Q2: SELECT PNUMBER, DNUM, LNAM
E,BDATE, ADDRESS
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN ANDPLOCATION='Stafford’;
IM ISU 35Database
Retrieval Queries in SQL (cont.)» In Q2, there are two join conditions» The join condition DNUM=DNUMBER relates a
project to its controlling department» The join condition MGRSSN=SSN relates the
controlling department to the employee who manages that department
IM ISU 36Database
Retrieval Queries in SQL (cont.) Aliases (Renaming) with SQL
In SQL, we can use the same name for two (or more) attributes as long as the attributes are in different relations
A query that refers to two or more attributes with the same name must qualify the attribute name with the relation name by prefixing the relation name to the attribute name» e.g., EMPLOYEE.LNAME, DEPARTMENT.DNAME
IM ISU 37Database
Retrieval Queries in SQL (cont.) Some queries need to refer to the same relation
twice; In this case, aliases are given to the relation name
Query 8: For each employee, retrieve the employee's name, and the name of his or her
immediate supervisorQ8: SELECT E.FNAME, E.LNAME,
S.FNAME, S.LNAMEFROM EMPLOYEE E SWHERE E.SUPERSSN=S.SSN;
IM ISU 38Database
Retrieval Queries in SQL (cont.)» In Q8, the alternate relation names E and S are
called aliases for the EMPLOYEE relation» We can think of E and S as two different copies of
the EMPLOYEE relation– E represents employees in the role of supervisees
– S represents employees in the role of supervisors
» We an also use the AS keyword to specify aliases, e.g., for Q8
FROM EMPLOYEE E S
FROM EMPLOYEE AS E, EMPLOYEE AS S
IM ISU 39Database
Retrieval Queries in SQL (cont.)» Aliasing can also be used in any SQL query for
convenience
Q1B: SELECT E.FNAME, E.NAME, E.ADDRESS
FROM EMPLOYEE E, DEPARTMENT D
WHERE D.NAME=‘Research’ AND
D.DNUMBER=E.DNUMBER;
IM ISU 40Database
Retrieval Queries in SQL (cont.)Unspecified WHERE-clause
A missing WHERE-clause indicates no condition; hence, all tuples of the relations in the FROM-clause are selected
This is equivalent to WHERE TRUE Query 9: Retrieve the SSN values for all empl
oyeesQ9: SELECT SSN
FROM EMPLOYEE;
IM ISU 41Database
Retrieval Queries in SQL (cont.) If more than one relation is specified in the FRO
M-clause and there is no join condition, then the CARTESIAN PRODUCT of tuples is selected» Query 10: Retrieve all combinations of EMPLOYEE S
SN and DEPARTMENT DNAME
Q10: SELECT SSN, DNAME
FROM EMPLOYEE, DEPARTMENT;
» It is extremely important not to overlook specifying any selection and join conditions in the WHERE-clause
IM ISU 42Database
Retrieval Queries in SQL (cont.)Use of *
To retrieve all the attribute values of the selected tuples, a * is used, which stands for all the attributes
Query 1C: Retrieves all the attribute values of EMPLOYEE at DEPARTMENT number 5 Q1C: SELECT *
FROM EMPLOYEE
WHERE DNO=5;
IM ISU 43Database
Retrieval Queries in SQL (cont.) Query Q1D: Retrieves all the attributes of an
EMPLOYEE and the attributes of the DEPARTMENT he or she works in, for every employee of the ‘Research’ departmentQ1D: SELECT *
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='Research' AND
DNO=DNUMBER;
IM ISU 44Database
Retrieval Queries in SQL (cont.) Use of DISTINCT
SQL does not treat a relation as a set; duplicate tuples can appear
To eliminate duplicate tuples in a query result, the keyword DISTINCT is usedQ11: SELECT SALARY
FROM EMPLOYEE;
Q11A: SELECT DISTINCT SALARY
FROM EMPLOYEE;
IM ISU 45Database
Retrieval Queries in SQL (cont.) » The result of Q11 may have duplicate SALARY
values whereas Q11A does not have any duplicate values
IM ISU 46Database
Retrieval Queries in SQL (cont.) Set Operations
SQL has directly incorporated some set operations
There is a union operation (UNION), and in some versions of SQL there are set difference (MINUS) and intersection (INTERSECT) operations
The resulting relations of these set operations are sets of tuples; duplicates are eliminated
IM ISU 47Database
Retrieval Queries in SQL (cont.) The set operations apply only to union compatible relations
Query 4: Make a list of all project numbers for projects that involve an employee whose last name is 'Smith' as a worker or as a manager of the department that controls the project
IM ISU 48Database
Retrieval Queries in SQL (cont.) Q4: (SELECT PNAME
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Smith');
UNION
(SELECT PNAME
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER=PNO AND ESSN=SSNAND LNAME='Smith');
IM ISU 49Database
Retrieval Queries in SQL (cont.) Substring Comparison
The LIKE comparison operator is used to compare partial strings
Two reserved characters are used » '%' (or '*' in some implementations) replaces an
arbitrary number of characters» '_' replaces a single arbitrary character
Query 12: Retrieve all employees whose address is in Houston, Texas
IM ISU 50Database
Retrieval Queries in SQL (cont.) Q12: SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE ADDRESS LIKE '%Houston,TX%’;
Query 12A: Retrieve all employees who were born during the 1950sQ12A: SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE BDATE LIKE '__5_______’;
» Here, '5' must be the 3th character of the string
IM ISU 51Database
Retrieval Queries in SQL (cont.) Arithmetic Operations
The standard arithmetic operators '+', '-'. '*', and '/' can be applied to numeric values in an SQL query result
Query 13: Show the effect of giving all employees who work on the 'ProductX' project a 10% raise
Q13: SELECT FNAME, LNAME, 1.1*SALARYFROM EMPLOYEE, WORKS_ON, PROJECTWHERE SSN=ESSN AND PNO=PNUMBER
AND PNAME='ProductX’;
IM ISU 52Database
Retrieval Queries in SQL (cont.) ORDER BY
The ORDER BY clause is used to sort the tuples in a query result based on the values ofsome attribute(s)
The default order is in ascending order Query 15: Retrieve a list of employees and
the projects each works in, ordered by the employee's department, and within each department ordered alphabetically by employee last name
IM ISU 53Database
Retrieval Queries in SQL (cont.) Q15:SELECT DNAME, LNAME, FNAME,
PNAME FROM DEPARTMENT, EMPLOYEE,
WORKS_ON, PROJECTWHERE DNUMBER=DNO AND SSN=ESSN
AND PNO=PNUMBERORDER BY DNAME, LNAME, FNAME;
We can specify the keyword DESC if we want a descending orderORDER BY DNAME DESC, LNAME ASC,
FNAME ASC
IM ISU 54Database
More Complex Queries Nesting of Queries
A complete SELECT query, called a nested query , can be specified within the WHERE-clause of another query, called the outer query
Many of the previous queries can be specified in an alternative form using nesting
Query 1: Retrieve the name and address of all employees who work for the 'Research' department
IM ISU 55Database
More Complex Queries (cont.) Q1: SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE
WHERE DNO IN
(SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research');» The nested query selects the number of the 'Rese
arch' department» The outer query select an EMPLOYEE tuple if it
s DNO value is in the result of either nested query
IM ISU 56Database
More Complex Queries (cont.) » The comparison operator IN compares a value v
with a set (or multi-set) of values V, and evaluates to TRUE if v is one of the elements in V
» In general, we can have several levels of nested queries
» In this example, the nested query is not correlated with the outer query
A reference to an unqualified attribute refersto the relation declared in the innermostnested query
IM ISU 57Database
More Complex Queries (cont.) Query 16: Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee
Q16: SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE E.SSN IN
(SELECT ESSN FROM DEPENDENT WHERE
E.FNAME=DEPENDENT_NAME AND E.SEX=SEX);
IM ISU 58Database
More Complex Queries (cont.) Correlated Nested Queries
If a condition in the WHERE-clause of a nested query references an attribute of a relation declared in the outer query, the two queries are said to be correlated
The result of a correlated nested query is different for each tuple (or combination of tuples) of the relation(s) the outer query
IM ISU 59Database
More Complex Queries (cont.) A query written with nested SELECT... FROM... WHERE... blocks and using the = or IN comparison operators can always be expressed as a single block query
For example, Q16 may be written as in Q16A
Q16A: SELECT E.FNAME, E.LNAME
FROM EMPLOYEE AS E, DEPENDENT AS D
WHERE E.SSN = D.ESSN AND E.SEX = D.SEX
AND E.FNAME=D.DEPENDENT_NAME;
IM ISU 60Database
More Complex Queries (cont.) The EXISTS Function
EXISTS is used to check whether the result of a correlated nested query is empty or not
We can formulate Query 16 in an alternative form that uses EXISTS as Q16B below
IM ISU 61Database
More Complex Queries (cont.) Q16: SELECT E.FNAME, E.LNAME
FROM EMPLOYEE AS E WHERE EXISTS
(SELECT * FROM DEPENDENT WHERE E.SSN = ESSN AND
E.SEX = SEX AND E.FNAME = DEPENDENT_NAME);
IM ISU 62Database
More Complex Queries (cont.) Query 6: Retrieve the names of employees who have no dependentsQ6: SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN);
» In Q6, the correlated nested query retrieves all DEPENDENT tuples related to an EMPLOYEE
» If none exist, EMPLOYEE tuple is selected
IM ISU 63Database
More Complex Queries (cont.) Explicit Sets
It is also possible to use an explicit (enumerated) set of values in the WHERE-clause rather than a nested query
Query 17: Retrieve the social security numbers of all employees who work on project number 1, 2, or 3Q13: SELECT DISTINCT ESSN
FROM WORKS_ONWHERE PNO IN (1, 2, 3);
IM ISU 64Database
More Complex Queries (cont.) NULLS in SQL Queries
SQL allows queries that check if a value is NULL (missing or undefined or not applicable)
Any comparison with null returns unknown» E.g.
5 < null or null <> null or null = null
IM ISU 65Database
More Complex Queries (cont.) Three-valued logic using the truth value
unknown: OR: (unknown or true) = true, (unknown or false) =
unknown, (unknown or unknown) = unknown AND: (true and unknown) = unknown, (false and
unknown) = false, (unknown and unknown) = unknown
NOT: (not unknown) = unknown “P is unknown” evaluates to true if predicate P
evaluates to unknown
IM ISU 66Database
More Complex Queries (cont.) SQL uses IS or IS NOT to compare NULLs because it considers each NULL value distinct from other NULL values
Query 18: Retrieve the names of all employees who do not have supervisorsQ18: SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE SUPERSSN IS NULL;
» Result of Q18
IM ISU 67Database
More Complex Queries (cont.) Aggregate Functions
Include COUNT, SUM, MAX, MIN, and AVG
Query 19: Find the sum of salaries, the maximum salary, the minimum salary, and the average salary among all employees
Q19: SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY),
AVG(SALARY) FROM EMPLOYEE;
IM ISU 68Database
More Complex Queries (cont.) 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;
Q22: SELECT COUNT (*)
FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND DNAME='Research’;
IM ISU 69Database
More Complex Queries (cont.) Grouping
In many cases, we want to apply the aggregate functions to subgroups of tuples in a relation
Each subgroup consists of the tuples having the same value for the grouping attribute(s)
The function is applied to each subgroup independently
SQL has a GROUP BY-clause for specifying the grouping attributes, which must also appear in the SELECT-clause
IM ISU 70Database
More Complex Queries (cont.) Query 24: For each department, retrieve the department number, the number of employees in the department, and their average salaryQ24: SELECT DNO, COUNT
(*), AVG (SALARY)
FROM EMPLOYEEGROUP BY DNO;
» In Q24, the EMPLOYEE are divided into groups having the same value for attribute DNO
» The COUNT and AVG functions are applied to each subgroup of tuples separately
IM ISU 72Database
More Complex Queries (cont.) Query 25: For each project, retrieve the project
number, project name, and the number of employees working on that projectQ25: SELECT PNUMBER, PNAME,
COUNT (*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME;» In this case, the grouping and functions are applied
after the joining of the two relations
IM ISU 73Database
More Complex Queries (cont.) The HAVING-CLAUSE
Sometimes we want to retrieve the values of functions for only those groups that satisfy certain conditions
The HAVING-clause is used for specifying a selection condition on groups (rather than onindividual tuples)
IM ISU 74Database
More Complex Queries (cont.) Query 26: For each project on which more than
two employees work, retrieve the project number, project name, and the number of employees Q26: SELECT PNUMBER, PNAME,
COUNT (*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME
HAVING COUNT (*) > 2;
IM ISU 77Database
More Complex Queries (cont.) Summary of SQL Queries
A query in SQL can consist of up to six clauses, but only the first two, SELECT and FROM, are mandatory, ordered as followsSELECT <attribute list>
FROM <table list>
[WHERE <condition>]
[GROUP BY <grouping attribute(s)>]
[HAVING <group condition>]
[ORDER BY <attribute list>]
IM ISU 78Database
More Complex Queries (cont.) A query is evaluated by » first applying the FROM-clause,
» followed by the WHERE-clause,
» then GROUP BY and HAVING, and
» finally the SELECT-clause
IM ISU 79Database
Specifying Updates in SQL SQL commands to modify the database
INSERT, DELETE, and UPDATE
INSERT Command In its simplest form, it is used to add one or m
ore tuples to a relation Attribute values should be listed in the same o
rder as the attributes were specified in the CREATE TABLE command
IM ISU 80Database
Specifying Updates in SQL (cont.) Example: Add a new tuple to the EMPLOYEE
relationU1: INSERT INTO EMPLOYEE
VALUES ('Richard','K','Marini',
'653298653', '30-DEC-52',
'98 Oak Forest,Katy,TX',
'M', 37000,'987654321', 4);
IM ISU 81Database
Specifying Updates in SQL (cont.) An alternate form of INSERT specifies explicitly t
he 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 attributesU1A: INSERT INTO EMPLOYEE (FNAME,
LNAME, SSN)
VALUES ('Richard', 'Marini', '653298653')
IM ISU 82Database
Specifying Updates in SQL (cont.) Note: Only the constraints specified in the DDL
commands are automatically enforced by the DBMS when updates are applied to the database
If a system does not support some constraint, the users must enforce the constraintU2: INSERT INTO EMPLOYEE (FNAME,
LNAME, SSN, DNO)
VALUES (‘Robert’, ‘Hatcher’, ‘980760540’, 2);
(* U2 is rejected if referential integrity checking is provided by DBMS *)
IM ISU 83Database
Specifying Updates in SQL (cont.) A DBMS enforcing NOT NULL will reject an
INSERT command in which an attribute declared to be NOT NULL does not have a value
U2A: INSERT INTO EMPLOYEE (FNAME, LNAME, DNO)
VALUES (‘Robert’, ‘Hatcher’, 5);
(* U2A is rejected if NOT NULL checking is provided by DBMS *)
IM ISU 84Database
Specifying Updates in SQL (cont.) Another variation of INSERT allows insertion o
f multiple tuples resulting from a query into a relation
Example: Suppose we want to create a temporary table that has the name, number of employees, and total salaries for each department. A table DEPTS_INFO is created by U3A, and is loaded with the summary information retrieved from the database by the query in U3B
IM ISU 85Database
Specifying Updates in SQL (cont.) U3A: CREATE TABLE DEPTS_INFO
(DEPT_NAME VARCHAR(10),
NO_OF_EMPS INTEGER,
TOTAL_SAL INTEGER);
U3B: INSERT INTO DEPTS_INFO (DEPT_NAME,
NO_OF_EMPS, TOTAL_SAL)
SELECT DNAME, COUNT (*), SUM (SALARY)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO
GROUP BY DNAME ;
IM ISU 86Database
Specifying Updates in SQL (cont.) DELETE Command
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
IM ISU 87Database
Specifying Updates in SQL (cont.) Examples:
U4A: DELETE FROM EMPLOYEE
WHERE LNAME='Brown’
U4B: DELETE FROM EMPLOYEE
WHERE SSN='123456789’
U4C: DELETE FROM EMPLOYEE
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHEREDNAME='Research')
U4D: DELETE FROM EMPLOYEE
IM ISU 88Database
Specifying Updates in SQL (cont.) UPDATE Command
Used to modify attribute values of one or more selected tuples
A WHERE-clause selects the tuples to be modified
An additional SET-clause specifies the attributes to be modified and their new values
Each command modifies tuples in the same relation
Referential integrity should be enforced
IM ISU 89Database
Specifying Updates in SQL (cont.) Example: Change the location and controlling
department number of project number 10 to 'Bellaire' and 5, respectively.
U5: UPDATE PROJECT
SET PLOCATION = 'Bellaire', DNUM = 5
WHERE PNUMBER=10
Example: Give all employees in the 'Research' department a 10% raise in salary.
IM ISU 90Database
Specifying Updates in SQL (cont.) U6: UPDATE EMPLOYEE
SET SALARY = SALARY *1.1
WHERE 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
IM ISU 91Database
Relational Views in SQL Basic concepts
A view is a single virtual table that is derived from other tables, which could be base tables or previously defined views
A view does not necessarily exist in physical form; that means the view does not store any tuples
There are limitations on possible update operations that can be applied to views
There is no limitation on querying a view
IM ISU 92Database
Relational Views in SQL (cont.) Specify Views
Using CREATE VIEW command The view attribute names can be inherited from the
attribute names of the tables in the defining query Examples:
V1:CREATE VIEW WORKS_ON1 AS SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE SSN=ESSN AND PNO=PNUMBER;
IM ISU 93Database
Relational Views in SQL (cont.) V2:CREATE VIEW DEPT_INFO
(DEPT_NAME, NO_OF_EMPS, TOTAL_SAL) AS
SELECT DNAME, COUNT (*), SUM (SALARY)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO
GROUP BY DNAME;
IM ISU 94Database
Relational Views in SQL (cont.) Queries on Views
A view can be defined to simplify frequently occurring queries
Example: Retrieve the last name and first name of all employees who work on 'ProjectX'.QV1: SELECT PNAME, FNAME, LNAME
FROM WORKS_ON1
WHERE PNAME='ProjectX';
» Without the view WORKS_ON1, this query specification would require two join conditions
IM ISU 95Database
Relational Views in SQL (cont.) A view is always up-to-date
» If the base tables on which the view is defined are modified, the DBMS is responsible for keeping it up to date
» Hence, the view is not realized at the time of view definition, but rather at the time we specify a query on the view
A view is removed using the DROP VIEW command» Example:
V1A: DROP VIEW WORKS_ON1;
IM ISU 96Database
Relational Views in SQL (cont.) Updating of Views
A view update operation may be mapped in multiple ways to update operations on the defining base relations
The topic of updating views is still an active research area
Example: Suppose we issue the command in UV1 to update the WORKS_ON1 view by modifying the PNAME attribute of 'John Smith' from 'ProductX' to 'ProductY'.
IM ISU 97Database
Relational Views in SQL (cont.) UV1: UPDATE WORKS_ON1
SET PNAME = 'ProductY' WHERE LNAME='Smith' AND FNAME='John'
AND PNAME='ProductX’» This can be mapped into several updates on the base
relations to give the desired update on the view» Two possibilities
1. Change the name of the 'ProductX' tuple in the PROJECT relation to 'ProductY’
2. Relate 'John Smith' to the 'ProductY' PROJECT tuple in place of the 'ProductX' PROJECT tuple
IM ISU 98Database
Relational Views in SQL (cont.) » Method 1
UPDATE PROJECTSET PNAME = 'ProductY'
WHERE PNAME = 'ProductX’
– This has the effect of changing all the view tuples with PNAME = ‘ProductX’
– It is quite unlikely that the user who specified view update UV1 wants the update to be interpreted this way
IM ISU 99Database
Relational Views in SQL (cont.) » Method 2
UPDATE WORKS_ONSET PNO = (SELECT PNUMBER
FROM PROJECT WHERE PNAME='ProductY')
WHERE ESSN IN (SELECT SSN FROM EMPLOYEE
WHERE LNAME='Smith' AND FNAME='John') AND
PNO IN (SELECT PNUMBER FROM PROJECT
WHERE PNAME='ProductX')
IM ISU 100Database
Relational Views in SQL (cont.) Some view updates may not make much sense
» Example: Modify the TOTAL_SAL attribute of DEPT_INFO as in UV2
UV2: UPDATE DEPT_INFO
SETTOTAL_SAL=100000
WHERE DNAME='Research'; In general, we cannot guarantee that any view can
be updated A view update is feasible only if one update on the
base relations can accomplish the desired update effect on the view
IM ISU 101Database
Relational Views in SQL (cont.) If a view update can be mapped to more than
one update on the underlying base relations, we have to choose the desired update
General guideline» A view with a single defining table is updatable if
the view attributes contain the primary key» Views defined on multiple tables using joins are
generally not updatable» Views defined aggregate functions are not updatable