This page consists of SQL interview questions and answers.

Q1: For the given project table how will you swap all the IN_PROGRESS project status to ON_HOLD and all the ON_HOLD projects to IN_PROGRESS using one single query without the use of any other table?

mysql> SELECT * FROM project;
+-----------+---------------+
| projectid | projectstatus |
+-----------+---------------+
| p01       | ON_HOLD       |
| p02       | IN_PROGRESS   |
| p03       | IN_PROGRESS   |
| p04       | ON_HOLD       |
+-----------+---------------+

Answer:

For this we can use CASE.

mysql> UPDATE project
SET
  projectstatus = CASE projectstatus
    WHEN 'ON_HOLD'
    THEN 'IN_PROGRESS'
    ELSE 'ON_HOLD'
  END;

Output:

mysql> SELECT * FROM project;
+-----------+---------------+
| projectid | projectstatus |
+-----------+---------------+
| p01       | IN_PROGRESS   |
| p02       | ON_HOLD       |
| p03       | ON_HOLD       |
| p04       | IN_PROGRESS   |
+-----------+---------------+

mysql> denotes MySQL command line.

Q2: Update the price column of the product table by adding 2 to all the even value price and 3 to all the odd value price

The product table:

mysql> SELECT * FROM product;
+-----------+-------+
| productid | price |
+-----------+-------+
| p01       | 99.00 |
| p02       | 22.00 |
| p03       | 17.00 |
+-----------+-------+

Answer:

For this we can use CASE.

mysql> UPDATE product
SET
  price = CASE
    WHEN price % 2 = 0
    THEN price + 2
    ELSE price + 3
  END;

Output:

mysql> SELECT * FROM product;
+-----------+--------+
| productid | price  |
+-----------+--------+
| p01       | 102.00 |
| p02       |  24.00 |
| p03       |  20.00 |
+-----------+--------+

Q3: Find the 4th highest paid employee name and salary

The employee table:

mysql> SELECT * FROM employee;
+------------+-----------+----------+---------------------+
| employeeid | firstname | lastname | created_at          |
+------------+-----------+----------+---------------------+
| e01        | John      | Doe      | 2018-01-01 10:20:30 |
| e02        | Jane      | Doe      | 2018-01-01 12:13:14 |
| 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 |
+------------+-----------+----------+---------------------+

The employeesalary table:

mysql> SELECT * FROM employeesalary;
+------------+------------+
| employeeid | salary     |
+------------+------------+
| e01        | 1000000.00 |
| e02        |  700000.00 |
| e03        | 1500000.00 |
| e04        |  900000.00 |
| e05        | 1100000.00 |
+------------+------------+

Answer:

To find the 4th highest salary we will write the following query.

mysql> SELECT
  e.employeeid,
  e.firstname,
  e.lastname,
  es.salary
FROM
  employee e, employeesalary es
WHERE
  e.employeeid = es.employeeid
  AND e.employeeid = (
    SELECT employeeid
    FROM employeesalary
    ORDER BY salary DESC
    LIMIT 3, 1
  );

Output:

+------------+-----------+----------+-----------+
| employeeid | firstname | lastname | salary    |
+------------+-----------+----------+-----------+
| e04        | Bruce     | Banner   | 900000.00 |
+------------+-----------+----------+-----------+

The subquery is fetching the top 3 highest paid employeeid. Then using the LIMIT 3,1 we are selecting only the 4th highest paid employeeid.

Q4: List all the students who scored above average in descending order

The student table:

mysql> SELECT * FROM student;
+-----------+-----------+----------+---------------------+---------------------+
| studentid | firstname | lastname | modified_at         | created_at          |
+-----------+-----------+----------+---------------------+---------------------+
| s01       | Jane      | Doe      | 2018-01-01 00:00:00 | 2018-01-01 00:00:00 |
| s02       | John      | Doe      | 2018-01-01 00:00:00 | 2018-01-01 00:00:00 |
| s03       | Tin       | Tin      | 2018-01-01 00:00:00 | 2018-01-01 00:00:00 |
| s04       | Bruce     | Wayne    | 2018-01-01 00:00:00 | 2018-01-01 00:00:00 |
| s05       | Clark     | Kent     | 2018-01-01 00:00:00 | 2018-01-01 00:00:00 |
+-----------+-----------+----------+---------------------+---------------------+

The studentscore table:

mysql> SELECT * FROM studentscore;
+-----------+--------+
| studentid | score  |
+-----------+--------+
| s01       | 78.600 |
| s02       | 99.023 |
| s03       | 60.780 |
| s04       | 56.030 |
| s05       | 83.007 |
+-----------+--------+

Answer:

For this we want to first compute the average using the AVG function then filter students based on their score in descending order that are above the average score.

SQL query to find the average is given below.

mysql> SELECT AVG(score) AS avg_score FROM studentscore;
+------------+
| avg_score  |
+------------+
| 75.4880005 |
+------------+

To list the students who scored above average we will write the following query.

mysql> SELECT
  s.studentid,
  s.firstname,
  s.lastname,
  sc.score
FROM
  student s, studentscore sc
WHERE
  s.studentid = sc.studentid
  AND s.studentid IN (
    SELECT sc1.studentid
    FROM studentscore sc1
    WHERE sc1.score > (SELECT AVG(score) FROM studentscore)
  )
ORDER BY sc.score DESC;

Output:

+-----------+-----------+----------+--------+
| studentid | firstname | lastname | score  |
+-----------+-----------+----------+--------+
| s02       | John      | Doe      | 99.023 |
| s05       | Clark     | Kent     | 83.007 |
| s01       | Jane      | Doe      | 78.600 |
+-----------+-----------+----------+--------+

Q5: What is a subquery?

Any query inside another query is called a subquery.

