How to extract parts from a composite key?

Question

I have the following SQL tables which describe a relation where users can rate books:

CREATE TABLE Users 
(
    UID INT PRIMARY KEY,
    Username VARCHAR(256),
    DoB DATE,
    Age INT,
);

CREATE TABLE Book 
(
    ISBN VARCHAR(17) PRIMARY KEY,
    Title VARCHAR(256),
    Published DATE,
    Pages INT,
    Language VARCHAR(256)
);

CREATE TABLE Rating 
(
    UID INT REFERENCES Users (UID),
    ISBN VARCHAR(17) REFERENCES Book (ISBN),
    PRIMARY KEY (UID,ISBN),
    Rating INT
)

I want to get a list of book-titles with a rating of 5 that ALLOWS REPETITIONS for every rating. For example, if Book "A" has three ratings of 10, then its tile "A" should be in the list three times.

My idea is to extract the "isbn" from the primary key "(uid,isbn)" from rating and then select the respective titles. So I wrote the code that selects all "(uid,isbn)" tuples:

select (uid,isbn)
from rating
where rating = 10

However, I do not know how to access the isbn part from there. Could you please tell me how to do this?

`where rating = 10` => `where rating = 10 and isbn = 'some-isbn-value'` ? — Paul T., May 14 '23 at 01:22
Which dbms? "sql" is not a good tag to use ("*tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used. If your question relates solely to a specific DBMS (uses specific extensions/features), use that DBMS's tag instead.*") https://stackoverflow.com/questions/tagged/sql — Paul Maxwell, May 14 '23 at 01:53
Please add a few rows of sample data and the expected result. — The Impaler, May 14 '23 at 02:40
Please read: [Why historically do people use 255 not 256 for database field magnitudes?](https://stackoverflow.com/questions/2340639/why-historically-do-people-use-255-not-256-for-database-field-magnitudes), and stop doing historically correct things... — Luuk, May 14 '23 at 14:58

score 2 · Accepted Answer · answered May 14 '23 at 03:05

You do not need to extract parts of the composite key, because they exist as separate columns and hence already available independently. e.g.

SELECT
     Book.Title, Rating.UID, Users.Username, Rating.ISBN, Rating.Rating
FROM Book
JOIN Rating ON Book.ISBN = Rating.ISBN
JOIN Users On Rating.UID = Users.UID
WHERE Rating.Rating = 5
ORDER BY
     Book.Title
;

Title	UID	Username	ISBN	Rating
1984	1	Alice	9780141187761	5
1984	3	Charlie	9780141187761	5
The Catcher in the Rye	2	Bob	9783161484100	5
The Lord of the Rings	1	Alice	9780547928227	5
The Lord of the Rings	2	Bob	9780547928227	5
The Lord of the Rings	3	Charlie	9780547928227	5
The Lord of the Rings	4	David	9780547928227	5
To Kill a Mockingbird	1	Alice	9780061120084	5
To Kill a Mockingbird	3	Charlie	9780061120084	5

fiddle

How to extract parts from a composite key?

1 Answers1