Banco de Dados Exercício 5

DROP TABLE R11_DEPARTMENT CASCADE CONSTRAINTS;
DROP TABLE R11_EMPLOYEE CASCADE CONSTRAINTS;
DROP TABLE R11_DEPT_LOCATIONS CASCADE CONSTRAINTS;
DROP TABLE R11_PROJECT CASCADE CONSTRAINTS;
DROP TABLE R11_WORKS_ON CASCADE CONSTRAINTS;
DROP TABLE R11_DEPENDENT CASCADE CONSTRAINTS;

CREATE TABLE R11_DEPARTMENT (
DNAME VARCHAR(15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL,
MGRSTARTDATE DATE,
PRIMARY KEY (DNUMBER),
UNIQUE(DNAME)
);

CREATE TABLE R11_EMPLOYEE (
FNAME VARCHAR(15) NOT NULL,
MINIT CHAR,
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR(30),
SEX CHAR,
SALARY DECIMAL(10,2),
SUPERSSN CHAR(9),
DNO INT NOT NULL,
PRIMARY KEY (SSN),
FOREIGN KEY (SUPERSSN) REFERENCES R11_EMPLOYEE(SSN),
FOREIGN KEY (DNO) REFERENCES R11_DEPARTMENT(DNUMBER)
);

ALTER TABLE R11_DEPARTMENT ADD CONSTRAINT FK_R11_EMPLOYEEDEPT FOREIGN KEY (MGRSSN) REFERENCES R11_EMPLOYEE(SSN) DEFERABLE;

CREATE TABLE R11_DEPT_LOCATIONS (
DNUMBER INT NOT NULL,
DLOCATION VARCHAR(15) NOT NULL,
PRIMARY KEY (DNUMBER, DLOCATION),
FOREIGN KEY (DNUMBER) REFERENCES R11_DEPARTMENT(DNUMBER)
);

CREATE TABLE R11_PROJECT (
PNAME VARCHAR(15) NOT NULL,
PNUMBER INT NOT NULL,
PLOCATION VARCHAR(15),
DNUM INT NOT NULL,
PRIMARY KEY (PNUMBER),
UNIQUE(PNAME),
FOREIGN KEY (DNUM) REFERENCES R11_DEPARTMENT(DNUMBER)
);

CREATE TABLE R11_WORKS_ON (
ESSN CHAR(9) NOT NULL,
PNO INT NOT NULL,
HOURS DECIMAL(3,1) NOT NULL,
PRIMARY KEY(ESSN, PNO),
FOREIGN KEY(ESSN) REFERENCES R11_EMPLOYEE(SSN),
FOREIGN KEY(PNO) REFERENCES R11_PROJECT(PNUMBER)
);

CREATE TABLE R11_DEPENDENT (
ESSN CHAR(9) NOT NULL,
DEPENDENT_NAME VARCHAR(15) NOT NULL,
SEX CHAR,
BDATE DATE,
RELATIONSHIP VARCHAR(8),
PRIMARY KEY (ESSN, DEPENDENT_NAME),
FOREIGN KEY(ESSN) REFERENCES R11_EMPLOYEE(SSN)
);

INSERT INTO R11_DEPARTMENT VALUES ('Research', 5, '333445555', '22/05/1988');
INSERT INTO R11_DEPARTMENT VALUES ('Administration', 4, '987654321', '01/01/1995');
INSERT INTO R11_DEPARTMENT VALUES ('Headquarters', 1, '888665555', '19/06/1981');

INSERT INTO R11_DEPT_LOCATIONS VALUES (1, 'Houston');
INSERT INTO R11_DEPT_LOCATIONS VALUES (4, 'Stafford');
INSERT INTO R11_DEPT_LOCATIONS VALUES (5, 'Bellaire');
INSERT INTO R11_DEPT_LOCATIONS VALUES (5, 'Sugarland');
--#INSERT INTO R11_DEPT_LOCATIONS VALUES (null, 'Houston');

INSERT INTO R11_PROJECT VALUES ('ProdutoX', 1, 'Bellaire', 5);
INSERT INTO R11_PROJECT VALUES ('ProdutoY', 2, 'Sugarland', 5);
INSERT INTO R11_PROJECT VALUES ('ProdutoZ', 3, 'Houston', 4);
INSERT INTO R11_PROJECT VALUES ('Computerization', 10, 'Stafford', 4);
INSERT INTO R11_PROJECT VALUES ('Reorganization', 20, 'Houston', 1);
INSERT INTO R11_PROJECT VALUES ('Newbenefits', 30, 'Stafford', 4);

INSERT INTO R11_EMPLOYEE VALUES ('James', 'E', 'Borg', '888665555', '10/11/1937', '450 Stone, Houston, TX', 'M', 55000, null, 1);
INSERT INTO R11_EMPLOYEE VALUES ('Franklin', 'T', 'Wong', '333445555', '08/12/1955', '638 Voss, Houston, TX', 'M', 40000, '888665555', 5);
INSERT INTO R11_EMPLOYEE VALUES ('John', 'B', 'Smith', '123456789', '01/09/1965', '731 fondren, Houston, TX', 'M', 30000, '333445555', 5);
INSERT INTO R11_EMPLOYEE VALUES ('Jennifer', 'S', 'Wallace', '987654321', '20/06/1941', '291 Berry, Bellaire, TX', 'F', 43000, '888665555', 4);
INSERT INTO R11_EMPLOYEE VALUES ('Alicia', 'J', 'Zelaya', '999887777', '19/01/1968', '3321 Castle, Spring, TX', 'F', 25000, '987654321', 4);
INSERT INTO R11_EMPLOYEE VALUES ('Ramesh', 'K', 'Narayan', '666884444', '15/09/1962', '975, Fire Oak, Humble, TX', 'M', 38000, '333445555', 5);
INSERT INTO R11_EMPLOYEE VALUES ('Joyce', 'A', 'English', '453453453', '31/07/1972', '6531 Rice, Houston, TX', 'F', 25000, '333445555', 5);
INSERT INTO R11_EMPLOYEE VALUES ('Ahmad', 'V', 'Jabbar', '987987987', '29/03/1969', '980 Dallas, Houston, TX', 'M', 25000, '987654321', 4);

INSERT INTO R11_WORKS_ON VALUES ('123456789', 1, 32.5);
INSERT INTO R11_WORKS_ON VALUES ('123456789', 2, 7.5);
INSERT INTO R11_WORKS_ON VALUES ('666884444', 3, 40.0);
INSERT INTO R11_WORKS_ON VALUES ('453453453', 1, 20.0);
INSERT INTO R11_WORKS_ON VALUES ('453453453', 2, 20.0);
INSERT INTO R11_WORKS_ON VALUES ('333445555', 2, 10.0);
INSERT INTO R11_WORKS_ON VALUES ('333445555', 3, 10.0);
INSERT INTO R11_WORKS_ON VALUES ('333445555', 10, 10.0);
INSERT INTO R11_WORKS_ON VALUES ('333445555', 20, 10.0);
INSERT INTO R11_WORKS_ON VALUES ('999887777', 30, 30.0);
INSERT INTO R11_WORKS_ON VALUES ('999887777', 10, 10.0);
INSERT INTO R11_WORKS_ON VALUES ('987987987', 10, 35.0);
INSERT INTO R11_WORKS_ON VALUES ('987987987', 30, 5.0);
INSERT INTO R11_WORKS_ON VALUES ('987654321', 30, 20.0);
INSERT INTO R11_WORKS_ON VALUES ('987654321', 20, 15.0);
INSERT INTO R11_WORKS_ON VALUES ('888665555', 20, null);

INSERT INTO R11_DEPENDENT VALUES ('333445555', 'Alice', 'F', '05/04/1986', 'FILHA');
INSERT INTO R11_DEPENDENT VALUES ('333445555', 'Theodore', 'M', '25/10/1983', 'FILHO');
INSERT INTO R11_DEPENDENT VALUES ('333445555', 'Joy', 'F', '03/05/1958', 'CONJUGE');
INSERT INTO R11_DEPENDENT VALUES ('987654321', 'Abner', 'M', '28/02/1942', 'CONJUGE');
INSERT INTO R11_DEPENDENT VALUES ('123456789', 'Michael', 'M', '04/01/1988', 'FILHO');
INSERT INTO R11_DEPENDENT VALUES ('123456789', 'Alice', 'F', '30/12/1988', 'FILHA');
INSERT INTO R11_DEPENDENT VALUES ('123456789', 'Elizabeth', 'F', '05/05/1967', 'CONJUGE');

Com base no esquema acima responda as seguintes perguntas :

1. Selecionar, para os empregados que possuem menos de 200 meses de tempo de serviço, o nome do empregado, a data de admissão, a data em que ele completou 6 meses de tempo de serviço, a data da primeira sexta-feira (FRIDAY) após sua admissão, e o último dia do mês em que ele foi admitido.

SELECT ENAME,HIREDATE,ADD_MONTHS(HIREDATE,6),
NEXT_DAY(HIREDATE,'SEXTA'),LAST_DAY(HIREDATE)
FROM EMP
WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE) > 200;

