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

SQL Queries - Essay Example expositionI have added a sub query in WHERE article to filter those schoolchilds 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_noFROM Employment, Student, musical arrangementWHERE Student.stu_no=Employment.stu_no And Student.stu_no=Placement.stu_no And Placement.co_id=Employment.co_idDescriptionIn 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_noFROM StudentWHERE (( SELECT count(*) FROM Placement WHERE (Student.stu_no=Placement.stu_no) ) = 2 )DescriptionI 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_noFROM StudentORDER BY Student.stu_fname DESCDescriptionIn 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 companyGROUP BY Company.co_name, Company.co_idHAVING Company.co_name=SonyDescriptionFirst I have selected the Average weigh 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 effec ted their placement (i.e. having a starting date for employment while still on placement). Create a query that lists all such occurrences.