lecture-8 (sql_part-ii)c-3.pdf

ConstraintsSQL Query

Exercise

CIT-4307Database Management Systems

Abdullah Al Hasib

Department of Computer Science and Information Technology (CIT)Islamic University of Technology (IUT)

Gazipur - 1704, BangladeshEmail: [email protected]

February 20, 2011

Abdullah Al Hasib 1 / 49 Lecture-8 (SQL: Part-II)


Exercise

Outline

1 ConstraintsNot NullPrimary KeyUniqueForeign KeyCheck

2 SQL QueryAggregate functionsString operationsSet comparison

3 ExerciseSQL ConstraintsSQL Query



Exercise

Not NullPrimary KeyUniqueForeign KeyCheck

Outline

1 Constraints

2 SQL Query

3 Exercise



Exercise


Integrity Constraint

Constraints are conditions that must be satisfied by everydatabase instance.

The constraints should be declared in Create Table

SQL checks if each modification preserves constraints

Constraints can be defined in two ways

- Column level definition

- Table level definition



Exercise


SQL Integrity Constraints

Not Null: Used to prevent the the entry of NULL values.

Unique: Ensures that all values in a column are different.

Primary Key: Used to uniquely identify a row in the table.

Foreign Key: Used to ensure referential integrity of thedata.

Check: Makes sure that all values in a column satisfy certaincriteria.


USER

Highlight


Exercise


Not Null constraint

Used to prevent the the entry of NULL values

- [Constraint <constraint name>] Not Null

Create Table Movies (mID int,title varchar2(20) Not Null,director varchar2(10),year int default 0 );

Note:

It is recommended to use constraint name so that referring toconstraint will be easier later on.

Not Null constraint can only be defined at column level



Exercise


Primary Key Constraint

Defines a column or combination of columns which uniquelyidentifies each row in the table

Syntax to define a Primary key at column level

- <Column name> <Data type> [Constraint<constraint name>] Primary Key

Syntax to define a Primary key at table level

- [Constraint <constraint name>] Primary Key (<Column name1>, <Column name2>)

Note:

You have to use table constraint to define composite primarykey.



Exercise



Two equivalent ways to declare primary keys:

Create Table Movies (mID int Primary key,title varchar2(20),director varchar2(10),year int default 0 );

Create Table Movies (mID int,title varchar2(20),director varchar2(10),year int default 0,Primary key (mID));

What if we have another key, e.g., (title, director)?

- We cannot declare it as another primary key

- But we can declare it as unique



Exercise



Revised Examples (with constraint-names)

1 Create Table Movies (mID int Constraint movies mid pk Primary key,title varchar2(20),director varchar2(10),year int default 0);

2 Create Table Movies (mID int,title varchar2(20),director varchar2(10),year int default 0,Constraint movies mid pk Primary key (mID));



Exercise


Unique Constraint

Unique specifications are verified in the same way as primarykey(also accept NULL values)

- [Constraint <constraint name>] Unique

- [Constraint <constraint name>] Unique (<column name>)

Create Table Movies (mID int,title varchar2(20),director varchar2(10),year int default 0,Constraint movies mid pk Primary key (mID),Constraint movies tdir un Unique (title, director));



Exercise


Foreign key or Referential Integrity

Attributes of one relation that refer to attributes of anotherrelation

Most often this constraint references the PRIMARY KEY ofanother table

Syntax to define a Foreign key at column level

- [Constraint <constraint name>]References <referenced table name>(<column name>)

Syntax to define a Foreign key at table level

- [Constraint <constraint name>]Foreign Key (<column name>)References <referenced table name>(<column name>)



Exercise


Foreign key or Referential Integrity

* Referenced Table Schema:Product(product id, product name, unit price)

Create Table order details (order id number(5) Not Null,product id number(5),quantity number(5),order date date,delivery date date,Constraint order oid pk Primary key (order id),Constraint order pid fkForeign Key (product id)References Product(product id) );


USER

Highlight


Exercise


More on referential integrity constraints

It is possible to associate reaction policies to the violations ofreferential integrity constraints.

Violations arise from

- updates on referred attribute, or

- tuple deletions

Reactions operate on referencing table, after changes to themaster(referenced) table. They are:

* No Action: reject the change on the master table;* Restrict: reject the change on the master table;* Cascade: propagate the change;* Set Null: nullify the referring attribute;* Set Default: assign default value to the referring attribute.



Exercise


More on referential integrity constraints

* Revised Example* Referenced Table Schema:

