TheLazy.Dev
Portfolio GitHub

Blog

Sem V MYSQL Practical 7

devabdul

Abdul Haseeb

Posted on September 3, 2024

Sem V MYSQL Practical 7

1. Create the Bike Table 

CREATE DATABASE BCOMXXX;

USE BCOMXXX;

CREATE TABLE Bike (
    Bikeid SMALLINT AUTO_INCREMENT PRIMARY KEY,
    BName VARCHAR(20) NOT NULL,
    Model VARCHAR(10) NOT NULL,
    BCost DECIMAL(8, 2),
    Pr_dt DATE
);
Enter fullscreen mode Exit fullscreen mode

2. Insert Rows into the Bike Table 

INSERT INTO Bike (Bikeid, BName, Model, BCost, Pr_dt) VALUES
(101, 'Royal Enfield', 'Hunter 350', 250000, '2023-06-25'),
(107, 'Bajaj', 'Platina', 90000, '2006-02-15'),
(45, 'Bajaj', 'Pulsar ns160', 160000, '2008-08-12'),
(105, 'Yamaha', 'R15', 190000, '2014-07-20'),
(185, 'Royal Enfield', 'Interceptor 650', 450000, '2018-11-14');
Enter fullscreen mode Exit fullscreen mode

3. Display the Structure of the Table 

DESCRIBE Bike;
Enter fullscreen mode Exit fullscreen mode

4. Rename the Column BName to BK_Name 

ALTER TABLE Bike CHANGE COLUMN BName BK_Name VARCHAR(20) NOT NULL;
Enter fullscreen mode Exit fullscreen mode

5. Add a Column Sale Date (sl_dt) Before pr_dt and Insert Values 

ALTER TABLE Bike ADD COLUMN sl_dt DATE BEFORE Pr_dt;

-- Inserting values for the first two rows
UPDATE Bike SET sl_dt = '2023-06-01' WHERE Bikeid = 101;
UPDATE Bike SET sl_dt = '2006-01-01' WHERE Bikeid = 107;
Enter fullscreen mode Exit fullscreen mode

6. Display the Contents of the Table 

SELECT * FROM Bike;
Enter fullscreen mode Exit fullscreen mode

7. Delete the Column Sale Date (sl_dt) 

ALTER TABLE Bike DROP COLUMN sl_dt;
Enter fullscreen mode Exit fullscreen mode

8. Insert a New Row into the Bike Table 

INSERT INTO Bike (Bikeid, BK_Name, Model, BCost, Pr_dt) VALUES
(1, 'Honda', 'Unicon', 175000, '2009-09-21');
Enter fullscreen mode Exit fullscreen mode

9. Update the Cost Value of Bikeid 101 

UPDATE Bike SET BCost = 230000 WHERE Bikeid = 101;
Enter fullscreen mode Exit fullscreen mode

10. Display the Bike Name, Cost, and 20% Rise of Bike Cost 

SELECT BK_Name, BCost, BCost * 1.20 AS Cost_Rise FROM Bike;
Enter fullscreen mode Exit fullscreen mode

11. Display the Total Price for Each BK_Name 

SELECT BK_Name, SUM(BCost) AS Total_Price FROM Bike GROUP BY BK_Name;
Enter fullscreen mode Exit fullscreen mode

12. Display Records in Descending Order of Bike Cost 

SELECT * FROM Bike ORDER BY BCost DESC;
Enter fullscreen mode Exit fullscreen mode

13. Delete Rows Where BK_Name is ‘Bajaj’ 

DELETE FROM Bike WHERE BK_Name = 'Bajaj';
Enter fullscreen mode Exit fullscreen mode

14. Rename the Table Bike to MBike 

RENAME TABLE Bike TO MBike;
Enter fullscreen mode Exit fullscreen mode

15. Delete All Rows from the Table and Delete the Database 

DELETE FROM MBike;

-- Alternatively, to remove all rows and reset auto-increment
-- TRUNCATE TABLE MBike;

DROP DATABASE BCOMXXX;
Enter fullscreen mode Exit fullscreen mode
💖 💪 🙅 🚩
devabdul
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

What was your win this week?
weeklyretro What was your win this week?

November 29, 2024

If you're a beginner, definitely check this open source guide. I've explained almost everything you need to know.
undefined If you're a beginner, definitely check this open source guide. I've explained almost everything you need to know.

November 28, 2024

Exploring Typesafe design tokens in Tailwind 4
tailwindcss Exploring Typesafe design tokens in Tailwind 4

November 29, 2024

Where GitOps Meets ClickOps
devops Where GitOps Meets ClickOps

November 29, 2024

The best way to get better at writing code is...
development The best way to get better at writing code is...

November 28, 2024