7/29/2019 IT 450 Ch 7 SQL

1/43

1

Chapter 7 SQL

1 Data Definition in SQL. Used to CREATE, DROP, and ALTER the descriptions of thetables (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(INTEGER,FLOAT,DECIMAL(i,j),CHAR(n),VARCHAR(n))

. A constraint NOT NULL may be specified on an attribute

. Example

CREATE TABLE DEPARTMENT(DNAME VARCHAR(10) NOT NULL,DNUMBER INTEGER NOT NULL,MGRSSN CHAR(9)MGRSTARTDATE CHAR(9));

. In SQL2, we can use the CREATE TABLE command forspecifying the primary key attributes, secondary keys, andreferential integrity constraints (foreign keys)

7/29/2019 IT 450 Ch 7 SQL

2/43

2

. Key attributes can be specified via the PRIMARY KEY andUNIQUE phrases

. DROP TABLE

. Used to remove a relation (base table) and its definition

. The relation can no longer be used in queries, updates, orany other commands since its description no longer exists

. Example

DROP TABLE DEPENDENT;

. ALTER TABLE

. Used to add an attribute to one of the base relations

. The new attribute will have NULLs in all the tuples of therelation right after the command is executed; hence, theNOT NULL constraint is not allowed for such an attribute

. Example

ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12);

. The database users must still enter a value for the newattribute JOB for each EMPLOYEE tuple. This can be doneusing the UPDATE command.

7/29/2019 IT 450 Ch 7 SQL

3/43

3

. Features Added in SQL2

. CREATE SCHEMA

. Specifies a new database schema by giving it a name

CREATE SCHEMA COMPANY:

. Referential Integrity Options

. In SQL2, we can specify CASCADE or SET NULL or SETDEFAULT on referential integrity constraints (foreignKeys)

2 Retrieval Queries in SQL

. SQL has one basic statement for retrieving information froma database; the SELECT statement

. This is not the same as the SELECT operation of therelational algebra

. Important distinction between SQL and the formal relationalmodel; SQL allows a table (relation) to have two or moretuples that are identical in all their attribute values

. Hence, an SQL relation (table) is a multi-set (sometimes

7/29/2019 IT 450 Ch 7 SQL

4/43

4

called a bag) of tuples; it is not a set of tuples

. SQL relations can be constrained to be sets by using the

CREATE UNIQUE INDEX command, or by using theDISTINCT option in a query

. Basic form of the SQL SELECT statement is called a mappingor a SELECT-FROM-WHERE block

SELECT FROM

WHERE

. is a list of attribute names whose valuesare to be retrieved by the query

. is a list of the relation names required toprocess the query

. is a conditional (Boolean) expression thatidentifies the tuples to be retrieved by the query

2.1 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

7/29/2019 IT 450 Ch 7 SQL

5/43

5

. Example of a simple query on one relation

Query: Retrieve the birthdate and address of the employeewhose name is 'John B. Smith'.

SELECT BDATE, ADDRESSFROM EMPLOYEEWHERE FNAME='John' AND MINIT='B' ANDLNAME='Smith';

. Similar to a SELECT-PROJECT pair of relational algebraoperations; the SELECT-clause specifies the projectionattributes and the WHERE-clause specifies the selectioncondition

. However, the result of the query may contain duplicatetuples

Query: Retrieve the name and address of all employees whowork for the 'Research' department.

SELECT FNAME, LNAME, ADDRESSFROM EMPLOYEE, DEPARTMENTWHERE DNAME='Research' AND DNUMBER=DNO;

. Similar to a SELECT-PROJECT-JOIN sequence of relationalalgebra operations

7/29/2019 IT 450 Ch 7 SQL

6/43

6

. (DNAME='Research') is a selection condition (corresponds toa SELECT operation in relational algebra)

. (DNUMBER=DNO) is a join condition (corresponds to aJOIN operation in relational algebra)

Query: For every project located in 'Stafford' list theproject number, the controlling department number,and the department manager's last name, address, andbirthdate.

SELECT PNUMBER, DNUM, LNAME,BDATE,ADDRESSFROM PROJECT, DEPARTMENT, EMPLOYEEWHERE DNUM=DNUMBER AND MGRSSN=SSN

AND PLOCATION='Stafford';

. In this query, there are two join conditions

