Edu-s-SQL-ORACLE11G

Datasets creation

CREATE TABLE EMP(EMPNO NUMBER(6) PRIMARY KEY,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(9),HIREDATE DATE,SAL NUMBER(9),COMM NUMBER(9),DEPTNO NUMBER(9) REFERENCES DEPT ON DELETE CASCADE);

Inserting values

INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,'17/12/80',800,NULL,20);

INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'20/02/81',1600,300,30);

INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'22/02/81',1250,500,30);

INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'02/04/81',2975,NULL,20);

INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'28/09/81',1250,1400,30);

INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'01/05/81',2850,NULL,30);

INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'09/06/81',2450,NULL,10);

INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'19/04/87',3000,NULL,20);

INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'17/11/81',5000,NULL,10);

INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'08/09/81',1500,0,30);

INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'23/05/87',1100,NULL,20);

INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'03/12/81',950,NULL,30);

INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'03/12/81',3000,NULL,20);

INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'23/01/82',1300,NULL,10); 

Tables creation

CREATE TABLE DEPT (DEPTNO NUMBER(9) PRIMARY KEY,DNAME VARCHAR2(14),LOC VARCHAR2(12));

Inserting values

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');

INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');

INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

Tables creation

CREATE TABLE OFICIO(JOB VARCHAR2(9) PRIMARY KEY,FUNCIONES VARCHAR2(20),CATEGORIA NUMBER(1),EMPLEADOS NUMBER(1));

Inserting values

INSERT INTO OFICIO VALUES(‘CLERK’,’DEPENDIENTE’,1,4);

Adding constraints

ALTER TABLE EMP ADD CONSTRAINT FK_OFICIO FOREIGN KEY(JOB) REFERENCES OFICIO(JOB) ON DELETE CASCADE;

Queries

SELECT EMPNO,ENAME FROM EMP WHERE SAL BETWEEN 1000 AND 2000 ORDER BY ENAME ASC;

SELECT EMPNO FROM EMP WHERE COMM <>0;

SELECT ENAME,HIREDATE,COMM FROM EMP WHERE SAL>500 AND COMM<>0 AND MGR IS NOT NULL;

SELECT ENAME FROM EMP WHERE HIREDATE < '1/05/1981';

SELECT EMPNO FROM EMP WHERE HIREDATE BETWEEN '1/1/1980' AND '1/12/1981' AND DEPTNO IN(10,20) AND JOB IN ('SALESMAN','MANAGER') AND MGR=7698 ORDER BY EMPNO;

SELECT ENAME NOMBRE,EMPNO CODIGO_EMPLEADO,SAL SALARIO FROM EMP WHERE JOB LIKE '_A%A_' OR DEPTNO IN(10,20);

SELECT ROUND(AVG(SAL),2) SALARIO_MEDIO, DEPTNO DEPARTAMENTO FROM EMP GROUP BY DEPTNO ORDER BY AVG(SAL) DESC;

SELECT ENAME, SAL*12 AS SAL_ANUAL, COMM*12 AS COM_ANUAL FROM EMP;

SELECT DISTINCT SAL FROM EMP WHERE DEPTNO=30;

SELECT DEPTNO FROM DEPT WHERE LOC LIKE '%A%' OR LOC LIKE '%E%'OR LOC LIKE '%I%';

SELECT ENAME FROM EMP WHERE COMM<>0;

SELECT ENAME NOMBRE, SAL SALARIO FROM EMP WHERE JOB IN ('SALESMAN','MANAGER') AND HIREDATE< '01/01/83' ORDER BY HIREDATE DESC;

SELECT SAL*1.05 SALARIO_IPC FROM EMP WHERE (SAL NOT BETWEEN 3000 AND 5000 OR EMPNO LIKE '_9%' OR DEPTNO NOT IN (10,20)) AND JOB IN ('CLERK','ANALYST','SALESMAN') ORDER BY SAL ASC;

SELECT ENAME NOMBRE FROM EMP WHERE (COMM=0 OR COMM IS NULL) AND MGR=7839 AND HIREDATE BETWEEN ‘01/1/80’ AND ‘31/12/83’ ORDER BY NOMBRE ASC;

SELECT DEPTNO "Nº DEPARTAMENTO", ENAME NOMBRE, (SAL+COMM)/12 "SALARIO MENSUAL CON COMISION" FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE    DNAME='SALES' AND LOC='CHICAGO') AND JOB IN ('SALESMAN','CLERK') AND COMM IS NOT NULL AND COMM<>0 ORDER BY COMM DESC;

