Relational Model
EVEN More SQL!
Copyright By PowCoder代写 加微信 powcoder
More Complex SQL Retrieval Queries
Additional features allow users to specify more complex retrievals from database:
Nested queries
Joined tables
Outer joins
Aggregate functions
Views in SQL
A view is a single table defined by other tables
A virtual table
Can be actual tables or other views
Use the CREATE VIEW command to create a new view in the database
Uses a SELECT statement to build the view
Any SELECT statement can form the basis of a view
Views are dynamic
Not copies of the underlying tables
Kept up-to-date with changes to the underlying tables
Can be very efficient for joining commonly-used data
Can store “virtual” values that only exist upon querying
Views in SQL
Example view creation:
CREATE VIEW WORKS_ON_V1
AS SELECT Fname, Lname,Pname, Hours
FROM Employee, Project, Works_on
WHERE Ssn=Essn AND Pno=Pnumber;
CREATE VIEW DEPT_INFO(Dept_name, Num_emps, Total_sal)
AS SELECT Dname, COUNT(*), SUM(Salary)
FROM Department, Employee
WHERE Dnumber=Dno
GROUP BY Dname;
Views in SQL
Specify SQL queries on a view
View always up-to-date
Responsibility of the DBMS and not the user
Several different approaches to storing the view
Using Views in SQL
Create a view called PROGS consisting of the EMPNO, name and salary of all programmers. Include the locations of their departments.
CREATE VIEW Progs (EMPNO, Name,
Salary, Homebase)
AS SELECT EMPNO, Lname, Salary, Dlocation
FROM Employee EMP, Department DEPT,
Dept_Locations DL
WHERE EMP.Dno = DEPT.Dnumber
AND DL.Dnumber = DEPT.Dnumber
AND EMP.Job = ‘PROGRAMMER’;
Using Views in SQL
Using the PROGS view, find the average salary of programmers in Columbus.
SELECT AVG(Salary)
FROM Progs
WHERE Homebase = ‘Columbus’;
Nested Queries
Nested queries
Complete select-from-where blocks within WHERE clause of another query
Comparison operator IN
Compares value v with a set (or multiset) of values V
Evaluates to TRUE if v is one of the elements in V
Select Using “IN”
List the name of employees in departments 25, 47, or 53
SELECT Lname, Fname
FROM Employee
WHERE Dno IN (25, 47, 53);
Nested Queries
Use other comparison operators to compare a single value v
= ANY (or = SOME) operator
Returns TRUE if the value v is equal to some value in the set V and is hence equivalent to IN
Other operators that can be combined with ANY (or SOME): >, >=, <, <=, and <>
Select Using “ANY”
List the name of employees not in departments 25, 47, or 53
SELECT Lname, Fname
FROM Employee
WHERE Dno <> ANY (25, 47, 53);
Nested Queries
Can also use tuples instead of a single value in the where condition:
SELECT Essn
FROM Works_On
WHERE (Pno, Hours) IN
(SELECT Pno, Hours
FROM Works_On
WHERE Essn=‘123456789’);
Find all employee SSN who work on the same
project and same hours as employee ‘123456789’
Nested Queries
Find all the project names for projects being managed by employee last name ‘Wong’:
SELECT Pname
FROM Project
WHERE Pnumber IN
(SELECT Pnumber
FROM Project, Department, Employee
WHERE Dnum=Dnumber AND
Mgr_ssn=Ssn AND Lname=‘Wong’);
Nested Queries (cont’d)
When an inner query references some attributes of a relation declared in an outer query, the queries are correlated:
SELECT E.Fname, E.Lname
FROM Employee AS E
WHERE E.Ssn IN
(SELECT Essn
FROM Dependent AS D
WHERE E.Fname = D.Dependent_name
AND E.Sex = D.Sex);
Think of the nested query as being run once for each tuple in the outer query
Nested Queries (cont’d)
Correlated nested queries can always be rewritten as a single-block query with a bit of thought:
SELECT E.Fname, E.Lname
FROM Employee AS E, Dependent AS D
WHERE E.Ssn = D.Essn AND E.Sex = D.Sex
AND E.Fname = D.Dependent_name;
This is equivalent to the block query on previous slide.
WHERE Condition – Test For Existence
EXISTS and NOT EXISTS
Typically used in conjunction with a correlated nested query
Check whether the result of a correlated nested query is empty or not
WHERE Condition – EXISTS
Use the EXISTS function to check whether a nested query is not empty:
SELECT E.Fname, E.Lname
FROM Employee AS E
WHERE EXISTS
FROM Dependent AS D
WHERE E.Fname = D.Dependent_name
AND E.Ssn = D.Essn
AND E.Sex = D.Sex);
Find all employees who have a dependent with the
same first name and same sex as the employee
WHERE Condition – NOT EXISTS
Use NOT EXISTS function to check whether a nested query is empty:
SELECT E.Fname, E.Lname
FROM Employee AS E
WHERE NOT EXISTS
FROM Dependent AS D
WHERE E.Ssn = D.Essn);
Find all employees who do not have a dependent
WHERE Condition – Existence examples
List the names of employees who work in departments in Columbus:
SELECT Lname, Fname
FROM Employee EMP
WHERE EXISTS
(SELECT *
FROM Department DEPT
WHERE EMP.Dno = DEPT.Dnumber
AND LOC = ‘Columbus’ );
List the names of employees who do not work in departments in Columbus:
SELECT Lname, Fname
FROM Employee EMP
WHERE NOT EXISTS
(SELECT *
FROM Department DEPT
WHERE EMP.Dno = DEPT.Dnumber
AND LOC = ‘Columbus’ );
Comparisons involving NULL
NULL has three possible meanings:
Unknown value
Withheld value
Not Applicable value
Often not possible to tell which meaning is intended
SQL itself does not distinguish between different meanings for NULL (need to deal with that in applications)
When making comparisons, SQL considers NULL to mean “Unknown”
Three-valued logic:
Three-Valued Logic
Comparisons involving NULL
Note that because NULL means “Unknown”, we can’t test for equality to NULL
FROM Employee
WHERE Super_ssn = NULL;
Instead we use “IS” or “IS NOT” when looking for NULL values
FROM Employee
WHERE Super_ssn IS NULL;
Aggregate Functions in SQL
Used to summarize information from multiple tuples into a single-tuple summary
Built-in aggregate functions
COUNT, SUM, MAX, MIN, and AVG
If NULLs exist in grouping attribute, separate NULL group is created
Functions can be used in the SELECT clause
Aggregate Functions in SQL
Calculate the average salary of clerks
SELECT avg(Salary)
FROM Employee
WHERE Job = ‘Clerk’;
How many different jobs are held by employees in department 50?
SELECT count(distinct Job)
FROM Employee
WHERE Dno = 50;
SQL Retrieval
SQL Query Syntax:
SELECT
FROM
[ GROUP BY
[ ORDER BY
Grouping Aggregate Functions
GROUP BY clause
Specifies grouping attributes
Aggregate functions are applied to each such group independently
Grouping Aggregate Functions – GROUP BY
List department names and the average salary of each department
SELECT Dname, avg(Salary)
FROM Employee E, Department D
WHERE E.Dno = D.Dnumber
GROUP BY Dname;
Like functions in relational algebra:
Dname avg(Salary) (Employee ⨝Dno=Dnumber Department)
Grouping Aggregate Functions – GROUP BY
GROUP BY clauses can contain more than one grouping:
SELECT Dno, COUNT(*), AVG(Salary)
FROM Employee
GROUP BY Dno, Super_ssn;
This would group together everyone who works in the same department and have the same manager
SQL Retrieval
SQL Query Syntax:
SELECT
FROM
[ GROUP BY
[ HAVING
[ ORDER BY
Grouping Aggregate Functions
GROUP BY clause
Specifies grouping attributes
Aggregate functions are applied to each such group independently
HAVING clause
Provides a condition on the summary information
Functions can be used in the HAVING clause to select certain GROUPs
Grouping Aggregate Functions – HAVING
Selects certain results based on the summary information
Ex., List departments (DNO, DNAME) in which the average employee salary < $25,000
SELECT Dno, Dname
FROM Employee E, Department D
WHERE E.Dno = D.Dnumber
GROUP BY Dno, Dname
HAVING avg(Salary) < 25000;
Grouping Aggregate Functions – HAVING
List departments (numbers) that employ more than 10 clerks:
SELECT Dno
FROM Employee
WHERE Job = ‘Clerk’
GROUP BY Dno
HAVING count(*) > 10;
Grouping Aggregate Functions – HAVING
We can mix nested selects into our HAVING clauses
SELECT Dno, AVG(Salary)
FROM Employee
GROUP BY Dno
HAVING AVG(Salary) >
(SELECT MIN(average)
FROM (SELECT AVG(Salary) AS average
FROM Employee GROUP BY Dno));
Find all departments and their average salaries
which aren’t the lowest average in the company
Grouping Aggregate Functions – HAVING
Another way of performing the previous SELECT
SELECT Dno, AVG(Salary)
FROM Employee,
(SELECT MIN(average) AS min_avg FROM
(SELECT AVG(Salary) AS average
FROM Employee GROUP BY DNO)
GROUP BY Dno
HAVING AVG(Salary) > MINA.min_avg;
Find all departments and their average salaries
which aren’t the lowest average in the company
Altogether Now…
For all departments in Columbus with average salary > $25,000, list the department number, name, and average salary ordered by average salary in descending order:
SELECT Dno, Dname, avg(Salary)
FROM Employee EMP, Department DEPT,
Dept_Locations DL
WHERE EMP.Dno = DEPT.Dnumber
AND DEPT.Dnumber = DL.Dnumber
AND DL.Dlocation = ‘Columbus’
GROUP BY Dno, Dname
HAVING avg(Salary) > 25000
ORDER BY 3 DESC;
SQL Retrieval
SQL Query Syntax:
SELECT
FROM
[ GROUP BY
[ HAVING
[ ORDER BY
More interesting SQL
Show the result of giving everyone in departments in Columbus a 10% pay raise. List the employee number and name
SELECT Ssn, Lname,
1.1 * Salary AS NEWSAL
FROM Employee EMP, Department DEPT,
Dept_Locations DL
WHERE EMP.Dno = DEPT.Dnumber
AND DEPT.Dnumber = DL.Dnumber
AND Dlocation = ‘Columbus’;
More interesting SQL
Give a 10% raise to all employees in Employee, whose employee number appears in the Candidates table.
UPDATE Employee
SET Salary = Salary * 1.1
WHERE EMPNO IN
(SELECT EMPNO
FROM Candidates);
More interesting SQL
Insert employee named ‘Jones’ with employee number 535 in department 51. Other attributes are null.
INSERT INTO Employee (Empno, Lname, Dno)
VALUES (535, ‘Jones’, 51);
More interesting SQL
Add all employees whose commission is greater than half their salary to the CANDIDATES relation
Given: CANDIDATES(EMPNO, NAME, DNO, SAL)
INSERT INTO Candidates
(SELECT EMPNO, Lname, Dno, Salary
FROM Employee
WHERE COMM > 0.5 * SAL);
More interesting SQL
Delete from Employee the employee with employee number 561.
DELETE FROM Employee
WHERE EMPNO = 561;
Delete from the Department table the departments having no employees.
DELETE FROM Department DEPT
WHERE (SELECT COUNT(*)
FROM Employee
WHERE Dno = DEPT.Dnumber) = 0;
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com