Consider the tables BOOKS and PUBLISHERS with the following sample records:…
2026
Consider the tables BOOKS and PUBLISHERS with the following sample records:
Table: BOOKS
BOOKID | BookTitle | Author | Price | Edition | PubID |
|---|---|---|---|---|---|
2026103 | Data Science | J P Singh | 350 | 2025 | P12 |
2026112 | Lets Python | Harpreet Kaur | 400 | 2026 | P12 |
2025125 | RDBMS | Gems Gomez | 300 | 2026 | P09 |
Table: PUBLISHERS
PubID | PubName | City | EmailID |
|---|---|---|---|
P09 | Rupa Prakashan | Kolkata | rupa@bharat.in |
P10 | Modern Books | Noida | modernbooks@gmail.com |
P12 | IA Publishers | Delhi | iap@yahoo.co.in |
(Note: Both tables contain additional records.)
Write appropriate MySQL queries to perform the following tasks:
(A) Display the title of each book along with the name of its publisher.
(B) Display the average price of books published by publishers located in Delhi.
(C) Display the total number of books published from each city.
(D) Display the book title and corresponding publisher name for books whose title contains the word “RDBMS” anywhere in the BookTitle.
Attempted by 11 students.
Show answer & explanation
(A) Book Title with Publisher Name
• Use JOIN to combine both tables
• Match using PubID
SQL Query:
SELECT BookTitle, PubName
FROM BOOKS B, PUBLISHERS P
WHERE B.PubID = P.PubID;
(B) Average Price for Delhi Publishers
• Use AVG() with condition on City
• Join both tables
SQL Query:
SELECT AVG(B.Price) AS Avg_Price
FROM BOOKS B, PUBLISHERS P
WHERE B.PubID = P.PubID AND P.City = 'Delhi';
(C) Total Books from Each City
• Use COUNT() with GROUP BY
• Group using City
SQL Query:
SELECT P.City, COUNT(*) AS Total_Books
FROM BOOKS B, PUBLISHERS P
WHERE B.PubID = P.PubID
GROUP BY P.City;
(D) Books with “RDBMS” in Title
• Use LIKE operator
• Combine with JOIN
SQL Query:
SELECT BookTitle, PubName
FROM BOOKS B, PUBLISHERS P
WHERE B.PubID = P.PubID
AND BookTitle LIKE '%RDBMS%';