Subquery returns an output that is used by the outer query which gives the final result.

Q6: Find the project managers of employee 'e01'

The employee table:

mysql> SELECT * FROM employee;
+------------+-----------+----------+---------------------+
| employeeid | firstname | lastname | created_at          |
+------------+-----------+----------+---------------------+
| e01        | John      | Doe      | 2018-01-01 10:20:30 |
| e02        | Jane      | Doe      | 2018-01-01 12:13:14 |
| 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 |
+------------+-----------+----------+---------------------+

The projectemployee table:

mysql> SELECT * FROM projectemployee;
+-----------+------------+----------+
| projectid | employeeid | emp_role |
+-----------+------------+----------+
| p01       | e01        | JUNIOR   |
| p01       | e02        | LEAD     |
| p01       | e03        | JUNIOR   |
| p01       | e04        | MANAGER  |
| p01       | e05        | SENIOR   |
| p02       | e01        | JUNIOR   |
| p02       | e03        | JUNIOR   |
| p02       | e05        | LEAD     |
+-----------+------------+----------+

Answer:

To list the project managers of the employee 'e01' we have to first find the projects assigned to employee 'e01'. Then we can find the project manager, if any, of the project.

To list the projects employee 'e01' is working on we can write the following query.

mysql> SELECT projectid
FROM projectemployee
WHERE employeeid = 'e01';
+-----------+
| projectid |
+-----------+
| p01       |
| p02       |
+-----------+

Using this information we can filter the project managers.

So, to get the project manager we can write the following query.

mysql> SELECT
  e.employeeid,
  e.firstname,
  e.lastname
FROM
  employee e
WHERE
  e.employeeid IN (
    SELECT pe.employeeid
    FROM projectemployee pe
    WHERE pe.emp_role = 'MANAGER'
    AND pe.projectid IN (
      SELECT projectid
      FROM projectemployee
      WHERE employeeid = 'e01'
    )
  )
ORDER BY e.employeeid;

Output:

+------------+-----------+----------+
| employeeid | firstname | lastname |
+------------+-----------+----------+
| e04        | Bruce     | Banner   |
+------------+-----------+----------+

Q7: Write SQL query to copy data from one column to another in a given table

Copy the data from column old_data to column new_data.

mysql> SELECT * FROM sampletable;
+----+----------+----------+
| id | old_data | new_data |
+----+----------+----------+
|  1 | a1       |          |
|  2 | a2       |          |
|  3 | a3       |          |
+----+----------+----------+

Answer:

To copy data from old_data column to new_data column we will write the following SQL query.

mysql> UPDATE sampletable
SET new_data = old_data;

New result:

mysql> SELECT * FROM sampletable;
+----+----------+----------+
| id | old_data | new_data |
+----+----------+----------+
|  1 | a1       | a1       |
|  2 | a2       | a2       |
|  3 | a3       | a3       |
+----+----------+----------+

Q8: Write SQL query to copy data from one table to anther table

The source table: employee

mysql> SELECT * FROM employee;
+------------+-----------+----------+---------------------+
| employeeid | firstname | lastname | created_at          |
+------------+-----------+----------+---------------------+
| e01        | John      | Doe      | 2018-01-01 10:20:30 |
| e02        | Jane      | Doe      | 2018-01-01 12:13:14 |
| 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 |
+------------+-----------+----------+---------------------+

The destination table is emp and it has the following columns similar to the employee table.

• empid
• first_name
• last_name
• created_at

Answer:

To copy data from one table to another we use the following SQL query.

INSERT INTO destination_tbl (d_col1, d_col2, ...)
SELECT (s_col1, s_col2, ...)
FROM source_tbl
WHERE some_condition;

So, to copy the data from the source table to the destination table we will write the following SQL query.

mysql> INSERT INTO emp (
  empid,
  first_name,
  last_name,
  created_at
)
SELECT
  e.employeeid,
  e.firstname,
  e.lastname,
  e.created_at
FROM
  employee e;

Now, if we check the content of emp table we will get the following result.

mysql> SELECT * FROM emp;
+-------+------------+-----------+---------------------+
| empid | first_name | last_name | created_at          |
+-------+------------+-----------+---------------------+
| e01   | John       | Doe       | 2018-01-01 10:20:30 |
| e02   | Jane       | Doe       | 2018-01-01 12:13:14 |
| 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 |
+-------+------------+-----------+---------------------+

Q9: Briefly explain the use of WHERE, GROUP BY and HAVING

We use the WHERE clause to filter the result from the SELECT statement.

We use the GROUP BY clause to group the result.

The HAVING clause is used to filter the result in a group.

Q10: List the total number of employees in each role for the given table

The projectemployee table:

mysql> SELECT * FROM projectemployee;
+-----------+------------+----------+
| projectid | employeeid | emp_role |
+-----------+------------+----------+
| p01       | e01        | JUNIOR   |
| p01       | e02        | LEAD     |
| p01       | e03        | JUNIOR   |
| p01       | e04        | MANAGER  |
| p01       | e05        | SENIOR   |
| p02       | e01        | JUNIOR   |
| p02       | e03        | JUNIOR   |
| p02       | e05        | LEAD     |
+-----------+------------+----------+

Answer:

To get the employee count based on employee role in the projects we have to use GROUP BY to group the result.

Following query will give us the desired result.

mysql> SELECT
  emp_role,
  COUNT(emp_role) AS emp_count
FROM
  projectemployee
GROUP BY
  emp_role
ORDER BY emp_count DESC;

And we will get the following output.

+----------+-----------+
| emp_role | emp_count |
+----------+-----------+
| JUNIOR   |         4 |
| LEAD     |         2 |
| MANAGER  |         1 |
| SENIOR   |         1 |
+----------+-----------+