SQL – Q2
1. Create a database named SchoolDB.
2. Select the SchoolDB database for use.
3. Display the list of all databases.
4. Create a table named Student with the following fields and constraints:
o AdmNo – INT, PRIMARY KEY, NOT NULL
o Name – VARCHAR(30), NOT NULL
o Gender – CHAR(1), CHECK (Gender IN ('M','F'))
o Class – INT, NOT NULL, CHECK (Class IN (11,12))
o Section – CHAR(1), NOT NULL, CHECK (Section IN ('A','B','C','D'))
o City – VARCHAR(20)
o Marks – FLOAT, CHECK (Marks BETWEEN 0 AND 100)
o DOB – DATE, NOT NULL
o Phone – VARCHAR(10), UNIQUE
5. Display the structure of the Student table.
6. Insert five student records into the Student table.
INSERT INTO Student (AdmNo, Name, Gender, Class, Section, City, Marks, DOB, Phone)
VALUES
(101, 'Aman', 'M', 12, 'A', 'Kolkata', 89.5, '2008-04-15', '9876543210'),
(102, 'Priya', 'F', 12, 'B', 'Delhi', NULL, '2008-02-10', '9876543211'),
(103, 'Rahul', 'M', 11, 'A', 'Mumbai', 76.5, '2009-06-22', '9876543212'),
(104, 'Sneha', 'F', 11, 'C', 'Chennai', NULL, '2009-09-18', '9876543213'),
(105, 'Arjun', 'M', 12, 'B', 'Pune', 68.5, '2008-11-30', '9876543214');
7. Display all the records from the Student table.
8. Display only the Name and Marks of all students.
9. Display the Name, City and Marks of all students.
10. Display the Name using the alias Student_Name.
11. Display the Name, Class and Marks using suitable aliases.
12. Display the Name and Marks after adding 5 bonus marks.
13. Display the Name and Marks after deducting 2 marks.
14. Display the Name and Marks after increasing the marks by 10%.
15. Display the Name and half of the Marks.
16. Display the distinct cities of all students.
17. Display the distinct sections.
18. Display students scoring more than 90 marks.
19. Display students scoring less than 50 marks.
20. Display students scoring 80 or above.
21. Display students scoring 75 or below.
22. Display students whose marks are not equal to 60.
23. Display students belonging to Class 12.
24. Display students of Class 12 from Kolkata.
25. Display students from Delhi or Mumbai.
26. Display students who are not from Kolkata.
27. Display female students of Class 12.
28. Display students belonging to Delhi, Kolkata and Mumbai.
29. Display all students arranged in ascending order of Name.
30. Display all students arranged in descending order of Marks.
31. Display students arranged first by Class and then by Name.
32. Display students whose marks are NULL.
33. Display students whose marks are NOT NULL.
34. Display students whose names start with 'A'.
35. Display students whose names end with 'a'.
36. Display students whose names contain 'h'.
37. Display students whose city starts with 'K'.
38. Display students whose names have exactly 5 characters.
SQL – Q3
Using the table named Student from previous question
1. Update the marks of Aman to 95.
2. Increase the marks of all Class 12 students by 5.
3. Change the city of Rahul to Bengaluru.
4. Update the section of all students of Class 11 to 'C'.
5. Delete the record of the student whose Admission Number is 105.
6. Delete all students scoring below 40 marks.
7. Display the highest marks obtained by any student.
8. Display the lowest marks obtained by any student.
9. Display the average marks of all students.
10. Display the total marks of all students.
11. Display the total number of students.
12. Display the number of students whose marks are available.
13. Display the average marks city-wise.
14. Display the maximum marks obtained in each city.
15. Display the minimum marks obtained in each section.
16. Display the total number of students in each class.
17. Display the total marks obtained by students of each section.
18. Display the cities having an average marks greater than 80.
19. Display the classes having more than 3 students.
20. Display the sections where the maximum marks are greater than 90.
21. Display the cities having more than 2 students.