Set-12

A. List the details of the employees in ascending order of the Department numbers and descending order of Jobs:
SELECT *
FROM emp_details
ORDER BY dept_no ASC, job DESC;

B. Query for adding a new column called PF:
ALTER TABLE emp_details ADD PF NUMBER;

C. List the employees who joined before 1981:
SELECT *
FROM emp_details
WHERE hiredate < TO_DATE('1981-01-01', 'YYYY-MM-DD');
D. Display employees whose salary is between 10,000 and 20,000:
SELECT *
FROM emp_details
WHERE sal BETWEEN 10000 AND 20000;

E. Display employees whose name starts with 'S':
SELECT *
FROM emp_details
WHERE emp_name LIKE 'S%';

F. Display employees who were born between January 1st, 1970, and January 1st, 1990:
SELECT *
FROM emp_details
WHERE EXTRACT(YEAR FROM hiredate) BETWEEN 1970 AND 1990;
G. Syntax of DDL Commands:
//CREATE: Used to create a new database object like a table, view, or index.
CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
//ALTER: Used to modify an existing database object like adding, modifying, or dropping columns.
ALTER TABLE table_name ADD column_name datatype;
//DROP: Used to delete an existing database object like a table, view, or index.
DROP TABLE table_name;
H. PL/SQL Program to find the maximum number among three numbers:
DECLARE
    num1 NUMBER := 10;
    num2 NUMBER := 20;
    num3 NUMBER := 30;
    max_num NUMBER;
BEGIN
    max_num := GREATEST(num1, num2, num3);
    DBMS_OUTPUT.PUT_LINE('Maximum number: ' || max_num);
END;

I. PL/SQL Program to print the salary changes when the salary is changed using a Trigger:
CREATE OR REPLACE TRIGGER salary_change_trigger
BEFORE UPDATE OF sal ON emp_details
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('Old Salary: ' || :OLD.sal);
    DBMS_OUTPUT.PUT_LINE('New Salary: ' || :NEW.sal);
END;

J. Find the maximum average salary drawn for each job except for 'President':
SELECT job, MAX(AVG(sal)) AS max_avg_salary
FROM emp_details
WHERE job != 'President'
GROUP BY job;

Entities:

Employee (emp_no, emp_name, job, hiredate, mgr, sal, comm, dept_no)
Department (dept_no, dept_name, location)
Relationships:

Employee works in Department (many-to-one)
Employee (mgr) manages other Employees (one-to-many)

            +------------------+
            |     Employee     |
            +------------------+
            | emp_no (PK)      |
            | emp_name         |
            | job              |
            | hiredate         |
            | sal              |
            | comm             |
            | dept_no (FK)     |
            +------------------+
                    |
                    |
                    | works in
                    |
                    v
            +------------------+
            |   Department     |
            +------------------+
            | dept_no (PK)     |
            | dept_name        |
            | location         |
            +------------------+