. The join condition DNUM=DNUMBER relates a project toits controlling department

. The join condition MGRSSN=SSN relates the controllingdepartment to the employee who manages that department

2.2 Aliases, * and DISTINCT, Unspecified WHERE-clause

. In SQL, we can use the same name for two (or more)

7/29/2019 IT 450 Ch 7 SQL

7/43

7

attributes as long as the attributes are in differentrelations

. A query that refers to two or more attributes with the samename must qualify the attribute name with the relation nameby prefixing the relation name to the attribute name

. Example

EMPLOYEE.LNAME or DEPARTMENT.DNAME

. ALIASES

. Some queries need to refer to the same relation twice

. In this case, aliases are given to the relation name

Query: For each employee, retrieve the employee's name, and

the name of his or her immediate supervisor.

SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAMEFROM EMPLOYEE E SWHERE E.SUPERSSN=S.SSN;

. In this query, the alternate relation names E and S arecalled aliases for the EMPLOYEE relation

. We can think of E and S as two different copies of theEMPLOYEE relation; E represents employees in the role of

7/29/2019 IT 450 Ch 7 SQL

8/43

8

supervisees and S represents employees in the role ofsupervisors

. Aliasing can also be used in any SQL query for convenience;can also use the AS keyword to specify aliases

Query:

SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAMEFROM EMPLOYEE AS E, EMPLOYEE AS SWHERE E.SUPERRSSN=S.SSN;

. UNSPECIFIED WHERE-clause

. A missing WHERE-clause indicates no condition; hence, alltuples of the relations in the FROM-clause are selected

. This is equivalent to the condition WHERE TRUE

Query: Retrieve the SSN values for all employees.

SELECT SSNFROM EMPLOYEE;

. If more than one relation is specified in the FROM-clauseand there is no join condition, then the CARTESIANPRODUCT of tuples is selected.

. Example

7/29/2019 IT 450 Ch 7 SQL

9/43

9

Query:

SELECT SSN, DNAMEFROM EMPLOYEE, DEPARTMENT;

. It is extremely important not to overlook specifying anyselection and join conditions in the WHERE-clause;otherwise, incorrect and very large relations may result.

. USE OF *

. To retrieve all the attribute values of the selectedtuples, a * is used, which stands for all the attributes

. Examples

SELECT *

FROM EMPLOYEEWHERE DNO=5;

SELECT *FROM EMPLOYEE, DEPARTMENTWHERE DNAME='Research' AND DNO=DNUMBER;

. USE OF DISTINCT

. SQL does not treat a relation as a set; duplicate tuplescan appear.

7/29/2019 IT 450 Ch 7 SQL

10/43

10

. To eliminate duplicate tuples in a query result, the keywordDISTINCT is used.

. For example, the result of the first may have duplicateSALARY values whereas the second does not have anyduplicate values.

Query: SELECT SALARYFROM EMPLOYEE;

Query: SELECT DISTINCT SALARYFROM EMPLOYEE;

2.3 Set Operations, Nesting of Queries, Set Comparisons

. SET OPERATIONS:

. SQL has directly incorporated some set operations

. There is a union operation (UNION), and in some versionsof SQL there are set difference (MINUS) and intersection(INTERSECT) operations

. The resulting relations of these set operations are setsof tuples; duplicate tuples are eliminated from theresult.

7/29/2019 IT 450 Ch 7 SQL

11/43

11

. The set operations apply only to union compatiblerelations; the two relations must have the sameattributes and the attributes must appear in the

same order.

Query: Make a list of all project numbers for projects thatinvolve an employee whose last name is 'Smith' as aworker or as a manager of the department thatcontrols the project.

(SELECT PNAME

FROM PROJECT, DEPARTMENT, EMPLOYEEWHERE DNUM=DNUMBER AND MGRSSN=SSN

AND LNAME='Smith')UNION(SELECT PNAME

FROM PROJECT, WORKS_ON, EMPLOYEEWHERE PNUMBER=PNO AND ESSN=SSN AND

LNAME='Smith')

. NESTING OF QUERIES

. A complete SELECT query, called a nested query, can bespecified within the WHERE-clause of another query,called the outer query

. Many of the previous queries can be specified in analternative form using nesting

7/29/2019 IT 450 Ch 7 SQL

12/43

12

Query: Retrieve the name and address of all employees whowork for the 'Research' department.

