Need some help with query

Hi Guys,

I'm working on a SQL problem and am a bit stuck. I'll have to apologize for I'm still very new to this and can't do a lot of complex queries yet. I've attached what I've done so far, thank you in advance for your help.

Given the following relation schemas:

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.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER = b. PNUMBER and b.PLOCATION= Houston

2. List the name(s) of employee(s) who only works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER=b.PNUMBER and b.PLOCATION in (Houston) and ?

3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c. PNUMBER=b.PNUMBER and b.PLOCATION not in (Houston)

4. List name(s) of employee(s) who works(work) on exactly all projects located in Houston.

?

Quote:

Originally Posted by alvinguy

Hi Guys,

I'm working on a SQL problem and am a bit stuck. I'll have to apologize for I'm still very new to this and can't do a lot of complex queries yet. I've attached what I've done so far, thank you in advance for your help.

Given the following relation schemas:

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.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER = b. PNUMBER and b.PLOCATION= Houston

2. List the name(s) of employee(s) who only works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER=b.PNUMBER and b.PLOCATION in (Houston) and ?

3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c. PNUMBER=b.PNUMBER and b.PLOCATION not in (Houston)

4. List name(s) of employee(s) who works(work) on exactly all projects located in Houston.

?

Try to use joins instead of alias names
sample:
3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.

SELECT a.NAME from Project b
left join WORKSON c on C.Pnumber=b.Pnumber
left join EMPLOYEE a on a.SSN=c.SSN
WHERE b.PLOCATION !=Houston

2. List the name(s) of employee(s) who only works(work) on every project located in Houston.

select distinct Employee.Name from Project
left join Workson on Workson.PNumber=Project.PNumber
left join Employee on Employee.SSN=Workson.SSN
where Project.PLOcation='Houston'

4. List name(s) of employee(s) who works(work) on exactly all projects located in Houston.

Select Name from Employee where SSN=(Select SSN from
(Select SSN,count(SSN) as cnt from
(select distinct SSN,Workson.PNumber from Project
left join Workson on Workson.Pnumber= Project.PNumber
where Project.PLocation='Houston'
group by SSN,Workson.PNumber) as A
group by SSN) as B
where cnt=(select Count(Pnumber) from Project Where Plocation='Houston'))|||

Quote:

Originally Posted by alvinguy

Hi Guys,

I'm working on a SQL problem and am a bit stuck. I'll have to apologize for I'm still very new to this and can't do a lot of complex queries yet. I've attached what I've done so far, thank you in advance for your help.

Given the following relation schemas:

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.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER = b. PNUMBER and b.PLOCATION= Houston

2. List the name(s) of employee(s) who only works(work) on every project located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c.PNUMBER=b.PNUMBER and b.PLOCATION in (Houston) and ?

3. List the name(s) of employee(s) who works(work) on every project except the one(s) located in Houston.

SELECT a.NAME
FROM EMPLOYEE a, PROJECT b, WORKSON c
WHERE a.SSN = c.SSN and c. PNUMBER=b.PNUMBER and b.PLOCATION not in (Houston)

4. List name(s) of employee(s) who works(work) on exactly all projects located in Houston.

?

Hi
Different way for different queries exist.
if u still have problem in 3rd query u can use the following.

SELECT a.NAME
FROM
EMPLOYEE AS a, WORKSON AS b
WHERE a.SSN=b.SSN and b.PNUMBER IN
(SELECT PNUMBER
FROM PROJECT
WHERE PLOCATION NOT IN('HOUSTON'));

IF U STILL HAVE ANY PROBLEM IN ANY OTHER QUERY PLZ TELL I WILL TRY MY BEST TO SOLVE THAT ONE.sql