Hi Guys, New to sql and need help with this: EMPLOYEE(SSN, NAME, SEX, DNUMBER) D
ID: 3627991 • Letter: H
Question
Hi Guys,
New to sql and need help with this:
EMPLOYEE(SSN, NAME, SEX, DNUMBER)
DEPARTMENT(DNUMBER, DNAME, DMGRSSN)
DLOCATION(DNUMBER, DLOCATION)
PROJECT(PNUMBER, PNAME, PLOCATION)
WORKSON(SSN, PNUMBER, HOURS)
Write the following queries in SQL:
1. List the name(s) of employee(s) who works(work) on every project located in ‘Houston.’
2. List the name(s) of employee(s) who only works(work) on every project located in ‘Houston.’
3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.
4. List name(s) of employee(s) who works(work) on exactly all projects located in ‘Houston.’
Explanation / Answer
I am using Oracle SQL syntax.
And I am sure there is a better way to do it.
1. SELECT DISTINCT(c.NAME)
FROM PROJECT a, WORKSON b, EMPLOYEE c
WHERE
a.PLOCATION = 'Houston' AND
a.PNUMBER = b.PNUMBER AND
b.SSN = c.SSN
2.SELECT DISTINCT(f.NAME)
FROM
(SELECT c.SSN,
-- check if the person exists in projects located other than 'Houston'
(CASE WHEN EXISTS
(SELECT 1
FROM PROJECT d, WORKSON e
WHERE d.PNUMBER = e.PNUMBER AND
e.SSN = c.SSN AND
d.PLOCATION != 'Houston') THEN 1 ELSE 0
END) as FLAG
FROM
(SELECT DISTINCT(b.SSN)
FROM PROJECT a, WORKSON b
WHERE
a.PLOCATION = 'Houston' AND
a.PNUMBER = b.PNUMBER AND) c) tmp, EMPLOYEE f
WHERE
tmp.SSN = f.SSN AND
tmp.FLAG = 0
3.SELECT DISTINCT(f.NAME)
FROM
(SELECT c.SSN,
-- check if the person exists in projects in 'Houston'
(CASE WHEN EXISTS
(SELECT 1
FROM PROJECT d, WORKSON e
WHERE d.PNUMBER = e.PNUMBER AND
e.SSN = c.SSN AND
d.PLOCATION = 'Houston') THEN 1 ELSE 0
END) as FLAG
FROM
(SELECT DISTINCT(b.SSN)
FROM PROJECT a, WORKSON b
WHERE
a.PLOCATION != 'Houston' AND
a.PNUMBER = b.PNUMBER AND) c) tmp, EMPLOYEE f
WHERE
tmp.SSN = f.SSN AND
tmp.FLAG = 0
4.SELECT tmp.NAME
FROM
(SELECT c.NAME, COUNT(*) as NumProject
FROM PROJECT a, WORKSON b, EMPLOYEE c
WHERE
a.PLOCATION = 'Houston' AND
b.PNUMBER = b.PNUMBER AND
b.SSN = c.SSN
GROUP BY c.NAME) tmp
WHERE
tmp.NumProject = (SELECT COUNT(*)
FROM PROJECT d
WHERE d.PLOCATION = 'Houston')