SQL Interview Questions - Set 1

This page consists of SQL interview questions and answers.

Q1: What is SQL?

SQL or Structured Query Language and we use it to add and modify data in a database.

Q2: Define trigger

A trigger is a piece of code that is associated with insert, update and delete query and gets executed whenever the associated query is executed on a particular table or view.

Q3: Define stored procedure

A stored procedure is a function that consists of a list of operations to perform database tasks.

Q4: What is the difference between a trigger and a stored procedure?

A stored procedure can be called independently whereas, a trigger can't be executed directly but must be associated with some queries.

Q5: Write SQL query to fetch firstname, lastname and userid from users table for userid 'u10'

The SQL query to select the required columns is given below.

mysql> SELECT u.userid, u.firstname, u.lastname
FROM user u
WHERE u.userid = 'u10';

mysql> denotes MySQL command line.

Q6: Write SQL query to get the average score of a team having id 12

Assuming the score of the teams gets stored in the teamscore table having teamid and score column.

So, to get the average score we will write the following query.

mysql> SELECT AVG(score) AS average_score
FROM teamscore
WHERE teamid = 12;

Q7: Find the 2nd highest salary in the employeesalary table

Lets say the employeesalary table has 2 columns employeeid and salary and the table has the following 5 entries.

mysql> SELECT es.employeeid, es.salary
  FROM employeesalary es
  ORDER BY es.salary DESC;

+------------+------------+
| employeeid | salary     |
+------------+------------+
| e03        | 1500000.00 |
| e05        | 1100000.00 |
| e01        | 1000000.00 |
| e04        |  900000.00 |
| e02        |  700000.00 |
+------------+------------+

We have already sorted the data in descending order using ORDER BY now, we will get the 2nd highest salary using LIMIT.

mysql> SELECT es.employeeid, es.salary
  FROM employeesalary es
  ORDER BY es.salary DESC
  LIMIT 1,1;

+------------+------------+
| employeeid | salary     |
+------------+------------+
| e05        | 1100000.00 |
+------------+------------+

LIMIT 1,1 means we want 1 row and offset is 1 so, the second row will be selected which holds the 2nd highest salary.

Q8: Write SQL query to select first 10 highest marks from the examscore table

Lets say the examscore table has two columns namely studentid and score. To get the first 10 highest marks we have to sort the data in descending order and them limit our SELECT query to 10 rows.

mysql> SELECT es.studentid, es.score
FROM examscore es
ORDER BY es.score DESC
LIMIT 10;

Q9: Fetch the last 3 entries from the employee table

Lets say the employee table has 4 columns employeeid, firstname, lastname and created_at.

Lets say the table has the following entries.

mysql> SELECT employeeid, firstname, lastname
  FROM employee
  ORDER BY created_at;

+------------+-----------+----------+
| employeeid | firstname | lastname |
+------------+-----------+----------+
| e01        | John      | Doe      |
| e02        | Jane      | Doe      |
| e03        | Peter     | Parker   |
| e04        | Bruce     | Banner   |
| e05        | Bruce     | Wayne    |
+------------+-----------+----------+

So, the query to select the last three entries will be the following.

mysql> SELECT *
  FROM (
    SELECT employeeid, firstname, lastname, created_at
    FROM employee
    ORDER BY employeeid DESC
    LIMIT 3
  ) tbl
  ORDER BY tbl.employeeid ASC;

+------------+-----------+----------+---------------------+
| employeeid | firstname | lastname | created_at          |
+------------+-----------+----------+---------------------+
| e03        | Peter     | Parker   | 2018-01-02 15:16:17 |
| e04        | Bruce     | Banner   | 2018-01-03 10:20:30 |
| e05        | Bruce     | Wayne    | 2018-01-04 12:00:00 |
+------------+-----------+----------+---------------------+

Q10: Write SQL query to print the sum of 10 and 20

mysql> SELECT (10 + 20) AS sum;

+-----+
| sum |
+-----+
|  30 |
+-----+