Performance Task 2.1

-- PERFORMANCE TASK: 2.1
CREATE TABLE employees(
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    emp_department VARCHAR(20),
    emp_salary INT
);

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

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


-- 1:
SELECT * FROM employees
WHERE (emp_department = 'IT' OR emp_department = 'HR')
AND emp_salary > 50000 ;
-- OUTPUT:
-- |  EMP_ID |     EMP_NAME       | EMP_DEPART | EMP_SALARY |
-- |---------|--------------------|------------|------------|
-- |    3    |  Khier Lapurga     |     IT     |    60000   |
-- |    7    |  Azer Marayag      |     IT     |    80000   |
-- |    1    |  Gaudenz Padullon  |     IT     |    511000  |


-- 2:
SELECT
      MAX(emp_id) AS "Total Employees",
    AVG(emp_salary) AS "Average Salary"
FROM employees;
--OUTPUT:
-- |Total Employees | Average Salary |
-- |----------------|----------------|
-- |        10      |     90600      |


-- 3:
SELECT
    ROW_NUMBER() OVER (ORDER BY emp_salary DESC) AS "Employee Rank",
    emp_name,
    emp_salary
FROM employees;
--OUTPUT:
-- | Employee Rank  |    EMP_NAME         | EMP_SALARY  |
-- |----------------|---------------------|-------------|
-- |        1       |  Gaudenz Padullon   |   511000    |
-- |        2       |  Azer Marayag       |   80000     |
-- |        3       |  John Credo         |   70000     |
-- |        4       |  Khier Lapurga      |   60000     |
-- |        5       |  Jules Manila       |   50000     |
-- |        6       |  Khenjie Amosco     |   45000     |
-- |        7       |  Frederick Santigo  |   40000     |
-- |        8       |  Je Ar Padullon     |   20000     |
-- |        9       |  Barron Obana       |   20000     |
-- |       10       |  Cedric Acong       |   10000     |
-- |----------------|---------------------|-------------|


-- 4:
SELECT * FROM employees
ORDER BY emp_salary DESC
FETCH FIRST 5 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    |
--  |----------|--------------------|----------------|------------|