week13 lec1 sql
TRANSCRIPT
-
8/8/2019 Week13 Lec1 SQL
1/18
SQL
Summarizing underlying rows:Select s.ssn, s.lname, count(r.lname)
from em lo ee s em lo ee r
where s.ssn=r.superssn
group y s.ssn, s. name;
s.ssn s.lname r.lname
333445555 Wong 3
987654321 Wallace 2
1
-
8/8/2019 Week13 Lec1 SQL
2/18
SQL
Eliminating Groups from the result:Select s.ssn, s.lname, count(r.lname)
from em lo ee s em lo ee r
where s.ssn=r.superssn
group y s.ssn, s. name
having count(r.lname) < 3;
s.ssn s.lname count(r.lname)
888665555 Borg 2
987654321 Wallace 2
2
-
8/8/2019 Week13 Lec1 SQL
3/18
SQL
Special joins:
Outer join table
this example is a right outer join - lists every department
regardless of whether or not it has a manager
SELECT department.dnumber, department.dname,
employee.fname, employee.lnameFROM
employee RIGHT OUTER JOIN department
=. .
3
-
8/8/2019 Week13 Lec1 SQL
4/18
SQL
Special joins:
Inner join table
this example is an inner join - lists employees and their
departments
. , . , . ,
employee.lname
FROM
de artment INNER JOIN em loyee
ON department.dnumber = employee.dno;
SELECT department.dnumber, department.dname, employee.fname,
employee.lnameFROM department, employee
WHERE department.dnumber = employee.dno;
4
-
8/8/2019 Week13 Lec1 SQL
5/18
SQL
Special joins:
Inner join table with a Where clause
this example is an inner join - lists employees and their
departments, but only for the Research department
SELECT department.dnumber, department.dname,
employee.fname, employee.lnameFROM
department INNER JOIN employee
=. .
WHERE dname = 'Research';
5
-
8/8/2019 Week13 Lec1 SQL
6/18
SQL
Special joins:
Inner join table with a Where clause
this example is an inner join - lists employees and their
departments, but only for the Research department
SELECT department.dnumber, department.dname,
employee.fname, employee.lnameFROM department, employee
WHERE department.dnumber = employee.dno and
= ' '
6
-
8/8/2019 Week13 Lec1 SQL
7/18
SQL
Special joins:
Inner joins and a Where clause
this example lists employees working on a project that is
not where their department is located
SELECT employee.fname, employee.lname, works_on.pno, project.plocation
FROMpro ec
(employee INNER JOIN works_on ON employee.ssn = works_on.essn)
ON project.pnumber = works_on.pno
plocation not in (select dlocation from dept_locations where dnumber = dno);
7
-
8/8/2019 Week13 Lec1 SQL
8/18
SQL
Comparison:
SELECT employee.ssn, employee.fname, employee.lname,
project.pnumber, project.plocation
FROM em lo ee ro ect works on _
WHERE
employee.ssn = works_on.essn and. = _ .
plocation NOT IN
(SELECT dlocation FROM dept_locations WHERE
dnumber=employee.dno);
8
-
8/8/2019 Week13 Lec1 SQL
9/18
SQL
.
Insert
DeleteINSERT INTO employee ( fname, lname, ssn, dno )
VALUES ( "Joe", "Smith", 909, 1);
' '_ , , .
Create table depts_info (dept_name varchar(15), no_o_emp
n eger, o a _sa ec ma ,
Insert into depts_info select dname, count(*), sum(salary)
9
from department d, employee e where d.dnumber=e.dno
group by dname
-
8/8/2019 Week13 Lec1 SQL
10/18
SQL
Insert
DeleteUPDATE employee SET salary = 100000
WHERE ssn=909;
Set salary = salary*1.1
Where dno in ( select dnumber
rom epar men w ere name= esearc
DELETE FROM employee WHERE ssn=909;
10
-
8/8/2019 Week13 Lec1 SQL
11/18
SQL
Views:
Use a Create View command
essentially a select specifying the data that makes up the view
,
CREATE VIEW Enames lname fname
AS SELECT LNAME, FNAME
FROM EMPLOYEE
11
-
8/8/2019 Week13 Lec1 SQL
12/18
SQL
CREATE VIEW DEPT_INFO (DEPT_NAME,
NO_OF_EMPS,
TOTAL_SAL)
AS SELECT DNAME, COUNT(*), SUM(SALARY)
FROM DEPARTMENT EMPLOYEE
WHERE DNUMBER = DNO
GROUP BY DNAME;
12
-
8/8/2019 Week13 Lec1 SQL
13/18
SQL
Views are very common in business systems
users view of data is simplified
reate ew mp ro as se ect name, name, pno rom
employee inner join .
a form of securit - user sees onl the data he/she needs to
As complex as needed
if the primary key is preserved, updates to a base table through a
view is possible
13
-
8/8/2019 Week13 Lec1 SQL
14/18
SQL
Assertions can be used for some constraints
e.g. rea e sser on ... ... Executed andenforced by DBMS
Constraint: The salary of an employee must not be greater than
the salary of the manager of the department that the employee
works for.
CREATE ASSERTION salary_constraint
* ,employee m, department d
where e.salary > m.salary and e.dno=d.dnumber and
14
d.mgrssn=m.ssn));
-
8/8/2019 Week13 Lec1 SQL
15/18
SQL
ssert ons n o vers on o
Assert
trigger
Assert statementAssert SALARY_Constraint on employee e,
,
not (e.salary > m.salary and e.dno=d.dnumber and
d.mgrssn=m.ssn);Trigger
DEFINE trigger SALARY_TRIGGER on
em lo ee e em lo ee m de artment d:
(e.salary > m.salary and e.dno=d.dnumber and
d.mgrssn=m.ssn
15
_ _ .
-
8/8/2019 Week13 Lec1 SQL
16/18
SQL
CREATE TABLE T4 (a INTEGER, b CHAR(10));
c , ;
CREATE TRIGGER trig1
AFTER INSERT ON T4
FOR EACH ROW WHEN (newRow.a
-
8/8/2019 Week13 Lec1 SQL
17/18
SQL
Security: Grant and Revoke are used to specify user privileges
Grant select, update on Employee to Diane;
_
Embedded SQL: SQL can be placed within 3GL programs
ransac ons: sys ems mp emen e proper es
17
-
8/8/2019 Week13 Lec1 SQL
18/18
SQL
Exercises 8.18 to 8.20 and 8.24
Homework
18