Product(product id, product name, unit price)Create Table order details (order id number(5) Not Null,product id number(5),quantity number(5),order date date,delivery date date,Constraint order oid pk Primary key (order id),Constraint order pid fkForeign Key (product id)References Product(product id)On Update Cascade On Delete Restrict );



Exercise


Check Constraints

The most generic constraint type

It allows you to specify that the value of an attribute mustsatisfy some condition

- [Constraint <constraint name>]

Check (<column name> <condition>)

Create Table Students (sID int,name varchar2(20),gender char(1),department varchar2(10),Constraint std gdr chk Check(gender in (’M’,’F’))


USER

Highlight


Exercise


Adding/Romoving Constraint using Alter Table

Adding a constraint using Alter table

- Alter Table <table name>Add Constraint <constraint name>Check(<column name> <condition>) [Disable]

- Alter Table StudentsAdd Constraint std gdr chkCheck(gender in (’M’,’F’))

Dropping a constraint using Alter table

- Alter Table <table name>Drop Constraint <constraint name>

- Alter Table StudentsDrop Constraint std gdr chk



Exercise


To enable/disable a Constraint

To enable a constraint

- Alter Table <table name>Enable Constraint <constraint name>

To disable a constraint

- Alter Table <table name>Disable Constraint <constraint name>



Exercise

Aggregate functionsString operationsSet comparison

Outline

1 Constraints

2 SQL Query

3 Exercise



Exercise


SQL Query: Basic structure

A typical SQL query has the form:Select [Dinstinct] <a1, a2, ...., an>From <r1, r2, ...., rn>[Where <condition>][ Order By <attributes> [asc|desc] ]

- Distinct eliminates the duplicate tuples.

- From clause produces Cartesian product of listed relations.

- Where clause selects the tuples that satisfy the givencondition(s).

- Simple conditions can be combined using the logicalconnectives And, Or, Not.

- Order By clause specifies a sorting order in which thetuples of a query are displayed.



Exercise


Sample Database schema for query

Consider the Movie schema below, consisting of three relations.The key attributes are underlined.

Movies(mID, title, director, year, length)That is, the ID, the title, the director of a movie, the yearwhen it was released and its length.

Artists(aID, aName, nat)That is, the ID and the name of an artist and his/hersnationality.

Roles(mID, aID, character)That is, the ID of a movie in which an artist (aID) played acharacter.


USER

Highlight


Exercise


More on the SELECT clause

An asterisk in the SELECT clause denotes ”all attributes”

Select *From Movies

- Result:

mID title director year length

1 Shining Kubrick 1980 1462 Player Altman 1982 1463 Chinatown Polanski 1974 1314 Repulsion Polanski 1965 143


USER

Highlight


Exercise



The SELECT clause can contain arithmetic expressionsinvolving the operators +, -, *, and /, and operating onconstants or attributes of tuples.

- Select mID, title, director, 2010 – year As numOfYearsFrom Movies

Result:

mID title director noOfYears

1 Shining Kubrick 302 Player Altman 283 Chinatown Polanski 364 Repulsion Polanski 34


USER

Highlight


Exercise



To force the elimination of duplicates, use the keywordDistinct after Select.

Query: Find the names of directors who directed at least onemovie

- Select Distinct directorFrom Movies

Result

director

KubrickAltmanPolanski



Exercise



To force the elimination of duplicates, use the keywordDistinct after Select.

Query: Find the names of directors who directed at least onemovie

- Select Distinct directorFrom Movies

Result

director

KubrickAltmanPolanski


USER

Highlight


Exercise


Join queries

Query: Find actors playing in movies directed by Lucas:

Select A.aNameFrom Artists A, Roles R, Movies MWhere M.director = ’Lucas’ And

R.mID = M.mID AndA.aID = R.aID

Evaluation strategy

[-] From clause produces Cartesian product of listed relations;[-] Where clause:

Selection condition M.director = ’Lucas’eliminates irrelevant tuples;

Join conditions R.mID = M.mID And A.aID = R.aIDrelates facts to each other;

[-] Select clause retains only the listed attributes.



Exercise


Join queries


Select A.aNameFrom Artists A, Roles R, Movies MWhere M.director = ’Lucas’

AndR.mID = M.mID AndA.aID = R.aID

Evaluation strategy







Exercise


Join queries



R.mID = M.mID

AndA.aID = R.aID

Evaluation strategy







Exercise


Join queries



R.mID = M.mID AndA.aID = R.aID

Evaluation strategy






USER

Highlight


Exercise


Nested subqueries

SQL provides a mechanism for the nesting of subqueries.

A subquery is a Select-From-Where expression that isnested within another query.

In general, a Where clause could contain another query,and test some relationship between an attribute and theresult of that query.

A common use of subqueries is to perform tests for

Set-valued subqueries- <expression> [Not] In ( subquery )- <expression> <comparison operator> In ( subquery )

Test for non-existence- [Not] Exists ( subquery )



Exercise


Nested subqueries cont’d

Query: Find actors(name) playing in movies directed by Lucas:

Select A.aNameFrom Artists AWhere A.aID In

(Select Distinct R.aIDFrom Roles R, Movies MWhere M.director = Lucas ANDR.mID = M.mID)



Exercise


Nested subqueries cont’d

Evaluation strategy:

The subquery is evaluated once to produce theset of aID’s of actors who played in movies ofLucas

aID

24

Each tuple (as A) is tested against this set

aName

Harrison FordCarrie Fisher



Exercise


Aggregate functions

These functions operate on the values of a column of a relation,and return a value.

Avg: average value

Min: minimum value

Max: maximum value

Sum: sum of the values

Count: number of values

Stddev: standard deviation of the values

Var: variance of the values


USER

Highlight


Exercise


Aggregate functions cont’d


1 Shining Kubrick 1980 1462 Player Altman 1982 1463 Chinatown Polanski 1974 1314 Repulsion Polanski 1965 1435 Star Wars IV Lucas 1977 1266 American Graffiti Lucas 1973 1107 Full Metal Jacket Kubrick 1987 156

Query: Count the no of tuples in Movies

- Select Count(*) As noTuplesFrom Movies

- Result:noTuples

7



Exercise







- Result:noTuples

7


USER

Highlight


Exercise







- Result:noTuples

7



Exercise



Query: Find the number of directors

Select Count(Distinct director)As noDirectors

From Movies

Result:noDirectors

4

Query: Find the average length of Lucas’smovies.

Select Avg(length) As AvglFrom MoviesWhere Director = ’Lucas’

Result:Avgl

118



Exercise



Query: Find the number of directors

Select Count(Distinct director)As noDirectors

From Movies

Result:noDirectors

4

Query: Find the average length of Lucas’smovies.

Select Avg(length) As AvglFrom MoviesWhere Director = ’Lucas’

Result:Avgl

118


USER

Highlight


Exercise


Aggregation and grouping

Query: For each director, return the average running time ofhis/her movies.

Select director, AVG(Length) As AlengthFrom MoviesGroup By director

First, create groups based on the values of the director attribute.


1 Player Altman 1982 1462 Shining Kubrick 1980 1463 Full Metal Jacket Kubrick 1987 1564 Chinatown Polanski 1974 1315 Repulsion Polanski 1965 1436 Star Wars IV Lucas 1977 1267 American Graffiti Lucas 1973 110

Then, for each group, compute the averagelength of the movies in it.Result:

Director Alength

Altman 146Kubrick 151Polanski 137

Lucas 118



Exercise


Selection based on aggregation results

Query: Find directors and average length of their movies,provided they made at least two movies

Idea:

from all the groups of directors, consider only those for whomCOUNT(mID) >= 2;for those directors, compute AVG(Length)

SQL has a special syntax for it: HAVING.

Select director, AVG(Length)From MoviesGroup By directorHaving Count(mID) >= 2


USER

Highlight


Exercise


Important considerations for Where and Having clause

Where clause can be used to check for conditions based onvalues of columns and expressions related to individual rows.It cannot be used with conditions related to groups.

- Select director, AVG(Length) From MoviesWhere Count(mID) >= 2Group By director

Having clause is specially designed to evaluate the conditionsthat are based on group functions such as Sum and Count.Having clause cannot be used for conditions that are notrelated to groups.

- Select aNameFrom ArtistsHaving nat = ’USA’



Exercise


Selection based on aggregation results

Results of aggregates can be used for comparisons not only inthe HAVING clause.

Query: Find the director and the title of the longest movie

Select M.director, M.titleFrom Movies MWhere M.length =

( Select Max (M1.length) As maxLenFrom Movies M1)



Exercise


String operations

SQL includes a string-matching operator for comparisons oncharacter strings e.g Like operator)