SELECT DEPTNO "Nº DEPARTAMENTO", ENAME NOMBRE, (SAL+COMM)/12 "SALARIO MENSUAL CON COMISION" FROM EMP NATURAL JOIN DEPT WHERE DNAME='SALES' AND LOC='CHICAGO' AND JOB IN ('SALESMAN','CLERK') AND COMM IS NOT NULL AND COMM<>0 ORDER BY COMM DESC;

SELECT DEPTNO "Nº DEPARTAMENTO", ENAME NOMBRE FROM EMP NATURAL JOIN DEPT WHERE LOC='NEW YORK' ORDER BY DEPTNO;

SELECT DEPTNO "Nº DEPARTAMENTO", ENAME NOMBRE FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK') ORDER BY DEPTNO;

SELECT ENAME FROM EMP WHERE SAL/12>=250 AND (COMM=0 OR COMM IS NULL);

SELECT ENAME, JOB FROM EMP WHERE (ENAME LIKE '_A%____' OR ENAME LIKE '__A%___' OR ENAME LIKE '____S%_') AND (MGR>7600 OR SAL/12<200) AND (DEPTNO IN (20,30) OR EMPNO>7700) ORDER BY MGR;

SELECT ENAME, DEPTNO FROM EMP WHERE (ENAME LIKE '_A%' OR ENAME LIKE '__A%'  OR ENAME LIKE '____S%') AND ENAME LIKE '______' AND (MGR>7600 OR SAL/12<200) AND (DEPTNO IN (20,30) OR EMPNO>7700) ORDER BY MGR;

SELECT ENAME NOMBRE, HIREDATE "FECHA INGRESO" FROM EMP WHERE DEPTNO IN (10,20,30) AND LENGTH(ENAME)<5 ORDER BY HIREDATE DESC;

SELECT SAL "SALARIO MENSUAL" FROM EMP  WHERE MGR IS NOT NULL OR MGR>=7900 AND DEPTNO NOT IN (10,40) ORDER BY DEPTNO ASC;

SELECT ENAME, JOB, EMPNO FROM EMP WHERE SAL BETWEEN 600 AND 1500 AND SAL NOT BETWEEN 1000 AND 3000 ORDER BY DEPTNO ASC;

SELECT DISTINCT JOB,SAL "SALARIO MENSUAL" FROM EMP WHERE JOB='SALESMAN' ORDER BY 'SALARIO MENSUAL' DESC;

SELECT ROUND(SAL+NVL(COMM,0),0)*1.05 "SALARIO CON COMISION",ENAME FROM EMP WHERE  JOB<> 'CLERK' AND HIREDATE<='01/01/85' ORDER BY "SALARIO CON COMISION" DESC;

SELECT ROUND(SAL*1.3*1.05+NVL(COMM,0),0) "NUEVO SUELDO",ENAME FROM EMP WHERE JOB='SALESMAN' OR COMM IS NOT NULL AND COMM<>0;

SELECT ROUND(SAL*1.03*0.90+NVL(COMM,0),0) "NUEVO SUELDO",ENAME FROM EMP WHERE JOB NOT IN ('MANAGER',’PRESIDENT’) OR COMM IS NULL OR COMM=0;

SELECT ENAME FROM EMP NATURAL JOIN DEPT NATURAL JOIN OFICIO WHERE (COMM IS NOT NULL OR SAL>1400 OR COMM<>0 OR DEPTNO IN (10,20) OR HIREDATE<'01/01/87' OR CATEGORIA IN (2,3,4)) AND MGR>7800 ORDER BY CATEGORIA DESC;

SELECT CATEGORIA,COUNT(EMPLEADOS),AVG(SAL) FROM EMP NATURAL JOIN DEPT NATURAL JOIN OFICIO WHERE LOC='&LOCALIDAD' GROUP BY CATEGORIA HAVING CATEGORIA=&CATEGORIA AND CATEGORIA>1;

SELECT DNAME PUESTO,COUNT(EMPLEADOS)"Nº DE EMPLEADOS",ROUND(AVG(SAL),0) "SALARIO MEDIO",ROUND(MAX(SAL),0) "SALARIO MAXIMO",ROUND(MIN(SAL),0) "SALARIO MINIMO",ROUND(SUM(SAL),0) "SALARIO TOTAL" FROM OFICIO NATURAL JOIN DEPT NATURAL JOIN EMP GROUP BY DNAME HAVING DNAME IN ('SALES','RESEARCH') ORDER BY "SALARIO TOTAL" DESC;

SELECT DNAME DEPARTAMENTO,NVL(JOB,'SUMATORIO') PUESTO, ROUND(COUNT(EMPLEADOS),0) "Nº DE EMPLEADOS",ROUND(AVG(SAL),0) "SALARIO MEDIO",ROUND(MAX(SAL),0) "SALARIO MAXIMO",ROUND(MIN(SAL),0) "SALARIO MINIMO",ROUND(SUM(SAL),0) "SALARIO TOTAL"FROM OFICIO NATURAL JOIN DEPT NATURAL JOIN EMPGROUP BY ROLLUP(DNAME,JOB) HAVING DNAME IN ('SALES','RESEARCH') ORDER BY DEPARTAMENTO DESC;

SELECT ROUND(AVG(SAL),2) MEDIA_SALARIAL, JOB PUESTO, DEPTNO DEPARTAMENTO FROM EMP GROUP BY DEPTNO,JOB HAVING JOB='&PUESTO'ORDER BY DEPARTAMENTO DESC;

SELECT ' El total de empleados con comisión real es de  ' " ", COUNT(EMPLEADOS) "TOTAL EMPLEADOS",‘su media salarial es de ' " ", ROUND(AVG(SAL),2) "MEDIA SALARIAL ",' su nombre de departamento es el " ",DNAME DEPARTAMENTO, 'categoria ' " ", CATEGORIA FROM EMP NATURAL JOIN OFICIO NATURAL JOIN DEPT WHERE COMM<>0 AND COMM IS NOT NULL  GROUP BY DNAME,CATEGORIA;

SELECT SUBSTR(SAL,-4,1)||'mil' MIL_SAL, SUBSTR(SAL,-3,1)||'cen' CEN_SAL, DECODE(COMM,NULL,0,SUBSTR(COMM,-4,1))||'mil' MIL_COM, DECODE(COMM,NULL,0,SUBSTR(COMM,-3,1))||'cen' CEN_COM, SAL, COMM FROM EMP WHERE SAL>999;

SELECT COUNT(EMPLEADOS) FROM DEPT NATURAL JOIN OFICIO  NATURAL JOIN EMP WHERE DEPTNO LIKE '&VALOR%';

SELECT COUNT(ENAME) FROM EMP WHERE ENAME LIKE 'M%';

SELECT COUNT(DISTINCT(JOB)) TOTAL_TRABAJOS FROM EMP;

SELECT ENAME FROM EMP WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE)/12>35 ORDER BY ENAME;

SELECT SUM(SAL) SALARIO, SUM(COMM) COMISION, GREATEST(SUM(SAL),SUM(COMM)) MAYOR FROM EMP NATURAL JOIN DEPT  WHERE DNAME='SALES' AND COMM IS NOT NULL AND COMM<>0;

SELECT COUNT(*),DECODE(JOB,'CLERK','DEPENDIENTE','SALESMAN','VENDEDOR','ANALYST','ANALISTA','MANAGER','RESPONSABLE','PRESIDENT','PRESIDENT',JOB) FROM EMP GROUP BY JOB HAVING COUNT(*)>2;

SELECT DEPTNO,COUNT(DEPTNO) FROM EMP NATURAL JOIN OFICIO WHERE CATEGORIA <>4 GROUP BY DEPTNO HAVING COUNT(DEPTNO)>2;

SELECT DISTINCT DNAME FROM EMP NATURAL JOIN DEPT WHERE SAL>500;

SELECT DEPTNO,ENAME FROM EMP WHERE MGR IN (SELECT EMPNO FROM EMP WHERE DEPTNO IN (10,20));

SELECT ENAME, SAL, COMM,MGR FROM EMP WHERE COMM IS NULL AND MGR IN (SELECT EMPNO FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK'));

SELECT ENAME, EMPNO, HIREDATE FROM EMP WHERE MGR IN (SELECT EMPNO FROM EMP WHERE MGR IS NULL);

SELECT ROUND(SAL,0) "SALARIO MENSUAL", ENAME FROM EMP WHERE MGR IN( SELECT EMPNO FROM EMP WHERE EMPNO>7800 AND EMPNO<7900);

SELECT ENAME FROM EMP WHERE DEPTNO IN  (SELECT DEPTNO FROM DEPT WHERE DNAME='SALES') AND MGR IN (SELECT EMPNO FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC<>'BOSTON'));

SELECT DNAME FROM (SELECT DNAME, DEPTNO FROM DEPT) --VV DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM (SELECT DEPTNO,SAL FROM EMP ) --VV EMP WHERE SAL >SOME (SELECT SAL FROM (SELECT SAL, DEPTNO FROM EMP) --VV EMP WHERE DEPTNO IN (SELECT DEPTNO FROM (SELECT DEPTNO,LOC FROM DEPT) --VV DEPT WHERE LOC ='NEW YORK')));

SELECT EMPNO, ENAME FROM EMP  WHERE JOB='MANAGER'  AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME NOT IN('SALES') AND JOB IN (SELECT JOB FROM DEPT WHERE LOC='CHICAGO'));

SELECT DNAME FROM DEPT WHERE DEPTNO>(SELECT DEPTNO FROM EMP WHERE EMPNO=7698 AND DEPTNO IN (SELECT DEPTNO FROM EMP NATURAL JOIN OFICIO WHERE CATEGORIA<2));

SELECT DNAME FROM DEPT WHERE DEPTNO > (SELECT DEPTNO FROM EMP WHERE SAL BETWEEN 4000 AND 5000) AND DEPTNO >= ANY (SELECT DEPTNO FROM EMP WHERE COMM IS NOT NULL) ORDER BY DNAME;

SELECT ENAME, EMPNO, FUNCIONES, LOC FROM (SELECT ENAME, EMPNO, FUNCIONES,LOC,SAL FROM EMP, DEPT, OFICIO WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.JOB=OFICIO.JOB) WHERE SAL IN (SELECT COMM*10 FROM (SELECT COMM FROM EMP WHERE COMM IS NOT NULL)) ORDER BY EMPNO DESC;

SELECT ENAME, CATEGORIA FROM EMP, OFICIO WHERE EMP.JOB=OFICIO.JOB AND CATEGORIA NOT IN ( SELECT CATEGORIA FROM OFICIO WHERE EMPLEADOS NOT BETWEEN 1 AND 3 AND JOB IN ( SELECT JOB FROM EMP WHERE DEPTNO IN ( SELECT DEPTNO FROM DEPT WHERE LOC <> 'NEW YORK'))) AND DEPTNO IN ( SELECT DEPTNO FROM DEPT WHERE DNAME LIKE '%A%' OR DNAME LIKE '%E%');

SELECT ENAME FROM EMP WHERE SAL < ANY ( SELECT SAL FROM EMP WHERE DEPTNO IN ( SELECT DEPTNO FROM DEPT WHERE LOC ='DALLAS')) AND SAL > ANY ( SELECT AVG(SAL) FROM EMP WHERE JOB IN ( SELECT JOB FROM OFICIO WHERE EMPLEADOS >2) GROUP BY DEPTNO HAVING AVG(SAL) >= 1000) ORDER BY SAL DESC;

SELECT DNAME FROM (SELECT DNAME, DEPTNO FROM DEPT) WHERE DEPTNO > SOME ( SELECT DEPTNO FROM (SELECT DEPTNO, HIREDATE, JOB FROM EMP) WHERE HIREDATE < '01-01-1983' AND JOB NOT IN ( SELECT JOB FROM (SELECT JOB, CATEGORIA FROM OFICIO) WHERE CATEGORIA =1 AND CATEGORIA IN ( SELECT DEPTNO/2 FROM (SELECT DEPTNO FROM EMP)))) ORDER BY DNAME;

SELECT ENAME, SAL, DNAME, FUNCIONES, EMPLEADOS FROM (SELECT ENAME, SAL , DNAME, FUNCIONES, EMPLEADOS, MGR, COMM, EMP.JOB, OFICIO.JOB FROM EMP, DEPT, OFICIO WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.JOB=OFICIO.JOB) WHERE MGR NOT IN ( SELECT EMPNO FROM EMP WHERE COMM > SOME (SELECT SAL/12 FROM EMP WHERE JOB IN ( SELECT JOB FROM OFICIO WHERE FUNCIONES IN('DEPENDIENTE','VENDEDOR'))));</h6>

DDL AND DML

Tables creation

CREATE TABLE PROVEEDORES(CIF VARCHAR2(10) CONSTRAINT PK_CIF_PRO PRIMARY KEY,NOMBREEMP VARCHAR2(30) CONSTRAINT UQ_NE_PRO UNIQUE,NOMBRECONTACTO VARCHAR2(25),DIRECCION VARCHAR2(100), CIUDAD VARCHAR2(20) DEFAULT 'MADRID', FECHAALTA DATE, FECHABAJA DATE, CONSTRAINT F_PROV CHECK (FECHAALTA<=FECHABAJA));

CREATE TABLE PRODUCTOS(CODIGOPRODUCTO NUMBER(3) CONSTRAINT PK_CP_PROD PRIMARY KEY,NOMBREPRO VARCHAR2(30) CONSTRAINT UQ_NP_PROD UNIQUE,UDSTOCK NUMBER(5) CONSTRAINT NN_US_PROD NOT NULL,TIEMPOENTREGA NUMBER(2),CIF VARCHAR2(10) CONSTRAINT FK_CIF_PROD REFERENCES PROVEEDORES ON DELETE CASCADE,PRECIOUD NUMBER(8,2),MARGENUD NUMBER(8,2),PRECIOTOTAL NUMBER(8,2));

CREATE TABLE CLIENTES(CODIGOCLI NUMBER(4) CONSTRAINT PK_CC_CLI PRIMARY KEY,NOMBRE VARCHAR2(20),PREFERENTE VARCHAR2(2) CONSTRAINT C_PREF_CLI CHECK (PREFERENTE IN ('SI','NO')));

CREATE TABLE PEDIDOS(CODIGOPED NUMBER(4) CONSTRAINT PK_CODPED_PED PRIMARY KEY,FECHAPEDIDO DATE DEFAULT SYSDATE,FECHAENTREGA DATE DEFAULT SYSDATE+3,FECHACOBRO DATE DEFAULT SYSDATE+3,DESTINATARIO VARCHAR2(100) NOT NULL,CODIGOCLI NUMBER(4) CONSTRAINT FK_CC_CLI REFERENCES CLIENTES ON DELETE CASCADE);

CREATE TABLE DETALLESPEDIDO(CODIGOPRO NUMBER(3) CONSTRAINT FK_CODPRO_DETPED REFERENCES PRODUCTOS ON DELETE CASCADE,CODIGOPED NUMBER(4) CONSTRAINT FK_CODPED_DETPED REFERENCES PEDIDOS ON DELETE CASCADE,PRECIO NUMBER(8,2) CONSTRAINT NN_PR_DETPED NOT NULL,CANTIDADPROD NUMBER(4) CONSTRAINT NN_CANTPROD_DETPED NOT NULL,DTOXUD NUMBER(2) CHECK (DTOXUD IN (NULL,0,2,5,8,10)),IVA NUMBER(2) CHECK( IVA IN (4,10,21)),PVPFINAL NUMBER(8,2) AS (ROUND(PRECIO*((PRECIO/PRECIO)+(IVA/100)),2)),PVPFINALCONDTO NUMBER(8,2) AS (ROUND((PRECIO*((PRECIO/PRECIO)+(IVA/100))-(PRECIO*(DTOXUD/100)),2)),CONSTRAINT PK_CODPROCODPED_DETPED PRIMARY KEY (CODIGOPRO,CODIGOPED),CONSTRAINT C_DP CHECK ((CANTIDADPROD BETWEEN 0 AND 50 AND DTOXUD=2) OR(CANTIDADPROD BETWEEN 51 AND 100 AND DTOXUD=5) OR(CANTIDADPROD BETWEEN 101 AND 200 AND DTOXUD=8) OR(CANTIDADPROD>200 AND DTOXUD=10)));

Inserting values

INSERT INTO PROVEEDORES VALUES ('G87567823','MUEBLES BOOM','PEPE','CALLE RUEDA, 23','23-5-15',NULL,'VALENCIA');

INSERT INTO PRODUCTOS  VALUES (1,'TUERCAS',100,2,'G87567823',5.25,0.5);

INSERT INTO PRODUCTOS VALUES (2,'ARANDELAS',100,2,'G87567823',4.25,0.75);

Adding constraints

ALTER TABLE PRODUCTOS ADD (FECHAALTA DATE CONSTRAINT UQ_FA_PROD UNIQUE);

ALTER TABLE CLIENTES MODIFY (NOMBRE VARCHAR(50));

ALTER TABLE PEDIDOS RENAME COLUMN DESTINATARIO TO CLIENTE;

ALTER TABLE DETALLESPEDIDO MODIFY (DTOXUD NUMBER(2) DEFAULT (0));

Tables creation

ALTER TABLE DETALLESPEDIDO MODIFY (DTOXUD NUMBER(2) DEFAULT (0));

ALTER TABLE DETALLESPEDIDO MODIFY (DTOXUD NUMBER(2) DEFAULT (0));
 CREATE TABLE CLIENTES ( DNIC VARCHAR2(9) CONSTRAINT PK_DNIC_CLI PRIMARY KEY,NOMBRE VARCHAR2(30) CONSTRAINT UQ_N_CLI UNIQUE,APELLIDOS VARCHAR2(40) NOT NULL,FECHAALTA DATE DEFAULT SYSDATE);

CREATE TABLE VENTAS (CODIGOV NUMBER(4) CONSTRAINT PK_CODV_V PRIMARY KEY,FECHAALTA DATE DEFAULT SYSDATE,PRODUCTO VARCHAR2(30) NOT NULL,STOCK NUMBER(4) NOT NULL,STOCKNUEVO NUMBER(4) DEFAULT 0,FECHAALTASTOCK DATE,TOTALSTOCK NUMBER(4) AS (STOCK+STOCKNUEVO));

CREATE TABLE ORDENES (DNICLIENTE VARCHAR2(9) CONSTRAINT FK_DC_ORD REFERENCES CLIENTES ON DELETE CASCADE,CODIGOVENTA NUMBER(4) NOT NULL CONSTRAINT FK_CV_ORD REFERENCES VENTAS ON DELETE CASCADE,FECHAALTA DATE,FECHAENVIO DATE,FECHAPAGO DATE,PRECIOUD NUMBER(6,2),IVA NUMBER(2),CANTIDAD NUMBER(4),TOTAL NUMBER(8,2) AS (PRECIOUD*(1+(IVA/100))*CANTIDAD),CONSTRAINT PK_DNICCV_ORD PRIMARY KEY (DNICLIENTE,CODIGOVENTA),CONSTRAINT C_FAFE_ORD CHECK (FECHAALTA<=FECHAENVIO),CONSTRAINT C_FEFP_ORD CHECK (FECHAENVIO<=FECHAPAGO));

Inserting values

INSERT INTO CLIENTES VALUES (05311954L,'EDUARDO','MATOS','16/09/96');

INSERT INTO VENTAS (CODIGOV, FECHAALTA, PRODUCTO, STOCK, STOCKNUEVO, FECHAALTASTOCK) VALUES (1234,SYSDATE,'CALEFACTOR',50,100,SYSDATE);

Tables creation

CREATE TABLE FABRICANTES(COD_FABRICANTE NUMBER(3) CONSTRAINT NN_CF_FAB NOT NULL CONSTRAINT PK_CF_FAB PRIMARY KEY, NOMBRE VARCHAR2(15) DEFAULT 'NO DEFINIDO', PAIS VARCHAR2(15) DEFAULT 'ESPANA'));

CREATE TABLE ARTICULOS(ARTICULO VARCHAR2(20) NOT NULL,COD_FABRICANTE NUMBER(3) NOT NULL CONSTRAINT FK_CODF_ART REFERENCES FABRICANTES ON DELETE CASCADE,PESO NUMBER(3) CONSTRAINT NN_P_ART NOT NULL CONSTRAINT C_P_ART CHECK (PESO>0),CATEGORIA VARCHAR2(10) CONSTRAINT NN_C_ART NOT NULL,PRECIO_VENTA NUMBER(4) CHECK (PRECIO_VENTA>0),PRECIO_COSTO NUMBER(4) CHECK (PRECIO_COSTO>0),EXISTENCIAS NUMBER(5),CONSTRAINT PK_ARTCODPCAT PRIMARY KEY (ARTICULO,COD_FABRICANTE,PESO,CATEGORIA))

Inserting values

INSERT INTO FABRICANTES  (COD_FABRICANTE) VALUES (123);

INSERT INTO ARTICULOS VALUES ('ZAPATILLAS',123,4,'NIKE',300,200,12345);

Updating values

UPDATE ARTICULOS SET PRECIO_VENTA=800 WHERE ARTICULO='ZAPATILLAS';

Deleting values

DELETE FROM ARTICULOS WHERE ARTICULO='ZAPATILLAS';

Tables creation

CREATE TABLE TIENDAS(NIF VARCHAR2(10) CONSTRAINT PK_TIENDAS PRIMARY KEY,NOMBRE VARCHAR2(15) DEFAULT 'NO DEFINIDO',DIRECCION VARCHAR2(20) DEFAULT 'CALLE DESCONOCIDA',POBLACION VARCHAR2(20),PROVINCIA VARCHAR2(10) CONSTRAINT NN_T NOT NULL,CODPOSTAL NUMBER(5) CONSTRAINT U1 UNIQUE,FEC_APERTURA DATE DEFAULT SYSDATE);

CREATE TABLE VENTAS(NIF VARCHAR2(10) CONSTRAINT NN_V NOT NULL,PROVINCIA VARCHAR2(10) CONSTRAINT PK_VENTAS PRIMARY KEY,UD_VENTAS NUMBER(4) CONSTRAINT CV1 CHECK(UD_VENTAS >=100),ARTICULO VARCHAR2(10),FEC_VENTA DATE);

Adding constraints

ALTER TABLE TIENDAS ADD CONSTRAINT FK_TIENDAS FOREIGN KEY(PROVINCIA) REFERENCES VENTAS(PROVINCIA) ON DELETE CASCADE;

ALTER TABLE TIENDAS DISABLE CONSTRAINT FK_TIENDAS;

Inserting values

INSERT INTO TIENDAS VALUES('123333-X','MUEBS','CALLE MADRID','LEGANES','MADRID',28914,SYSDATE);

INSERT INTO VENTAS (NIF, PROVINCIA) VALUES('1212121-F','TOLEDO');

DCL

Default TABLESPACE creation

CREATE TABLESPACE CLASE ONLINE DATAFILE ‘D:\CLASE.DBF’ SIZE 300M REUSE DEFAULT STORAGE (INITIAL 10M NEXT 10M PCTINCREASE 10);

Temporary TABLESPACE creation

CREATE TEMPORARY TABLESPACE TEMPORAL TEMPFILE ‘D:\APP\TEMPORAL.DBF’ SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G; 

Exercise

CONN SYSTEM/*****
GRANT DBA TO SCOTT;

Role creation

CREATE ROLE R_RRHH;

Tablespace creation

CREATE TABLESPACE RRHH DATAFILE 'RRHH.ORA' SIZE 100M DEFAULT STORAGE(INITIAL 10M NEXT 10M PCTINCREASE 10);

Profile creation

CREATE PROFILE P_RRHH LIMIT CONNECT_TIME 400 IDLE_TIME 30 SESSIONS_PER_USER 1 LOGICAL_READS_PER_CALL 2;

Users RRHH1,RRHH2 and RRHH3 creation

CREATE USER RRHH1 IDENTIFIED BY RRHH1 DEFAULT TABLESPACE RRHH QUOTA 10M ON RRHH PROFILE P_RRHH; 

CREATE USER RRHH2 IDENTIFIED BY RRHH2 DEFAULT TABLESPACE RRHH QUOTA 10M ON RRHH PROFILE P_RRHH;

CREATE USER RRHH3 IDENTIFIED BY RRHH3 DEFAULT TABLESPACE RRHH QUOTA 10M ON RRHH PROFILE P_RRHH;

Assignament object’ privileges

GRANT SELECT ON EMP TO R_RRHH;
GRANT SELECT ON DEPT TO R_RRHH;

Assignament sistem’ privileges

GRANT CREATE SESSION, CREATE SYNONYM TO R_RRHH;
GRANT R_RRHH TO RRHH1,RRHH2,RRHH3;

Coming back to the standard stage

DROP TABLESPACE RRHH INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP PROFILE P_RRHH CASCADE;
DROP USER RRHH1 CASCADE;
DROP USER RRHH2 CASCADE;
DROP USER RRHH3 CASCADE;
DROP ROLE R_RRHH CASCADE;
CONN system/manager
REVOKE DBA FROM SCOTT;

Exercise

CONN SYSTEM/*****

Sequence creation

CREATE SEQUENCE CICLO MAXVALUE 1345 MINVALUE 56 START WITH 1345 INCREMENT BY -3 CYCLE;

Table creation

CREATE TABLE numeros (CODIGO NUMBER(4) CONSTRAINT PK_NUMEROS PRIMARY KEY) TABLESPACE CLASE;

Inserting values

INSERT INTO numeros VALUES (CICLO.NEXTVAL);

INSERT INTO numeros VALUES (CICLO.NEXTVAL);

INSERT INTO numeros VALUES (CICLO.NEXTVAL);

INSERT INTO numeros VALUES (CICLO.NEXTVAL);

INSERT INTO numeros VALUES (CICLO.NEXTVAL);

SELECT * FROM NUMEROS;

DROP TABLE NUMEROS;

DROP SEQUENCE CICLO;

Exercise

CREATE SEQUENCE CICLO MAXVALUE 1345 MINVALUE 56 START WITH 1345 INCREMENT BY -3 CYCLE;

CONN SYSTEM/*****
CREATE TABLE numeros (CODIGO NUMBER(4) CONSTRAINT PK_NUMEROS PRIMARY KEY) TABLESPACE CLASE; 

Inserting values

INSERT INTO numeros VALUES (CICLO.NEXTVAL);

INSERT INTO numeros VALUES (CICLO.NEXTVAL);

INSERT INTO numeros VALUES (CICLO.NEXTVAL);

INSERT INTO numeros VALUES (CICLO.NEXTVAL);

INSERT INTO numeros VALUES (CICLO.NEXTVAL);

SELECT * FROM NUMEROS;

Coming back to the standard stage

DROP TABLE NUMEROS;
DROP SEQUENCE CICLO;

Exercise

CONN SYSTEM/***** 

Tablespace creation

CREATE TABLESPACE VENTAS

OFFLINE DATAFILE 'VENTAS.ORA' SIZE 50M;

User CURRITO and JEFE creation

CREATE USER CURRITO IDENTIFIED BY CURRITO DEFAULT TABLESPACE VENTAS QUOTA 10M ON VENTAS;
CREATE USER JEFE IDENTIFIED BY JEFE DEFAULT TABLESPACE CLASE QUOTA UNLIMITED ON CLASE;

Bringing online VENTAS TABLESPACE to be available for Users

ALTER TABLESPACE VENTAS ONLINE;

Assignament sistem’ privileges

GRANT DBA TO JEFE;
GRANT CONNECT TO CURRITO;

Assignament object’ privileges

GRANT ALL ON SCOTT.EMP TO JEFE;
GRANT SELECT ON SCOTT.EMP TO CURRITO;

Coming back to the standard stage

DROP TABLESPACE VENTAS INCLUDING CONTENTS AND DATAFILES;

DROP USER JEFE;

DROP USER CURRITO;

Exercise

CONN SYSTEM/*****
User’ JIMENO creation

CREATE USER JIMENO
IDENTIFIED BY EL_CID
DEFAULT TABLESPACE CLASE
QUOTA 4M ON CLASE;

Assignament sistem’ privileges

GRANT CREATE SESSION, CREATE USER TO JIMENO WITH ADMIN OPTION;

Connecting as JIMENO

CONN JIMENO/***

CREATE USER CAMPEADOR IDENTIFIED BY EL DEFAULT TABLESPACE CLASE QUOTA 5M ON CLASE;

Assignament sistem’ privileges

GRANT CREATE SESSION, CREATE USER TO CAMPEADOR WITH ADMIN OPTION;

Connecting as CAMPEADOR

CONN CAMPEADOR/***

User’ Fernando creation

CREATE USER FERNANDO IDENTIFIED BY REY DEFAULT TABLESPACE CLASE QUOTA 2M ON CLASE;

Coming back to the standard stage

CONN system/manager
DROP USER FERNANDO;
DROP USER CAMPEADOR;
DROP USER JIMENO;

Exercise

CONN system/manager

Tablespace creation

CREATE TABLESPACE EMPRESA DATAFILE 'PROPIEDAD.ORA'B SIZE 100M DEFAULT STORAGE (INITIAL 10M NEXT 20M PCTINCREASE 0);

User DUENO and DIRECTOR creation

CREATE PROFILE DUENO LIMIT SESSIONS_PER_USER UNLIMITED CONNECT_TIME UNLIMITED IDLE_TIME UNLIMITED CPU_PER_CALL UNLIMITED LOGICAL_READS_PER_CALL UNLIMITED LOGICAL_READS_PER_SESSION UNLIMITED;
CREATE USER DIRECTOR IDENTIFIED BY DIRECTOR DEFAULT TABLESPACE EMPRESA QUOTA UNLIMITED ON EMPRESA PROFILE DUENO;

Role creation

CREATE ROLE GERENTE;

Assignament sistem’ privileges

GRANT DBA, RESOURCE TO GERENTE;
GRANT GERENTE TO DIRECTOR;
CONN DIRECTOR/****
CREATE PROFILE EMPLEADO LIMIT
SESSIONS_PER_USER 1;

Role creation

CREATE ROLE TRABA;

Assignament sistem’ privileges

GRANT CREATE SESSION TO TRABA; 

User DOS and TRES creation

CREATE USER UNO IDENTIFIED BY UNO DEFAULT TABLESPACE EMPRESA QUOTA 1M ON EMPRESA PROFILE EMPLEADO;
CREATE USER DOS IDENTIFIED BY DOS DEFAULT TABLESPACE EMPRESA QUOTA 1M ON EMPRESA PROFILE EMPLEADO;
CREATE USER TRES IDENTIFIED BY TRES DEFAULT TABLESPACE EMPRESA QUOTA 1M ON EMPRESA PROFILE EMPLEADO;

Assignament sistem’ privileges

GRANT TRABA TO UNO,DOS,TRES;
GRANT CREATE TABLE TO UNO;

Table creation

CONN UNO/UNO
CREATE TABLE TRABAJO (CODIGO NUMBER(5), EMPRESA VARCHAR2(15));

Sequence creation

CONN DIRECTOR/****
CREATE SEQUENCE SEC1 START WITH 500 INCREMENT BY 500 MAXVALUE 10000 CYCLE;

Assignament system’ privileges

GRANT SELECT ANY SEQUENCE TO UNO;
CONN UNO/****

INSERT INTO TRABAJO VALUES(DIRECTOR.SEC1.NEXTVAL,'ABENGOA');

INSERT INTO TRABAJO VALUES(DIRECTOR.SEC1.NEXTVAL,'FYCSA');

SELECT DIRECTOR.SEC1.CURRVAL FROM DUAL;

Assignament object’ privileges

GRANT SELECT ON UNO.TRABAJO TO DOS;

GRANT UPDATE, INSERT, DELETE ON UNO.TRABAJO TO TRES;

Coming back to the standard stage

CONN system/manager
DROP TABLESPACE EMPRESA INCLUDING CONTENTS AND DATAFILES;
DROP USER UNO CASCADE;
DROP USER DOS CASCADE;
DROP USER TRES CASCADE;
DROP SEQUENCE DIRECTOR.SEC1;
DROP USER DIRECTOR CASCADE;
DROP ROLE TRABA;
DROP PROFILE DUENO CASCADE;
DROP PROFILE EMPLEADO;
DROP ROLE GERENTE;