plz can any one help me on this queris

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

plz can any one help me on this queris

Author

Topic

AnGeL EyEs
Starting Member

5 Posts

Posted - 2008-04-19 : 16:37:57

hi Guys..

i have an assignment and i don't know how to do it

every time i tried, the answers are incorrect :(

these are the questions:

EMPLOYEE (fmane, minit, lname, ssn, birthdate, address, sex, salary, superssn, dno)

DEPARTMENT (dname, dnumber, mgrssn, mgrstartdate)

PROJECT (pname, pnumber, plocation, dnum)

WORKS_ON (essn, pno, hours)
DEPENDENT (essn, dependent-name, sex, bdate, relationship)

1. Give the last name and SSN of single employees who do not work on any of the Stafford projects (interpret single as: no spouse, children are OK).

2. Give the last name and SSN of employees who only work on Stafford projects.

3. Give the last name and SSN of female managers who (a) are married, (b) have no children, (c) are in control of departments located in Houston or Stafford.

i upload the question & the MS Access files on this link

[url]http://www.speedyshare.com/885115830.html[/url]

the assignment is due tommorow

Thanks million in avdvanced for your help

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-19 : 17:39:51

What have you tried and what was the result?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AnGeL EyEs
Starting Member

5 Posts

Posted - 2008-04-19 : 18:05:34

for the second question

2. Give the last name and SSN of employees who only work on Stafford projects.

SELECT lname, ssn
FROM department, employee, works_on, project
WHERE dnumber=dno and ssn=essn and pno=pnumber And PLOCATION= "Stafford";

i do this statement but it's wrong

because it will list all people who work in Stafford and also work on other places

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-19 : 19:22:52

First of all it's a lot easier to work with if you always prefix columns with a table alias.

SELECT e.lname, e.ssn
FROM department d, employee e, works_on wo, project p
WHERE d.dnumber=e.dno and e.ssn=wo.essn and wo.pno=p.pnumber And p.PLOCATION= "Stafford";

from that we can see that department doesn't play any part in the query so
SELECT e.lname, e.ssn
FROM employee e, works_on wo, project p
WHERE e.ssn=wo.essn and wo.pno=p.pnumber And p.PLOCATION= "Stafford";

so all we need to do is exclude any employees with projects not in stafford
and not exists (select * from project p, works_on wo where e.ssn=wo.essn and wo.pno=p.pnumber and p.PLOCATION <> "Stafford")
now this won't work because the previous query excludes non-staffrd projects so turn that into a similar subquery
and we get

SELECT e.lname, e.ssn
FROM employee e
where exists (select * from project p, works_on wo where e.ssn=wo.essn and wo.pno=p.pnumber and p.PLOCATION = "Stafford")
and not exists (select * from project p, works_on wo where e.ssn=wo.essn and wo.pno=p.pnumber and p.PLOCATION <> "Stafford")

in the subqueries e.ssn=wo.essn references the employee and makes the subquery correlated i.e. acting for each row in the outer table.

You should be able to use a similar technique for the other questions

a simpler way of doing this but maybe not as obvious is
SELECT e.lname, e.ssn
FROM employee e, works_on wo, project p
WHERE e.ssn=wo.essn and wo.pno=p.pnumber
group by e.lname, e.ssn
having max(p.PLOCATION) = "Stafford"
and min(p.PLOCATION) = "Stafford"

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AnGeL EyEs
Starting Member

5 Posts

Posted - 2008-04-19 : 19:51:55

ahaa

Thanks for your help ^^

I will try again to do the others

if I stuck I will come here

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources