-- 1.
Create database in your name
CREATE DATABASE jon;
-- 2. Create Table in your Father's name, with 2
columns (ID & Name). (No primary key)
USE jon;
CREATE TABLE Father (
ID INT,
Name VARCHAR(50)
);
-- 3. Display table structure
DESC Father;
-- 4. Grant all permissions to a user (username:
'DBMS') on this table
GRANT ALL PRIVILEGES ON Ismail.Father TO
'DBMS'@'localhost';
-- 5. Insert 3 records
INSERT INTO Father (ID, Name) VALUES (1,
'Ali'), (2, 'Ahmed'), (3, 'Rahim');
-- 6. Insert value into any one column
INSERT INTO Father (ID) VALUES (4);
-- 7. Display all records
SELECT * FROM Father;
-- 8. Add Primary key & NOT NULL constraint
(name it ‘TOP’)
ALTER TABLE Father ADD CONSTRAINT TOP
PRIMARY KEY (ID);
ALTER TABLE Father MODIFY Name
VARCHAR(50) NOT NULL;
-- 9. Display table structure
DESC Father;
-- 10. Insert 4th record and display all records
INSERT INTO Father (ID, Name) VALUES (5,
'Hassan');
SELECT * FROM Father;
-- 11. Insert 5th record to check primary key &
not null constraints (Should fail if
duplicate/null)
INSERT INTO Father (ID, Name) VALUES (5,
NULL); -- Should fail due to NOT NULL
INSERT INTO Father (ID, Name) VALUES (6,
'Karim'); -- Should succeed
-- 12. Add column ‘designation’ with domain
(HR, Trainee, Team lead)
ALTER TABLE Father ADD COLUMN
Designation ENUM('HR', 'Trainee', 'Team
lead');
-- 13. Insert 2 records
INSERT INTO Father (ID, Name, Designation)
VALUES (7, 'Sameer', 'HR'), (8, 'Irfan', 'Trainee');
-- 14. Change column Name to X
ALTER TABLE Father CHANGE Name X
VARCHAR(50);
-- 15. Update 1st designation value to 'X'
UPDATE Father SET Designation = 'X' WHERE
ID = 1;
-- 16. Delete 2nd record
DELETE FROM Father WHERE ID = 2;
-- 17. Implement rollback concept for insert
operation
START TRANSACTION;
INSERT INTO Father (ID, X, Designation)
VALUES (9, 'Faizan', 'Team lead');
ROLLBACK; -- This will undo the insert
-- 18. Create another Table in your Mother's
name with constraints
CREATE TABLE Mother (
MID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT CHECK (Age > 18),
Address VARCHAR(100) UNIQUE,
Salary DECIMAL(10,2) DEFAULT 50000
);
-- 19. Implement foreign key concept
(Constraint name: 'MY')
ALTER TABLE Father ADD COLUMN MID INT;
ALTER TABLE Father ADD CONSTRAINT MY
FOREIGN KEY (MID) REFERENCES
Mother(MID);
-- 20. Implement rollback for update
operation
START TRANSACTION;
UPDATE Father SET Designation = 'HR' WHERE
ID = 3;
ROLLBACK;
-- 21. Rename table Mother to MOM
ALTER TABLE Mother RENAME TO MOM;
-- 22. Create last table named AITM_Student
CREATE TABLE AITM_Student (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Mark INT
);
-- 23. Add columns - ID, Name, Age, Mark
(already added in step 22)
-- 24. Insert 5 records (Age should be a
combination of 20 & 21)
INSERT INTO AITM_Student VALUES
(1, 'Aman', 20, 75),
(2, 'Bilal', 21, 80),
(3, 'Chetan', 20, 65),
(4, 'Deepak', 21, 50),
(5, 'Ehsan', 20, 40);
-- 25. Count number of student names and
display as ‘MYCOUNTING’
SELECT COUNT(Name) AS MYCOUNTING
FROM AITM_Student;
-- 26. Find max age & min age
SELECT MAX(Age) AS Max_Age, MIN(Age) AS
Min_Age FROM AITM_Student;
-- 27. Calculate sum & average of marks
SELECT SUM(Mark) AS Total_Marks,
AVG(Mark) AS Average_Marks FROM
AITM_Student;
-- 28. Display marks in descending order
SELECT * FROM AITM_Student ORDER BY
Mark DESC;
-- 29. Group the marks
SELECT Mark, COUNT(*) FROM AITM_Student
GROUP BY Mark;
-- 30. Display ID column
SELECT ID FROM AITM_Student;
-- 31. Display particular name (Example:
'Bilal')
SELECT * FROM AITM_Student WHERE Name =
'Bilal';
-- 32. Display records with condition (age=20
& mark>50) or (age=21 & mark<50)
SELECT * FROM AITM_Student WHERE (Age =
20 AND Mark > 50) OR (Age = 21 AND Mark <
50);
-- 33. Display marks between 60 & 80
SELECT * FROM AITM_Student WHERE Mark
BETWEEN 60 AND 80;
-- 34. Display names starting with a particular
letter ('A')
SELECT * FROM AITM_Student WHERE Name
LIKE 'A%';
-- 35. Display names starting & ending with a
particular letter ('A' and 'n')
SELECT * FROM AITM_Student WHERE Name
LIKE 'A%n';
-- 36. Display names starting with a particular
letter ('C') and having a specific letter in the
3rd place ('e')
SELECT * FROM AITM_Student WHERE Name
LIKE 'C_e%';
