SQL Statements

SQL	Description
INSERT	Used to insert new rows into a table.
UPDATE	Used to modify existing rows in a table.
DELETE	Used to remove rows from a table.
ALTER TABLE	Used to modify the structure of an existing table, such as adding or dropping columns.
TRUNCATE	Removes all rows from a table, but the table itself remains.
UNION	Combines the result sets of two or more `SELECT` queries.
INDEX	Creates an index to improve the speed of data retrieval.
DISTINCT	Used to remove duplicates from the result set.
HAVING	Used to filter rows after the `GROUP BY` clause.
LIMIT/OFFSET	Limits the number of rows returned by a query and allows for pagination.
ORDER BY	Used to sort the result set in ascending or descending order.
EXISTS	Used to test for the existence of any record in a subquery.
RENAME	Changes the name of an existing table or column.
CREATE VIEW	Creates a virtual table based on a `SELECT` query.
DROP VIEW	Deletes a view.

Using JOIN:

Write a query to retrieve employee names along with their department names from employees and departments tables.

SELECT e.name, d.department_name
FROM employees e
JOIN departments d
  ON e.department_id = d.department_id;

+-------------+------------------+
| Name        | Department Name  |
+-------------+------------------+
| Alice       | Engineering      |
| Bob         | Marketing        |
| Charlie     | HR               |
+-------------+------------------+

GROUP BY and Aggregate Functions:

Find the total salary paid in each department.

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

+---------------+---------------+
| Department ID | Total Salary  |
+---------------+---------------+
| 1             | 150000        |
| 2             | 220000        |
| 3             | 180000        |
+---------------+---------------+

HAVING Clause:

Retrieve departments that have a total salary greater than 100,000.

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING total_salary > 100000;

+--------------+--------------+
| Department ID | Total Salary |
+--------------+--------------+
| 2            | 220000       |
| 3            | 180000       |
+--------------+--------------+

CASE Statement:

Write a query to categorize employees as High, Medium, or Low salary based on their salary values.

SELECT name,
       salary,
       CASE
           WHEN salary >= 80000 THEN 'High'
           WHEN salary >= 50000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_category
FROM employees;

+---------+--------+------------------+
| Name    | Salary | Salary Category  |
+---------+--------+------------------+
| Alice   | 85000  | High             |
| Bob     | 60000  | Medium           |
| Charlie | 40000  | Low              |
+---------+--------+------------------+

Self-JOIN:

Find all employees who share the same manager.

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
JOIN employees e2
  ON e1.manager_id = e2.employee_id;

+----------+----------+
| Employee | Manager  |
+----------+----------+
| Alice    | Bob      |
| Charlie  | Bob      |
| Dave     | Alice    |
+----------+----------+

Window Functions:

Rank employees based on their salaries within each department.

SELECT name, department_id, salary,
       RANK() OVER (PARTITION BY department_id
                    ORDER BY salary DESC) AS rank
FROM employees;

+---------+--------------+--------+------+
| Name    | Department ID| Salary | Rank |
+---------+--------------+--------+------+
| Alice   | 1            | 85000  | 1    |
| Bob     | 1            | 60000  | 2    |
| Charlie | 2            | 90000  | 1    |
+---------+--------------+--------+------+

CTE (Common Table Expression):

Use a CTE to find employees who earn above the department’s average salary.

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d
  ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

+---------+--------+------------+
| Name    | Salary | Avg Salary |
+---------+--------+------------+
| Alice   | 85000  | 70000      |
| Charlie | 90000  | 60000      |
+---------+--------+------------+

Recursive CTE:

Write a query to generate a hierarchical list of employees and their managers.

WITH RECURSIVE emp_hierarchy AS (
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN emp_hierarchy eh
      ON e.manager_id = eh.employee_id
)
SELECT * FROM emp_hierarchy
ORDER BY level;

+-------------+--------+------------+-------+
| Employee ID | Name   | Manager ID | Level |
+-------------+--------+------------+-------+
| 1           | Alice  | NULL       | 1     |
| 2           | Bob    | 1          | 2     |
| 3           | Charlie| 2          | 3     |
+-------------+--------+------------+-------+

Subquery:

Retrieve employees whose salary is above the average salary in the company.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

+---------+--------+
| Name    | Salary |
+---------+--------+
| Alice   | 85000  |
| Charlie | 90000  |
+---------+--------+

Handling NULLs:

Write a query to list employees and replace NULL values in the bonus column with 0.

SELECT name, COALESCE(bonus, 0) AS bonus
FROM employees;

+---------+-------+
| Name    | Bonus |
+---------+-------+
| Alice   | 1000  |
| Bob     | 0     |
| Charlie | 500   |
+---------+-------+

WITH: Common Table Expressions

Select Positive and Negative Image Samples

WITH ordered_images AS (
    -- Order images by score descending and ascending, assigning row numbers --
    SELECT image_id, score,
           ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num_desc,
           ROW_NUMBER() OVER (ORDER BY score ASC) AS row_num_asc
    FROM unlabeled_image_predictions
),
positive_samples AS (
    -- Select every 3rd image from the highest scores, starting from the first row --
    SELECT image_id, 1 AS weak_label
    FROM ordered_images
    WHERE row_num_desc % 3 = 1
    ORDER BY row_num_desc
    LIMIT 10000
),
negative_samples AS (
    -- Select every 3rd image from the lowest scores, starting from the first row --
    SELECT image_id, 0 AS weak_label
    FROM ordered_images
    WHERE row_num_asc % 3 = 1
    ORDER BY row_num_asc
    LIMIT 10000
)
-- Combine positive and negative samples and order by image_id --
SELECT image_id, weak_label
FROM positive_samples

UNION ALL

SELECT image_id, weak_label
FROM negative_samples

ORDER BY image_id;

Step	Description
1. Common Table Expression - `ordered_images`	Orders the images by score both descending and ascending, assigning row numbers using `ROW_NUMBER()`. These row numbers help select specific samples from the top and bottom images.
2. Common Table Expression - `positive_samples`	Selects every 3rd image from the highest-scoring images, starting with the first image. Assigns a `weak_label` of 1 to these samples. The result is limited to 10,000 samples.
3. Common Table Expression - `negative_samples`	Selects every 3rd image from the lowest-scoring images, starting with the first image. Assigns a `weak_label` of 0 to these samples. The result is limited to 10,000 samples.
4. Final Selection	Combines the positive and negative samples using `UNION ALL`, preserving all records. Orders the combined result by `image_id` in ascending order.

Output

+----------+------------+
| image_id | weak_label |
+----------+------------+
|    1001  |      1     |
|    1003  |      0     |
|    1005  |      1     |
|    1007  |      0     |
|    1009  |      1     |
+----------+------------+