Syntax: match expression [Not] Like pattern

Patterns are described using two special characters:

Wildcard Description Example

underscore ( ) Any single character ’ a b ’ matches cacbc, aabba etcpercent (%) Any string of zero or ’%a%b’ matches ab, ccaccbc,

more characters aaaabcbcbbd, aba, etc



Exercise


String operations cont’d

Find the list of movies containing the word ”War”.

Select TitleFrom MoviesWhere title Like ’%War%’

Is Polanski spelled with a ”y” or with an ”i”?

Select Title, DirectorFrom MoviesWhere director Like ’Polansk ’


USER

Highlight

USER

Highlight


Exercise



SQL supports a variety of string operations such as

Function Description

CONCAT(x, y) Appends y to xINITCAP(x) Converts the initial letter of each word in x to

uppercaseLENGTH(x) Returns the number of characters in xLOWER(x) Converts the letters in x to lowercaseREPLACE(x, str1, str2) Searches x for str1 and replaces it with str2SUBSTR(x, start[, length]) Returns a substring of x that begins at the

position specified by start. You can supplyan optional length for the substring

UPPER(x) Converts the letters in x to uppercase



Exercise



Find out the full name of all customers

Select Concat(first name, last name)From Customers

Find out the length of the titles of all the movies

Select Title, Length(title)From Movies



