Post date: Oct 11, 2018 9:10:54 AM
/!\ Midterm Practical on Wednesday
Chapter 4: Basic SQL (Continued)
SQL data definition and data types
Specifying constraints in SQL
Basic retrieval queries in SQL
INSERT, DELETE, and UPDATE statements in SQL
Simplified DML SQL:
SELECT column1, column2, ...
FROM table1, table2, ...
WHERE condition;
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
DELETE FROM table_name
WHERE condition;
Exercises: Using the above figures, answer the following questions using relational algebra and SQL:
List the names of all male employees.
List the Last name and Salary of all employees working in the department number 4.
List the name of the projects associated to the department number 5.
List the name of all sons of employees that are born in 1980.
List the name, birth-date, and salary of the manager of the company.
List the first name of all employees working in the Administration department.
List the SSN, first name, and last name of all employees working in Houston.
Retrieve the names of employees in department 5 who work more than 10 hours on the 'ProductX' project.
List the names of employees who have a dependent with the same first name as themselves.
Find the names of employees that are directly supervised by 'Franklin Wong'.
Online Exercises:
https://www.w3resource.com/sql-exercises/sql-boolean-operators.php
https://www.w3resource.com/sql-exercises/sql-joins-exercises.php
Solution:
∏Fname, Minit, Lname (σSex = "M" (EMPLOYEE))
SELECT Fname, Minit, Lname
FROM EMPLOYEE
WHERE Sex = "M";
∏Lname, Salary (σDno = 4 (EMPLOYEE))
SELECT Lname, Salary
FROM EMPLOYEE
WHERE Dno = 4
∏Pname (σDnum = 5 (PROJECT))
SELECT Pname
FROM PROJECT
WHERE Dnum = 5
∏Dependent_name (σ((Bdate >= "1980-01-01" AND Bdate <= "1980-12-31") AND Relationship = "Son")(DEPENDENT))
SELECT Dependent_name
FROM DEPENDENT
WHERE (Bdate >= "1980-01-01" AND Bdate <= "1980-12-31") AND Relationship = "Son";
or
SELECT Dependent_name
FROM DEPENDENT
WHERE (Bdate BETWEEN "1980-01-01" AND "1980-12-31") AND Relationship = "Son";
∏Fname, Minit, Lname, Bdate, Salary (σ(Super_ssn = NULL)(EMPLOYEE))
SELECT Fname, Minit, Lname, Bdate, Salary
FROM EMPLOYEE
WHERE Super_ssn IS NULL;
∏Fname (σDname = "Administration" (EMPLOYEE ⨝Dno=Dnumber DEPARTMENT))
SELECT Fname
FROM EMPLOYEE, DEPARTMENT
WHERE Dno = Dnumber AND Dname = "Administration";
or
SELECT Fname
FROM EMPLOYEE e, DEPARTMENT d
WHERE e.Dno = d.Dnumber AND Dname = "Administration";
or
SELECT Fname
FROM EMPLOYEE INNER JOIN DEPARTMENT ON Dno = Dnumber
WHERE Dname = "Administration";
∏SSN, Fname, Lname (σDlocation = "Houston" (EMPLOYEE ⨝Dno=Dnumber DEPT-LOCATIONS))
SELECT SSN, Fname, Lname
FROM EMPLOYEE, DEPT-LOCATIONS
WHERE Dno = Dnumber AND Dlocation = "Houston";
or
SELECT SSN, Fname, Lname
FROM EMPLOYEE INNER JOIN DEPT-LOCATIONS ON Dno = Dnumber
WHERE Dlocation = "Houston";
∏Fname, Minit, Lname (σDno = 5 AND Hours > 10 AND Pname = "ProductX"(EMPLOYEE ⨝SSN=Essn WORKS-ON ⨝Pno=Pnumber PROJECT))
SELECT Fname, Minit, Lname
FROM EMPLOYEE, WORKS-ON, PROJECT
WHERE SSN=Essn AND Pno=Pnumber AND Dno = 5 AND Hours > 10 AND Pname = "ProductX";
or
SELECT Fname, Minit, Lname
FROM EMPLOYEE INNER JOIN WORKS-ON ON SSN=Essn INNER JOIN PROJECT ON Pno=Pnumber
WHERE Dno = 5 AND Hours > 10 AND Pname = "ProductX";
Another way to write it:
DATA ← EMPLOYEE ⨝SSN=Essn WORKS-ON ⨝Pno=Pnumber PROJECT
CONDITION ← σDno = 5 AND Hours > 10 AND Pname = "ProductX"(DATA)
∏Fname, Minit, Lname (CONDITION)
∏Fname, Minit, Lname (σFname = Dependent_name (EMPLOYEE ⨝SSN=Essn DEPENDENT))
SELECT Fname, Minit, Lname
FROM EMPLOYEE, DEPENDENT
WHERE SSN = Essn AND Fname = Dependent_name;
∏E1.Fname, E1.Minit, E1.Lname(σ(E2.Fname = "Franklin" AND E2.Lname= "Wong")(EMPLOYEE E1 ⨝Super_ssn=SSN EMPLOYEE E2))
SELECT e1.Fname, e1.Minit, e1.Lname
FROM EMPLOYEE e1, EMPLOYEE e2
WHERE e1.Super_ssn = e2.SSN AND e2.Fname = "Franklin" AND e2.Lname= "Wong";