Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

SQL-Introduction to Database Systems-Lecture 04 Slides-Computer Science, Slides of Introduction to Database Management Systems

SQL, Structured Query Language, SQL DDL Statements, Allowable Data Types, User-defined Types, Attribute Constraints, Primary Key, Referential Constraints, SQL Queries, Multiple Relations, Set Operators, Queries With Nested Structures, Tuple Calculus, Aggregate Functions, Grouping Queries, Update Commands, View Management, Outer Join, Embedded SQL, Cursors

Typology: Slides

2011/2012

Uploaded on 02/13/2012

richiey
richiey 🇨🇦

32 documents

1 / 41

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
4-1
SQL
Structured Query Language
Declarative
Specify the properties that should hold in the result, not
how to obtain the result
Complex queries have procedural elements
International Standard
SQL1 (1986)
SQL2 (SQL-92)
SQL3 (pieces have started to appear; also known as
SQL-99)
Two components
DDL statements
DML statements
4-2
SQL DDL Statements
Create schema
CREATE SCHEMA Schema_Name AUTHORIZATION User_Name
Create table
Specify a new relation scheme
General form
CREATE TABLE <Table_Name>
(Attribute_1 <Type>[DEFAULT <value>][<Null constraint>],
Attribute_2 <Type>[DEFAULT <value>][<Null constraint>],
Attribute_n <Type>[DEFAULT <value>][<Null constraint>],
[<Constraints>])
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18
pf19
pf1a
pf1b
pf1c
pf1d
pf1e
pf1f
pf20
pf21
pf22
pf23
pf24
pf25
pf26
pf27
pf28
pf29

Partial preview of the text

Download SQL-Introduction to Database Systems-Lecture 04 Slides-Computer Science and more Slides Introduction to Database Management Systems in PDF only on Docsity!

4-

SQL

 Structured Query Language

 Declarative

 Specify the properties that should hold in the result, not how to obtain the result  Complex queries have procedural elements

 International Standard

 SQL1 (1986)

 SQL2 (SQL-92)

 SQL3 (pieces have started to appear; also known as SQL-99)

 Two components

 DDL statements  DML statements

SQL DDL Statements

 Create schema CREATE SCHEMA Schema_Name AUTHORIZATION User_Name

 Create table

 Specify a new relation scheme  General form CREATE TABLE <Table_Name> (Attribute_1 [DEFAULT ][], Attribute_2 [DEFAULT ][], … Attribute_n [DEFAULT ][], [])

4-

Example

 Design

Emp (Eno, Ename, Title, City)

Project (Pno, Pname, Budget, City)

Pay (Title, Salary)

Works (Eno, Pno, Resp, Dur)

 Definition of Project

CREATE TABLE Project (Pno CHAR(3), Pname VARCHAR(20), Budget DECIMAL(10,2)DEFAULT 0. City CHAR(9));

 Assume others are defined similarly.

Allowable Data Types

 Numeric  INT, SHORTINT  REAL (FLOAT), DOUBLE PRECISION  DECIMAL( i , j )  Character-string  CHAR( n ), VARCHAR( n )  Bit-string  BIT( n ), BIT VARYING( n )  Date  YYYY-MM-DD  Time  HH:MM:SS  Timestamp  both DATE and TIME fields plus a minimum of six positions for fractions of seconds

4-

Example

 Given

Emp (Eno, Ename, Title, City)

Project (Pno, Pname, Budget, City)

Pay (Title, Salary)

Works (Eno, Pno, Resp, Dur)

 Enhance the previous definition of Project :

CREATE TABLE Project (Pno CHAR(3), Pname VARCHAR(20), Budget DECIMAL(10,2)DEFAULT 0. City CHAR(9)); PRIMARY KEY (PNO));

 REFERENTIALLY TRIGGERED ACTION

 Referential integrity: A key of one relation appears as an attribute (foreign key) of another relation.  Example: Emp (Eno, Ename, Title, City) Pay (Title, Salary)  Deletion or update of primary key tuple requires action on the foreign key tuple. Specify constraint on delete or update.  How to manage? ➠ reject ➠ cascade: (on delete) automatically remove foreign keys if a referenced key is removed or (on update) change the foreign key value to the new value of the referenced key ➠ set null ➠ set default

Referential Constraints

4-

Example

Emp (Eno, Ename, Title, City) Project (Pno, Pname, Budget, City) Works (Eno, Pno, Resp, Dur)

 Definition of deposit CREATE TABLE Works ( Eno CHAR(3) , Pno CHAR(3) , Resp CHAR(15), Dur INT, PRIMARY KEY (Eno,Pno), FOREIGN KEY (Eno) REFERENCES Emp(Eno) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (Pno) REFERENCES Project(Pno));

