Student Table with SQL Queries
Student Table
Roll No Name Class Subject 1 Subject 2 Subject 3 Subject
101 Alice 12 Mathematics Physics Chemistry English
102 Bob 12 Biology Physics Chemistry English
103 Charlie 11 Mathematics Computer Chemistry English
104 Diana 12 Mathematics Physics Biology English
105 Ethan 11 Mathematics History Geography English
SQL Queries and Outputs
Query: Retrieve all students in Class 12.
SQL Command: SELECT * FROM Student WHERE Class = 12;
Output:
| Roll No | Name | Class | Subject 1 | Subject 2 | Subject 3 | Subject 4 |
|---------|-------|-------|-------------|-----------|-------------|-----------|
| 101 | Alice | 12 | Mathematics | Physics | Chemistry | English |
| 102 | Bob | 12 | Biology | Physics | Chemistry | English |
| 104 | Diana | 12 | Mathematics | Physics | Biology | English |
Query: Find students taking 'Mathematics' as Subject 1.
SQL Command: SELECT RollNo, Name FROM Student WHERE Subject1 = 'Mathematics';
Output:
| Roll No | Name |
|---------|---------|
| 101 | Alice |
| 103 | Charlie |
| 104 | Diana |
| 105 | Ethan |
Query: Count the number of students in each class.
SQL Command: SELECT Class, COUNT(*) AS StudentCount FROM Student GROUP BY Class;
Output:
| Class | StudentCount |
|-------|--------------|
| 11 |2 |
| 12 |3 |
Query: Retrieve the name and roll number of students studying 'Physics' in Subject 2.
SQL Command: SELECT RollNo, Name FROM Student WHERE Subject2 = 'Physics';
Output:
| Roll No | Name |
|---------|-------|
| 101 | Alice |
| 102 | Bob |
| 104 | Diana |
Query: List all unique subjects in the table.
SQL Command: SELECT DISTINCT Subject1 AS Subject FROM Student
UNION
SELECT DISTINCT Subject2 FROM Student
UNION
SELECT DISTINCT Subject3 FROM Student
UNION
SELECT DISTINCT Subject4 FROM Student;
Output:
| Subject |
|---------------|
| Mathematics |
| Biology |
| Physics |
| Chemistry |
| Computer |
| History |
| Geography |
| English |
