· last year · Oct 06, 2024, 06:50 AM
1-- Valuing Lesson
2CREATE TABLE IF NOT EXISTS employees (
3 emp_id INT PRIMARY KEY ,
4 emp_name VARCHAR(255),
5 emp_department VARCHAR(255),
6 emp_salary INT
7);
8
9INSERT INTO employees (emp_id, emp_name, emp_department, emp_salary) VALUES
10(1, 'Gaudenz Padullon', 'IT', 511000),
11(2, 'Je Ar Padullon', 'IT', 20000),
12(3, 'Khier Lapurga', 'IT', 60000),
13(4, 'John Credo', 'Sales', 70000),
14(5, 'Frederick Santigo', 'Sales', 40000),
15(6, 'Barron Obana', 'IT', 20000),
16(7, 'Azer Marayag', 'IT', 80000),
17(8, 'Cedric Acong', 'Sales', 10000),
18(9, 'Jules Manila', 'IT', 50000),
19(10, 'Khenjie Amosco', 'Sales', 45000);
20
21-- Employees Table:
22-- | EMP_ID | EMP_NAME | EMP_DEPARTMENT | EMP_SALARY |
23-- |--------|------------------------|----------------|------------|
24-- | 1 | Gaudenz Padullon | IT | 511000 |
25-- | 2 | Je Ar Padullon | IT | 20000 |
26-- | 3 | Khier Lapurga | IT | 60000 |
27-- | 4 | John Credo | Sales | 70000 |
28-- | 5 | Frederick Santigo | Sales | 40000 |
29-- | 6 | Barron Obana | IT | 20000 |
30-- | 7 | Azer Marayag | IT | 80000 |
31-- | 8 | Cedric Acong | Sales | 10000 |
32-- | 9 | Jules Manila | IT | 50000 |
33-- | 10 | Khenjie Amosco | Sales | 45000 |
34-- |--------|------------------------|----------------|------------|
35
36
37-- TASK 1: Using Logical Functions
38SELECT * FROM employees
39WHERE ( emp_department = 'Sales'
40 OR emp_department = 'IT')
41AND emp_salary > 40000;
42-- OUTPUT:
43-- | EMP_ID | EMP_NAME | EMP_DEPARTMENT | EMP_SALARY |
44-- |--------|--------------------|----------------|------------|
45-- | 1 | Gaudenz Padullon | IT | 511000 |
46-- | 3 | Khier Lapurga | IT | 60000 |
47-- | 4 | John Credo | Sales | 70000 |
48-- | 7 | Azer Marayag | IT | 80000 |
49-- |--------|--------------------|----------------|------------|
50
51
52--TASK 2: Applying Aggregate Functions
53SELECT MAX(emp_id), AVG(emp_salary) FROM employees;
54-- OUTPUT:
55-- | MAX_EMP_ID | AVG_EMP_SALARY |
56-- |------------|----------------|
57-- | 10 | 90600 |
58-- |------------|----------------|
59
60
61-- TASK 3: Exploring Window Functions
62SELECT ROW_NUMBER() OVER (ORDER BY emp_salary DESC) AS 'emp_rank', emp_name FROM employees;
63-- OUTPUT:
64-- | EMP_RANK | EMP_NAME |
65-- |----------|--------------------|
66-- | 1 | Gaudenz Padullon |
67-- | 2 | Azer Marayag |
68-- | 3 | John Credo |
69-- | 4 | Khier Lapurga |
70-- | 5 | Jules Manila |
71-- | 6 | Khenjie Amosco |
72-- | 7 | Frederick Santigo |
73-- | 8 | Je Ar Padullon |
74-- | 9 | Barron Obana |
75-- | 10 | Cedric Acong |
76-- |----------|--------------------|
77
78
79-- TASK 4: Utilizing Rowset Functions
80SELECT * FROM employees ORDER BY emp_salary DESC FETCH FIRST 10 ROWS ONLY;
81-- OUTPUT:
82-- | EMP_ID | EMP_NAME | EMP_DEPARTMENT | EMP_SALARY |
83-- |--------|--------------------|----------------|------------|
84-- | 1 | Gaudenz Padullon | IT | 511000 |
85-- | 7 | Azer Marayag | IT | 80000 |
86-- | 4 | John Credo | Sales | 70000 |
87-- | 3 | Khier Lapurga | IT | 60000 |
88-- | 9 | Jules Manila | IT | 50000 |
89-- | 10 | Khenjie Amosco | Sales | 45000 |
90-- | 5 | Frederick Santigo | Sales | 40000 |
91-- | 2 | Je Ar Padullon | IT | 20000 |
92-- | 6 | Barron Obana | IT | 20000 |
93-- | 8 | Cedric Acong | Sales | 10000 |
94-- |--------|--------------------|----------------|------------|