Other SQL DDL Commands

 DROP SCHEMA

 Delete an entire schema  CASCADE: delete all the tables in the schema  RESTRICT: delete only if empty

 DROP TABLE

 RESTRICT: delete only if not referenced in a constraint

 ALTER TABLE

 change definition

4-

Example Simple Queries

 List names of all employees.

SELECT Ename FROM Emp

 List names of all projects together with their

budgets.

SELECT Pname, Budget FROM Project

 Find all cities where at least one project exists.

SELECT DISTINCT City FROM Project

Emp (Eno, Ename, Title, City) Project (Pno, Pname, Budget, City) Pay (Title, Salary) Works (Eno, Pno, Resp, Dur)

Queries With Predicates

 Find all professions that make more than $50,000.

SELECT Title FROM Pay WHERE Salary > 50000

 Find all employees who work on a project as

managers for longer than 17 months.

SELECT Eno FROM Works WHERE Dur > 17 AND Resp=‘Manager’

Emp (Eno, Ename, Title, City) Project (Pno, Pname, Budget, City) Pay (Title, Salary) Works (Eno, Pno, Resp, Dur)

4-

Ordering the Results

 Find the names and budgets of all projects with

budget greater than $250,000 and order the result

in ascending order of budget values.

SELECT Pname, Budget FROM Project WHERE Budget > 250000 ORDER BY Budget

 Default is ascending order, but descending order can be specified by the DESC keyword.

Emp (Eno, Ename, Title, City) Project (Pno, Pname, Budget, City) Pay (Title, Salary) Works (Eno, Pno, Resp, Dur)

Queries Over Multiple Relations

 List the name and titles of all employees who

work on a project for more than 17 months.

SELECT Ename, Title FROM Emp, Works WHERE Dur > 17 AND Emp.Eno = Works.Eno

 Find the name and titles of all employees who

work on a project located in Waterloo.

SELECT Ename, Title FROM Emp E,Works W,Project P WHERE P.City = ‘Waterloo’ AND E.Eno = W.Eno AND W.Pno = P.Pno

Emp (Eno, Ename, Title, City) Project (Pno, Pname, Budget, City) Pay (Title, Salary) Works (Eno, Pno, Resp, Dur)

4-

Queries With Set Operators

 Find all cities where there is either an employee or a project. (SELECT City FROM Emp) UNION (SELECT City FROM Project)  Find all cities in which an employee works but no projects are located. (SELECT City FROM Emp) EXCEPT (SELECT City FROM Project)

Emp (Eno, Ename, Title, City) Project (Pno, Pname, Budget, City) Pay (Title, Salary) Works (Eno, Pno, Resp, Dur)

Queries With Set Operators

 Find all cities where there is both an employee and a project.

(SELECT City FROM Emp) INTERSECT (SELECT City FROM Project)

 List names of all projects and employees in

Waterloo.

(SELECT Ename FROM Emp WHERE City = ‘Waterloo’) UNION ALL (SELECT Pname FROM Project WHERE City = ‘Waterloo’)

Emp (Eno, Ename, Title, City) Project (Pno, Pname, Budget, City) Pay (Title, Salary) Works (Eno, Pno, Resp, Dur)

4-

 Queries within the WHERE clause of an outer query

SELECT FROM WHERE OPERATOR (SELECT FROM WHERE)

 There can be multiple levels of nesting

 These can usually be written using other constructs

(UNION, JOIN, etc).

 Three operators: IN, (NOT) EXISTS, [CONTAINS]

Queries With Nested Structures

SELECT

FROM

WHERE A 1 , ..., A (^) n IN (SELECT A 1 , ..., A (^) n FROM R 1 , ..., R (^) m WHERE P )

 Semantics: Tuples with attributes A 1 , ..., An are

found in the relation that is returned as a result of

the calculation of the inner query.

 Other comparison operators {<, <=, >, >=, <>} can

be used with ALL or with ANY in place of IN.

“IN” Construct

4-

“IN” Construct Example

 Find the names of all the projects that have budgets greater than all projects in Calgary.

SELECT Pname FROM Project WHERE Budget > ALL (SELECT Budget FROM Project WHERE City = ‘Calgary’)  One can use ANY if what is desired is to find projects whose budget is greater than some project in Calgary.

Emp (Eno, Ename, Title, City) Project (Pno, Pname, Budget, City) Pay (Title, Salary) Works (Eno, Pno, Resp, Dur)

SELECT

FROM

WHERE (NOT) EXISTS

