AS Level Database SQL Commands – Questions + Solutions
AS Level Database SQL Commands – Questions + Solutions
1. SELECT
Question: Display all records from the Students table.
Solution:
SELECT * FROM Students;
Question: Display only StudentName and Age from the table.
Solution:
SELECT StudentName, Age
FROM Students;
2. WHERE
Question: Show students whose age is greater than 16.
Solution:
SELECT * FROM Students
WHERE Age > 16;
Question: Display students living in Karachi.
Solution:
SELECT * FROM Students
WHERE City = 'Karachi';
3. ORDER BY
Question: Display all students sorted by marks in ascending order.
Solution:
SELECT * FROM Students
ORDER BY Marks ASC;
Question: Sort students by marks from highest to lowest.
Solution:
SELECT * FROM Students
ORDER BY Marks DESC;
4. INSERT INTO
Question: Insert a new student into the table.
Solution:
INSERT INTO Students
(StudentID, StudentName, Age, City)
VALUES
(101, 'Ali', 17, 'Karachi');
5. UPDATE
Question: Change Ali's city to Lahore.
Solution:
UPDATE Students
SET City = 'Lahore'
WHERE StudentName = 'Ali';
Question: Increase marks by 5 for StudentID 101.
Solution:
UPDATE Students
SET Marks = Marks + 5
WHERE StudentID = 101;
6. DELETE
Question: Delete the student whose ID is 101.
Solution:
DELETE FROM Students
WHERE StudentID = 101;
7. CREATE TABLE
Question: Create a table named Students.
Solution:
CREATE TABLE Students (
StudentID INT,
StudentName VARCHAR(50),
Age INT,
City VARCHAR(30)
);
8. ALTER TABLE
Question: Add a column called Email.
Solution:
ALTER TABLE Students
ADD Email VARCHAR(50);
Question: Remove the Email column.
Solution:
ALTER TABLE Students
DROP COLUMN Email;
9. DROP TABLE
Question: Delete the Students table completely.
Solution:
DROP TABLE Students;
10. COUNT()
Question: Count total students.
Solution:
SELECT COUNT(*)
FROM Students;
11. SUM()
Question: Find total marks of all students.
Solution:
SELECT SUM(Marks)
FROM Students;
12. AVG()
Question: Find average marks.
Solution:
SELECT AVG(Marks)
FROM Students;
13. MAX()
Question: Find highest marks.
Solution:
SELECT MAX(Marks)
FROM Students;
14. MIN()
Question: Find lowest marks.
Solution:
SELECT MIN(Marks)
FROM Students;
15. LIKE
Question: Find students whose name starts with A.
Solution:
SELECT * FROM Students
WHERE StudentName LIKE 'A%';
Question: Find names ending with n.
Solution:
SELECT * FROM Students
WHERE StudentName LIKE '%n';
16. BETWEEN
Question: Find students aged between 15 and 18.
Solution:
SELECT * FROM Students
WHERE Age BETWEEN 15 AND 18;
17. IN
Question: Find students from Karachi or Lahore.
Solution:
SELECT * FROM Students
WHERE City IN ('Karachi', 'Lahore');
18. AND
Question: Find students from Karachi with marks above 80.
Solution:
SELECT * FROM Students
WHERE City = 'Karachi'
AND Marks > 80;
19. OR
Question: Find students from Karachi or students older than 18.
Solution:
SELECT * FROM Students
WHERE City = 'Karachi'
OR Age > 18;
20. NOT
Question: Find students not from Karachi.
Solution:
SELECT * FROM Students
WHERE NOT City = 'Karachi';
21. PRIMARY KEY
Question: Create a table with StudentID as primary key.
Solution:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),
Age INT
);
22. FOREIGN KEY
Question: Create Orders table linked with Customers table.
Solution:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
);
23. DISTINCT
Question: Show different city names only.
Solution:
SELECT DISTINCT City
FROM Students;
24. GROUP BY
Question: Count students in each city.
Solution:
SELECT City, COUNT(*)
FROM Students
GROUP BY City;
25. HAVING
Question: Show cities having more than 5 students.
Solution:
SELECT City, COUNT(*)
FROM Students
GROUP BY City
HAVING COUNT(*) > 5;
26. INNER JOIN
Question: Display student names with their course names.
Solution:
SELECT [Link], [Link]
FROM Students
INNER JOIN Courses
ON [Link] = [Link];
27. AS (Alias)
Question: Display StudentName as Name.
Solution:
SELECT StudentName AS Name
FROM Students;
28. LIMIT
Question: Display first 5 students.
Solution:
SELECT * FROM Students
LIMIT 5;
29. IS NULL
Question: Find students whose email is empty.
Solution:
SELECT * FROM Students
WHERE Email IS NULL;
30. IS NOT NULL
Question: Find students having email addresses.
Solution:
SELECT * FROM Students
WHERE Email IS NOT NULL;