Sem V MYSQL Practical 5

Books Table:

• BookID (Primary Key)
• Title
• Author
• Publisher
• Category
• Price
• DateOfIssue

Students Table:

• StudentID (Primary Key)
• StudentName
• RollNo
• 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';