SQL Query Interview Questions

In this article, you will learn many simple and complex SQL queries asked in IT interviews. Let's take two tables which help in solving various queries. The name of the first table is Student, and the name of the second table is Subject.

The Student table consists of Student_ID, Stu_Name, Stu_Subject_ID, Stu_Marks, and Stu_Age columns, and the Subject table consists of Subject_ID and Subject_Name columns.

Student Table:

Column Table:

Query 1: Write a query to create the table in Structured Query Language.

Sol:

Syntax to Create a Table in SQL:

We can create a table using Create Table keyword. This keyword creates only one table at a time.

Examples:

Example 1:

The following example creates the Student table:

Example 2:

The following example creates the Subject table:

Query 2: Write a query to insert the data into the table.

Sol:

Syntax to insert data into a table:

We can easily insert the record using the INSERT statement in SQL.

Examples:

Example 1:

The following queries insert the data of students into Student table:

Example 2:

The following query inserts Subject_ID and Subject_Name into the Subject table:

Query 3: Write a query to view the specific record of the table by using the WHERE clause.

Sol:

Syntax to access specific records from the table:

Examples:

Example 1:

The following query shows all the rows of those Students whose age is 20:

The WHERE clause in this query shows only those rows which satisfy the specified condition.

Output:

Example 2: The following query shows the Subject_Name of those subjects whose Subject_ID is BCA103 and BCA106:

The WHERE clause in this query shows only those rows which satisfy the specified condition.

Output:

Query 4: Write a query in SQL to find the minimum and maximum number from the integer column:

Sol:

Syntax to find the maximum and minimum number from the column:

We can easily find the maximum and minimum values of any integer column using the MAX and MIN aggregate functions.

Example:

The following query shows the maximum and minimum marks of the Stu_Marks column from the Student table:

Query 5: Write a query to access the first record from the SQL table?

Sol:

Syntax to find the first record from the table:

We can easily find the first row of any table by assigning 1 to the Rownum keyword in the WHERE clause of the SELECT statement.

Example:

The following query shows the first row of the student table in the output:

Output:

Query 6: Write a query to access the last record from the table?

Sol:

Syntax to find the first record from the table:

We can easily find the last row of any table by using the above syntax.

Example:

The following query shows the last row of the student table in the output:

Output:

Query 7: Write a query to access the first N^th rows from the table?

Sol:

Syntax to find the first N^th records from the table:

We can easily retrieve the first five rows of any table by using the Rownum keyword. We have to use the 'Less than equals to' comparison operator for this operation.

Here, N defines the number of rows to be shown in the output.

Example:

The following query shows the first five rows of the student table in the output:

Output:

Query 8: Write a query to access the last N^th rows from the SQL table?

Sol:

Syntax to find the last N^th records from the table:

We can easily retrieve the first five rows of any table by using the Rownum keyword.

Example:

The following query shows the last four rows of the Subject table:

Output:

Query 9: Write a query in SQL to retrieve only even rows from the table?

Sol:

Syntax to find the Even rows from the table:

We can easily retrieve the even rows from the table by using the MOD function in the WHERE clause of the SELECT statement.

Example:

The following query shows even rows of student table in the result:

Output:

Query 10: Write a query in SQL to retrieve only an odd number of rows from the table?

Sol:

Syntax to find the Odd number of rows from the table:

We can easily retrieve the odd rows from the table by using the MOD function in the WHERE clause of the SELECT statement.

Example:

The following query shows odd rows of Student table in the result:

Output:

Query 11: Write a query in SQL to create a new table with the same data and structure as an existing table.

Sol:

Syntax:

Example: The following query creates Student_Marks table from the existing Student table:

Query 12: Write a Query to find the Nth highest value of an integer column from the table.

Sol:

Syntax:

Example:

The following query shows the 3^rd highest marks from the Student table:

Query 13: Write a query in SQL to find the second-highest value of an integer column from the table?

Sol:

Syntax to find the second highest value of the integer column:

Example:

The following query shows the second-highest marks from the student table:

Query 14: Write a query in Structured Query Language to view the current date and time.

Sol:

Query 15: Write a query in SQL to show the record of the three highest values of an integer column from the table.

Sol:

Syntax:

Example:

The following query shows the record of the three highest marks from the student table:

Output:

Query 16: Write an SQL query to fetch the Stu_Name and Stu_Marks of those students whose age is 20.

Sol:

For this operation, you have to use the WHERE clause in the SELECT statement.

Output:

Query 17: Write a query to show the maximum marks of each subject.

Sol:

For this operation, you need to use the MAX function with the GROUP BY statement.

Output:

Query 18: Write a query to show all the record of those students whose Marks is greater than 82 and age is 22

Sol:

Here, you have to use the AND operator between the two conditions in the WHERE clause. The AND operator returns those records which match the specified conditions.

Output:

Query 19: Write a query to show the record of those students whose name begins with the 'm' character.

Sol:

Here, you have to use the LIKE operator, which matches the given pattern in the table.

Output:

Query 20: Write a query to show all Subject_ID along with the number of students in there.

Sol: The following query uses the GROUP BY statement with the COUNT function, which returns the number of students in each subject.

Output:

Query 21: Write a query in SQL to fetch the values of the Stu_Name column from the Student table in the upper case.

Sol:

The following query uses the UPPER function with that column name whose values are to be shown in upper case:

Query 22: Write an SQL query to show the unique values of Stu_Age from the student table:

Sol:

The following query uses the SQL DISTINCT function with the Stu_Age column:

Query 23: Write a Query in SQL to show the first N characters of the string column from the Student table.

Sol:

Syntax:

This syntax uses the SUBSTRING function, which shows the specific characters of the string.

Example:

The following query shows the first two characters of Stu_Name from the Student table:

Query 24: Write a query in structured query language to view all student details from the Student table order by Stu_Name Descending.

Sol:

Here, we have to use the ORDER BY clause, which shows the student details in the descending order of Stu_Name:

Output:

Query 25: Write a query to show the values from one table that does not exist in another table in the same database.

Sol:

Syntax:

This syntax uses the SQL MINUS operator, which shows the values of Table1 that does not exist in Table2.

Example: Let's take another table, Student2, consisting of 3 columns Bus_ID, Stu_Name, and Stu_Address.

The following query shows only those rows of Stu_Name and Stu_Subject_ID of student table which does not exist in Student2 table:

Output:

SELECT Stu_Name, Stu_Subject_ID from Student 
MINUS 
SELECT Stu_Name, Stu_Subject_ID from Student2;   

Output:

Query 26: Write a query in SQL to show the three minimum values of the integer column from the table.

Sol:

Syntax:

Example:

The following query shows the three minimum marks from the student table:

Query 27: Write a query to find the average of the integer column from the table.

Sol:

Syntax:

Example:

The following query finds the average of marks of Student table:

Query 28: Write a query to create a View in Structured Query Language,

Sol:

Syntax:

Example:

The following query creates the View of those students whose Marks is greater than 85 from the Student table:

You can see the view table by using the following query:

Output:

Query 29: Write a Query to add another column in the existing table:

Sol:

Syntax:

If you want to add another column or field to the existing table, you must use the ALTER statement in SQL.

Example:

The following query adds the Stu_Address column to the existing Student table:

Query 30: Write a query to transform any value into the specific SQL data type.

Sol:

The following query converts the floating-point value into the integer type.