CSE 480: Database Systems

1
CSE 480 Database Systems

• Lecture 10 SQL - DML

Reference Read Chapter 4 of the textbook
2
Review
SQL
DML
DDL
UPDATE
RETRIEVAL
SELECTFROMWHEREGROUP BYHAVINGORDER BY
CREATE
DROP
ALTER
INSERT
DELETE
UPDATE
3
SQL DML (Updates)

• There are three SQL commands to update the
  database state
• INSERT
• DELETE
• UPDATE

4
COMPANY Database Schema
5
INSERT

• Add one or more tuples to a relation
• Attribute values must be listed in the same order
  as the attributes specified in the CREATE TABLE
  command
• INSERT INTO EMPLOYEE VALUES
  ('Richard','K','Marini','653298653','30-DEC-52',
  '98 Oak Forest,Katy,TX','M',37000,'987654321',
  4)

6
INSERT

• An alternate form of INSERT specifies explicitly
  the attribute names that correspond to values in
  the new tuple
• Attributes with NULL values can be left out
• INSERT INTO EMPLOYEE (FNAME, LNAME, SSN)
  VALUES ('Richard', 'Marini', '653298653')

7
Disable Foreign Key in MySQL

• Set foreign_key_checks 0
• Useful to insert a tuple for subordinate before
  inserting the tuple for supervisor
• INSERT INTO EMPLOYEE (FNAME, LNAME, SSN,
  SUPER_SSN) VALUES ('Rob', Stanley',
  '153298653', '431231123')

8
INSERT

• Insertion of multiple tuples resulting from a
  query into a relation
• Example Suppose we want to create a temporary
  table that has the name, number of employees, and
  total salaries for each department.
• CREATE TABLE DEPTS_INFO (DEPT_NAME VARCHAR(
  10), NO_OF_EMPS INTEGER,
  TOTAL_SAL INTEGER)
• INSERT INTO DEPTS_INFO (DEPT_NAME,
  NO_OF_EMPS, TOTAL_SAL) SELECT DNAME, COUNT (),
  SUM (SALARY) FROM DEPARTMENT,
  EMPLOYEE WHERE DNUMBERDNO GROUP BY DNAME

9
DELETE

• Removes tuples from a relation
• Includes a WHERE-clause to select the tuples to
  be deleted
• Examples
• DELETE FROM EMPLOYEE WHERE Lname'Brown
• DELETE FROM EMPLOYEE WHERE SSN'123456789
• DELETE FROM EMPLOYEE WHERE Dno IN
  (SELECT Dnumber FROM DEPARTMENT WHE
  RE Dname'Research')
• DELETE FROM EMPLOYEE

10
UPDATE

• Used to modify attribute values of one or more
  selected tuples
• UPDATE table_name
• SET set-clause
• WHERE where-clause
• WHERE-clause selects the tuples to be modified
• SET-clause specifies the attributes to be
  modified and their new values
• Each command modifies tuples in the same relation

11
UPDATE

• Example Change the location and controlling
  department number of project number 10 to
  'Bellaire' and 5, respectively
• UPDATE PROJECT
• SET PLOCATION 'Bellaire', DNUM 5
• WHERE PNUMBER 10

12
UPDATE

• Example Give all employees in the 'Research'
  department a 10 raise in salary
• UPDATE EMPLOYEE SET SALARY SALARY 1.1
  WHERE DNO IN (SELECT DNUMBER
  FROM DEPARTMENT WHERE DNAME'Research')

13
Retrieval Queries in SQL

• Basic form of the SQL retrieval queries
• SELECT ltattribute listgt
• FROM lttable listgt
• WHERE ltconditiongt
• ltattribute listgt is a list of attribute names
  whose values are to be retrieved by the query
• lttable listgt is a list of the relation names
  required to process the query
• ltconditiongt is a conditional (Boolean) expression
  that identifies the tuples to be retrieved by the
  query

14
Simple SQL Queries (from 1 Table)

• Query Retrieve the birthdate and address of the
  employee 'John B. Smith

SELECT FROM EMPLOYEE WHERE
15
Simple SQL Queries (from 1 Table)

• Query Retrieve the birthdate and address of the
  employee 'John B. Smith

SELECT Bdate, Address FROM EMPLOYEE WHERE
16
Simple SQL Queries (from 1 Table)

• Query Retrieve the birthdate and address of the
  employee 'John B. Smith

SELECT Bdate, Address FROM EMPLOYEE WHERE
Fname'John' AND Minit'B' AND Lname'Smith'
17
Simple SQL Queries (from 1 Table)
SELECT Bdate, Address FROM EMPLOYEE WHERE
Fname'John' AND Minit'B' AND Lname'Smith'
Another way to interpret this
For each row in Employee table If
row.Fname'John' AND row.Minit'B' AND
row.Lname'Smith' then print row.Bdate,
row.Address
18
Simple SQL Queries (from 1 Table)

• Query Retrieve the name and address of employees
  who work for department number 5

SELECT Fname, Lname, AddressFROM
EmployeeWHERE Dno 5
19
Simple SQL Queries (from 1 Table)

• Query Retrieve all the rows and columns in the
  Employee table

SELECT FROM Employee
Wildcard () in the SELECT clause means retrieve
all columns No WHERE clause means all the rows
will be retrieved
20
Simple SQL Queries (from 2 Tables)

• Query Retrieve the first name, last name and
  address of all employees who work for the
  'Research' department

21
Join Operation

• SQL uses JOIN operation to combine information
  from two or more tables

