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
);

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');

3. Display the Structure of the Table

DESCRIBE Bike;

4. Rename the Column BName to BK_Name

ALTER TABLE Bike CHANGE COLUMN BName BK_Name VARCHAR(20) NOT NULL;

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;

6. Display the Contents of the Table

SELECT * FROM Bike;

7. Delete the Column Sale Date (sl_dt)

ALTER TABLE Bike DROP COLUMN sl_dt;

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');

9. Update the Cost Value of Bikeid 101

UPDATE Bike SET BCost = 230000 WHERE Bikeid = 101;

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

SELECT BK_Name, BCost, BCost * 1.20 AS Cost_Rise FROM Bike;

11. Display the Total Price for Each BK_Name

SELECT BK_Name, SUM(BCost) AS Total_Price FROM Bike GROUP BY BK_Name;

12. Display Records in Descending Order of Bike Cost

SELECT * FROM Bike ORDER BY BCost DESC;

13. Delete Rows Where BK_Name is ‘Bajaj’

DELETE FROM Bike WHERE BK_Name = 'Bajaj';

14. Rename the Table Bike to MBike

RENAME TABLE Bike TO MBike;

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;