

































Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community for help and clear up your study doubts
Discover the best universities in your country according to Docsity users
Free resources
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
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
1 / 41
This page cannot be seen from the preview
Don't miss anything!
4-
Specify the properties that should hold in the result, not how to obtain the result Complex queries have procedural elements
SQL3 (pieces have started to appear; also known as SQL-99)
DDL statements DML 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
4-
CREATE TABLE Project (Pno CHAR(3), Pname VARCHAR(20), Budget DECIMAL(10,2)DEFAULT 0. City CHAR(9));
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-
CREATE TABLE Project (Pno CHAR(3), Pname VARCHAR(20), Budget DECIMAL(10,2)DEFAULT 0. City CHAR(9)); PRIMARY KEY (PNO));
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
4-
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));
Delete an entire schema CASCADE: delete all the tables in the schema RESTRICT: delete only if empty
RESTRICT: delete only if not referenced in a constraint
change definition
4-
SELECT Ename FROM Emp
SELECT Pname, Budget FROM Project
SELECT DISTINCT City FROM Project
Emp (Eno, Ename, Title, City) Project (Pno, Pname, Budget, City) Pay (Title, Salary) Works (Eno, Pno, Resp, Dur)
SELECT Title FROM Pay WHERE Salary > 50000
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-
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)
SELECT Ename, Title FROM Emp, Works WHERE Dur > 17 AND Emp.Eno = Works.Eno
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-
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)
(SELECT City FROM Emp) INTERSECT (SELECT City FROM Project)
(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-
SELECT FROM WHERE OPERATOR (SELECT FROM WHERE)
WHERE A 1 , ..., A (^) n IN (SELECT A 1 , ..., A (^) n FROM R 1 , ..., R (^) m WHERE P )
4-
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)
FROM R 1 , ..., R (^) m WHERE P )
4-
SELECT Ename FROM Emp WHERE EXISTS (SELECT * FROM Project WHERE Emp.City=Project.City)
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)
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-
Basic Rule: One level of nesting for each “¬ ∃”
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-
(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 )
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-
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)
SELECT A (^) i , …, A (^) n FROM R 1 , ..., R (^) m WHERE P GROUP BY A (^) j …, A (^) k
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 ( k ≥ n )
4-
SELECT A (^) i , …, A (^) n FROM R 1 , ..., R (^) m WHERE P GROUP BY A (^) j …, A (^) k HAVING Q
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
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)