ID Degree
1 BS
2 BS
2 MS
3 MS
S
ID Name
1 John
2 Mary
3 Bob
R
Join on R. ID S.ID
R.ID S.ID R.Name S.Degree
1 1 John BS
2 2 Mary BS
2 2 Mary MS
3 3 Bob MS
22
Join Operation
ID Degree
1 BS
2 BS
2 MS
3 MS
S
ID Name
1 John
2 Mary
3 Bob
R
Join on R. ID S.ID
R.ID S.ID R.Name S.Degree
1 1 John BS
2 2 Mary BS
2 2 Mary MS
3 3 Bob MS
SELECT FROM R, S WHERE R.ID S.ID
In this case, a row in R is merged with a row
in S if their IDs are the same
23
Simple SQL Queries (from 2 Tables)

• Query Retrieve the first name, last name and
  address of all employees who work for the
  'Research' department

SELECT FROM Employee, DepartmentWHERE
24
Simple SQL Queries (from 2 Tables)

• Query Retrieve the first name, last name and
  address of all employees who work for the
  'Research' department

SELECT Fname, Lname, AddressFROM Employee,
DepartmentWHERE
25
Simple SQL Queries (from 2 Tables)

• Query Retrieve the first name, last name and
  address of all employees who work for the
  'Research' department

SELECT Fname, Lname, AddressFROM Employee,
DepartmentWHERE Dname'Research'
26
Simple SQL Queries (from 2 Tables)

• Query Retrieve the first name, last name and
  address of all employees who work for the
  'Research' department

SELECT Fname, Lname, AddressFROM Employee,
DepartmentWHERE Dname'Research' AND
DnumberDno
27
Simple SQL Queries (from 2 Tables)

• Query Retrieve the name of each project and the
  name of the department that controls it

SELECT FROM Department, Project WHERE
28
Simple SQL Queries (from 2 Tables)

• Query Retrieve the name of each project and the
  name of the department that controls it

SELECT Pname, Dname FROM Department,
Project WHERE
29
Simple SQL Queries (from 2 Tables)

• Query Retrieve the name of each project and the
  name of the department that controls it

SELECT Pname, Dname FROM Department,
Project WHERE DnumDnumber
30
Exercise
List the names of all employees and their
corresponding department names
31
Exercise
List the names of managers for each department
32
Simple SQL Queries (from 3 Tables)

• Query For every project located in 'Stafford',
  list the project number, controlling department
  number, and the department manager's last name,
  address, and birthdate

SELECT FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE
33
Simple SQL Queries (from 3 Tables)

• Query For every project located in 'Stafford',
  list the project number, controlling department
  number, and the department manager's last name,
  address, and birthdate

SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS
FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE
34
Simple SQL Queries (from 3 Tables)

• Query For every project located in 'Stafford',
  list the project number, controlling department
  number, and the department manager's last name,
  address, and birthdate

SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS
FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE
PLOCATION'Stafford'
35
Simple SQL Queries (from 3 Tables)

• Query For every project located in 'Stafford',
  list the project number, controlling department
  number, and the department manager's last name,
  address, and birthdate

SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS
FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE
PLOCATION'Stafford AND DNUMDNUMBER AND
MGRSSNSSN
36
Aliases

• If a query refers to two or more attributes with
  the same name but in different relations, we must
  qualify the attribute name with the relation name
  by prefixing the relation name to the attribute
  name

S
ID Degree
1 BS
2 BS
2 MS
3 MS
ID Name
1 John
2 Mary
3 Bob
R
SELECT FROM R, S WHERE R.ID S.ID
R.ID S.ID R.Name S.Degree
1 1 John BS
2 2 Mary BS
2 2 Mary MS
3 3 Bob MS
What if we want to join the same table? Use tuple
variables
37
Tuple Variables

• Query For each employee, retrieve the employee's
  name and the name of his or her immediate
  supervisor

• SELECT FROM EMPLOYEE E, EMPLOYEE SWHERE
• E and S are tuple variables

38
Tuple Variables

• Query For each employee, retrieve the employee's
  name and the name of his or her immediate
  supervisor

• SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAMEFROM
  EMPLOYEE E, EMPLOYEE SWHERE
• E and S are tuple variables

39
Tuple Variables

• Query For each employee, retrieve the employee's
  name and the name of his or her immediate
  supervisor

• SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAMEFROM
  EMPLOYEE E, EMPLOYEE SWHERE E.SuperSSN
  S.SSN
• E and S are tuple variables

40
Exercise

• Query Find the names of employees who earn more
  than their supervisors

SELECT E.FNAME, E.LNAMEFROM EMPLOYEE E,
Employee SWHERE E.SUPERSSNS.SSN AND E.SALARY
gt S.SALARY
41

• Find the names of the department where John Smith
  is currently working.
• SELECT DNAME
• FROM EMPLOYEE, DEPARTMENT
• WHERE DNODNUMBER and FNAMEJohn and
  LNAMESmith
• Find names of the managers earning more than 100K
• SELECT FNAME, LNAME
• FROM EMPLOYEE, DEPARTMENT
• WHERE DNODNUMBER and MGRSSNSSN and SALARYgt
  10000
• Find names of employees worked on multiple (at
  least 2) projects.
• SELECT name
• FROM EMPLOYEE A, EMPLOEE B, DEPARTMENT C,
  DEPARTMENT D
• WHERE A.SSN B.SSN AND C.DNUM not D.DNUM
• OR
• SELECT FNAME, LNAME
• FROM EMPLOYEE
• WHERE (SELECT COUNT()