DEV Community

Abdul Haseeb
Abdul Haseeb

Posted on

SEM V MYSQL Practical 4

  1. Display those book names whose name is 6 characters long.
   SELECT Title FROM Book WHERE LENGTH(Title) = 6;
Enter fullscreen mode Exit fullscreen mode
  1. Display the first 2 records from the Book table.
   SELECT * FROM Book LIMIT 2;
Enter fullscreen mode Exit fullscreen mode
  1. Display from the 4th to the 7th records from the Book table.
   SELECT * FROM Book LIMIT 4, 4;
Enter fullscreen mode Exit fullscreen mode
  1. 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;
Enter fullscreen mode Exit fullscreen mode
  1. 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;
Enter fullscreen mode Exit fullscreen mode
  1. 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;
Enter fullscreen mode Exit fullscreen mode
  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;
Enter fullscreen mode Exit fullscreen mode
  1. 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;
Enter fullscreen mode Exit fullscreen mode
  1. 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;
Enter fullscreen mode Exit fullscreen mode
  1. 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;
    
  2. 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;
    
  3. 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;
    
  4. Display the book details in ascending order of their price for publisher name ‘Techmedia’.

    SELECT * FROM Book WHERE Pub = 'Techmedia' ORDER BY Price ASC;
    
  5. 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;
    
  6. Display all the unique publishers in descending order.

    SELECT DISTINCT Pub FROM Book ORDER BY Pub DESC;
    
  7. 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;
    
  8. 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;
    
  9. Display the sum of prices given to the Pearson publisher.

    SELECT SUM(Price) AS Total_Price FROM Book WHERE Pub = 'Pearson';
    
  10. 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;
    
  11. 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;
    
  12. 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;
    
  13. Display book details whose quantity is an odd value.

    SELECT * FROM Book WHERE Qty % 2 = 1;
    
  14. 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;
    

Top comments (0)