SEM V MYSQL Practical 4

Display those book names whose name is 6 characters long.

   SELECT Title FROM Book WHERE LENGTH(Title) = 6;

Display the first 2 records from the Book table.

   SELECT * FROM Book LIMIT 2;

Display from the 4th to the 7th records from the Book table.

   SELECT * FROM Book LIMIT 4, 4;

Display book name, publisher name, and price from the book table which do not have price value.

   SELECT Title, Pub, Price FROM Book WHERE Price IS NULL;

Display the Author name, price, and 15% of Price as Discount from the table Book.

   SELECT Author, Price, Price * 0.15 AS Discount FROM Book;

Display the student name and date of returning that have returned in the month ‘January’ from Book_Issue table.

   SELECT Name, DOR FROM Book_Issue WHERE MONTH(DOR) = 1;

Display Roll no wise no. of books taken from Book_Issue table.

   SELECT RNO, COUNT(*) AS Books_Taken FROM Book_Issue GROUP BY RNO;

Display the name in upper case, reverse of the student name, and total no. of characters of student name from Book_Issue table.

   SELECT UPPER(Name) AS Upper_Name, REVERSE(Name) AS Reversed_Name, LENGTH(Name) AS Name_Length FROM Book_Issue;

Display the number of days for which the book is taken by the student.

   SELECT Name, DATEDIFF(DOR, DOI) AS Days_Taken FROM Book_Issue;

Display the number of days for which the book is taken by the student where the number of days is greater than 10.
```
SELECT Name, DATEDIFF(DOR, DOI) AS Days_Taken FROM Book_Issue WHERE DATEDIFF(DOR, DOI) > 10;
```
Display the student name and Roll no. who have taken Science books or Thriller books (i.e., Bookid starts from S or T) from the Book_Issue table and who issued in the year 2023.
```
SELECT Name, RNO FROM Book_Issue 
WHERE (Book_id LIKE 'S%' OR Book_id LIKE 'T%') 
  AND YEAR(DOI) = 2023;
```
Increase the price of all books by 20%. Display the books and price whose price is more than 2500.
```
UPDATE Book SET Price = Price * 1.20;
SELECT * FROM Book WHERE Price > 2500;
```
Display the book details in ascending order of their price for publisher name ‘Techmedia’.
```
SELECT * FROM Book WHERE Pub = 'Techmedia' ORDER BY Price ASC;
```
Display the details of the books in ascending order of the publisher name and descending of price.
```
SELECT * FROM Book ORDER BY Pub ASC, Price DESC;
```

Display all the unique publishers in descending order.

SELECT DISTINCT Pub FROM Book ORDER BY Pub DESC;

Display the student name, bookid, and DOJ who have issued in the year on 05-01-2024, 18-02-2024, and 15-06-2023 in ascending order.

SELECT Name, Book_id, DOI FROM Book_Issue 
WHERE DOI IN ('2024-01-05', '2024-02-18', '2023-06-15') 
ORDER BY DOI ASC;

Display all the books except those published by ‘Pearson’ & ‘Techmedia’ in ascending order of price.
```
SELECT * FROM Book 
WHERE Pub NOT IN ('Pearson', 'Techmedia') 
ORDER BY Price ASC;
```

Display the sum of prices given to the Pearson publisher.

SELECT SUM(Price) AS Total_Price FROM Book WHERE Pub = 'Pearson';

Display the total quantity publisher-wise whose total quantity is less than 100.

SELECT Pub, SUM(Qty) AS Total_Qty 
FROM Book 
GROUP BY Pub 
HAVING Total_Qty < 100;

Display the category and number of books published for each category which contains at least 2 books.

SELECT Category, COUNT(*) AS Number_of_Books 
FROM Book 
GROUP BY Category 
HAVING COUNT(*) >= 2;

Display each category’s total, average, minimum, and maximum price, and number of books.

SELECT Category, 
       SUM(Price) AS Total_Price, 
       AVG(Price) AS Average_Price, 
       MIN(Price) AS Minimum_Price, 
       MAX(Price) AS Maximum_Price, 
       COUNT(*) AS Number_of_Books
FROM Book
GROUP BY Category;

Display book details whose quantity is an odd value.
```
SELECT * FROM Book WHERE Qty % 2 = 1;
```
Display all records from Book_Issue table showing message as (example) ‘Krishna issued the book R185 on 2023-06-02’.
```
SELECT CONCAT(Name, ' issued the book ', Book_id, ' on ', DOI) AS Issue_Message 
FROM Book_Issue;
```

Blog

SEM V MYSQL Practical 4

Abdul Haseeb

Join Our Newsletter. No Spam, Only the good stuff.

Related