SQL

SQL

1. INDICES (INDICES)

   • SINTAXIS "CREATE INDEX IDX_CUSTOMER_LAST_NAME ON Customer (Last_Name);

2. VIEWS (VISTAS)

   • CREATE VIEW view_dept_201 AS SELECT emp_id, name, hire_date FROM gdb.employees WHERE department = 201;

3. STORED PORCEDURES

   • CREATE [ OR REPLACE ] PROCEDURE procedure_name ([ [ IN | OUT | IN OUT ] parameter_name { parameter_type | ARRAY OF parameter_type }, ... ]) [ DECLARE variable_declaration;...[;] ] BEGIN procedure_body_statement;...[;] END

4. TRIGGERS

   • Creo que está mal y no lo puedo comprobar, a ver si alguien sabe. Es un trigger que se dispara después de que se actualice la Localidad (loc) de la tabla departamento (depart), una vez actualizada se coge a los empleados de la tabla emple que pertenecen a ese departamento que ha cambiado de localidad y se les sube un 20% el sueldo. CREATE OR REPLACE TRIGGER traslado AFTER UPDATE OF Loc ON DEPART FOR EACH ROW DECLARE vdep number; BEGIN SELECT dept_no INTO vdep FROM depart; UPDATE emple SET SALARIO=salario+salario*20/100 WHERE vdep=dept_no; END;

5. INNER JOIN

   • SELECT * FROM tabla1 INNER JOIN tabla2 WHERE tabla1.columna1 = tabla2.columna1

6. LEFT JOIN

   • SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

7. CROSS JOIN

   • select c.nombre as 'plato principal', p.nombre as 'postre' from comidas as c cross join postres as p;

8. FULL JOIN

   • SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;