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
 General SQL Server Forums
 New to SQL Server Programming
 Some SQL queries!!

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 Key

Students: {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_Class
SET 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_NAME
from Students, Classes, Students_Class
where 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 class
so as per question it should be

UPDATE Student_Class
SET GRADE = 'B'
FROM Student_Class,Classes
WHERE Classes.CLASS_ID = Students_Class.CLASS_ID
AND Classes.NAME = 'Math'
AND EXISTS (SELECT 1 from
Students
WHERE Students.STUDENT_ID = Students_Class.STUDENT_ID and
Students.GRADUATING_YEAR = 2010);


2.looks fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -