Valuing Lesson

-- Valuing Lesson
CREATE TABLE IF NOT EXISTS employees (
    emp_id INT PRIMARY KEY ,
    emp_name VARCHAR(255),
    emp_department VARCHAR(255),
    emp_salary INT
);

INSERT INTO employees (emp_id, emp_name, emp_department, emp_salary) VALUES
(1, 'Gaudenz Padullon', 'IT', 511000),
(2, 'Je Ar Padullon', 'IT', 20000),
(3, 'Khier Lapurga', 'IT', 60000),
(4, 'John Credo', 'Sales', 70000),
(5, 'Frederick Santigo', 'Sales', 40000),
(6, 'Barron Obana', 'IT', 20000),
(7, 'Azer Marayag', 'IT', 80000),
(8, 'Cedric Acong', 'Sales', 10000),
(9, 'Jules Manila', 'IT', 50000),
(10, 'Khenjie Amosco', 'Sales', 45000);

-- Employees Table:
-- | EMP_ID |        EMP_NAME        | EMP_DEPARTMENT | EMP_SALARY |
-- |--------|------------------------|----------------|------------|
-- |   1    | Gaudenz Padullon       |    IT          |   511000   |
-- |   2    | Je Ar Padullon         |    IT          |   20000    |
-- |   3    | Khier Lapurga          |    IT          |   60000    |
-- |   4    | John Credo             |    Sales       |   70000    |
-- |   5    | Frederick Santigo      |    Sales       |   40000    |
-- |   6    | Barron Obana           |    IT          |   20000    |
-- |   7    | Azer Marayag           |    IT          |   80000    |
-- |   8    | Cedric Acong           |    Sales       |   10000    |
-- |   9    | Jules Manila           |    IT          |   50000    |
-- |  10    | Khenjie Amosco         |    Sales       |   45000    |
-- |--------|------------------------|----------------|------------|


-- TASK 1: Using Logical Functions
SELECT * FROM employees
WHERE ( emp_department = 'Sales'
    OR  emp_department = 'IT')
AND emp_salary > 40000;
-- OUTPUT:
-- | EMP_ID |      EMP_NAME      | EMP_DEPARTMENT | EMP_SALARY |
-- |--------|--------------------|----------------|------------|
-- |   1    |  Gaudenz Padullon  |     IT         |    511000  |
-- |   3    |  Khier Lapurga     |     IT         |    60000   |
-- |   4    |  John Credo        |     Sales      |    70000   |
-- |   7    |  Azer Marayag      |     IT         |    80000   |
-- |--------|--------------------|----------------|------------|


--TASK 2: Applying Aggregate Functions
SELECT MAX(emp_id), AVG(emp_salary) FROM employees;
-- OUTPUT:
-- | MAX_EMP_ID | AVG_EMP_SALARY |
-- |------------|----------------|
-- |      10    |     90600      |
-- |------------|----------------|


-- TASK 3: Exploring Window Functions
SELECT ROW_NUMBER() OVER (ORDER BY emp_salary DESC) AS 'emp_rank', emp_name FROM employees;
-- OUTPUT:
-- | EMP_RANK |      EMP_NAME      |
-- |----------|--------------------|
-- |    1     | Gaudenz Padullon   |
-- |    2     | Azer Marayag       |
-- |    3     | John Credo         |
-- |    4     | Khier Lapurga      |
-- |    5     | Jules Manila       |
-- |    6     | Khenjie Amosco     |
-- |    7     | Frederick Santigo  |
-- |    8     | Je Ar Padullon     |
-- |    9     | Barron Obana       |
-- |   10     | Cedric Acong       |
-- |----------|--------------------|


-- TASK 4: Utilizing Rowset Functions
SELECT * FROM employees ORDER BY emp_salary DESC FETCH FIRST 10 ROWS ONLY;
-- OUTPUT:
-- | EMP_ID |      EMP_NAME      | EMP_DEPARTMENT | EMP_SALARY |
-- |--------|--------------------|----------------|------------|
-- |   1    | Gaudenz Padullon   |    IT          |   511000   |
-- |   7    | Azer Marayag       |    IT          |   80000    |
-- |   4    | John Credo         |    Sales       |   70000    |
-- |   3    | Khier Lapurga      |    IT          |   60000    |
-- |   9    | Jules Manila       |    IT          |   50000    |
-- |  10    | Khenjie Amosco     |    Sales       |   45000    |
-- |   5    | Frederick Santigo  |    Sales       |   40000    |
-- |   2    | Je Ar Padullon     |    IT          |   20000    |
-- |   6    | Barron Obana       |    IT          |   20000    |
-- |   8    | Cedric Acong       |    Sales       |   10000    |
-- |--------|--------------------|----------------|------------|