SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Some SQL queries!!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

adityasirohi
Starting Member

3 Posts

Posted - 06/20/2013 :  01:03:54  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/20/2013 :  01:21:04  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000