2. Selecionar o nome do empregado, o mês e o ano em que ele foi admitido.

SELECT ENAME,TO_CHAR(HIREDATE,'MM') AS MES_ADMISSAO,TO_CHAR(HIREDATE,'YYYY')AS ANO_ADMISSAO FROM EMP;

3. Selecionar o nome do empregado e o seu salário no formato ‘R$ 99,999’.

SELECT ENAME,TO_CHAR(SAL,'L9999,99')
FROM EMP;

4. Selecionar o nome do empregado, o salário, o valor da comissão e o salário anual.

SELECT ENAME,SAL,12*(SAL) AS SALARIO_ANUAL , COMM
FROM EMP;

5. Selecionar o nome do empregado, o salário, e o salário revisado.
OBS: O salário revisado é calculado de acordo com a seguinte regra:
SE job = ‘ANALYST’ ENTAO sal = sal * 1.1
SE job = ‘CLERK’ ENTAO sal = sal * 1.15
SE job = ‘MANAGER’ ENTAO sal = sal * 1.20
SENAO sal = sal

SELECT ENAME,SAL,DECODE(JOB, 'ANALYST' , (sal * 1.1) , 'CLERK' , (sal * 1.15) , 'MANAGER', (sal * 1.20), sal ) AS SALARIO_REVISADO
FROM EMP;

6. Computar o produto cartesiano das tabelas EMPR e DEPT.

SELECT * FROM EMP, DEPT;

7. Selecionar o nome do empregado, o nome e a localização do departamento onde ele
trabalha.

SELECT ENAME, DNAME , LOC
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;

8. Selecionar o nome do empregado, o salário e o grau (faixa de salário).

SELECT ENAME, SAL , GRADE
FROM EMP , SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL;

9. Selecionar o nome do empregado e o nome do subordinado, para os subordinados ate o
ultimo nível na hierarquia (operação de fechamento recursivo).

Select ...

10. Selecionar o nome do departamento e o numero de empregados, para os departamentos que possuem pelo menos 10 empregados.

SELECT DNAME , COUNT(EMPNO)
FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
GROUP BY DNAME
HAVING COUNT(EMPNO)>10;

11. Selecionar o nome do departamento e o salario total dos seus empregados, para os
departamentos cuja soma de salarios seja superior a 5000.

SELECT DNAME , SUM(SAL) AS TOTALSAL
FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
GROUP BY DNAME
HAVING SUM(SAL)>5000;