SQL Query Interview Questions and Answers

This section provides example SQL queries and their solutions, covering various SQL operations and techniques.

SQL Table Structures:

We will use two tables for the following SQL query examples:

Student Table:

Subject Table:

Query 1: Creating Tables.

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
);

(Example queries to create the Student and Subject tables are given in the original text.)

Query 2: Inserting Data.

INSERT INTO table_name VALUES (value1, value2, ...);

(Example queries to insert data into the Student and Subject tables are provided in the original text.)

Query 3: Selecting Specific Records.

SELECT * FROM table_name WHERE condition;

(Example queries demonstrating the use of the WHERE clause are given in the original text.)

Query 4: Finding Minimum and Maximum Values.

SELECT MAX(column_name), MIN(column_name) FROM table_name;

Query 5: Accessing the First Record.

SELECT * FROM table_name WHERE ROWNUM = 1;

Query 6: Accessing the Last Record.

SELECT * FROM table_name WHERE rowid = (SELECT MAX(rowid) FROM table_name);

Query 7: Accessing the First N Rows.

SELECT * FROM table_name WHERE ROWNUM <= N;

Query 8: Accessing the Last N Rows.

SELECT * FROM (SELECT * FROM table_name ORDER BY rowid DESC) WHERE ROWNUM <= N;

Query 9: Retrieving Even-Numbered Rows.

SELECT * FROM table_name WHERE MOD(ROWID, 2) = 0;

Query 10: Retrieving Odd-Numbered Rows.

SELECT * FROM table_name WHERE MOD(ROWID, 2) = 1;

Query 11: Creating a Table from an Existing Table.

CREATE TABLE new_table_name AS SELECT * FROM existing_table_name;

Query 12: Finding the Nth Highest Value.

SELECT TOP 1 column_name
FROM (SELECT DISTINCT TOP N column_name FROM table_name ORDER BY column_name DESC) AS temp
ORDER BY column_name ASC;

Query 13: Finding the Second Highest Value.

SELECT MAX(column_name) FROM table_name
WHERE column_name < (SELECT MAX(column_name) FROM table_name);

Query 14: Displaying the Current Date and Time.

SELECT GETDATE(); -- SQL Server
SELECT NOW(); -- MySQL
SELECT SYSDATE; -- Oracle

Query 15: Retrieving the Top Three Highest Values.

SELECT column_name FROM (SELECT DISTINCT column_name FROM table_name ORDER BY column_name DESC) WHERE ROWNUM <= 3;

Query 16: Selecting Specific Columns Based on a Condition.

SELECT Stu_Name, Stu_Marks FROM Student WHERE Stu_Age = 20;

Query 17: Finding the Maximum Marks per Subject.

SELECT Stu_Subject_ID, MAX(Stu_Marks) AS MaxMarks
FROM Student
GROUP BY Stu_Subject_ID;

Query 18: Selecting Records Based on Multiple Conditions.

SELECT * FROM Student WHERE Stu_Marks > 82 AND Stu_Age = 22;

Query 19: Selecting Records Based on a Pattern.

SELECT * FROM Student WHERE Stu_Name LIKE 'B%';

This query uses the LIKE operator to find students whose names begin with 'B'.

Query 20: Counting Records per Group.

SELECT Stu_Subject_ID, COUNT(*) AS StudentCount
FROM Student
GROUP BY Stu_Subject_ID;

This query uses GROUP BY and COUNT(*) to count the number of students in each subject.

Query 21: Converting to Uppercase.

SELECT UPPER(Stu_Name) FROM Student;

This uses the UPPER() function to convert names to uppercase.

Query 22: Selecting Unique Values.

SELECT DISTINCT Stu_Age FROM Student;

This uses DISTINCT to retrieve only unique ages.

Query 23: Extracting Substrings.

SELECT SUBSTR(Stu_Name, 1, 2) FROM Student;

This extracts the first two characters of each name using the SUBSTR() function (or equivalent function like `SUBSTRING()` in some database systems).

Query 24: Ordering Results.

SELECT * FROM Student ORDER BY Stu_Name DESC;

This sorts student records by name in descending order.

Query 25: Finding Values in One Table Not Present in Another.

SELECT Stu_Name, Stu_Subject_ID
FROM Student
EXCEPT
SELECT Stu_Name, Stu_Subject_ID
FROM Student2;

(This example uses `EXCEPT` which is standard SQL. `MINUS` may be used in some database systems like Oracle.)

Query 26: Retrieving the Three Lowest Values.

SELECT DISTINCT Stu_Marks
FROM Student a
WHERE 3 <= (SELECT COUNT(DISTINCT Stu_Marks) FROM Student b WHERE a.Stu_Marks >= b.Stu_Marks)
ORDER BY a.Stu_Marks ASC
LIMIT 3;

Query 27: Calculating the Average.

SELECT AVG(Stu_Marks) FROM Student;

Query 28: Creating a View.

CREATE VIEW LowMarksStudents AS
SELECT Stu_Name, Stu_Age
FROM Student
WHERE Stu_Marks < 85;

Query 29: Adding a Column to a Table.

ALTER TABLE Student ADD Stu_Address VARCHAR(25);

Query 30: Type Conversion.

SELECT CAST(3025.58 AS INT); -- Or CONVERT in some systems