Curriculum

SQL-100-Queries-Solutions

MySQL

0/5

Text lesson

DML(Data Manipulation Language) Commands.

DML

Data Manipulation Language

INSERT, UPDATE, DELETE, SELECT, GROUP BY, HAVING, WHERE, LIKE, IN, NOT IN, JOIN( INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN), SET (UNION, UNION ALL, INTERSECTION, EXCEPT).

INSERT

1. Insert one record into the student table.

INSERT INTO student VALUES (1, ‘Amit’, 20, ‘M’, ‘BCA’, 85, ‘Kolkata’);

2. Insert another student record.

INSERT INTO student VALUES (2, ‘Riya’, 19, ‘F’, ‘BCA’, 78, ‘Delhi’);

3. Insert multiple records.

INSERT INTO student VALUES

(3, ‘Suman’, 21, ‘M’, ‘BSc’, 67, ‘Mumbai’),

(4, ‘Neha’, 20, ‘F’, ‘BCA’, 90, ‘Kolkata’);

4. Insert data without marks.

INSERT INTO student (student_id, name, age, gender, course, city)

VALUES (5, ‘Rahul’, 22, ‘M’, ‘BSc’, ‘Delhi’);

5. Insert 5 more records into the student table.

INSERT INTO student VALUES (6,’Pooja’,19,’F’,’BCom’,72,’Mumbai’);

INSERT INTO student VALUES (7,’Arjun’,21,’M’,’BCom’,88,’Kolkata’);

INSERT INTO student VALUES (8,’Sneha’,20,’F’,’BCA’,60,’Delhi’);

INSERT INTO student VALUES (9,’Vikas’,23,’M’,’BSc’,49,’Mumbai’);

INSERT INTO student VALUES (10,’Anita’,21,’F’,’BCom’,95,’Kolkata’);

INSERT INTO student VALUES (2,’Tina’,20,’F’,’BCom’,88,’Delhi’);

UPDATE

6. Update marks 80 for Student_id 3.

UPDATE student SET marks = 80 WHERE student_id = 3;

7. Change the city name to Pune for student_id 3.

UPDATE student SET city = ‘Pune’ WHERE student_id = 6;

8. Increase the marks of all BCA students by 5.

UPDATE student SET marks = marks + 5 WHERE course = ‘BCA’;

9. Change course BBA for student_id 3.

UPDATE student SET course = ‘BBA’ WHERE student_id = 8;

10. Increase age of all students by 1.

UPDATE student SET age = age + 1;

11. Chang marks 0 whose marks are in NULL.

UPDATE student SET marks = 0 WHERE marks IS NULL;

12. Change city name Chennai whose name is Rahul.

UPDATE student SET city = ‘Chennai’ WHERE name = ‘Rahul’;

13. Change marks 100 for student_id 10.

UPDATE student SET marks = 100 WHERE student_id = 10;

14. Change course BTech for student_id 5.

UPDATE student SET course = ‘BTech’ WHERE student_id = 5;

15. Change gender Female to F for all students,

UPDATE student SET gender = ‘F’ WHERE gender = ‘Female’;

16. Change semester 3 for all BCA students.

UPDATE student SET semester = 3 WHERE course = ‘BCA’;

17. Change semester 5 for all BSc students.

UPDATE student SET semester = 5 WHERE course = ‘BSc’;

18. Input admission_year 2025 of all students.

UPDATE student SET admission_year = 2024;

19. Increase marks by 10 for all students whose marks are greater than 80.

UPDATE student SET marks = marks + 10 WHERE marks > 80;

DELETE

20. Delete a student by ID 9.

DELETE FROM student WHERE student_id = 9;

21. Delete students with marks below 50.

DELETE FROM student WHERE marks < 50;

22. Delete records from students whose city is Delhi.

DELETE FROM student WHERE city = ‘Delhi’;

23. Delete records from students whose course is BBA.

DELETE FROM student WHERE course = ‘BBA’;

24. Delete records from students whose age is below 22.

DELETE FROM student WHERE age > 22;

25. Delete records from students whose marks are NULL.

DELETE FROM student WHERE marks IS NULL;

26. Delete records from students whose student_id is 12 and 13.

DELETE FROM student WHERE student_id IN (12,13);

27. Delete the records named Rahul.

DELETE FROM student WHERE name = ‘Rahul’;

28. Delete the records of BTech course.

DELETE FROM student WHERE course = ‘BTech’;

29. Delete all records from the student table.

DELETE FROM student;

SELECT & ORDER BY

30. Display all records from the student table.

SELECT * FROM student;

31. Display student names and marks.

SELECT name, marks FROM student;

32. Display records in ascending order of marks.

SELECT * FROM student ORDER BY marks ASC;

33. Display records in descending order of age.

SELECT * FROM student ORDER BY age DESC;

34. Display distinct cities.

SELECT DISTINCT city FROM student;

35. Display students in descending order of marks.

SELECT * FROM student ORDER BY marks DESC;

36. Display students in ascending order of name.

SELECT * FROM student ORDER BY name;

37. Display students in descending order of age.

SELECT * FROM student ORDER BY age DESC;

38. Display students in descending order of city and marks.

SELECT * FROM student ORDER BY city, marks DESC;

39. Display name, marks in ascending order of marks.

SELECT name, marks FROM student ORDER BY marks;

40. Display all unique cities from the student table in ascending order.

SELECT DISTINCT city FROM student ORDER BY city;

41. Display records from the student table in ascending order of course.

SELECT * FROM student ORDER BY course;

42. Display students in descending order of student_id.

SELECT * FROM student ORDER BY student_id DESC;

43. Display name in descending order of name.

SELECT name FROM student ORDER BY name DESC;

SELECT & GROUP BY (Aggregation Functions)

44. Count total students.

SELECT COUNT(*) FROM student;

45. Average marks course-wise.

SELECT course, AVG(marks) FROM student GROUP BY course;

46. Maximum marks city-wise.

SELECT city, MAX(marks) FROM student GROUP BY city;

47.Minimum marks city-wise.

SELECT city, MIN(marks) FROM student GROUP BY city;

48. Course-wise total marks.

SELECT course, SUM(marks) FROM student GROUP BY course;

49. Count the total students, gender-wise.

SELECT gender, COUNT(*) FROM student GROUP BY gender;

50. Course-wise total Students.

SELECT course, COUNT(student_id) FROM student GROUP BY course;

51. City-wise average age of students.

SELECT city, AVG(age) FROM student GROUP BY city;

52. Course-wise maximum marks of students.

SELECT course, MAX(marks) FROM student GROUP BY course;

53. Course-wise minimum marks of students.

SELECT course, MIN(marks) FROM student GROUP BY course;

54. City-wise total marks of students.

SELECT city, SUM(marks) FROM student GROUP BY city;

55. Gender-wise average marks.

SELECT gender, AVG(marks) FROM student GROUP BY gender;

56. Count unique courses City-wise.

SELECT city, COUNT(DISTINCT course) FROM student GROUP BY city;

SELECT, WHERE & LIKE, IN, NOT IN

57. Find students whose name starts with ‘A’.

SELECT * FROM student WHERE name LIKE ‘A%’;

58. Names ending with ‘a’.

SELECT * FROM student WHERE name LIKE ‘%a’;

59. Names having ‘i.

SELECT * FROM student WHERE name LIKE ‘%i%’;

60. Students record who lives in Kolkata & Delhi.

SELECT * FROM student WHERE city IN (‘Kolkata’,’Delhi’);

61. Find students’ records who got 70 to 90.

SELECT * FROM student WHERE marks BETWEEN 70 AND 90;

62. Students record those who do not live in Kolkata & Delhi.

