structured query language

CHAPTER : STRUCTURED QUERY LANGUAGE

Prof. Erwin M. Globio, MSIT 7 - 1

Chapter Objectives At the end of this chapter, you should be able to:

understand the general concepts of the relational model;

explain the common integrity rules.

Essential Reading An introduction to Database System Volume I (5th Edition), C.J.Date (1990), Addison

Wesley. [Chapter 11 - 12]

Modem Database Management (4th Edition), Fred McFadden & Jeffrey A. Hoffer (1994),

Benjamin/Cummings. [Chapter 8, page 283-316]

Useful Websites to learn Database and Programming:

http://erwinglobio.wix.com/ittraining

http://ittrainingsolutions.webs.com/

http://erwinglobio.sulit.com.ph/

http://erwinglobio.multiply.com/








DB212 CHAPTER 7: STRUCTURED QUERY LANGUAGE

7 - 2 Prof. Erwin M. Globio, MSIT



7.1 Relational Data Structures and Concepts

Two-dimensional tables whose attributes values are atomic. This is to say that at every

row-and-column position within the table, there always exists precisely one value, never a list

of values. (Recalls: Relations do not contain repeating groups after INF.)

There are no duplicate rows. (Recall: There is always a primary key in a relation.)

Rows (tuples) are unordered.

S# SNAME STATUS CITY

S1 Smith 21 London

S2 Jones 10 Paris

S3 Mary 30 Paris

S4 Clark 20 Athens

<----------------------------------- Degree ------------------------------------->

Degree of a relation is the number of attributes in a relation.

Cardinality of a relation is the number of tuples in a relation.

Domain is a pool of values, from which one of more attributes draw their actual values. For

example, a domain of supplier numbers is the set of all possible supplier numbers and the

domain.

7.2 Integrity Rules

The entity integrity rules No component of the primary key of a relation is allowed to

accept nulls. Bu "null", we mean that information is missing for some reason. Suppose in the supplier relation, there is a tuple for which the supplier-no is null. This would be like saying

that there was a supplier in the real world that had no identity. This is absurd.

The referential integrity rule The database must not contain any unmatched foreign key

values. By the term "unmatched foreign key value", we mean a non-null foreign key value for

which there does not exist a matching value of the primary key in the relevant target relation.

The referential integrity rule says that if B references A, then A must exist.

Note: Foreign key and referential integrity concepts are defined in terms of one

another. Thus, "support for referential integrity" and "support for foreign keys" mean

exactly the same thing.)

The basic idea is as follows:

For each foreign key, there are three questions that need to be answered:

Can that foreign key accept nulls? For example, does it make sense for a shipment to exist for which the supplier is unknown?



What should happen on an attempt to delete the target of a foreign key references?

For example, an attempt to delete a supplier for which there exists at least at one

matching shipment? Let us consider more definitely the following cases:

Restricted The delete operation is "restricted" to the case where there are

no such matching shipments (it is rejected otherwise)

Cascades The delete operation "cascades" to delete those matching

shipments also

Nullifies The foreign key is set to null in all such matching shipments

and the supplier is then deleted (of course, this case could not

apply if the foreign key cannot accept nulls in the first place)

What should happen on an attempt to update the primary key of a foreign key

reference? For example, an attempt to update the supplier number for a supplier for

which there exists at least one matching shipment? Let us consider the following

cases:

Restricted The update operation is "restricted" to the case where there are

no such matching shipments (it is rejected otherwise)

Cascades The update operation "cascades" to updates the foreign key in

those matching shipments also

Nullifies The foreign key is set to null in all such matching shipments

and the supplier is then updated (of course, this case could not

apply if the foreign cannot accept nulls in the first place)

Note: Restricted-Cascaded-Nullifies options for the foreign key delete and update rules do

not exhaust the possibilities. They are just common practices.

7.3 SQL and Relational Model

SQL (Structured Query Language) is a relational language, which provides an interface

between the user and the RDBMS

7.3.1 A Brief Decomposition

SQL's capabilities are divided between the data description language (DDL) and the data

manipulation language (DML).

DDL

Create tables Query table data

Modify tables (ALTER) Modify table data

Create indexes - Insert

Drop tables - Delete

Drop indexes - Update

Authorization (GRANT) Aggregate functions

Deauthorisation (REVOKE)



The purpose of the DDL is to create entries in the catalogue (sometimes called the data

dictionary or, in some large systems, the repository). The catalogue is managed by the

RDBMS and it is a requirement of the Relational Model that the catalogue itself takes the

form of normalized tables. The update of these tables is quite transparent to the user.

7.4 Relational Data Definition Insql (DDL)

Data Types

DECIMAL (m,n) Signed numbers, where m is the total number of

digits to the right of the decimal point.

INTEGER Large (up to 11 digits) positive or negative

whole numbers