(SELECT *

FROM R 1 , ..., R (^) m WHERE P )

 Semantics: For each tuple of the outer query,

execute the inner query; if there is at least one (no)

tuple in the result of the inner query, then retrieve

that tuple of the outer query.

 This accounts for the “there exists” type of queries

“EXISTS” Construct

4-

“EXISTS” Construct Example

 Find the names of employees who work in a city in

which some project is located.

SELECT Ename FROM Emp WHERE EXISTS (SELECT * FROM Project WHERE Emp.City=Project.City)

 Find the names of employees who work in a city in

which no project is located.

SELECT Ename FROM Emp WHERE NOT EXISTS (SELECT * FROM Project WHERE Emp.City=Project.City)

Emp (Eno, Ename, Title, City) Project (Pno, Pname, Budget, City) Pay (Title, Salary) Works (Eno, Pno, Resp, Dur)

“EXISTS” Construct Example

 Find the names and titles of all the employees who do

not work in a project.

SELECT Ename, Title FROM Emp WHERE NOT EXISTS (SELECT * FROM Works WHERE Emp.Eno = Works.Eno)

Emp (Eno, Ename, Title, City) Project (Pno, Pname, Budget, City) Pay (Title, Salary) Works (Eno, Pno, Resp, Dur)

4-

Tuple Calculus & SQL

 Example: “Find all the employees who

work on every project.”

 Tuple Calculus

{ e | e ∈ Emp ∧∀ p ∈ Project (∃ w ∈ Works

( p [Pno] = w [Pno] ∧ w [Pno]= e [Pno]))}

 Eliminate ∀: ∀ x F ( x ) ≡ ¬∃ x ¬ F ( x )

{ e | e ∈ Emp ∧¬∃ p ∈ Project ¬(∃ w ∈ Works

( p [Pno] = w [Pno] ∧ w [Pno] = e [Pno]))}

Tuple Calculus & SQL

 Convert to SQL Query

 Basic Rule: One level of nesting for each “¬ ∃”

 The corresponding SQL query becomes:

SELECT *

FROM Emp WHERE NOT EXISTS (SELECT Pno FROM Project WHERE NOT EXISTS (SELECT * FROM Works WHERE Project.Pno=Works.Pno AND Emp.Eno = Works.Eno))

4-

“CONTAINS” Construct

(SELECT A 1 , ..., A (^) n FROM R 1 , ..., R (^) m WHERE P ) CONTAINS (SELECT B 1 , ..., B (^) p FROM S 1 , ..., S (^) r WHERE Q )

 Semantics: Compare the result relations of the two

queries and return TRUE if the second one is a

subset of the first.

“CONTAINS” Construct Example

 Find all the employees who work on all the projects

located in Edmonton.

SELECT * FROM Emp WHERE ( (SELECT Pno FROM Works WHERE Emp.Eno = Works.Eno) CONTAINS (SELECT Pno FROM Project WHERE City = ‘Edmonton’))

Emp (Eno, Ename, Title, City) Project (Pno, Pname, Budget, City) Pay (Title, Salary) Works (Eno, Pno, Resp, Dur)

4-

Aggregate Query Examples

 Find the names of projects which have budgets

greater than the average budget of all the projects.

SELECT Pname FROM Project WHERE Budget > (SELECT AVG(Budget) FROM Project)

Emp (Eno, Ename, Title, City) Project (Pno, Pname, Budget, City) Pay (Title, Salary) Works (Eno, Pno, Resp, Dur)

Grouping Queries

 Group the results according to a set of attributes

SELECT A (^) i , …, A (^) n FROM R 1 , ..., R (^) m WHERE P GROUP BY A (^) j …, A (^) k

 Rules:

 All of the attributes in the SELECT clause that are not involved in an aggregation operation have to be included in the GROUP BY clause.  GROUP BY can have more attributes ( kn )

4-

Predicates on Groups

 Group the results according to a set of attributes if

they satisfy a certain condition

SELECT A (^) i , …, A (^) n FROM R 1 , ..., R (^) m WHERE P GROUP BY A (^) j …, A (^) k HAVING Q

 Rules:

 Q must have a single value per group.  An attribute in Q has to either appear in an aggregation operator or be listed in the GROUP BY

Grouping Query Examples

 Find the cities in which more than 2 employees live. SELECT City FROM Emp GROUP BY City HAVING COUNT() > 2  Find the projects on which more than 2 employees share a responsibility. SELECT DISTINCT Pno FROM Works GROUP BY Pno,Resp HAVING COUNT() > 2

Emp (Eno, Ename, Title, City) Project (Pno, Pname, Budget, City) Pay (Title, Salary) Works (Eno, Pno, Resp, Dur)