Exercise


Set comparison

< value >< condition > ALL(< query >)Is true if either

< Query > evaluates the empty setfor every < value1 > in the result of < query >,< value >< condition >< value1 > is true.

where < condition > can be <,≤, >,≥, 6=,=

For example

5 > ALL(∅) is true;5 > ALL(1, 2, 3) is true;5 > ALL(1, 2, 3, 4, 5, 6) is false.5 6= ALL(1, 2, 3, 4) is true.5 = ALL(4, 5) is false.

6= ALL is equivalent to NOT IN

But, = ALL is not equivalent to IN



Exercise


Set comparison cont’d

Query: Find directors whose all movies have been completedbefore 1980

Select Distinct M.directorFrom Movies MWhere 1980 > ALL

(Select M1.yearFrom Movies M1Where M1.director = M.director)



Exercise



< value >< condition > ANY (< query >)Is true if

for some < value1 > in the result of < query >,< value >< condition >< value1 > is true.

where < condition > can be <,≤, >,≥, 6=,=

For example

5 < ANY (∅) is false;5 < ANY(1, 2, 3) is false;5 < ANY(1, 2, 3, 4, 5, 6) is true.5 6= ANY(1, 2, 3, 4) is true.5 = ANY(4, 5) is true.

= ANY is equivalent to IN

6= ANY is not equivalent to NOT IN



Exercise



Query: Find directors who completed some movies after 1980

Select Distinct M.directorFrom Movies MWhere 1980 < ANY

(Select M1.yearFrom Movies M1Where M1.director = M.director)



Exercise


Set operations

The set operations UNION, INTERSECT, and EXCEPToperate on relations and correspond to the relational algebraoperations ∪,∩,−Each of the above operations automatically eliminatesduplicates; to retain all duplicates use UNION ALL,INTERSECT ALL, and EXCEPT ALL.



Exercise

SQL ConstraintsSQL Query

Outline

1 Constraints

2 SQL Query

3 Exercise



Exercise


Exercise

Create a client master table as described below

Column Name Data Type Size Attributes

client no char 6 Primary Key

name varchar2 10 Not Null, *

address varchar2 10 *

city varchar2 10

pincode float

state varchar2 15

bal due number 10,2 Cannot be negative

*(Name & address) uniquely identify each row



Exercise


Exercise

Create a order details table as described below

Column Name Data Type Size Attributes

order no char 6 Primary Keyorder date dateclient no char 6 Foreign Key references

client no of client master table;if a row in master table is deletedcorresponding row in child is alsodeleted

delivery date date Can’t be less than order date



Exercise



Consider the Shipment schema below, consisting of fourrelations. The key attributes are underlined.

Customer(cid, cname, address, city, state)Product(pid, pname, price, inventory)Shipment(sid, cid, shipdate)ShippedProduct(sid, pid, amount)

Write down the sql statements for the following queries:1 Return the product names and inventory value of each product

(price*inventory) ordered by product name.2 For all customers in California (’CA’) list the customer name,

product name, and amount for all shipments3 Return total value of products in inventory.



Exercise





Write down the sql statements for the following queries:4 Return product names and total amount shipped

(price*amount) for products shipping over $1,000.5 Return the shipment id and total value of the entire shipment

(price*amount) ordered by the shipment values.6 Return the products (name) whose name contains ’RAM’ with

a price more than the average price.



Exercise





Write down the sql statements for the following queries:7 Return customer names and total sales of products shipped to

each customer. Only show customers with total sales of over$200 with the results ordered in descending order of total sales.

8 Return the number of shipments to customers with first nameScott.

9 Return the list of customers (no duplicates) that have neverreceived a shipment.

10 Return all customers and their states that share a state withanother customer.


lecture-8 (sql_part-ii)c-3.pdf

Documents