Lab 2

--***Calculated Fields****:
-- to see what will be the salary of each employee if they all get
-- an increase of 10%, type and run





















SELECT Empno, Sal*0.1
FROM Emp;

---list the employee number, name and salary of each employee if they
-- all get a 100% salary increase.













SELECT Empno, Ename, Sal, Sal+Sal
FROM Emp;

---Write a query to list employee number, employee name, job and yearly
-- (12 months) salary for each employee after the 100% increase.











SELECT empno, ename, sal, (Sal+sal)*12
FROM Emp;


---***The WHERE clause***

-- list the employee numbers, names and department number of only those
-- employees belonging to department 10, type and run












SELECT Empno, Ename, Deptno
FROM Emp
WHERE Deptno = 10;

--list the names and salaries of all Salesmen, type and run














SELECT Ename, Sal, Job
FROM Emp
WHERE Job = 'Salesman';

--- If the salary of each Salesman is reduced by 200, produce a list of
--  all Salesmen showing their new salaries.













SELECT empno, ename, sal, sal-200 AS Netsalary
FROM Emp;




-------------------- EXERCISE LAB2: *** FUNCTIONS ***-----------------

--1)FIND OUT HOW MANY MANAGERS THERE ARE WITHOUT LISTING THEM.
-- coun (job)













        SELECT COUNT (JOB ) AS "NUMBER OF MANAGERS"
        FROM EMP
        WHERE JOB = 'Manager';



--2)COMPUTE THE AVERAGE ANNUAL SALARY + COMMISSION FOR ALL SALESMEN
    -- AVG(SAL+COMM)*12












        SELECT AVG ( SAL+COMM ) * 12 AS "ANNUAL NET SALARY"
        FROM EMP
        WHERE JOB = 'Salesman';

--3)FIND THE HIGHEST AND LOWEST SALARIES
--  MAX(SAL), MIN(SAL)










        SELECT MAX ( SAL ), MIN ( SAL )
        FROM EMP ;


-- 4) FIND THE NUMBER OF CHARACTERS IN THE LONGEST DEPARTMENT NAME
   --  MAX(LENGTH(DNAME))











        SELECT MAX (LENGTH (DNAME ) )
        FROM DEPT ;

 -- 5) COUNT THE NUMBER OF PEOPLE IN DEPARTMENT 30 WHO RECEIVE A SALARY
 --    AND THE NUMBER OF PEOPLE WHO RECEIVE A COMMISSION
--     COUNT(SAL), COUNT(COMM)













        SELECT COUNT ( SAL ), COUNT (COMM)
        FROM EMP
        WHERE DEPTNO = 30;


-- 6) COMPUTE THE AVERAGE COMMISSION OF EMPLOYEES WHO RECEIVE A COMMISSION
--    AND THE AVERAGE COMMISSION OF ALL EMPLOYEES (TREATING EMPLOYEES WHO
--    DO NOT RECEIVE A COMMISSION AS RECEIVING A ZERO COMMISSION)
--    AVG(COMM), AVG(NVL(COMM,0))


















        SELECT AVG ( COMM ), AVG (NVL (COMM, 0 ) )
        FROM EMP;
                            -- NVL() converts a null value to an actual value.


--7) LIST THE AVERAGE SALARY OF EMPLOYEES THAT RECEIVE A SALARY, THE
 --  AVERAGE COMMISSION OF EMPLOYEES THAT RECEIVE A COMMISSION, THE
--   AVERAGE SALARY PLUS COMMISSION OF ONLY THOSE EMPLOYEES THAT RECEIVE
--   A COMMISSION AND AVERAGE SALARY PLUS COMMISSION OF ALL EMPLOYEES
 --  INCLUDING THOSE WHO DO NOT RECEIVE A COMMISSION

 --  AVG(SAL), AVG(COMM), AVG(SAL+COMM), AVG(SAL+NVL(COMM,0))












    SELECT AVG (SAL), AVG ( COMM ), AVG ( SAL + COMM ),
    AVG (SAL + NVL(COMM, 0 ) )
    FROM EMP;



--8) COMPUTE THE DAILY AND HOURLY SALARIES FOR EMPLOYEES IN DEPARTMENT
  -- 30. ROUND THE RESULTS TO THE NEAREST PENNY.  ASSUME THERE ARE 22
  -- WORKING DAYS IN A MONTH AND 8 WORKING HOURS IN A DAY.










        SELECT ENAME, SAL AS MONTHLY, ROUND ( SAL/22, 2 )AS DAILY,
        ROUND ( SAL/ (22* 8 ) , 2 )AS HOURLY
        FROM EMP
        WHERE DEPTNO = 30;



-- 9) ISSUE THE SAME QUERY AS THE PREVIOUS ONE EXCEPT THAT THIS TIME
   -- TRUNCATE (TRUNC) TO THE NEAREST PENNY RATHER THAN ROUND.









        SELECT ENAME, SAL AS MONTHLY, TRUNC ( SAL/22, 2 ) AS DAILY,
        TRUNC ( SAL/ (22 * 8 ) , 1 ) AS HOURLY
        FROM EMP
        WHERE DEPTNO = 30;


--- Create New table and name it student

CREATE TABLE student (
Sid CHAR (10) PRIMARY KEY ,
Sname varchar2 (20),
Sphone NUMBER(10));

DESC student;


-- Insert the following Values:


INSERT INTO student VALUES (10, 'Ahmad', 05400000);
INSERT INTO student VALUES (20, 'Omar', 05400000);
INSERT INTO student VALUES (30, 'hassan', 05400000);

SELECT * FROM student;

-- Delete row or taple from a table:

DELETE FROM student
WHERE Sid='30';

-- Delete all data from a table:

DELETE FROM student;

-- ALTER ---> ADD, DROP, MODIFY (DDL)

      --- Add New column for table:

        ALTER TABLE student ADD Address varchar2 (20);

    --- Delete column from a table:

        ALTER TABLE student DROP COLUMN Address;

    -- MODIFY a column inside a table:

        ALTER TABLE student MODIFY Sphone varchar2 (20);

    -- Delete row from a table:

        DELETE FROM student WHERE sid = 10;

    -- Update single value  in table

        UPDATE student
        SET Sname= 'Khalid'
        WHERE Sid=20;

    -- Update Multivalues in table

        UPDATE student
        SET Sname= 'ali'

    -- Delete table from Database :

        DROP TABLE student;