-
Write a solution to calculate the number of unique subjects each teacher teaches in the university.
Example 1:
Input:
Teacher table:
+------------+------------+---------+
| teacher_id | subject_id | dept_id |
+------------+------------+---------+
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 1 | 3 | 3 |
| 2 | 1 | 1 |
| 2 | 2 | 1 |
| 2 | 3 | 1 |
| 2 | 4 | 1 |
+------------+------------+---------+
Output:
+------------+-----+
| teacher_id | cnt |
+------------+-----+
| 1 | 2 |
| 2 | 4 |
+------------+-----+
Explanation:
Teacher 1:
- They teach subject 2 in departments 3 and 4.
- They teach subject 3 in department 3.
Teacher 2:
- They teach subject 1 in department 1.
- They teach subject 2 in department 1.
- They teach subject 3 in department 1.
- They teach subject 4 in department 1.
-
Write a solution to report the name and bonus amount of each employee with a bonus less than 1000.
Example 1:
Input:
Employee table:
+-------+--------+
| empId | name |
+-------+--------+
| 3 | Brad |
| 1 | John |
| 2 | Dan |
| 4 | Thomas |
+-------+--------+
Bonus table:
+-------+-------+
| empId | bonus |
+-------+-------+
| 2 | 500 |
| 4 | 2000 |
+-------+-------+
Output:
+------+-------+
| name | bonus |
+------+-------+
| Brad | null |
| John | null |
| Dan | 500 |
+------+-------+
cNote Brad and John did not have bonus
-
Write a solution to report the movies with an odd-numbered ID and a description that is not "boring".
Input:
Cinema table:
+----+------------+-------------+
| id | movie | description |
+----+------------+-------------+
| 1 | War | great 3D |
| 2 | Science | fiction |
| 3 | irish | boring |
| 4 | Ice song | Fantacy |
| 5 | House card | Interesting |
+----+------------+-------------+
Output:
+----+------------+-------------+
| id | movie | description |
+----+------------+-------------+
| 5 | House card | Interesting |
| 1 | War | great 3D |
+----+------------+-------------+
Explanation:
We have three movies with odd-numbered IDs: 1, 3, and 5. The movie with ID = 3 is boring so we do not include it in the answer.
-
Find EmpID for third Highest salary
+-------+-------+
| empId | salary|
+-------+-------+
| 1 | 500 |
| 2 | 2000 |
| 3 | 3000 |
| 4 | 2000 |
+-------+-------+
output
+-------+-------+
| empId | salary|
+-------+-------+
| 4 | 2000 |
+-------+-------+
-
Delete dulplicate rows from table
+-------+-------+
| empId | salary|
+-------+-------+
| 1 | 500 |
| 1 | 500 |
| 2 | 3000 |
| 3 | 2000 |
+-------+-------+
output
+-------+-------+
| empId | salary|
+-------+-------+
| 1 | 500 |
| 2 | 3000 |
| 3 | 2000 |
+-------+-------+
-
find rows that contain only numerical data
+-------+-------+
| empId | val |
+-------+-------+
| 1 | 500 |
| 2 | abc |
| 3 | 2A |
| 4 | 2000 |
+-------+-------+
output
+-------+-------+
| empId | val |
+-------+-------+
| 1 | 500 |
| 4 | 2000 |
+-------+-------+
-
Department with Highest Number of Employees
+-------+--------+----+
| empId | Dept_name |
+-------+--------+----+
| 1 | IT |
| 2 | IT |
| 3 | HR |
| 4 | Finance |
+-------+--------+----+
output
+-------+---+
| Dept_name |
+-------+---+
| IT |
+-----------+
-
Difference between Inner and Left Join
-
Find next Hire date
Employee table:
+-------+--------+---+
| empId | Hire_Date |
+-------+--------+---+
| 1 | 1/1/2023 |
| 2 | 2/1/2023 |
| 3 | 3/1/2023 |
| 4 | 4/1/2023 |
+-------+--------+---+
Output:
+-------+--------+---------------------+
| empId | Hire_Date | Next_Hire_Date |
+-------+--------+---------------------+
| 1 | 1/1/2023 | 2/1/2023 |
| 2 | 2/1/2023 | 3/1/2023 |
| 3 | 3/1/2023 | 4/1/2023 |
| 4 | 4/1/2023 | Null |
+-------+--------+---------------------+
