Given the following relation schemas: EMPLOYEE(SSN, NAME, SEX, DNUMBER) DEPARTME

Question

Given the following relation schemas:

      EMPLOYEE(SSN, NAME, SEX, DNUMBER)

      DEPARTMENT(DNUMBER, DNAME, DMGRSSN)

      DLOCATION(DNUMBER, DLOCATION)

      PROJECT(PNUMBER, PNAME, PLOCATION, DNUM)

      WORKSON(SSN, PNUMBER, HOURS)

Write the following queries in SQL:

1. If an employee works on every project located in ‘Houston,’ then list the employee’s social security number and name.

2.    If an employee works on only the project(s) located in ‘Houston,’ then list the employee’s social security number and name.

3.    If an employee works on every project except the one(s) controlled by the department that has no location in ‘Houston,’ then list the employee’s social security number and name.

4.    If an employee works on exactly all projects located in ‘Houston,’ then list the employee’s social security number and name.

Explanation / Answer

1. Logic: make a join on DNUMBER and query with condition DLOCATION="Houston"

select SSN,NAME from EMPLOYEE,DLOCATION

where EMPLOYEE.DNUMBER=DLOCATION.DNUMBER and DLOCATION="Houston";

2. Logic: make a join on DNUMBER and query with condition PLOCATION="Houston"

select SSN,NAME from EMPLOYEE,PROJECT

where EMPLOYEE.DNUMBER=PROJECT.DNUMBER and PLOCATION="Houston";

3. join department and dlocation table and apply in query

select SSN,NAME from EMPLOYEE where DNUMBER in (select DEPARTMENT.DNUMBER from DEPARTMENT,DLOCATION where DEPARTMENT.DNUMBER=DLOCATION.DNUMBER and DLOCATION != "Houstan");

4. join employee and project table and query

  select SSN,NAME from EMPLOYEE,PROJECT where EMPLOYEE.DNUMBER=PROJECT.DNUMBER and PLOCATION = "Houstan";

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.