SELECT FNAME, LNAME, ADDRESSFROM EMPLOYEEWHERE DNO IN

(SELECT DNUMBERFROM DEPARTMENTWHERE DNAME='Research');

. The nested query selects the number of the 'Research'

department

. The outer query select an EMPLOYEE tuple if its DNOvalue is in the result of either nested query

. 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

. A reference to an unqualified attribute refers to therelation declared in the innermost nested query

. In this example, the nested query is not correlated withthe outer query

. CORRELATED NESTED QUERIES

7/29/2019 IT 450 Ch 7 SQL

13/43

13

. If a condition in the WHERE-clause of a nested queryreferences 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 foreach tuple (or combination of tuples) of the relation(s)the outer query

Query: Retrieve the name of each employee who has adependent with the same first name as the employee.

SELECT E.FNAME, E.LNAMEFROM EMPLOYEE EWHERE E.SSN IN

(SELECT ESSNFROM DEPENDENTWHERE ESSN=E.SSN AND

E.FNAME=DEPENDENT_NAME);

. In this query, the nested query has a different result foreach tuple in the outer query

. A query written with nested SELECT... FROM... WHERE...blocks and using the = or IN comparison operators canalways be expressed as a single block query. For example,the above query may be written as follows,

Query: SELECT E.FNAME, E.LNAME

7/29/2019 IT 450 Ch 7 SQL

14/43

14

FROM EMPLOYEE E, DEPENDENT DWHERE E.SSN=D.ESSN ANDE.FNAME=D.DEPENDENT_NAME;

. The original SQL as specified for SYSTEM R also had aCONTAINS comparison operator, which is used inConjunction with nested correlated queries

. This operator was dropped from the language, possiblybecause of the difficulty in implementing it efficiently

. Most implementations of SQL do not have this operator

. The CONTAINS operator compares two sets of values, andreturns TRUE if one set contains all values in the otherset

Query: Retrieve the name of each employee who works on all

the projects controlled by department number 5.

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE

((SELECT PNOFROM WORKS_ONWHERE SSN=ESSN)CONTAINS

(SELECT PNUMBERFROM PROJECT

7/29/2019 IT 450 Ch 7 SQL

15/43

15

WHERE DNUM=5));

. In this query, the second nested query, which is not

correlated with the outer query, retrieves the projectnumbers of all projects controlled by department 5

. The first nested query, which is correlated, retrieves theproject numbers on which the employee works, which isdifferent for each employee tuple because of thecorrelation

2.4 The EXISTS function, NULLs, Explicit Sets

. THE EXISTS FUNCTION

. EXISTS is used to check whether the result of a correlatednested query is empty (contains no tuples) or not

Query: Retrieve the name of each employee who has adependent with the same first name as the employee.

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE EXISTS

(SELECT *FROM DEPENDENTWHERE SSN=ESSN AND

FNAME=DEPENDENT_NAME);

7/29/2019 IT 450 Ch 7 SQL

16/43

16

Query: Retrieve the names of employees who have nodependents.

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE NOT EXISTS

(SELECT *FROM DEPENDENTWHERE SSN=ESSN);

. In this query, the correlated nested query retrieves allDEPENDENT tuples related to an EMPLOYEE tuple. IfNone exist, the EMPLOYEE tuple is selected.

. EXISTS is necessary for the expressive power of SQL

. EXPLICIT SETS

. It is also possible to use an explicit set of values inthe WHERE-clause rather than a nested query

Query: Retrieve the social security numbers of all employeeswho work on project number 1, 2, or 3.

SELECT DISTINCT ESSNFROM WORKS_ONWHERE PNO IN (1, 2, 3);

7/29/2019 IT 450 Ch 7 SQL

17/43

17

. NULLS IN SQL QUERIES

. SQL allows queries that check if a value is NULL (missing

or undefined or not applicable)

. SQL uses IS or IS NOT to compare NULLs because itconsiders each NULL value distinct from other NULLvalues, so equality comparison is not appropriate

Query: Retrieve the names of all employees who do not havesupervisors.

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE SUPERSSN IS NULL;

. Note: If a join condition is specified, tuples with NULLvalues for the join attributes are not included in

the result

2.5 Aggregate Functions and Grouping

. AGGREGATE FUNCTIONS

