← Back to Home
MySQL
CS10 Laboratory
Data Tables
Table: STUDENT
| Roll no. |
Name |
Class |
Marks |
City |
| 1 |
Amit Sharma |
XII |
85 |
Delhi |
| 2 |
Neha Verma |
XI |
78 |
Mumbai |
| 3 |
Rohit Kumar |
XII |
92 |
Delhi |
| 4 |
Sneha Singh |
XI |
88 |
Kolkata |
| 5 |
Arjun Mehta |
XII |
74 |
Chennai |
Table: SPORTS
| Sports ID |
Roll no. |
Sports Name |
Level |
| 101 |
1 |
Football |
School |
| 102 |
2 |
Cricket |
State |
| 103 |
3 |
Badminton |
National |
| 104 |
4 |
Athletics |
School |
| 105 |
5 |
Basketball |
State |
| 106 |
6 |
Athletics |
School |
| 107 |
7 |
Hockey |
National |
Questions and Queries
Q.1) Display name of all students.
SELECT Name FROM STUDENT;
| Name |
| Amit Sharma |
| Neha Verma |
| Rohit Kumar |
| Sneha Singh |
| Arjun Mehta |
Q.2) Show the name of students who scored more than 80 marks.
SELECT Name FROM STUDENT WHERE Marks > 80;
| Name |
| Amit Sharma |
| Rohit Kumar |
| Sneha Singh |
Q.3) List the name of students belonging to Class 12.
SELECT Name FROM STUDENT WHERE Class = 'XII';
| Name |
| Amit Sharma |
| Rohit Kumar |
| Arjun Mehta |
Q.4) Display the name and marks of students in descending order of marks.
SELECT Name, Marks FROM STUDENT ORDER BY Marks DESC;
| Name |
Marks |
| Rohit Kumar |
92 |
| Sneha Singh |
88 |
| Amit Sharma |
85 |
| Neha Verma |
78 |
| Arjun Mehta |
74 |
Q.5) Count the no. of students from each city.
SELECT City, COUNT(*) AS "Total Students" FROM STUDENT GROUP BY City;
| City |
Total Students |
| Delhi |
2 |
| Mumbai |
1 |
| Kolkata |
1 |
| Chennai |
1 |
Q.6) Display the highest marks obtained by any student.
SELECT MAX(Marks) AS "Highest Marks" FROM STUDENT;
Q.7) Show the name of student who participated in sports (using join).
📚 Solution by Sir:
SELECT STUDENT.Name AS "STUDENT NAME", SPORTS.Sports_Name AS "SPORTS NAME" FROM STUDENT, SPORTS WHERE STUDENT.Roll_no = SPORTS.Roll_no;
| STUDENT NAME |
SPORTS NAME |
| Amit Sharma |
Football |
| Amit Sharma |
Athletics |
| Neha Verma |
Athletics |
| Rohit Kumar |
Cricket |
| Rohit Kumar |
Hockey |
| Sneha Singh |
Badminton |
| Arjun Mishra |
Basketball |
🤖 Solution by ChatGPT:
Without JOIN keyword:
SELECT Name FROM STUDENT, SPORTS WHERE STUDENT.Roll_no = SPORTS.Roll_no;
Using INNER JOIN:
SELECT STUDENT.Name FROM STUDENT INNER JOIN SPORTS ON STUDENT.Roll_no = SPORTS.Roll_no;
| Name |
| Amit Sharma |
| Neha Verma |
| Rohit Kumar |
| Sneha Singh |
| Arjun Mehta |
Q.8) Display the name of students who play at the national level.
SELECT Name FROM STUDENT, SPORTS WHERE STUDENT.Roll_no = SPORTS.Roll_no AND Level = 'National';
Q.9) Display all the unique cities of students.
SELECT DISTINCT City FROM STUDENT;
| City |
| Delhi |
| Mumbai |
| Kolkata |
| Chennai |
Q.10) Find the total marks obtained by all the students.
SELECT SUM(Marks) AS "Total Marks" FROM STUDENT;