TroyK
asked on
TroyK's T-SQL Challenge #1: Students and unread books
Hello Experts;
Since I have a bunch of points and no "real-life" questions, I thought I'd start issuing some "T-SQL Challenges" for y'all to puzzle over.
I'll award points to each unique answer (let's say up to the first 3) that conforms to the rules for that particular challenge, so if someone has answered, and you see another way to do it, post your solution.
This first one is pretty easy...
Rules:
- No cursors, while loops, etc., set-based only
- T-SQL is OK, but if you have an ANSI-92 compliant solution, let's see that, too!
A teacher has a series of books she has assigned as Spring Break reading for her students. She wants to track their completion, so she has created 3 tables; a Books table, a Students table, and a table to join the two in a many-to-many relationship, BooksRead.
She has populated the tables with data indicating which students have read which books. Now, she needs a query to find which books have _not yet_ been read, and by which students. The desired output is contained within the body of the test script below. Please provide the SELECT statement to obtain the desired results:
CREATE TABLE Students(
-- In a real implementation, this might be SSN
StudentID int NOT NULL PRIMARY KEY,
StudentName varchar(35) NOT NULL
)
GO
CREATE TABLE Books(
-- In a real implementation, this would probably be ISBN
BookID int NOT NULL PRIMARY KEY,
BookTitle varchar(24) NOT NULL
)
GO
CREATE TABLE BooksRead(
BookID int NOT NULL REFERENCES Books(BookID),
StudentID int NOT NULL REFERENCES Students(StudentID),
CONSTRAINT pk_BooksRead
PRIMARY KEY (BookID, StudentID)
)
GO
SET NOCOUNT ON
INSERT INTO Students
SELECT 1, 'John Doe' UNION ALL
SELECT 2, 'Sally Smith' UNION ALL
SELECT 3, 'Frank Johnson'
INSERT INTO Books
SELECT 101, 'Harry Potter' UNION ALL
SELECT 102, 'Stuart Little' UNION ALL
SELECT 103, 'Catcher In The Rye' UNION ALL
SELECT 104, 'War And Peace'
INSERT INTO BooksRead
SELECT 101, 1 UNION ALL
SELECT 102, 1 UNION ALL
SELECT 101, 2 UNION ALL
SELECT 103, 2
SET NOCOUNT OFF
GO
-- Fill in your SELECT statement here...
-- Desired output: Book Titles that haven't been read by particular students
/*
BookID BookTitle StudentID StudentName
----------- ------------------------ ----------- -------------------------- ---------
101 Harry Potter 3 Frank Johnson
102 Stuart Little 2 Sally Smith
102 Stuart Little 3 Frank Johnson
103 Catcher In The Rye 1 John Doe
103 Catcher In The Rye 3 Frank Johnson
104 War And Peace 1 John Doe
104 War And Peace 2 Sally Smith
104 War And Peace 3 Frank Johnson
*/
DROP TABLE BooksRead, Books, Students
GO
Have Fun!
TroyK, MCSD
Since I have a bunch of points and no "real-life" questions, I thought I'd start issuing some "T-SQL Challenges" for y'all to puzzle over.
I'll award points to each unique answer (let's say up to the first 3) that conforms to the rules for that particular challenge, so if someone has answered, and you see another way to do it, post your solution.
This first one is pretty easy...
Rules:
- No cursors, while loops, etc., set-based only
- T-SQL is OK, but if you have an ANSI-92 compliant solution, let's see that, too!
A teacher has a series of books she has assigned as Spring Break reading for her students. She wants to track their completion, so she has created 3 tables; a Books table, a Students table, and a table to join the two in a many-to-many relationship, BooksRead.
She has populated the tables with data indicating which students have read which books. Now, she needs a query to find which books have _not yet_ been read, and by which students. The desired output is contained within the body of the test script below. Please provide the SELECT statement to obtain the desired results:
CREATE TABLE Students(
-- In a real implementation, this might be SSN
StudentID int NOT NULL PRIMARY KEY,
StudentName varchar(35) NOT NULL
)
GO
CREATE TABLE Books(
-- In a real implementation, this would probably be ISBN
BookID int NOT NULL PRIMARY KEY,
BookTitle varchar(24) NOT NULL
)
GO
CREATE TABLE BooksRead(
BookID int NOT NULL REFERENCES Books(BookID),
StudentID int NOT NULL REFERENCES Students(StudentID),
CONSTRAINT pk_BooksRead
PRIMARY KEY (BookID, StudentID)
)
GO
SET NOCOUNT ON
INSERT INTO Students
SELECT 1, 'John Doe' UNION ALL
SELECT 2, 'Sally Smith' UNION ALL
SELECT 3, 'Frank Johnson'
INSERT INTO Books
SELECT 101, 'Harry Potter' UNION ALL
SELECT 102, 'Stuart Little' UNION ALL
SELECT 103, 'Catcher In The Rye' UNION ALL
SELECT 104, 'War And Peace'
INSERT INTO BooksRead
SELECT 101, 1 UNION ALL
SELECT 102, 1 UNION ALL
SELECT 101, 2 UNION ALL
SELECT 103, 2
SET NOCOUNT OFF
GO
-- Fill in your SELECT statement here...
-- Desired output: Book Titles that haven't been read by particular students
/*
BookID BookTitle StudentID StudentName
----------- ------------------------ ----------- --------------------------
101 Harry Potter 3 Frank Johnson
102 Stuart Little 2 Sally Smith
102 Stuart Little 3 Frank Johnson
103 Catcher In The Rye 1 John Doe
103 Catcher In The Rye 3 Frank Johnson
104 War And Peace 1 John Doe
104 War And Peace 2 Sally Smith
104 War And Peace 3 Frank Johnson
*/
DROP TABLE BooksRead, Books, Students
GO
Have Fun!
TroyK, MCSD
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nice job, everyone!
We have a comparison of CROSS JOIN vs. INNER JOIN ... ON 1=1, and a comparison of LEFT JOIN ... WHERE <key> IS NULL vs. NOT EXISTS(...).
I'll try to come up with something more difficult next time : )
TroyK, MCSD
We have a comparison of CROSS JOIN vs. INNER JOIN ... ON 1=1, and a comparison of LEFT JOIN ... WHERE <key> IS NULL vs. NOT EXISTS(...).
I'll try to come up with something more difficult next time : )
TroyK, MCSD
ASKER