D.A.V.
School Pallikaranai
CLASS XII – COMPUTER SCIENCE (083)
SQL WORKSHEET
Database: SCHOOL
Table: STUDENT
The following table is to be used wherever required:
AdmNo Name Class Section Gender Marks City
101 Aarav 12 A M 92 Chennai
102 Diya 12 A F 85 Chennai
103 Rohan 12 B M 76 Madurai
104 Ananya 12 B F 95 Chennai
105 Kavin 12 A M 68 Coimbatore
106 Meera 12 B F 88 Madurai
107 Arjun 12 A M 72 Chennai
108 Nisha 12 B F 91 Coimbatore
Questions
1. Write an SQL command to create a database named SCHOOL.
2. Write the command to select the SCHOOL database for use.
3. Write the command to display all the databases available in MySQL.
4. Create a table named STUDENT with the following fields: AdmNo (INT, Primary Key), Name
(VARCHAR(30)), Class (INT), Section (CHAR(1)), Gender (CHAR(1)), Marks (INT) and City
(VARCHAR(20)).
5. Write the command to display all the tables present in the currently selected database.
6. Write the command to display the structure of the STUDENT table.
7. Add a new column Phone of type VARCHAR(15) to the STUDENT table.
8. Remove the Phone column from the STUDENT table.
9. Assuming that AdmNo is not currently a primary key, write the command to make AdmNo
the primary key.
10. Write the command to remove the primary key constraint from the STUDENT table.
11. Write a command to permanently remove the STUDENT table from the database.
12. Write a command to permanently remove the SCHOOL database.
13. Insert the following record into the STUDENT table:
109, 'Vikram', 12, 'A', 'M', 81, 'Chennai'
14. Display all the records of the STUDENT table.
15. Display only the Name and Marks of all students.
16. Display the names of students who have scored more than 85 marks.
17. Display the names of students who belong to Chennai and have scored more than 80
marks.
18. Display the details of students who belong to Chennai or Madurai.
19. Display the name of each student along with their marks after adding 5 marks to their
existing marks.
20. Display Name as Student_Name and Marks as Total_Marks.
21. Display the different cities from which the students belong, without repeating any city.
22. Display the details of students belonging to Section A.
23. Display the details of students whose city is either Chennai, Madurai or Coimbatore using
the appropriate operator.
24. Display the names and marks of students whose marks are between 75 and 90, including
both limits.
25. Display all student records in descending order of marks.
26. Display all students in ascending order of city and, within each city, in descending order of
marks.
27. What does NULL represent in a database? Does NULL mean zero or an empty string?
28. Assuming some students have no city entered, write a query to display the records where
City contains a NULL value.
29. Write a query to display the records of students whose City is not NULL.
30. Display the names of students whose names start with the letter A.
31. Display the names of students whose names end with the letter a.
32. Display the names of students whose names contain the characters an.
33. Increase the marks of all students by 3 marks.
34. Change the city of the student whose admission number is 103 to Chennai.
35. Find the highest marks obtained by any student.
36. Find the lowest marks obtained by any student.
37. Find the average marks of all students.
38. Find the total of all marks obtained by the students.
39. Find the total number of students in the STUDENT table.
40. Count the number of students whose City has been entered.
41. Display each city along with the number of students belonging to that city.
42. Display each city along with the average marks of students belonging to that city.
43. Display each section along with the highest marks obtained in that section.
44. Display the cities having more than 2 students.
45. Display those cities where the average marks are greater than 80.
46. Display each city and its average marks, considering only those cities whose average marks
are greater than 75. Arrange the result in descending order of average marks.
47. Display the names and marks of students who scored more than 80 marks.
48. Display the names of female students who belong to Chennai.
49. Display the details of students whose marks are either less than 70 or greater than 90.
50. Display the city and average marks of students belonging to each city, but show only those
cities where the average marks are greater than 80. Arrange the result in descending order
of average marks.