SMALL Small (5 or 6 digits, depending on the DBMS)

positive or negative whole numbers. By

specifying this data type, less storage space

is required FLOAT (m,n) Whole and fractional numbers represented in

scientific notation, where m is the total number

of digits (including sign), and n is the number

of digits to the right of the decimal point.

CHAR (n) Alphanumeric (character) data, where n is the

maximum length for the character string; n

character positions are allocated to each

instance of a CHAR column.

DATE Calendar dates. A system variable can be used

to set the format for the date data type.

LOGICAL True or false values

DDL Commands Description

CREATE TABLE Define the structure of a new table

INSERT Places a new row in a table based on values

supplied in the statement, copies one or more

rows derived from other database into a table,

extracts data from one table and inserts them

into another.

UPDATE Changes values in one or more specified rows

of a table by replacing current values with

constant or the results of calculation.

DELETE Removes one of more rows from a table. CREATE VIEW Names the view and optionally specifies

column names to be used in place of those of

the base table.

CREATE INDEX Defines an index on one column that enables

rapid access to the rows of a table in a sequence

or randomly by the key value.

ALTER TABLE Adds columns to an existing.

DROP TABLE Deletes table.

DROP INDEXES Deletes index.

GRANT Granting user access.

REVOKE

(DBA privileges)

Removing privileges given to a user.



Illustrations: The following is the suppliers-parts-projects database

SUPPLIER PROJECTS


S1 SMITH 20 LONDON

S2 JONES 10 PARIS

S3 BLAKE 30 PARIS

S4 CLARK 20 LONDON

S5 ADAMS 30 ATHENS

PART

P# PNAME COLOR WEIGHT CITY

P1 NUT RED 12 LONDON

P2 BOLT GREEN 17 PARIS

P3 SCREW BLUE 17 ROME

P4 SCREW RED 14 LONDON

P5 CAM BLUE 14 PARIS

P6 COQ RED 19 LONDON

S# P# J# QTY

S1 P1 J1 200

S1 P1 J4 700

S2 P3 J1 400

S2 P3 J2 200

S2 P3 J3 200

S2 P3 J4 500

S2 P3 J5 600

S2 P3 J6 400

S2 P3 J7 800 S2 P5 J2 100

S3 P3 J1 200

S3 P4 J2 500

S4 P6 J3 300

S5 P6 J7 300

S5 P6 J2 200

S5 P1 J4 100

S5 P3 J4 200

S5 P4 J4 800

S5 P5 J4 400

S5 P6 J4 500

J# JNAME CITY

J1 SORTER PARIS

J2 PUNCH ROME

J3 READER ATHENS

J4 CONSOLE ATHENS

J5 COLLATOR LONDON

J6 TERMINAL OSLO

J7 TAPE LONDON

S# P# QTY

S1 P1 300

S1 P2 200

S1 P3 400

S1 P4 200

S1 P5 100

S1 P6 100

S2 P1 300

S2 P2 400

S3 P2 200

S4 P2 200

S4 P4 300 S4 P5 400



Example:

Adding data to a table

Adding of one new row to a table

INSERT INTO SUPPLIE

VALUES ( "S6", "MARY" , 30, "NEW YORK" ) ;

Transferring rows from external file to a database table

INSERT INTO SUPPLIER

SELECT *

FROM NEW-SUPPLIER

WHERE STATUS >20 ;

Deleting database contents

Rows can be deleted individually or in groups.

Deleting a row from a table

DELETE FROM PART

WHERE WEIGHT = 12 ;

Deleting all rows of a table DELETE FROM PART ;

Note: Deletion must be done with care when rows from several relations are involved.

For example, if we deleted a SUPPLIER row or a PROJECT row, or a PART

row, we will have a referential integrity violation in the SPJ or SP tables.)

Changing database contents

To update data in SQL, we must inform the DBMS what relation, columns and rows are

involved.

UPDATE SUPPLIER

SET CITY = ROME WHERE SUPPLIER-NO = "S3" ;

Constructing a View for more than one table

A view is also called a virtual table because it does not actually contain stored data,

combines data that is selected from one or more base tables of the other views. If the data

in the base tables changes, so does the information displayed using the view.

We can use a view to let someone access just the rows and columns that the person needs

from base tables. We can omit columns containing irrelevant or sensitive information.

For instance,

CREATE VIEW SUPPLIER-PART

(SUPPLIER-NO, PART-NO,QUANTITY)

AS SELECT S#, P#, QTY FROM SP

WHERE QTY > = 200;



This will create an external presentation to the user on the table SP.

CREATE VIEW DEALS

(SUPPLIER-NAME, CITY, PROJRCT)

AS SELECT SNAME, SUPPLIER. CITY, JNAME

FROM SUPPLIER, PROJECT

