SQL Assignment: Student Database

Task 1: Database and Table Creation

Query:
CREATE DATABASE college_db; USE college_db; CREATE TABLE student ( id INT PRIMARY KEY, name
VARCHAR(50), age INT, city VARCHAR(50), marks INT );
Output: Database and table created successfully.

Task 2: Insert Records

Query:
INSERT INTO student VALUES (1,'Amit',19,'Delhi',85), (2,'Rahul',18,'Dehradun',72), (3,'Sneha',20,'Delhi',90),
(4,'Priya',17,'Haridwar',65), (5,'Rohit',21,'Delhi',78), (6,'Anjali',19,'Dehradun',88), (7,'Karan',18,'Haridwar',55),
(8,'Simran',20,'Delhi',92);
Output:
ID Name Age City Marks
1 Amit 19 Delhi 85
2 Rahul 18 Dehradun 72
3 Sneha 20 Delhi 90
4 Priya 17 Haridwar 65
5 Rohit 21 Delhi 78
6 Anjali 19 Dehradun 88
7 Karan 18 Haridwar 55
8 Simran 20 Delhi 92

Task 3(a): Display all records

Query:
SELECT * FROM student;
Output:
ID Name Age City Marks
1 Amit 19 Delhi 85
2 Rahul 18 Dehradun 72
3 Sneha 20 Delhi 90
4 Priya 17 Haridwar 65
5 Rohit 21 Delhi 78
6 Anjali 19 Dehradun 88
7 Karan 18 Haridwar 55
8 Simran 20 Delhi 92

Task 3(b): Display name and marks

Query:
SELECT name, marks FROM student;
Output:
Name Marks
Amit 85
Rahul 72
 Sneha 90
Priya 65
Rohit 78
Anjali 88
Karan 55
Simran 92

Task 3(c): Age > 18

Query:
SELECT * FROM student WHERE age > 18;
Output:
ID Name Age City Marks
1 Amit 19 Delhi 85
3 Sneha 20 Delhi 90
5 Rohit 21 Delhi 78
6 Anjali 19 Dehradun 88
8 Simran 20 Delhi 92

Task 3(d): Marks < 70

Query:
SELECT * FROM student WHERE marks < 70;
Output:
ID Name Age City Marks
4 Priya 17 Haridwar 65
7 Karan 18 Haridwar 55

Task 4(a): Marks > 75

Query:
SELECT * FROM student WHERE marks > 75;
Output:
ID Name Age City Marks
1 Amit 19 Delhi 85
3 Sneha 20 Delhi 90
5 Rohit 21 Delhi 78
6 Anjali 19 Dehradun 88
8 Simran 20 Delhi 92

Task 4(b): City = Delhi

Query:
SELECT * FROM student WHERE city = 'Delhi';
Output:
ID Name Age City Marks
1 Amit 19 Delhi 85
3 Sneha 20 Delhi 90
 5 Rohit 21 Delhi 78
8 Simran 20 Delhi 92

Task 4(c): City = Delhi OR Dehradun

Query:
SELECT * FROM student WHERE city IN ('Delhi','Dehradun');
Output:
ID Name Age City Marks
1 Amit 19 Delhi 85
2 Rahul 18 Dehradun 72
3 Sneha 20 Delhi 90
5 Rohit 21 Delhi 78
6 Anjali 19 Dehradun 88
8 Simran 20 Delhi 92

Task 4(d): Marks between 60 and 80

Query:
SELECT * FROM student WHERE marks BETWEEN 60 AND 80;
Output:
ID Name Age City Marks
2 Rahul 18 Dehradun 72
4 Priya 17 Haridwar 65
5 Rohit 21 Delhi 78

Task 5(a): Age > 18 AND Marks > 75

Query:
SELECT * FROM student WHERE age > 18 AND marks > 75;
Output:
ID Name Age City Marks
1 Amit 19 Delhi 85
3 Sneha 20 Delhi 90
5 Rohit 21 Delhi 78
6 Anjali 19 Dehradun 88
8 Simran 20 Delhi 92

Task 5(b): Delhi students with marks > 80

Query:
SELECT * FROM student WHERE city='Delhi' AND marks > 80;
Output:
ID Name Age City Marks
1 Amit 19 Delhi 85
3 Sneha 20 Delhi 90
8 Simran 20 Delhi 92
Task 6(a): Order by marks ASC
Query:
SELECT * FROM student ORDER BY marks ASC;
Output:
ID Name Age City Marks
7 Karan 18 Haridwar 55
4 Priya 17 Haridwar 65
2 Rahul 18 Dehradun 72
5 Rohit 21 Delhi 78
1 Amit 19 Delhi 85
6 Anjali 19 Dehradun 88
3 Sneha 20 Delhi 90
8 Simran 20 Delhi 92

Task 6(b): Order by marks DESC

Query:
SELECT * FROM student ORDER BY marks DESC;
Output:
ID Name Age City Marks
8 Simran 20 Delhi 92
3 Sneha 20 Delhi 90
6 Anjali 19 Dehradun 88
1 Amit 19 Delhi 85
5 Rohit 21 Delhi 78
2 Rahul 18 Dehradun 72
4 Priya 17 Haridwar 65
7 Karan 18 Haridwar 55

Task 6(c): Top 3 students

Query:
SELECT * FROM student ORDER BY marks DESC LIMIT 3;
Output:
ID Name Age City Marks
8 Simran 20 Delhi 92
3 Sneha 20 Delhi 90
6 Anjali 19 Dehradun 88

Task 6(d): Bottom 2 students

Query:
SELECT * FROM student ORDER BY marks ASC LIMIT 2;
Output:
ID Name Age City Marks
7 Karan 18 Haridwar 55
4 Priya 17 Haridwar 65
Task 7(a): Name starts with 'A'
Query:
SELECT * FROM student WHERE name LIKE 'A%';
Output:
ID Name Age City Marks
1 Amit 19 Delhi 85
6 Anjali 19 Dehradun 88

Task 7(b): Name ends with 'a'

Query:
SELECT * FROM student WHERE name LIKE '%a';
Output:
ID Name Age City Marks
3 Sneha 20 Delhi 90
4 Priya 17 Haridwar 65

Task 8: Aggregate Functions

SELECT COUNT(*) FROM student; --> Output: 8
SELECT AVG(marks) FROM student; --> Output: 78.13
SELECT MAX(marks) FROM student; --> Output: 92
SELECT MIN(marks) FROM student; --> Output: 55

Task 9(a): Highest marks

Query:
SELECT * FROM student WHERE marks = (SELECT MAX(marks) FROM student);
Output:
ID Name Age City Marks
8 Simran 20 Delhi 92

Task 9(b): Second highest marks

Query:
SELECT * FROM student WHERE marks = ( SELECT MAX(marks) FROM student WHERE marks < (SELECT
MAX(marks) FROM student) );
Output:
ID Name Age City Marks
3 Sneha 20 Delhi 90

Task 9(c): Above average marks

Query:
SELECT * FROM student WHERE marks > (SELECT AVG(marks) FROM student);
Output:
ID Name Age City Marks
1 Amit 19 Delhi 85
3 Sneha 20 Delhi 90
6 Anjali 19 Dehradun 88
8 Simran 20 Delhi 92
Task 10: Top scoring student from Delhi
Query:
SELECT * FROM student WHERE city='Delhi' ORDER BY marks DESC LIMIT 1;
Output:
ID Name Age City Marks
8 Simran 20 Delhi 92