Another One Please Help - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

Another One Please Help

Author

Topic

alxtech
Yak Posting Veteran

66 Posts

Posted - 2006-03-03 : 16:55:10

this is another question Please help!!!!!!!!!
I need the sql statements

A relational database contains details about university information.

- Faculty (faculty_id, name, rank, salary, office, phone, did) - did is foreign key of table Department (did).
- Department (did, dname, faculty chair, school_name).
- Course (courseid, course_name, description, did) - did is a foreign key of table Department (did).
- Advisor (advisor_id, advisor_name, did, school_name) - did is a foreign key of table Department.
- Student (std_id, name, major_department, minor_department, advisor_id) - advisor_id is a foreign key of table Advisor (advisor_id). major_department and minor_department are foreign keys of table
Department(did).
- Student-Course (std_id, courseid, date) - std_id is a foreign key of table Student (std_id) and courseid is a foreign key of table Course (courseid).

Formulate the following queries using relational SQL:

1. Retrieve the faculty ids of all faculty who work in the department name 'Information Technology'

2. For each department that offers more than 5 courses list the total number of courses offered by each department.

3. List all students who are advised by the advisor "Mr. Jones".

4. For each department, retrieve the department number, the number of faculty in the department, and their average salary.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-03 : 17:03:41

Please post what code you have so far so that we can at least see that you have tried to do your homework on your own.

Tara Kizer
aka tduggan

alxtech
Yak Posting Veteran

66 Posts

Posted - 2006-03-03 : 17:17:29

Hey bro,
i tried but nothing comes out of my mind, this is a emergency, i am taking a final but this topic is confusing. please help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-03 : 17:22:59

We don't answer homework questions here. But we'd be happy to help if you showed us that you at least tried.

I'll give you some hints.

1. It's a simple select statement with a where clause
2. You'll use COUNT(*) along with GROUP BY and HAVING
3. Requires a join to the Advisor table
4. You'll need to use aggregates for this one

Tara Kizer
aka tduggan

alxtech
Yak Posting Veteran

66 Posts

Posted - 2006-03-03 : 17:30:11

This is what I got so far:

1. select faculty_id
from faculty, department
where dname = 'information technology'
and Department.did = faculty.did

2. select dname
from Department
where SUM Course > 5

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-03 : 17:35:39

1 looks good.

2 is probably this:

SELECT dname, COUNT(*)
FROM Department
GROUP BY dname
HAVING COUNT(*) > 5

Tara Kizer
aka tduggan

alxtech
Yak Posting Veteran

66 Posts

Posted - 2006-03-03 : 17:35:56

3. select name
from Student
where Advisor.advisor_name like '%Jones'
and Student.advisor_id = Advisor.advisor_id

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-03 : 17:37:39

3. select name
from Student, Advisor
where Advisor.advisor_name = 'Mr. Jones'
and Student.advisor_id = Advisor.advisor_id

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-03 : 17:37:57

I'm leaving for the day, so hopefully someone else can help you out.

Tara Kizer
aka tduggan

alxtech
Yak Posting Veteran

66 Posts

Posted - 2006-03-03 : 17:49:34

4. SELECT faculty_id, did, AVG(salary)
from faculty, department

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-03 : 18:28:18

quote:
Originally posted by alxtech

4. SELECT faculty_id, did, AVG(salary)
from faculty, department

You don't seem to have the requirements covered. You have no column in your result set with the number of faculty. The faculty_id column in your result set was not asked for. AVG is an aggregate function, so what clause is required when you use an aggregate? They asked for the number of faculty in each department, so how would you go about matching department to faculty in your query?

"4. For each department, retrieve the department number, the number of faculty in the department, and their average salary."

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources