SQL Interview Questions - Set 5
SQL Interview Questions
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 |
+----------+-----------+
ADVERTISEMENT