SQL Interview Questions
This page consists of SQL interview questions and answers.
mysql> SELECT * FROM project; +-----------+---------------+ | projectid | projectstatus | +-----------+---------------+ | p01 | ON_HOLD | | p02 | IN_PROGRESS | | p03 | IN_PROGRESS | | p04 | ON_HOLD | +-----------+---------------+
For this we can use CASE.
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.
mysql>
The product table:
product
mysql> SELECT * FROM product; +-----------+-------+ | productid | price | +-----------+-------+ | p01 | 99.00 | | p02 | 22.00 | | p03 | 17.00 | +-----------+-------+
mysql> UPDATE product SET price = CASE WHEN price % 2 = 0 THEN price + 2 ELSE price + 3 END;
mysql> SELECT * FROM product; +-----------+--------+ | productid | price | +-----------+--------+ | p01 | 102.00 | | p02 | 24.00 | | p03 | 20.00 | +-----------+--------+
The employee 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 employeesalary table:
employeesalary
mysql> SELECT * FROM employeesalary; +------------+------------+ | employeeid | salary | +------------+------------+ | e01 | 1000000.00 | | e02 | 700000.00 | | e03 | 1500000.00 | | e04 | 900000.00 | | e05 | 1100000.00 | +------------+------------+
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 );
+------------+-----------+----------+-----------+ | 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.
LIMIT 3,1
employeeid
The student table:
student
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:
studentscore
mysql> SELECT * FROM studentscore; +-----------+--------+ | studentid | score | +-----------+--------+ | s01 | 78.600 | | s02 | 99.023 | | s03 | 60.780 | | s04 | 56.030 | | s05 | 83.007 | +-----------+--------+
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.
AVG
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;
+-----------+-----------+----------+--------+ | studentid | firstname | lastname | score | +-----------+-----------+----------+--------+ | s02 | John | Doe | 99.023 | | s05 | Clark | Kent | 83.007 | | s01 | Jane | Doe | 78.600 | +-----------+-----------+----------+--------+
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.
The projectemployee table:
projectemployee
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 | +-----------+------------+----------+
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;
+------------+-----------+----------+ | employeeid | firstname | lastname | +------------+-----------+----------+ | e04 | Bruce | Banner | +------------+-----------+----------+
Copy the data from column old_data to column new_data.
old_data
new_data
mysql> SELECT * FROM sampletable; +----+----------+----------+ | id | old_data | new_data | +----+----------+----------+ | 1 | a1 | | | 2 | a2 | | | 3 | a3 | | +----+----------+----------+
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 | +----+----------+----------+
The source table: employee
The destination table is emp and it has the following columns similar to the employee table.
emp
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 | +-------+------------+-----------+---------------------+
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.
To get the employee count based on employee role in the projects we have to use GROUP BY to group the result.
GROUP BY
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 | +----------+-----------+