Stuck with SQL queries

[above8k]

Hi
I am stuck on 1) and not sure about 2) and 3)
Attachment 9926


1) Display all the columns in Library for those libraries that do not have any associated internships.

2) Display all the columns in Student for those that have more than the average number of placements.

3) Display all the columns in Student for those that have at least one Placement assigned
where the Faculty that is associated with the Placement’s Internship is not the same as the Student’s Faculty advisor.


***My attempts***

1)


2) SELECT Student.*
FROM Student
WHERE (SELECT COUNT(*) FROM Placement
WHERE Placement.s_id= Student.id)>
(SELECT placements/student AS avg_Stud
FROM (SELECT COUNT(i_code) AS placements FROM Placement) AS table1,
(SELECT COUNT(id) AS student
FROM Student)AS table2);

3) SELECT *
FROM Student
WHERE id IN(SELECT i_code FROM Placement
WHERE i_code IN(SELECT id FROM Faculty
WHERE Internship.f_id<>Faculty.id));

[June7]

Number 1 should be the easiest, assuming data structure is correct, try:

In Query designer, add the Employee, Library, Internship tables.
Jointype between Library and Employee 'Include all records from Library...'
Jointype between Employee and Internship 'Include all records from Employee...'
Criteria under Internship!Code: Is Null

What happens when you run the other 2?

[above8k]

Number 1 should be the easiest, assuming data structure is correct, try:

In Query designer, add the Employee, Library, Internship tables.
Jointype between Library and Employee 'Include all records from Library...'
Jointype between Employee and Internship 'Include all records from Employee...'
Criteria under Internship!Code: Is Null

What happens when you run the other 2?

thanks for heads up..I will try to do 1) later and update the thread.

2) It runs, but I am not sure if its correct. I mean what other ways it can be done?

3) I get a Type Mismatch in Expression error.

[June7]

You will have to audit the results to make sure they are correct

Don't know your data well enough to be sure other solutions possible. Domain aggregate functions (DCount, DSum, etc) often serve.

Don't know why the mismatch. All the criteria fields look like they are the same type (number).

[above8k]

I tried, still no luck on 1) and 3)

1) SELECT *
FROM Employee, Library, Internship
WHERE Library.code= Employee.l_code
WHERE Employee.id= Internship.e_id
WHERE Internship.e_id= NULL;

3) SELECT *FROM Project
WHERE code IN(SELECT pcode FROM Assignment
WHERE eid IN(SELECT id FROM Employee
WHERE dcode IN(SELECT code FROM Department
WHERE Project.dcode<>Employee.dcode)));

I am attaching my access file about the Type Mismatch in Expression error on 3)
Here is a link(file is more than 1MB) -->http://www.2shared.com/file/c1U_OgrefsPP/cwq.html

thanks

[June7]

You didn't do what I instructed for query 1. Include those 3 tables in the query and set joins as described. This is basic Access functionality. If you need to know more about using query designer, Access Help has guidance. Here is an on-line tutorial http://www.opengatesw.net/ms-access-...cess-Query.htm

With the data you have, all libraries have an internship so the query won't retrieve any records with the Is Null criteria.

Query2 requirements don't make sense to me. Can each student have more than one placement? The data has each student with 1 placement so none will exceed the average.

Query3 will also not return any records because the student advisor is also the faculty associated with the internship
SELECT Student.*
FROM Student LEFT JOIN (Internship RIGHT JOIN Placement ON Internship.code = Placement.i_code) ON Student.id = Placement.s_id
WHERE (((Student.f_id)<>[Internship]![f_id]));

[above8k]

So I did 1) as per your instruction, dunno if I hit the correct boxes in the designers.
link --> http://www.2shared.com/file/QXXQbdsds5Zp/cwq.html

2) if the average is more then I think student will have more than one placement.


About Query designer, I didnt know that tool even exists, prof never talked about it in the class.

[June7]

Suggest you get a basic introductory reference for Access to supplement instructor lecture. Is this a class using Access software specifically or is the class generic and students use app of their own choosing? Access is not the only db app that recognizes SQL and each will have their own tools to aid in generating SQL statements.

Might want more data for testing queries. You could have records in tables that would be retrieved for any criteria specified in query. Right now you get empty datasets. If student can have more than one internship placement then create some records to fit that situation. Then that should also provide some records where student advisor is not same as internship faculty. An empty recordset could be a valid result but maybe more satisfying and less disconcerting to see records retrieved.

Query1
Correct tables and criteria but did not change the jointypes as suggested. Need to learn difference between INNER and OUTER (LEFT, RIGHT) joins and how they can impact query results.

Query2
Actually the most difficult of the 3 because requires aggregate data (average of count). First have to count placements for each student then average the counts over all students and use that as criteria for selecting records. No records retrieve because each student has 1 placement so the average is 1 and none exceeds the average. Put in some data to test your query. Ironically, I think you got a working SQL from the start; however, consider alternate syntax:
SELECT Student.*
FROM (SELECT Placement.s_id, Count(Placement.i_code) AS CountOfi_code
FROM Placement
GROUP BY Placement.s_id) AS CtStud LEFT JOIN Student ON CtStud.s_id = Student.id
WHERE (((CtStud.CountOfi_code)>(SELECT Count(i_code) As PCt FROM Placement)/(SELECT Count(ID) AS SCt FROM Student)));

Query3
Assume you did copy/paste of my suggested SQL. Again, create records to test.