Sem V MYSQL Practical 5
Abdul Haseeb
Posted on September 3, 2024
Books Table:
-
BookID(Primary Key) TitleAuthorPublisherCategoryPriceDateOfIssue
Students Table:
-
StudentID(Primary Key) StudentNameRollNo-
BookID(Foreign Key) Fine
Let’s use SQL to perform the required joins and subqueries:
Joins
1. Display the BookID, book name (Title), and student name from both tables.
SELECT
Books.BookID,
Books.Title AS BookName,
Students.StudentName
FROM
Books
JOIN
Students ON Books.BookID = Students.BookID;
2. Display the book name and publisher name of the book taken by Payal.
SELECT
Books.Title AS BookName,
Books.Publisher
FROM
Books
JOIN
Students ON Books.BookID = Students.BookID
WHERE
Students.StudentName = 'Payal';
3. Display RollNo, book name (Title), date of issue, and fine for only Reference and Science books in ascending order of RollNo.
SELECT
Students.RollNo,
Books.Title AS BookName,
Books.DateOfIssue,
Students.Fine
FROM
Books
JOIN
Students ON Books.BookID = Students.BookID
WHERE
Books.Category IN ('Reference', 'Science')
ORDER BY
Students.RollNo ASC;
4. Display the name of the student, title of the book, author name, and price of the book for those books whose price is between 1500 and 2000 and the publisher name is Pearson.
SELECT
Students.StudentName,
Books.Title AS BookName,
Books.Author,
Books.Price
FROM
Books
JOIN
Students ON Books.BookID = Students.BookID
WHERE
Books.Price BETWEEN 1500 AND 2000
AND Books.Publisher = 'Pearson';
5. Display the name and title of the book for students who have taken the book with BookID 'T150'.
SELECT
Students.StudentName,
Books.Title AS BookName
FROM
Books
JOIN
Students ON Books.BookID = Students.BookID
WHERE
Books.BookID = 'T150';
6. Display the name of the books, BookID, and the number of books that have been issued by at least 2 students.
SELECT
Books.Title AS BookName,
Books.BookID,
COUNT(Students.StudentID) AS NumberOfIssues
FROM
Books
JOIN
Students ON Books.BookID = Students.BookID
GROUP BY
Books.BookID, Books.Title
HAVING
COUNT(Students.StudentID) >= 2;
Sub-Queries
1. Display the details of the book whose price is more than the price of the book 'The Sonnets'.
SELECT
*
FROM
Books
WHERE
Price > (SELECT Price FROM Books WHERE Title = 'The Sonnets');
2. Display the book name, category, and publisher name whose publisher is the same as that of 'Let Us C'.
SELECT
Title AS BookName,
Category,
Publisher
FROM
Books
WHERE
Publisher = (SELECT Publisher FROM Books WHERE Title = 'Let Us C');
3. Display the book details that have the maximum price.
SELECT
*
FROM
Books
WHERE
Price = (SELECT MAX(Price) FROM Books);
4. Display the book details that have the minimum price.
SELECT
*
FROM
Books
WHERE
Price = (SELECT MIN(Price) FROM Books);
5. Display the book details with a price greater than the average price.
SELECT
*
FROM
Books
WHERE
Price > (SELECT AVG(Price) FROM Books);
6. Display the book name that has been issued by the student before 2023.
SELECT
Title AS BookName
FROM
Books
WHERE
DateOfIssue < '2023-01-01';
💖 💪 🙅 🚩
Abdul Haseeb
Posted on September 3, 2024
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related
