Assignment -05
-TABLE 1:
CREATE TABLE EMPLOYEE (
EMPNO NUMBER(4) PRIMARY KEY,
EMPNAME VARCHAR2(50),
JOB VARCHAR2(30),
DEPTNO NUMBER(2),
SALARY NUMBER(8, 2),
COMM NUMBER(8, 2),
DOB DATE,
MNO NUMBER(4),
HIRE_DATE DATE
);
--TABLE 2:
CREATE TABLE DEPTT (
DPTNO NUMBER(2) PRIMARY KEY,
DPTNAME VARCHAR2(50),
LOCATION VARCHAR2(50)
);
--INSERT DATA:
INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (1, 'Oliver', ''Analyst', 1, 60000, 1000, TO_DATE('1989-01-10', 'yyyy-mm-dd'), 3, TO_DATE('2009-01-05', 'yyyy-mm-dd'));
INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (2, 'Samuel', 'Salesperson', 2, 25000, 150, TO_DATE('1995-09-21', 'yyyy-mm-dd'), 1, TO_DATE('2013-02-15', 'yyyy-mm-dd'));
INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (3, 'Alexander', 'Manager', 3, 50000, NULL, TO_DATE('1980-02-15', 'yyyy-mm-dd'), NULL, TO_DATE('2008-10-09', 'yyyy-mm-dd'));
INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (4, 'James', 'Analyst', 1, 40000, 5000, TO_DATE('1990-02-19', 'yyyy-mm-dd'), 1, TO_DATE('2016-05-01', 'yyyy-mm-dd'));
INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (5, Andrew', 'Salesperson', 2, 28000, 400, TO_DATE('1987-11-20', 'yyyy-mm-dd'), 1, TO_DATE('2016-05-10', 'yyyy-mm-dd'));
INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (6, 'Jennifer', 'Engineer', 3, 50000, 800, TO_DATE('1990-09-30', 'yyyy-mm-dd'), 2, TO_DATE('2015-12-05', 'yyyy-mm-dd'));
INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (7, 'Charles', 'Engineer', 4, 70000, 2500, TO_DATE('1987-02-09', 'yyyy-mm-dd'), 2, TO_DATE('2006-09-12', 'yyyy-mm-dd'));
INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (8, 'Anthony', 'Analyst', 1, 52000, 1500, TO_DATE('1995-09-06', 'yyyy-mm-dd'), 3, TO_DATE('2019-03-14', 'yyyy-mm-dd'));
INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (9, Joseph', 'Manager', 3, 68000, NULL, TO_DATE('1990-11-25', 'yyyy-mm-dd'), NULL, TO_DATE('2020-01-09', 'yyyy-mm-dd'));
INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (10, 'Isaac', 'Salesperson', 2, 26000, 400, TO_DATE('1996-02-25', 'yyyy-mm-dd'), 1, TO_DATE('2019-09-30', 'yyyy-mm-dd'));
--sql query
--Add a column "Marital Status" to the EMPLOYEE table
ALTER TABLE EMPLOYEE
ADD Marital_Status VARCHAR2(20) CHECK (Marital_Status IN ('Unmarried', 'Married', 'Divorce'));
--Change the column name from MNO to MGRNO
ALTER TABLE EMPLOYEE
RENAME COLUMN MNO TO MGRNO;
--Create a view named EMPVIEW containing EMPNO, EMPNAME, and SALARY of employees who work in department 3
CREATE VIEW EMPVIEW AS
SELECT EMPNO, EMPNAME, SALARY
FROM EMPLOYEE
WHERE DEPTNO = 3;
--Create a view named deptt_summary that consists of deptt_name, no. of employees, total_salary, maximum salary, and minimum salary for each department
CREATE VIEW deptt_summary AS
SELECT
D.DEPTNO AS Department_Number,
D.DEPTNAME AS Department_Name,
COUNT(E.EMPNO) AS No_of_Employees,
SUM(E.SALARY) AS Total_Salary,
MAX(E.SALARY) AS Maximum_Salary,
MIN(E.SALARY) AS Minimum_Salary
FROM
DEPARTMENT D
LEFT JOIN
EMPLOYEE E
ON
D.DEPTNO = E.DEPTNO
GROUP BY
D.DEPTNO, D.DEPTNAME;
--Grant INSERT and UPDATE privileges on DEPT to both RAM and SHAYM
GRANT INSERT, UPDATE ON DEPARTMENT TO RAM, SHAYM;
--Delete all information about department no. 3 and job='Engineer'
DELETE FROM EMPLOYEE
WHERE DEPTNO = 3 AND JOB = 'Engineer';
--Modify the data type of DPTNO from number to varchar2
ALTER TABLE EMPLOYEE
MODIFY (DEPTNO varchar2);
--Increase the salary of each employee by 10%
UPDATE EMPLOYEE
SET SALARY = SALARY * 1.10;
--Show the total salary of each employee (including SAL and COMM)
Show the total salary of each employee (including SAL and COMM)
--Show the salary of each employee who has not received any commission
SELECT EMPNO, EMPNAME, SALARY
FROM EMPLOYEE
WHERE COMM IS NULL;
--Increase the salary of each employee as the sum of salary and comm
UPDATE EMPLOYEE
SET SALARY = SALARY + COALESCE(COMM, 0);
Comments
Post a Comment