. Include COUNT, SUM, MAX, MIN, and AVG

Query: Find the maximum salary, the minimum salary, andthe average salary among all employees.

7/29/2019 IT 450 Ch 7 SQL

18/43

18

SELECT MAX(SALARY), MIN(SALARY),AVG(SALARY)

FROM EMPLOYEE;

. Some SQL implementations may not allow more than onefunction in the SELECT clause

Query: Find the maximum salary, the minimum salary, and theaverage salary among employees who work for the'Research' department.

SELECT MAX(SALARY), MIN(SALARY),AVG(SALARY)

FROM EMPLOYEE, DEPARTMENTWHERE DNO=DNUMBER AND DNAME='Research';

Queries a and b: Retrieve the total number of employees in

the company (a), and the number ofemployees in the 'Research' department (b).

a: SELECT COUNT(*)FROM EMPLOYEE;

b: SELECT COUNT(*)FROM EMPLOYEE, DEPARTMENTWHERE DNO=DNUMBER ANDDNAME='Research';

7/29/2019 IT 450 Ch 7 SQL

19/43

19

. GROUPING

. In many cases, we want to apply the aggregate functions

to subgroups of tuples in a relation

. Each subgroup of tuples consists of the set of tuplesthat have 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

Query: For each department, retrieve the department number,the number of employees in the department, and theiraverage salary.

SELECT DNO, COUNT(*), AVG(SALARY)

FROM EMPLOYEEGROUP BY DNO;

. In this query, the EMPLOYEE tuples are divided intogroups--each group having the same value for the groupingattribute DNO

. The COUNT and AVG functions are applied to each suchgroup of tuples separately

. The SELECT clause includes only the grouping attribute and

7/29/2019 IT 450 Ch 7 SQL

20/43

20

the functions to be applied on each group of tuples

. A join condition can be used in conjunction with grouping

Query: For each project, retrieve the project number, projectname, and the number of employees who work on thatproject.

SELECT PNUMBER, PNAME, COUNT(*)FROM PROJECT, WORKS_ONWHERE PNUMBER=PNO

GROUP BY PNUMBER, PNAME;

. In this case, the grouping and functions are applied afterthe joining of the two relations

. THE HAVING CLAUSE

. Sometimes we want to retrieve the values of thesefunctions for only those groups that satisfy certainconditions

. The HAVING clause is used for specifying a selectioncondition on groups rather than on individual tuples

Query: For each project on which more than two employeeswork, retrieve the project number, project name, andthe number of employees who work on that project.

7/29/2019 IT 450 Ch 7 SQL

21/43

21

SELECT PNUMBER, PNAME, COUNT(*)FROM PROJECT, WORKS_ONWHERE PNUMBER=PNO

GROUP BY PNUMBER, PNAMEHAVING COUNT (*) > 2;

2.6 Substring Comparisons, Arithmetic, ORDER BY

. SUBSTRING COMPARISON

. The LIKE comparison operator is used to compare partialstrings

. Two reserved characters are used: '%' (or '*' in someimplementations) replaces an arbitrary number ofcharacters, and '_' replaces a single arbitrary character

Query: Retrieve all employees whose address is in Houston,Texas. Here, the value of the ADDRESS attributemust contain the substring 'Houston, TX'.

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE ADDRESS LIKE '%Houston,TX%';

Query: Retrieve all employees who were born during the1990s. Here, '9' must be the 8th character of the string(according to our format for date), so the BDATE value

7/29/2019 IT 450 Ch 7 SQL

22/43

22

is '_______9_', with each underscore as a place holderfor a single arbitrary character.

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE BDATE LIKE '_______9_';

. The LIKE operator allows us to get around the fact thateach value is considered atomic and indivisible; hence, inSQL, character string attribute values are not atomic

. ARITHMETIC OPERATIONS

. The standard arithmetic operators '+', '-','*' and '/'(for addition, subtraction, multiplication, and division,respectively) can be applied to numeric values in an SQLquery result

Query: Show the effect of giving all employees who work onthe 'Product X' project a 10% raise.

SELECT FNAME,LNAME, 1.1*SALARYFROM EMPLOYEE, WORKS_ON, PROJECTWHERE SSN=ESSN AND PNO=PNUMBER AND

PNAME='Product X';

. ORDER BY

. The ORDER BY clause is used to sort the tuples in a query

