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.
Author |
Topic |
adityasirohi
Starting Member
3 Posts |
Posted - 2013-06-20 : 01:03:54
|
Hi All, I wrote some sql queries. Just wanted to confirm that i am correct:This is the scheme:PK --> Primary Key, FK --> Foreign KeyStudents: {STUDENT_ID(PK), GRADUATING_YEAR, FIRST_NAME, LAST_NAME} Classes: {CLASS_ID(PK), NAME, DESCRIPTION}Students_Class : {STUDENT_ID(FK), CLASS_ID(FK), GRADE}1. SQL statement to give every student graduating on 2010 and in the class "Math" a grade of "B".UPDATE Student_ClassSET GRADE = 'A'WHERE EXISTS (SELECT GRADUATING_YEAR from Students WHERE Students.STUDENT_ID = Students_Class.STUDENT_ID and Students.GRADUATING_YEAR = 2010);2. SQL statement to retrieve the ID and full name of all students in the class named "English"select Students.STUDENT_ID, Students.FIRST_NAME, Students.LAST_NAMEfrom Students, Classes, Students_Classwhere Students.STUDENT_ID = Students_Class.STUDENT_ID and Classes.CLASS_ID = Students_Class.CLASS_ID and Classes.NAME = 'English'; |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-20 : 01:21:04
|
1.your question and answers doesnt match. for example question says set grade to B but you're setting it to A. Is this intentional? Also you're missing check on classso as per question it should beUPDATE Student_ClassSET GRADE = 'B'FROM Student_Class,ClassesWHERE Classes.CLASS_ID = Students_Class.CLASS_IDAND Classes.NAME = 'Math'AND EXISTS (SELECT 1 from StudentsWHERE Students.STUDENT_ID = Students_Class.STUDENT_ID andStudents.GRADUATING_YEAR = 2010); 2.looks fine------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|