WHERE SUPPLIER, CITY = PROJECT. CITY; This view links suppliers with the project. The two tables are related using their common

CITY column.

Creating indexes

You can create indexes in most RDBMSs to provide rapid random and sequential access

to base-table data.

For example, to create an index on the supplier table for the S#:

CREATE INDEX S-NO

ON SUPPLIER (S#);

Changing table definitions

We may change table definitions in many SQL RDBMSs by ALTERING column

specifications.

For example, to add a new column ADDRESS in the SUPPLIER table:

ALTER TABLE SUPPLIER

ADD (ADDRESS CHAR (30) ) ;

Deleting tables

Once a table is deleted, all the data will be deleted permanently and you cannot get it

back.

DROP VIEW tablename;

Deleting views

Here, the TABLES(s) on which the VIEW is based will not be changed.

DROP INDEX indexname;



7.5 Basic SQL Construct (DML)

SQL's DML is divided into two parts, retrieval commands and updates commands. The

following are the eight operators used by the RM (Relational Model) for retrieval of data from

normalized relations:

Restrict Project

Union Intersection Difference

These represent the eight original operators used by the relational model for retrieval model

for retrieval of data from normalization relations. Product, union, insertion and difference are

operators in a branch of mathematics called set theory. Restrict, project, join and divide are operators in a branch of mathematics called relational theory.

a

b

c

X

y

a x

b y

c x a y

b x

c y



SELECT

SELECT *

SELECT DISTINCT

SELCT <column name. .>

WHERE

SQL Logical Operations

NOT

AND

OR

Predicates used:

BETWEEN

In

ANY

ALL

EXISTS

LIKE

GROUP BY

HAVING

ORDER BY

Aggregate functions:

COUNT

SUM

AVG

MAX

Columns to be projected out eventually.

To display all data in a table.

To display only one of the number of identical

rows.

To display data from specific rows.

Search condition for the rows.

Select rows that do not meet the search condition.

Both conditions must be true to qualify the row.

One or both of the conditions must be true.

Select column values that fall within a range.

Selects column values that match one of a

number of values.

Select rows whose values match any of the values

returned by the inner query. Select rows whose values have some relation to

all of the values returned by the inner query.

The EXISTS predicate determines whether the

outer WHERE condition is true or false. If the

inner query returns any value, the WHERE

condition is true. Otherwise, the condition is

false. Thus a simple subquery that uses EXISTS

returns all rows if the outer WHERE condition is

true.

Selects column values that contain a character

string Underscore (-) - represent any single

character.

Percent symbol (%) - represent no

characters or a number of characters.

Rearranges the rows extracted into partitions or

groups, such that within any one group, all rows

have the same value for the GROUP BY field.

Conditions applied to each group from previous

GROUP BY clause.

Sort required columns either in ascending or

descending order.

Number of values in the column

Sum of the values in the column

Average of the values in the column

Largest value in the column

Smallest value in the column



Restrict

Restriction retrieves rows from a relation based upon selection criteria. Thus,

SELECT * (* means all columns)

FROM S (target table)

WHERE SCITY= `LONDON' ; (selection criterion)

will retrieve the following:


S1 SMITH 20 LONDON

S2 CLARK 20 LONDON

Project

Projection retrieves columns form a relation based upon selection criteria. For example,

SELECT S#, SNAME (named columns)

FROM SUPPLIER

WHERE SUPPLIER.CITY = 1LONDON' ;

will retrieve the following:

S# SNAME

S1 SMITH

S2 CLARK

What would the following achieve?

SELECT STATUS

FROM SUPPLIER

WHERE SUPPLIER.CITY = `LONDON' :

The answer is

STATUS

20

20

Is this a proper relation? (Hint: it contains duplicate rows)

This can be solved easily by using the command called DISTINCT, i.e.

SELECT DISTINCT STATUS

FROM SUPPLIER

WHERE SUPPLIER.CITY = `LONDON' ;

which eliminates duplicates from the resultant relation.



Product

This is an expensive operation. For example, Let S is a relation with 5 rows; P is a

relation with 6 rows. Then the product S x P will contain 30 rows, i.e. [R1 (S)

R1(P), R1(S) R1(P), . . . R5(S) R6(P) ]

The importance of product is that it provides the basis for the relational operator join.

The following will result in a product of S, P

SELECT * (all columns)

FROM SUPLIER, PART; (from both tables SUPPLIER & PART)

Note: Such a product is called the Cartesian product and is specifically discouraged by

the Relational Model. For example, if 2 tables, each has a million entries, so if

combined, it would tie up computing resources.

Union

Union can be described as a collection of columns that exist in one relation, or both. Each

column must be of the same type and size and be drawn from the same domain. For example,

if we look at SUPPLIER and PART tables, only one column qualifies (CITY).

SELECT #

FROM SUPLIER

WHERE SUPPLIER.CITY = `LONDON' UNION SELECT S#

FROM SP

WHERE P# = `P2' ;

This will extract S# for those suppliers who supply the part P2 or who are located to

LONDON, or both. The answer is

S#

S1

S2

S3

Duplicates are automatically eliminated. It should be noted that UNION has an associative

property inbuilt, i.e. A UNION B and B UNION A are identical. Similarly, (A UNION B) UNION C is equal to (A UNION C) UNION B.

Intersection

Two (or more) tables which share one (or more) union-compatible attributes may be

intersected. However, SQL requires special construct to achieve this, each involving what

is called sub-query.

For example, we went to list what suppliers have supplied (some) parts.

SELCT S#

FROM SUPPLIER

WHERE S# IN

(SELECT S#

FROM SP) ;

This will find all S# in SUPPLIER which match some S# in SP.



Is this operation equivalent to equijoin operation?

Note: The outer SELCT list is not restricted. Thus,

SELECT *

FROM SUPPLIER

WHERE S# IN

(SELECT S# FROM SP) ;

is perfectly fine, since only S# in SUPPLIER and S# in SP are union compatible.

The Existential Quantifier ÈXISTS".

The above example can also be written as

SELECT S#

FROM S

WHERE EXISTS

(SELECT *

FROM SP

WHERE S.S# = SP.S#);

Note: The `SELECT *' in the subquery does not have the meaning `select all columns'

as you have been as far led to believe: it mean's `scan all rows'!) The method of execution is as follows: `For each S# in S, scan all rows in SP until the

expression S.S# is satisfied the search is exhausted, which ever occurs first. Return `YES' if a

match is made, `NO' otherwise. If `YES', then the current S# in S is selected.

In other words, ÈXISTS' returns a binary valued response whereas ÌN' returns a relation.

ÈXISTS" can always be used in place of ÌN', but not vice versa.

Difference

Difference is the negation of two relation. For example,

SELECT S#

FROM SUPPLIER

WHERE NOT S# IN

(SELECT S#

FROM SP) ;

(Think about: Is negation associative?)

Divide

For example, SP divide by PART will give S1 only.

SP divide by P

S1 P1 P1

S1 P2 P2

S1 P3 P3

S1 P4 P4

S1 P5 P5 Since this is not commonly represented in SQL.



Join

The purpose of a join is to create a new relation from two or more existing relations. In

SQL, this is achieved implicitly. This is no JOIN command in SQL. There are some rules

involved:

In order for two tables to be joined, they must share an attribute whose values are

taken from the same domain.

The result table must be a relation. Thus, if it has no primary key, the select list must

include `DISTINCT' to remove duplicate rows.

Where the result table may contain duplicate columns (SQL does not eliminate

them), the SELECT * construct is therefore discouraged.

The most meaningful join is one which joins a primary key to its foreign key(s).

(Recall: Normalization helps to decompose a complex model and JOIN is used to reconstruct

the original model.)

For example, we want to know for all suppliers, what parts and how many they have supplied.

SELECT SUPPLIER.S#, SNAME, P#, QTY

FROM SUPPLIER, SP (Tables to be joined)

WHERE SUPPLIER.S# = SP.S#; If we want to extract the name of the part supplied, then

SELECT SUPPLIER.S#, SNAME, SP.P#,PNAME,QTY

FROM SUPPLIER, SP, PART

WHERE SUPPLIER.S# = SP.S#

AND SP.P# = PART.P# ;

We can also use the greater sign `>', or smaller sign `<', or even the not equal sign `<>' in the

WHERE condition.



7.6 Review Questions

1. What are the differences between equijoin and natural join. Illustrate with examples.

[4 marks]

2. a. Answer the following questions using the below table structures.

Student Course

Student-ID Numeric 5

Name Character 15

Major Character 3

Registration

Student-ID Numeric 5

Name Character 15

Major Character 3

i. Write a full database description using the SQL data definition

language for the above table Student. [2 marks]

ii. Write a SQL command to add a new student "John" with student

number 12221 and majoring in "IT". [1 mark]

iii. Define the following view by SQL definition. [2 marks]

Student-ID Name Major Course-ID Grade

iv. Write a SQL command to change the grade for student number 38214 from 51 to 57 [2 marks]

v. Write a SQL command to display the average grade for each course.

[3 marks]

vi. Write a SQL command to count the number of students taught

by each instructor. [3 marks]

vii. Write a SQL command to display an ordered list of courses and the

respective instructors, of which all student's grades in those courses

are above 50. [3 marks]

b. Use SQL commands to illustrate:

i. Natural join [2 marks] ii.

Search condition where exact matches are necessary [2 marks]

Course-ID Character 5

Title Character 20

Instructor Character 10

Instructor

Instructor Character 10

Location

Character 4



Useful Websites to learn Database and Programming:












structured query language

Technology