Assume the table name is STUDENT with fields:
RollNo, Name, Marks, City
Q1. SQL Queries
1. Display all records from STUDENT table.
SELECT * FROM STUDENT;
2. Display Name and Marks of all students.
SELECT Name, Marks FROM STUDENT;
3. Display students scoring more than 80 marks.
SELECT * FROM STUDENT
WHERE Marks > 80;
4. Display records of students from Delhi.
SELECT * FROM STUDENT
WHERE City = 'Delhi';
5. Display names in alphabetical order.
SELECT Name FROM STUDENT
ORDER BY Name ASC;
6. Display highest marks from the table.
SELECT MAX(Marks) AS Highest_Marks
FROM STUDENT;
7. Display lowest marks from the table.
SELECT MIN(Marks) AS Lowest_Marks
FROM STUDENT;
8. Count total number of students.
SELECT COUNT(*) AS Total_Students
FROM STUDENT;
9. Display average marks of students.
SELECT AVG(Marks) AS Average_Marks
FROM STUDENT;
10. Display names starting with letter ‘R’.
SELECT Name FROM STUDENT
WHERE Name LIKE 'R%';
11. Display records where marks are between 70 and 90.
SELECT * FROM STUDENT
WHERE Marks BETWEEN 70 AND 90;
12. Increase marks by 5 for all students.
UPDATE STUDENT
SET Marks = Marks + 5;
13. Delete records of students scoring below 70.
DELETE FROM STUDENT
WHERE Marks < 70;
14. Display distinct city names.
SELECT DISTINCT City
FROM STUDENT;
15. Display second highest marks.
SELECT MAX(Marks) AS Second_Highest
FROM STUDENT
WHERE Marks < (SELECT MAX(Marks) FROM STUDENT);
Q2. SQL Functions
Aggregate Functions
1. Find maximum marks.
SELECT MAX(Marks) FROM STUDENT;
2. Find minimum marks.
SELECT MIN(Marks) FROM STUDENT;
3. Find average marks.
SELECT AVG(Marks) FROM STUDENT;
4. Find sum of marks.
SELECT SUM(Marks) FROM STUDENT;
5. Count number of students.
SELECT COUNT(*) FROM STUDENT;
String Functions
6. Display names in uppercase.
SELECT UPPER(Name) FROM STUDENT;
7. Display first three characters of each name.
SELECT SUBSTRING(Name,1,3) FROM STUDENT;
8. Find length of each student name.
SELECT Name, LENGTH(Name) FROM STUDENT;
9. Replace Delhi with New Delhi.
SELECT REPLACE(City,'Delhi','New Delhi')
FROM STUDENT;
Date Functions
10. Display current date.
SELECT CURDATE();
11. Display current month and year.
SELECT MONTH(CURDATE()), YEAR(CURDATE());
Numeric Functions
12. Find square root of 144.
SELECT SQRT(144);
13. Find remainder when 25 is divided by 4.
SELECT MOD(25,4);
14. Round off value 45.678 to 2 decimal places.
SELECT ROUND(45.678,2);
Q3. GROUP BY and ORDER BY
1. Display city-wise average marks.
SELECT City, AVG(Marks)
FROM STUDENT
GROUP BY City;
2. Display city-wise maximum marks.
SELECT City, MAX(Marks)
FROM STUDENT
GROUP BY City;
3. Count number of students city-wise.
SELECT City, COUNT(*)
FROM STUDENT
GROUP BY City;
4. Display records in descending order of marks.
SELECT * FROM STUDENT
ORDER BY Marks DESC;
5. Display names in ascending order.
SELECT Name FROM STUDENT
ORDER BY Name ASC;
6. Display city-wise total marks having average marks greater than 80.
SELECT City, SUM(Marks)
FROM STUDENT
GROUP BY City
HAVING AVG(Marks) > 80;