7/29/2019 IT 450 Ch 7 SQL

23/43

23

result based on the values of some attribute(s)

Query: Retrieve a list of employees and the projects each

works in, ordered by the employee's department, andwithin each department ordered alphabetically byemployee last name.

SELECT DNAME, LNAME,FNAME, PNAMEFROM DEPARTMENT, EMPLOYEE, WORKS_ON,

PROJECTWHERE DNUMBER=DNO AND SSN=ESSN AND

PNO=PNUMBERORDER BY DNAME, LNAME;

. The default order is in ascending order of values

. We can specify the keyword DESC if we want a descendingorder; the keyword ASC can be used to explicitly specify

ascending order, even though it is the default

2.7 Summary of SQL Queries

. A query in SQL can consist of up to six clauses, but onlythe first two, SELECT and FROM, are mandatory. Theclauses are specified in the following order,

SELECT FROM

7/29/2019 IT 450 Ch 7 SQL

24/43

24

[WHERE ][GROUP BY ][HAVING ]

[ORDER BY ]

. The SELECT clause lists the attributes or functions to beretrieved

. The FROM clause specifies all relations (or aliases) neededin the query but not those needed in nested queries

. The WHERE clause specifies the conditions for selection andjoin of tuples from the relations specified in theFROM clause

. GROUP BY specifies grouping attributes

. HAVING specifies a condition for selection of groups

. ORDER BY specifies an order for displaying the result of aquery

. A query is evaluated by first applying the WHERE clause,then GROUP BY and HAVING, and finally theSELECT clause

3 Specifying Updates in SQL

7/29/2019 IT 450 Ch 7 SQL

25/43

25

. There three SQL commands to modify the database; INSERT,DELETE, and UPDATE

. INSERT

. In its simplest form, it is used to add a single tuple toa relation

. Attribute values should be listed in the same order as theattributes were specified in the CREATE TABLE command

. Example

INSERT INTO EMPLOYEEVALUES ( 'Richard', 'K', 'Marini', '653298653',

'30-DEC-82', '98 Oak Forest,Katy,TX', 'M',37000, '987654321', 4 )

. An alternate form of INSERT specifies explicitly theattribute names that correspond to the values in the newtuple

. Attributes with NULL values can be left out

. Example: Insert a tuple for a new EMPLOYEE for whom weonly know the FNAME, LNAME, and SSNattributes.

INSERT INTO EMPLOYEE (FNAME, LNAME, SSN)

7/29/2019 IT 450 Ch 7 SQL

26/43

26

VALUES ('Richard', 'Marini', '653298653')

. Note: Only the constraints specified in the DDL commands

are automatically enforced by the DBMS when updates areapplied to the database

. Another variation of INSERT allows insertion of multipletuples resulting from a query into a relation

. Example: Suppose we want to create a temporary table thathas the name, number of employees, and total salaries for

each department. A table DEPTS_INFO is created by (a),and is loaded with the summary information retrieved fromthe database by the query in (b).

(a) CREATE TABLE DEPTS _INFO(DEPT_NAME VARCHAR(10),NO_OF_EMPS INTEGER,

TOTAL_SAL INTEGER);

(b) INSERT INTODEPTS_INFO (DEPT_NAME, NO_OF_EMPS,TOTAL_SAL)

SELECT DNAME, COUNT(*), SUM(SALARY)FROM DEPARTMENT, EMPLOYEEWHERE DNUMBER=DNOGROUP BY DNAME;

. Note: The DEPTS_INFO table may not be up-to-date if we

7/29/2019 IT 450 Ch 7 SQL

27/43

27

change the tuples in either the DEPARTMENT or theEMPLOYEE relations after issuing (b). We have to create aview (see later) to keep such a table up to date.

. 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

. A missing WHERE clause specifies that all tuples in therelation are to be deleted; the table then becomes anempty table

. The number of tuples deleted depends on the number oftuples in the relation that satisfy the WHERE-clause

condition

. Referential integrity should be enforced

. Examples

DELETE FROM EMPLOYEEWHERE LNAME='Brown';

DELETE FROM EMPLOYEEWHERE SSN='123456789';

7/29/2019 IT 450 Ch 7 SQL

28/43

28

DELETE FROM EMPLOYEEWHERE DNO IN

