Table 1: Students
Column Data Type Constraints
Name
StudentID INT PRIMARY KEY
Name VARCHAR(50) NOT NULL
Class VARCHAR(10)
DateOfBirth DATE
Gender CHAR(1) CHECK (Gender IN ('M','F'))
Table 2: Marks
Column Name Data Type Constraints
MarkID INT PRIMARY KEY
StudentID INT FOREIGN KEY REFERENCES Students(StudentID)
Subject VARCHAR(30) NOT NULL
MarksObtaine FLOAT
d
A. Data Definition Language (DDL)
1. Create the Students table with appropriate constraints.
2. Create the Marks table and define the foreign key referencing Students.
3. Show all databases and select the one you’re working on.
4. Alter the Students table to add a column Email of type VARCHAR(50).
5. Alter the Marks table to remove the column MarkID.
6. Drop the Marks table from the database.
7. Describe the structure of the Students table.
8. Show all the tables in the current database.
B. Data Manipulation Language (DML)
9. Insert 5 records into the Students table.
10. Insert corresponding marks for those students in at least 2 subjects each.
11. Update the class of a student whose name is 'Rohit Sharma' to 'XII-B'.
12. Delete the record of a student with StudentID = 104.
C. SELECT Queries and Clauses
13. Display all records from both tables.
14. Display names and classes of all students in 'XII-A'.
15. List students who scored more than 80 in 'Mathematics'.
16. Show names of students who were born between '2007-01-01' and '2008-12-31'.
17. Show all students whose name starts with 'A'.
18. Display the names and marks of students who scored less than 50 OR more than 90.
19. Show subjects where marks are between 40 and 60.
20. List student names and subjects for which marks are NULL or not NULL.
21. Use aliasing to show StudentID as ID and MarksObtained as Score.
D. Aggregate Functions and Grouping
22. Find the maximum, minimum, average, and total marks obtained in the subject 'Science'.
23. Count the number of students who have appeared for 'English'.
24. Display the total marks obtained by each student (use GROUP BY).
25. Display the average marks of each subject (GROUP BY Subject).
26. Show student IDs where the total marks are more than 150 (GROUP BY + HAVING).
E. JOINS
27. Display all student details along with their marks using a Cartesian Join.
28. Perform an Equi-Join to display StudentID, Name, Subject, and Marks.
29. Perform a Natural Join (if supported) between Students and Marks.
30. List students who have not received marks in any subject (use LEFT JOIN and IS NULL).
Answers to SQL Questions
A. Data Definition Language (DDL)
1. Create Students table:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Class VARCHAR(10),
DateOfBirth DATE,
Gender CHAR(1) CHECK (Gender IN ('M','F'))
);
2. Create Marks table:
CREATE TABLE Marks (
MarkID INT PRIMARY KEY,
StudentID INT,
Subject VARCHAR(30) NOT NULL,
MarksObtained FLOAT,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);
3. Show databases and use one:
SHOW DATABASES;
USE school;
4. Alter Students to add Email:
ALTER TABLE Students ADD Email VARCHAR(50);
5. Alter Marks to remove column MarkID:
ALTER TABLE Marks DROP COLUMN MarkID;
6. Drop Marks table:
DROP TABLE Marks;
7. Describe Students table:
DESCRIBE Students;
8. Show all tables:
SHOW TABLES;
B. Data Manipulation Language (DML)
9. Insert into Students:
INSERT INTO Students VALUES
(101, 'Rohit Sharma', 'XII-A', '2007-08-15', 'M'),
(102, 'Anjali Verma', 'XII-A', '2008-03-10', 'F'),
(103, 'Aman Khan', 'XII-B', '2007-12-25', 'M'),
(104, 'Neha Singh', 'XII-A', '2008-06-19', 'F'),
(105, 'Ravi Joshi', 'XII-B', '2007-11-11', 'M');
10. Insert into Marks:
INSERT INTO Marks VALUES
(1, 101, 'Maths', 88),
(2, 101, 'English', 76),
(3, 102, 'Maths', 92),
(4, 102, 'Science', 85),
(5, 103, 'English', 67),
(6, 104, 'Science', 81),
(7, 105, 'Maths', 55),
(8, 105, 'English', 45);
11. Update Class:
UPDATE Students SET Class = 'XII-B' WHERE Name = 'Rohit Sharma';
12. Delete StudentID = 104:
DELETE FROM Students WHERE StudentID = 104;
C. SELECT Queries and Clauses
13. All records from both tables:
SELECT * FROM Students;
SELECT * FROM Marks;
14. Names and class of XII-A students:
SELECT Name, Class FROM Students WHERE Class = 'XII-A';
15. Students scoring >80 in Maths:
SELECT StudentID, MarksObtained FROM Marks WHERE Subject = 'Maths' AND MarksObtained > 80;
16. DOB between range:
SELECT * FROM Students
WHERE DateOfBirth BETWEEN '2007-01-01' AND '2008-12-31';
17. Name starts with 'A':
SELECT * FROM Students WHERE Name LIKE 'A%';
18. Marks < 50 OR > 90:
SELECT * FROM Marks WHERE MarksObtained < 50 OR MarksObtained > 90;
19. Marks between 40 and 60:
SELECT * FROM Marks WHERE MarksObtained BETWEEN 40 AND 60;
20. NULL/Not NULL marks:
SELECT * FROM Marks WHERE MarksObtained IS NULL;
SELECT * FROM Marks WHERE MarksObtained IS NOT NULL;
21. Aliasing:
SELECT StudentID AS ID, MarksObtained AS Score FROM Marks;
D. Aggregate Functions and Grouping
22. Max, Min, Avg, Sum for Science:
SELECT MAX(MarksObtained), MIN(MarksObtained), AVG(MarksObtained), SUM(MarksObtained)
FROM Marks WHERE Subject = 'Science';
23. Count students in English:
SELECT COUNT(StudentID) FROM Marks WHERE Subject = 'English';
24. Total marks by each student:
SELECT StudentID, SUM(MarksObtained) AS TotalMarks
FROM Marks
GROUP BY StudentID;
25. Average marks per subject:
SELECT Subject, AVG(MarksObtained) AS AvgMarks
FROM Marks
GROUP BY Subject;
26. Total marks > 150:
SELECT StudentID, SUM(MarksObtained) AS Total
FROM Marks
GROUP BY StudentID
HAVING SUM(MarksObtained) > 150;
E. JOINS
27. Cartesian Join:
SELECT * FROM Students, Marks;
28. Equi-Join:
SELECT Students.StudentID, Name, Subject, MarksObtained
FROM Students
JOIN Marks ON Students.StudentID = Marks.StudentID;
29. Natural Join (if supported):
SELECT * FROM Students NATURAL JOIN Marks;
30. Students without marks:
SELECT Students.StudentID, Name
FROM Students
LEFT JOIN Marks ON Students.StudentID = Marks.StudentID
WHERE Marks.StudentID IS NULL;
