Link to home
Start Free TrialLog in
Avatar of TroyK
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
ASKER CERTIFIED SOLUTION
Avatar of amit_g
amit_g
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TroyK
TroyK

ASKER

Increasing points to split...
Avatar of TroyK

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