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
Abdul Haseeb
