SQL Queries and PL/SQL Functions Examples

SQL Queries

Find series name by episode:

SELECT s.nombre "NOMBRE SERIE"
FROM SERIE s
WHERE s.ID_CODSERIE = (
  SELECT e.SERIE_ID_CODSERIE
  FROM EPISODIO e
  WHERE e.NOMBRE = 'la manzana madura'
);

Find actor name by character:

SELECT a.nombre "NOMBRE ACTOR"
FROM actor a
WHERE a.ID_ACTOR = (
  SELECT p.ACTOR_ID_ACTOR
  FROM personaje p
  WHERE p.NOMBRE = 'Sheldon'
);

Find actors who won a Grammy:

SELECT a.NOMBRE "NOMBRE ACTOR", a.F_NAC "FECHA DE NACIMIENTO"
FROM ACTOR a
INNER JOIN GANA g ON a.ID_ACTOR = g.ACTOR_ID_ACTOR
WHERE g.PREMIO_ID_PREMIO = (
  SELECT p.ID_PREMIO
  FROM premio p
  WHERE p.NOMBRE = 'grammy'
);

PL/SQL Functions

Function to calculate salary with a 20% bonus:

CREATE OR REPLACE FUNCTION F_EJERCICIO1(sueldob NUMBER)
RETURN NUMBER
IS
  monto NUMBER;
  sueldoliq NUMBER;
BEGIN
  monto := (sueldob * 0.2);
  sueldoliq := monto + sueldob;
  RETURN sueldoliq;
END;

SELECT F_EJERCICIO1(52000) AS "Calculo Sueldo" FROM dual;

Function to calculate discounts based on day and product:

CREATE OR REPLACE FUNCTION F_EJERCICIO2 (dia VARCHAR2, prod VARCHAR2, precio NUMBER) RETURN NUMBER
IS
  operacion NUMBER;
BEGIN
  CASE
    WHEN dia = 'lunes' AND (prod = 'fruta' OR prod = 'verdura') THEN operacion := precio * 0.98;
    WHEN dia = 'miercoles' AND prod = 'electrodomesticos' THEN operacion := precio * 0.85;
    WHEN dia = 'sabado' AND prod = 'lacteos' THEN operacion := precio * 0.90;
  END CASE;
  RETURN operacion;
END F_EJERCICIO2;

SELECT F_EJERCICIO2 ('miercoles','electrodomesticos',100) AS "Descuento x Dia" FROM dual;

Function to check if a student is approved based on average grade:

CREATE OR REPLACE FUNCTION F_EJERCICIO3 (n1 NUMBER, n2 NUMBER, n3 NUMBER) RETURN VARCHAR2
IS
  PROMEDIO NUMBER;
BEGIN
  PROMEDIO := (N1 + N2 + N3) / 3;
  IF (PROMEDIO >= 3.95) THEN
    RETURN 'APROBADO';
  ELSE
    RETURN 'REPROBADO';
  END IF;
END F_EJERCICIO3;

SELECT F_EJERCICIO3 (6.9,2.0,3.0) AS "CALCULO 3 NOTAS°" FROM dual;

PL/SQL Cursors

Cursor to find actors born after a specific date:

DECLARE
  CURSOR c_fecha(ifecha DATE)
  IS
    SELECT a.nombre
    FROM actor a
    WHERE a.f_nac >= ifecha
    AND a.f_nac <= SYSDATE;
BEGIN
  FOR r_paso IN c_fecha('11/11/1980') LOOP
    dbms_output.put_line(r_paso.nombre);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('error');
END;

Cursor to find characters with an ‘a’ as the first letter of their nickname:

DECLARE
  CURSOR c_nombresito
  IS
    SELECT p.nombre, p.APODO
    FROM personaje p
    WHERE p.APODO LIKE 'a%';
BEGIN
  FOR r_paso IN c_nombresito LOOP
    dbms_output.put_line(r_paso.nombre || '-' || r_paso.apodo);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('error');
END;

Function returning a cursor with actor and character information:

CREATE OR REPLACE FUNCTION fc_ejercicio(iapodo VARCHAR2)
RETURN SYS_REFCURSOR
AS
  my_cursor SYS_REFCURSOR;
BEGIN
  OPEN my_cursor FOR
    SELECT a.f_nac, a.nombre, p.nombre, p.APODO
    FROM actor a
    INNER JOIN personaje p ON p.ACTOR_ID_ACTOR = a.ID_ACTOR
    WHERE p.APODO = iapodo;
  RETURN my_cursor;
END fc_ejercicio;

SELECT fc_ejercicio('terminator') FROM DUAL;