Assignment #2

1. Create the following three tables and populate each table using SQL statements provided by the link(sql.txt). Suppose NO NULL values are allowed in the tables and assume that both startday and endday are in the past(Note that final grading might be based on a different dataset)

Person(ssn, pname, birthday).

Company(companyid, cname, location).

WorkFor(ssn, companyid, startday, endday, salary).

2. Write the following 10 intermediate-level queries as SQL SELECT statements.

(1) select the ssn of persons who have never worked for any company.

Expected answer) 777777777

(2) select the ssn of persons who have worked for IBM or for Microsoft.

Expected answer) 111111111, 222222222, 333333333, 444444444, 555555555

(3) select the ssn of persons who have worked for IBM and for Microsoft.

Expected answer) 111111111, 555555555

(4) select the names of the companies that John ever worked in.

Expected answer) IBM, Microsoft, Google

(5) select the highest salary that John ever got.

Expected answer) 120000

(6) select the ssn of persons who worked for IBM on 6/20/1983.

Expected answer) 333333333

(7) select the names of the employees of IBM who were born after 1/1/1970 and earn more than $100,000.

Expected answer) John

(8) list from the oldest to the youngest, the ssn of the persons who worked for IBM

Expected answer) 333333333, 111111111, 555555555

(9) select for each ssn, the number of companies that they ever worked for.

Expected answer)

111111111, 3

222222222, 1

333333333, 1

444444444, 2

555555555, 3

666666666, 1

(10) select the names of the companies that John and Smith have both worked for with overlapping period.

Expected answer) Microsoft, Google

? Submission

Please submit your answers(only SELECT statements without results) in a text file named with your WSU access id, for example, “aa1111.txt”, via Digital Dropbox on Blackboard. Note that answers on papers are not acceptable for this assignment.