SELECT * FROM student WHERE city NOT IN (‘Kolkata’,’Delhi’);

JOIN( INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN),

Brief Table of All SQL JOIN Types

JOIN Type	What it Returns	Key Point	Example Syntax
INNER JOIN	Only matching rows from both tables	Most commonly used	`A INNER JOIN B ON A.id = B.id`
LEFT JOIN	All rows from left table + matched rows from right table	Unmatched right → `NULL`	`A LEFT JOIN B ON A.id = B.id`
RIGHT JOIN	All rows from right table + matched rows from left table	Unmatched left → `NULL`	`A RIGHT JOIN B ON A.id = B.id`
FULL JOIN	All rows from both tables	Unmatched → `NULL` on either side	`A FULL JOIN B ON A.id = B.id`
CROSS JOIN	All possible combinations	Cartesian Product	`A CROSS JOIN B`
SELF JOIN	Table joined with itself	Uses aliases	`A a JOIN A b ON a.id = b.ref_id`
NATURAL JOIN	Auto-joins on same column names	Not recommended	`A NATURAL JOIN B`

63. Create a table S1 having the following fields

S1(Sid, Name, City)

CREATE TABLE S1 (

sid INT PRIMARY KEY,

Name VARCHAR(20),

City VARCHAR(20)

);

64. Create a table C1 having the following fields

C1(Sid, Cid, Course)

CREATE TABLE C1 (

sid INT PRIMARY KEY,

Course VARCHAR(10)

);

65. Add the following records to the S1 Table.

sid | name | city

——————-

1 | Ram | Delhi

2 | Sita | Kolkata

3 | Mohan | Mumbai

4 | Rina | Chennai

66. Add the following records to the C1 Table.

cid | sid | course

——————-

101 | 1 | SQL

102 | 2 | Python

103 | 2 | Java

104 | 5 | C++

67. Display the student’s name and course name for students enrolled in a course.

SELECT s.name, c.course

FROM S1 s

INNER JOIN C1 c

ON s.sid = c.sid;

68. Display sid, name, and course using INNER JOIN.

SELECT s.sid, s.name, c.course

FROM S1 s

INNER JOIN C1 c

ON s.sid = c.sid;

69. Display all students along with their courses (if any).

SELECT s.sid, s.name, c.course

FROM S1 s

LEFT JOIN C1 c

ON s.sid = c.sid;

70. Find students who have no course assigned.

SELECT s.sid, s.name

FROM S1 s

LEFT JOIN C1 c

ON s.sid = c.sid

WHERE c.sid IS NULL;

71. Display course name and student name using RIGHT JOIN.

SELECT c.course, s.name

FROM S1 s

RIGHT JOIN C1 c

ON s.sid = c.sid;

72. Find courses that do not belong to any student.

SELECT c.cid, c.course

FROM S1 s

RIGHT JOIN C1 c

ON s.sid = c.sid

WHERE s.sid IS NULL;

73. Display all students and all courses (matched or not).

SELECT s.sid, s.name, c.course

FROM S1 s

FULL OUTER JOIN C1 c

ON s.sid = c.sid;

74. Display all possible combinations of students and courses.

SELECT s.name, c.course

FROM S1 s

CROSS JOIN C1 c;

Different SET Queries in SQL

(UNION, UNION ALL, INTERSECT, EXCEPT/MINUS)

75. Display all distinct student IDs from both the S1 table and the C1 table.

SELECT sid FROM student

UNION

SELECT sid FROM course;

76. Display all student IDs from both the S1 table and the C1 table, including duplicate values.

SELECT sid FROM student

UNION ALL

SELECT sid FROM course;

77. Display the student IDs that are common to both the S1 table and the C1 table.

SELECT sid FROM student

INTERSECT

SELECT sid FROM course;

78. Display the student IDs from the S1 table that are not present in the C1 table.

SELECT sid FROM student

EXCEPT

SELECT sid FROM course;