SQL Queries Essay Example | Topics and Well Written Essays - 750 words

SQL Queries - Essay Example Description I have added a sub query in WHERE clause to filter those students who have no placement. f. List the names and student ids for students who have gained employment with the company that they undertook a placement at. SELECT DISTINCT Student.stu_fname, Student.stu_no FROM Employment, Student, Placement WHERE Student.stu_no=Employment.stu_no And Student.stu_no=Placement.stu_no And Placement.co_id=Employment.co_id; Description: In this query I have selected Employment, Student, Placement tables and have joined them. In addition to that, I have also patched Company ID of Placement with Employment table as well. g. List the names and student ids for students that have undertaken precisely two (2) placements. SELECT DISTINCT Student.stu_fname, Student.stu_no FROM Student WHERE (( SELECT count(*) FROM Placement WHERE (Student.stu_no=Placement.stu_no) ) >= 2 ); Description: I have added a sub query in WHERE clause to filter those students who have at least two placements. h. List the names and student ids for all students and sort the list in reversed alphabetical order (Z to A). SELECT DISTINCT Student.stu_fname, Student.stu_no FROM Student ORDER BY Student.stu_fname DESC; Description: In this query I have added ORDER BY clause to sort data in Descending order. i. Show the average length for placements, where the placement is with Sony and the student goes on to gain employment (with any company). SELECT Company.co_name, avg(SELECT count(*) FROM Placement, Student, Employment WHERE Placement.co_id=Company.co_id and student.stu_no=Employment.stu_no And student.stu_no=Placement.stu_no ) FROM company GROUP BY Company.co_name, Company.co_id HAVING Company.co_name='Sony'; Description: First I have selected the Average number of placements... In a correctly implemented database a student may not be employed and on a placement at the same point in time. However in such a database it is still possible for a student to secure employment before they have completed their placement (i.e. having a starting date for employment while still on placement). Create a query that lists all such occurrences.