Advertisement
Gaudenz

Valuing Lesson - Task 1-4 - WEEK 3-4 - PERFORMANCE TASK: 2-3

Oct 16th, 2024 (edited)
137
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 2.18 KB | Cybersecurity | 0 0
  1. DROP TABLE employees;
  2.  
  3. -- VALUING LESSON
  4. CREATE TABLE employees(
  5.     emp_id INT PRIMARY KEY,
  6.     first_name VARCHAR(15),
  7.       last_name VARCHAR(15),
  8.     emp_department VARCHAR(10),
  9.       hire_date DATE,
  10.     emp_salary DECIMAL (10,4)
  11. );
  12.  
  13. INSERT INTO employees VALUES
  14. (1, 'Gaudenz', 'Padullon', 'IT', DATE '2023-01-11' , 511000.0000);
  15.  
  16. INSERT INTO employees VALUES
  17. (2, 'Je Ar', 'Raguingan', 'Sales', DATE  '2023-01-12', 20000.0000);
  18.  
  19. INSERT INTO employees VALUES
  20. (3, 'Khier', 'Lapurga', 'IT', DATE  '2023-01-23', 60000.0000);
  21.  
  22. INSERT INTO employees VALUES
  23. (4, 'John', 'Credo', 'Sales', DATE  '2023-01-31', 70000.0000);
  24.  
  25. INSERT INTO employees VALUES
  26. (5, 'Frederick', 'Santigo', 'HR', DATE '2023-01-31', 40000.0000);
  27.  
  28. INSERT INTO employees VALUES
  29. (6, 'Barron', 'Obana', 'IT', DATE  '2023-07-12', 20000.0000);
  30.  
  31. INSERT INTO employees VALUES
  32. (7, 'Azer', 'Marayag', 'IT', DATE  '2023-07-12', 80000.0000);
  33.  
  34. INSERT INTO employees VALUES
  35. (8, 'Cedric', 'Acong', 'Sales', DATE  '2024-06-13', 10000.0000);
  36.  
  37. INSERT INTO employees VALUES
  38. (9, 'Jules', 'Manila', 'IT', DATE  '2024-06-13', 50000.0000);
  39.  
  40. INSERT INTO employees VALUES
  41. (10, 'Khenjie', 'Amosco', 'HR', DATE '2024-06-13', 45000.0000);
  42.  
  43.  
  44. SELECT * FROM employees;
  45.  
  46. -- TASK 1 : String Function
  47. SELECT LOWER(first_name) AS "Lowercase name"
  48. FROM employees;
  49.  
  50. -- TASK 2: Number Function
  51. SELECT
  52.     emp_id,
  53.     first_name || ' ' || last_name AS "Full name",
  54.     ROUND(emp_salary) AS "Rounded Salary"
  55. FROM employees;
  56.  
  57. -- TASK 3: Date Function
  58. SELECT
  59.     emp_id,
  60.     first_name || ' ' || last_name AS "Full name",
  61.     ROUND(
  62.       MONTHS_BETWEEN(SYSDATE,hire_date)
  63.           ) AS "Months Worked"
  64. FROM employees;
  65.  
  66. -- TASK 4: Conversion Function
  67. SELECT  
  68.     emp_id,
  69.     first_name || ' ' || last_name AS "Full name",
  70.     TO_CHAR(emp_salary, '$999,999.99') AS "Formatted Salary"
  71. FROM employees;
  72.  
  73.  
  74. -- Performance Task 2-2
  75. SELECT
  76.   UPPER(last_name) AS "Uppercase Last Names",
  77.   ROUND(emp_salary) AS "Rounded Salary",
  78.   ROUND(MONTHS_BETWEEN(SYSDATE,hire_date)) AS "Months Worked",
  79.   '₱' || TO_CHAR(emp_salary, 'FM999,999,999') AS "Formatted Salary"
  80. FROM employees;
  81.  
  82. -- https://onecompiler.com/oracle/42va7rf97
  83.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement