SQL Commands for Database Management and Querying

Creating Tables and Constraints

SQL> CREATE TABLE ped (nif NUMBER(9) CONSTRAINT ped_tda_fk REFERENCES tda(nif), art VARCHAR2(20), c_fab NUMBER(3) CONSTRAINT ped_fab_fk REFERENCES fab(c_fab) ON DELETE CASCADE, pes NUMBER(3) CONSTRAINT ped_>0_ch CHECK(peso>0), cat CHAR(7) CONSTRAINT ped_cat_val_ch CHECK(cat='a' OR cat='b'), fec DATE, CONSTRAINT ped_pk PRIMARY KEY(nif, art, c_fab, pes, cat, fec), CONSTRAINT ped_art_fk FOREIGN KEY(art, pes, cat, c_fab) REFERENCES arts(art, pes, cat, c_fab) ON DELETE CASCADE;

Creating Views and Altering Tables

SQL> CREATE TABLE vent AS SELECT * FROM ped;
SQL> ALTER TABLE vent RENAME COLUMN uni TO uni_v;
SQL> ALTER TABLE vent ADD CONSTRAINT vent_>_0 CHECK(peso>0);
SQL> ALTER TABLE vent ADD CONSTRAINT vent_pk PRIMARY KEY(nif, art, c_fab, pes, cat, fec);
SQL> ALTER TABLE vent ADD CONSTRAINT vent_fab_fk FOREIGN KEY(c_fab) REFERENCES fab(c_fab) ON DELETE CASCADE;
SQL> ALTER TABLE ped MODIFY uni NUMBER(6);
SQL> CREATE VIEW conss (centro, con) AS SELECT nom, ape FROM pers, cent WHERE persl.cod_c=cent.cod_c AND fun='conserje';
SQL> CREATE TABLE t_asi (cod_asi NUMBER(2), n_asi VARCHAR2(20));
SQL> ALTER TABLE t_asi ADD CONSTRAINT t_asi_pk PRIMARY KEY(cod_asi);
SQL> SELECT constraint_name FROM user_constraints WHERE table_name='t_asi' OR table_name='prof';
SQL> DROP TABLE t_asi CASCADE CONSTRAINTS;
SQL> ALTER TABLE prof DROP COLUMN c_asi;

Querying Data with Subqueries and Joins

SQL> SELECT ape FROM emp WHERE (ofi, sal) = (SELECT ofi, sal FROM emp WHERE ape='gil');
SQL> SELECT * FROM dep, emp WHERE dep.dept_n=emp.dept_n AND loc IN('mad','bar') ... ORDER BY apellido ASC;
SQL> UPDATE emp SET com=0 WHERE com IS NULL;
SQL> ALTER TABLE emp MODIFY com CONSTRAINT emp_com_nn NOT NULL;
SQL> ALTER TABLE emp ADD CONSTRAINT emp_ape_unq UNIQUE(ape);
SQL> ALTER TABLE ej ADD sex CHAR(1);
SQL> ALTER TABLE ej MODIFY nom VARCHAR2(50);
SQL> CREATE VIEW dpt_30 (ap, ofi) AS SELECT ape, ofi FROM dp, emp WHERE dp.dpt_n=emp.dpt_n AND dp.dpt_n=30;

Nested Queries and Set Operations

SQL> SELECT ape, ofi FROM emp WHERE dept_n=2 AND ofi IN (SELECT ofi FROM emp WHERE dept_n=(SELECT dept_n FROM dept WHERE dnom='ventas'));
SQL> SELECT ofi, sal, fec_alt FROM emp WHERE ofi=(SELECT ofi FROM emp WHERE ape='j') AND sal>(SELECT sal FROM emp WHERE ape='f');
SQL> SELECT ape, sal, d.dept_n FROM emp, dep d WHERE emp.dept_n=d.dept_n AND sal>(SELECT AVG(sal) FROM emp WHERE d.dept_n=emp.dept_n);
SQL> SELECT dep.dept_n, dnom FROM dep, emp WHERE dep.dept_n=emp.dept_n AND ofi='emple' GROUP BY dep.dept_n, dnom HAVING COUNT(emp_n)=(SELECT MAX(COUNT(emp_n)) FROM emple WHERE ofi='emple' GROUP BY dept_no);
SQL> SELECT nom FROM alu INTERSECT SELECT nom FROM ant INTERSECT SELECT nom FROM nue;
SQL> SELECT alu.nom FROM alu, ant, nue MINUS SELECT alu.nom FROM alu, ant, nue WHERE alu.nom=ant.nom OR alu.nom=nue.nom;

Data Manipulation: Insert, Update and Delete

SQL> INSERT INTO emp (emp_n, ape, dept_n) SELECT 1, 'garicia', dept_n FROM emp GROUP BY dept_n HAVING COUNT(emp_n)=(SELECT MAX(COUNT(emp_n)) FROM emp GROUP BY dept_n);
SQL> UPDATE cen SET n_plaza=(SELECT n_plaza FROM cen WHERE c_cen=5) WHERE c_cen=1;
SQL> INSERT INTO prof (esp, dni, ape, c_cen) SELECT 'idiom', 1, 'clara', c_cen FROM per WHERE fun='adm' GROUP BY c_cen HAVING COUNT(dni)=1;
SQL> DELETE FROM per WHERE c_cen IN (SELECT centro.c_cen FROM centro, prof WHERE n_plazas<3 AND centro.c_cen=prof.c_cen(+) GROUP BY centro.c_cen HAVING COUNT(dni)<2);
SQL> DELETE FROM prof WHERE dni NOT IN (SELECT dni FROM pers);
SQL> DELETE FROM dept WHERE dept_no=(SELECT dept_no FROM emple GROUP BY dept_no HAVING COUNT(emp_no)>4);
... WHERE sal NOT BETWEEN 1 AND 2;
... NOT sal BETWEEN 1 AND 2;
... AND dept_n IN(1,2);

Distinct Selections with Nested Conditions

SQL> SELECT DISTINCT ape FROM nota, alu WHERE nota=(SELECT nota FROM nota, asi WHERE dni=(SELECT dni FROM alu WHERE ape='maría' AND n_asi='fol' AND asi.cod=nota.cod) AND alu.dni=notas.dni;