(SELECT DNUMBERFROM DEPARTMENTWHERE DNAME='Research');

DELETE FROM EMPLOYEE;

. UPDATE

. Used to modify attribute values of one or more selectedtuples

. A WHERE clause selects the tuples to be modified

. An additional SET clause specifies the attributes to bemodified and their new values

. Each command modifies tuples in the same relation

. Example: Change the location and controlling departmentnumber of project number 10 to 'Bellaire' and 5,respectively.

UPDATE PROJECTSET PLOCATION = 'Bellaire', DNUM = 5WHERE PNUMBER = 10;

7/29/2019 IT 450 Ch 7 SQL

29/43

29

. Example: Give all employees in the 'Research' department a10% raise in salary.

UPDATE EMPLOYEESET SALARY = SALARY*1.1WHERE DNO IN

(SELECT DNUMBERFROM DEPARTMENTWHERE 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

4 Relational Views in SQL

. A view is a single virtual table that is derived from othertables

. The other tables could be base tables or previously definedviews

. A view does not necessarily exist in physical form, which

7/29/2019 IT 450 Ch 7 SQL

30/43

30

limits the possible update operations that can be applied toviews

. There are no limitations on querying a view

. The CREATE VIEW command is used to specify a view byspecifying a (virtual) table name and a defining query

. The view attribute names can be inherited from the attributenames of the tables in the defining query

. Examples

V1: CREATE VIEW WORKS_ON1 ASSELECT FNAME, LNAME, PNAME, HOURSFROM EMPLOYEE, PROJECT, WORKS_ONWHERE SSN=ESSN AND PNO=PNUMBER;

V2: CREATE VIEW DEPT_INFO(DEPT_NAME, NO_OF_EMPS, TOTAL_SAL)ASSELECT DNAME, COUNT(*), SUM(SALARY)FROM DEPARTMENT, EMPLOYEEWHERE DNUMBER=DNOGROUP BY DNAME;

. In V1 the names of the view attribute names are inherited

. In V2, the view attribute names are listed using a

7/29/2019 IT 450 Ch 7 SQL

31/43

31

one-to-one correspondence with the entries in theSELECT-clause of the defining query

. QUERIES ON VIEWS:

. Example: Retrieve the last name and first name of allemployees who work on 'Project X'.

SELECT PNAME, FNAME, LNAMEFROM WORKS_ON1WHERE PNAME='Project X';

. Without the view WORKS_ON1, this query specificationwould require two join conditions

. A view can be defined to simplify frequently occurringqueries

. The DBMS is responsible for keeping the view alwaysup-to-date if the base tables on which the view is definedare modified

. Hence, the view is not realized at the time of viewdefinition, but rather at the time we specify a query onthe view

. A view is removed using the DROP VIEW command

. Example

7/29/2019 IT 450 Ch 7 SQL

32/43

32

DROP VIEW WORKS_ON1;

DROP VIEW DEPT_INFO;

. Views can also be used as a security and authorizationmechanism

. 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 researcharea

. Example: Suppose we issue the command to update theWORKS_ON1 view by modifying the PNAME

attribute of 'John Smith' from 'Product X' to'Product Y'.

UPDATE WORKS_ON1SET PNAME = 'Product Y'WHERE LNAME='Smith' AND FNAME='John' AND

PNAME='Product X';

. This can be mapped into several updates on the baserelations to give the desired update on the view. Twopossibilities are,

7/29/2019 IT 450 Ch 7 SQL

33/43

33

. Change the name of the 'Product X' tuple in the PROJECTrelation to 'Product Y'. It is quite unlikely that the

user who specified the view update wants the update tobe interpreted this way,

UPDATE PROJECTSET PNAME = 'Product Y'WHERE PNAME = 'Product X';

. Relate 'John Smith' to the 'Product Y' PROJECT tuple in

place of the 'Product X' PROJECT tuple. This is mostlikely the update the user means,

UPDATE WORKS_ONSET PNO = (SELECT PNUMBER FROM PROJECT

WHERE PNAME='Product Y')WHERE ESSN = (SELECT SSN FROM EMPLOYEE

WHERE LNAME='Smith' AND FNAME='John')ANDPNO = (SELECT PNUMBER FROM PROJECT

WHERE PNAME='Product X');

. Some view updates may not make much sense; for example,modifying the TOTAL_SAL attribute of DEPT_INFO,

MODIFY DEPT_INFOSET TOTAL_SAL=100000WHERE DNAME='Research';

7/29/2019 IT 450 Ch 7 SQL

34/43

34

. In general, we cannot guarantee that any view can be updated

. A view update is unambiguous only if one update on the baserelations can accomplish the desired update effect on theview

. If a view update can be mapped to more than one update onthe underlying base relations, we must have a certainprocedure to choose the desired update

. We can make the following general observations,

. A view with a single defining table is updatable if theview attributes contain the primary key

. Views defined on multiple tables using joins are generallynot updatable

. Views defined aggregate functions are not updatable

5 Creating Indexes in SQL

. An SQL base relation generally corresponds to a stored file

. SQL has statements to create and drop indexes on baserelations

7/29/2019 IT 450 Ch 7 SQL

35/43

35

. One or more indexing attributes are specified for each index

. The CREATE INDEX command is used to specify an index

. Each index is given an index name

. Example

CREATE INDEX LNAME_INDEX ONEMPLOYEE(LNAME);

. The index entries are in ascending (ASC) order of theindexing attributes; for descending order, the keywordDESC is added

. An index can be created on a combination of attributes

. Example

CREATE INDEX NAMES_INDEXON EMPLOYEE (LNAME ASC, FNAME DESC, MINIT);

. Two options on indexes in SQL are UNIQUE and CLUSTER

. To specify the key constraint on the indexing attribute orcombination of attributes, the keyword UNIQUE is used

. Example

7/29/2019 IT 450 Ch 7 SQL

36/43

36

CREATE UNIQUE INDEX SSN_INDEX ONEMPLOYEE(SSN);

. This is best done before any tuples are inserted in therelation

. An attempt to create a unique index on an existing basetable will fail if the current tuples in the table do notobey the constraint

. A second option on index creation is to specify that the

index is a clustering index using the keyword CLUSTER

. A base relation can have at most one clustering index, butany number of non-clustering indexes

. Example

CREATE INDEX DNO_INDEXON EMPLOYEE(DNO) CLUSTER;

. A clustering and unique index in SQL is similar to theprimary index

. A clustering but non-unique index in SQL is similar to theclustering index

. A non-clustering index is similar to the secondary index

7/29/2019 IT 450 Ch 7 SQL

37/43

37

. Each DBMS will have its own index implementationtechnique; in most cases, some variation of the B+-treedata structure is used

. To drop an index, we issue the DROP INDEX command

. The index name is needed to refer to the index when it is tobe dropped

. Example

DROP INDEX DNO_INDEX;

6 Embedding SQL in a Programming Language

. SQL can also be used in conjunction with a general purposeprogramming language, such as C, C++, COBOL, PL/I

. The programming language is called the host language

. The embedded SQL statement is distinguished fromprogramming language statements by prefixing it with a

specialcharacter or command so that a preprocessor can extract theSQL statements

. In some implementations, SQL statements are passed asparameters in procedure calls

7/29/2019 IT 450 Ch 7 SQL

38/43

38

. We will use PASCAL as the host programming language, anda

"$" sign to identify SQL statements in the program

. Within an embedded SQL command, we may refer to programvariables, which are prefixed by a "%" sign

. The programmer should declare program variables to matchthe

data types of the database attributes that the program will

process

. These program variables may or may not have names that areidentical to their corresponding attributes

. Example: Write a program segment (loop) that reads a socialsecurity number and prints out some information

from the corresponding EMPLOYEE tuple

LOOP:= 'Y';while LOOP = 'Y' dobeginwriteln('input social security number:');readln(SOC SEC NUM);$SELECT FNAME, MINIT, LNAME, SSN, BDATE,

ADDRESS, SALARYINTO %E.FNAME, %E.MINIT, %E.LNAME, %E.SSN,

%E.BDATE, %E.ADDRESS, %E.SALARY

7/29/2019 IT 450 Ch 7 SQL

39/43

39

FROM EMPLOYEEWHERE SSN=%SOC_SEC_NUM;writeln( E.FNAME, E.MINIT, E.LNAME, E.SSN,

E.BDATE, E.ADDRESS, E.SALARY);writeln('more social security numbers (Y or N?');readln(LOOP)end;

. In this example, a single tuple is selected by the embeddedSQL query; that is why we are able to assign its attributevalues directly to program variables

. In general, an SQL query can retrieve many tuples

. The concept of a cursor is used to allow tuple-at-a-timeprocessing by the PASCAL program

. CURSORS

. We can think of a cursor as a pointer that points to asingle tuple (row) from the result of a query

. The cursor is declared when the SQL query command isspecified

. A subsequent OPEN cursor command fetches the query resultand sets the cursor to a position before the first row inthe result of the query; this becomes the current rowfor the cursor

7/29/2019 IT 450 Ch 7 SQL

40/43

40

. Subsequent FETCH commands in the program advance thecursor to the next row and copy its attribute values into

PASCAL program variables specified in the FETCHcommand

. An implicit variable SQLCODE communicates to theprogram

the status of SQL embedded commands

. An SQLCODE of 0 (zero) indicates successful execution

. Different codes are returned to indicate exceptions anderrors

. A special END_OF_CURSOR code is used to terminate aloop over the tuples in a query result

. A CLOSE cursor command is issued to indicate that we aredone with the result of the query

. When a cursor is defined for rows that are to be updatedthe clause FOR UPDATE OF must be in the cursordeclaration, and a list of the names of any attributesthat will be updated follows

. The condition WHERE CURRENT OF cursor specifies thatthe current tuple is the one to be updated (or deleted)

7/29/2019 IT 450 Ch 7 SQL

41/43

41

. Example: Write a program segment that reads (inputs) adepartment name, then lists the names of

employees

who work in that department, one at a time. Theprogram reads a raise amount for each employeeand

updates the employee's salary by that amount.

writeln('enter the department name:');readln(DNAME);$SELECT DNUMBER INTO %DNUMBER

FROM DEPARTMENTWHERE DNAME=%DNAME;

$DECLARE EMP CURSOR FORSELECT SSN, FNAME, MINIT, LNAME, SALARY

FROM EMPLOYEEWHERE DNO=%DNUMBER

FOR UPDATE OF SALARY;

$OPEN EMP;$FETCH EMP INTO %E.SSN, %E.FNAME, %E.MINIT,

%E.LNAME, %E.SAL;while SQLCODE = 0 dobeginwriteln('employee name: ', E.FNAME, E.MINIT,

E.LNAME);writeln('enter raise amount: ');readln(RAISE);$UPDATE EMPLOYEE SET SALARY = SALARY +

%RAISE

7/29/2019 IT 450 Ch 7 SQL

42/43

42

WHERE CURRENT OF EMP;$FETCH EMP INTO %E.SSN, %E.FNAME, %E.MINIT,

%E.LNAME, %E.SAL;

end;$CLOSE CURSOR EMP;

7 Joined Relations Feature in SQL2

. Can specify a joined relation in the FROM clause

. Looks like any other relation but is the result of a join

. Allows the user to specify different types of joins (regulartheta JOIN, NATURAL JOIN, LEFT OUTER JOIN,RIGHT OUTER JOIN, CROSS JOIN, etc)

. Examples

. Select E.FNAME, E.LNAME, S.FNAME, S.LNAMEFrom EMPLOYEE E SWhere E.SUPERSSN=S.SSN;

. Can be written as,

. Select E.FNAME, E.LNAME,S.FNAME,S.LNAMEFrom (EMPLOYEE E LEFT OUTER JOIN

EMPLOYEE S ON E.SUPERSSN=S.SSN);

. Select FNAME, LNAME,ADDRESS

7/29/2019 IT 450 Ch 7 SQL

43/43

From EMPLOYEE, DEPARTMENTWhere DNAME=Research AND DNUMBER=DNO;

. Can be written as,

. Select FNAME, LNAME, ADDRESSFrom (EMPLOYEE JOIN DEPARTMENT

ON DNUMBER=DNO)Where DNAME=Research;

. Or as,

. Select FNAME, LNAME, ADDRESSFrom (EMPLOYEE NATURAL JOIN DEPARTMENT

AS DEPT (DNAME, DNO, MSSN, MSDATE)Where DNAME=Research;

. Select PNUMBER, DNUM,LNAME,BDATE,ADDRESS

From (PROJECT JOIN DEPARTMENT ONDNUM=DNUMBER) JOIN EMPLOYEE ONMGRSSN=SSN))

Where PLOCATION=Stafford;