=========================preview======================
(IELM230)asgt5m.pdf
Back to IELM230 Login to download
======================================================
IELM 230 Industrial Data Systems, Fall 2005
Assignment 5. MODEL

SQL and Indexes

For each of the following questions, please use the Employee-Department-Projects Database tables that you also used in your lab. In each question, write the SQL query(s) to give the required answer. No need to show expected output (unless specifically asked).

Q1. Report the Last Name and Address of Employees who are not in Dept 5.

SELECT Lname, Address
FROM EMPLOYEE
WHERE Dno != 5


Q2. Report the SSN and Last Name of Employees who do not have a Son as a dependent.

SELECT Lname, Address
FROM EMPLOYEE
WHERE SSN NOT IN (SELECT ESSN
FROM DEPENDENT
WHERE Relationship = Son)

(You can also use NOT EXISTS).

Q3. Report SSN and Last name of all employees whose supervisor is female.

SELECT E.SSN, E.Lname
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.SuperSSN = S.SSN AND
S.Sex = F)


Q4. Report the SSN and Last name of employees who work on all projects.
[Hint: you can use the double-NOT EXISTS for this query, as discussed in class].

SELECT SSN, Lname
FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
FROM PROJECT
WHERE NOT EXISTS (SELECT *
FROM WORKS_ON
WHERE SSN = ESSN
AND PNo = Pnumber)


Q5. Create a view called EmpWorksOn with the following fields: SSN, Lname, Fname, Dept (of Employee), Project No, Project Name, Project Location, Hours per week.

CREATE VIEW EmpWorksOn AS
(SELECT SSN, Fname, Lname, Dno, Pno, Pname, PLocation, Hours
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE SSN = ESSN AND PNo = Pnumber)

[Note: some of you may also join DEPARTMENT table to get the Dname field]

Q6. Using the view created in Q5, write a SQL query to report SSN of those employees of Research Dept who spend more than 30 Hours per week on projects in Stafford.


SELECT SSN, sum(Hours) as TotalTimeInStafford
FROM EmpWorksOn, DEPARTMENT
WHERE Plocation = Stafford AND Dname = Research
GROUP BY SSN
HAVING sum(Hours) > 30


Q7. [Indexes] The HKUST ARR maintains the records of 30,000 students, past and present. Assume that the table containing this data was created using the following command:

CREATE TABLE Student (
Name char(50),
admitDate datetime,
Program int,
Department int,
FTorPT char(2),
Sex char(1),
HomePhone char(12),
HomeAddress char(150),
MobilePhone char(8),
SID char(10),
GraduationDate datetime,
PRIMARY KEY (SID)
)

The database file is stored in a hard disk formatted with 1024 Byte blocks. The DBMS uses 2 byte end-of-field separators, and 2-byte end-of-record separators. An int requires 4 Bytes, and a datetime requires 16 Bytes.
The main file operation delays include hard disk seek time = 10 msec, read/write time = 4 msec, and transfer time= 1 msec. The CPU time for transactions is negligible.

A primary index was created using SID, and a secondary index was created using Name.