Advanced SQL

Objectives
• Use Advanced SQL JOIN Syntax in PostgreSQL
• Understand and Use Subqueries and Correlated Subqueries
• Manipulate Data Using PostgreSQL SQL Functions
• Apply Relational Set Operators in PostgreSQL
• Create and Use Views and Updatable Views
• Create and Use Triggers and Stored Procedures
• Create Embedded SQL
JOIN
JOIN
• A JOIN in SQL lets you combine rows from two or more tables
based on a related column between them.
• Basic Types of JOINs in PostgreSQL
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• CROSS JOIN
INNER JOIN
An INNER JOIN retrieves rows from two or more tables based on a
related column between them. Rows are included in the result only
when there is a match in both tables.

SELECT COLUMN_NAMES
FROM TABLE1 AS T1
INNER JOIN TABLE2 AS T2
ON T1.COLUMN_NAME = T2.COLUMN_NAME;
INNER JOIN
SELECT C.CUS_CODE, C.CUS_LNAME, C.CUS_FNAME,
I.INV_NUMBER, I.INV_DATE
FROM CUSTOMER AS C
INNER JOIN INVOICE AS I
ON C.CUS_CODE = I.CUS_CODE;

**w/o JOIN keyword

SELECT C.CUS_LNAME, C.CUS_FNAME,
I.INV_NUMBER, I.INV_DATE
FROM CUSTOMER AS C, INVOICE AS I
WHERE C.CUS_CODE = I.CUS_CODE

The query retrieves the customer’s last name, first name, invoice number,
and invoice date where there is a matching CUS_CODE in both the Customer and Invoice tables.
 INNER JOIN
SELECT V.V_CODE, V.V_NAME, P.P_CODE, P_DESCRIPT, P_PRICE
FROM VENDOR AS V
INNER JOIN PRODUCT AS P
ON V.V_CODE = P.V_CODE;
 INNER JOIN
SELECT P.P_CODE, P.P_DESCRIPT, L.LINE_NUMBER, L.INV_NUMBER
FROM PRODUCT AS P
INNER JOIN LINE AS L
ON P.P_CODE = L.P_CODE;
INNER JOIN
SELECT C.CUS_LNAME, C.CUS_FNAME, I.INV_NUMBER, L.P_CODE
FROM CUSTOMER AS C
INNER JOIN INVOICE AS I ON C.CUS_CODE = I.CUS_CODE
INNER JOIN LINE AS L ON I.INV_NUMBER = L.INV_NUMBER;
INNER JOIN with Filtering
SELECT L.INV_NUMBER, L.P_CODE, P.P_DESCRIPT, L.LINE_UNITS
FROM LINE AS L
INNER JOIN PRODUCT AS P ON L.P_CODE = P.P_CODE
WHERE L.LINE_UNITS > 3.0;
INNER JOIN with Filtering
SELECT V.V_CODE, V_NAME, P.P_CODE, P.P_DESCRIPT, P_PRICE
FROM VENDOR AS V
INNER JOIN PRODUCT AS P ON P.V_CODE = V.V_CODE
WHERE P.P_PRICE > 10;
INNER JOIN with Filtering
SELECT V.V_NAME,COUNT(P.P_CODE) AS PRODUCT_COUNT
FROM VENDOR AS V
INNER JOIN PRODUCT AS P ON V.V_CODE = P.V_CODE
GROUP BY V.V_NAME;
 INNER JOIN with Filtering
SELECT V.V_NAME, COUNT(P.P_CODE) AS PRODUCT_COUNT, SUM(L.LINE_UNITS * L.LINE_PRICE) AS
TOTAL_SALES
FROM VENDOR AS V
INNER JOIN PRODUCT AS P ON V.V_CODE = P.V_CODE
INNER JOIN LINE AS L ON P.P_CODE = L.P_CODE
GROUP BY V.V_NAME
HAVING COUNT(P.P_CODE) > 2;
 LEFT JOIN
A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left
table and the matching rows from the right table. If no match exists,
the result is NULL on the side of the right table. Keep everything on
the left

SELECT COLUMN1, COLUMN2, ...

FROM LEFT_TABLE AS L
LEFT JOIN RIGHT_TABLE AS R ON [Link] = [Link];
LEFT JOIN
SELECT C.CUS_LNAME, C.CUS_FNAME, I.INV_NUMBER, I.INV_DATE
FROM CUSTOMER AS C
LEFT JOIN INVOICE AS I ON C.CUS_CODE = I.CUS_CODE;
LEFT JOIN with Filtering
SELECT C.CUS_LNAME, C.CUS_FNAME, I.INV_NUMBER
FROM CUSTOMER AS C
LEFT JOIN INVOICE AS I ON C.CUS_CODE = I.CUS_CODE
WHERE I.INV_NUMBER IS NULL;
LEFT JOIN
SELECT V.V_CODE, V.V_NAME, P.P_CODE, P_DESCRIPT, P.P_PRICE
FROM VENDOR AS V
LEFT JOIN PRODUCT AS P ON V.V_CODE = P.V_CODE;
LEFT JOIN
SELECT P.P_CODE, P.P_DESCRIPT, L.LINE_UNITS, L.LINE_PRICE
FROM PRODUCT AS P
LEFT JOIN LINE AS L ON P.P_CODE = L.P_CODE;
 LEFT JOIN
SELECT V.V_CODE, V.V_NAME, L.P_CODE, P_DESCRIPT, P.P_PRICE, L.LINE_UNITS,
L.LINE_PRICE
FROM VENDOR AS V
LEFT JOIN PRODUCT AS P ON V.V_CODE = P.V_CODE
LEFT JOIN LINE AS L ON P.P_CODE = L.P_CODE;
 RIGHT JOIN
returns all rows from the right table, and the matching rows from the
left table. If there's no match from the left table, the result is NULL
for the left side. Keep everything on the right.

SELECT COLUMN1, COLUMN2, ...

FROM RIGHT_TABLE AS R
RIGHT JOIN LEFT_TABLE AS L ON [Link] = [Link];
RIGHT JOIN
SELECT C.CUS_LNAME, C.CUS_FNAME, I.INV_NUMBER,
I.INV_DATE
FROM CUSTOMER AS C
RIGHT JOIN INVOICE AS I ON C.CUS_CODE = I.CUS_CODE;
RIGHT JOIN
SELECT V.V_CODE, V.V_NAME, P.P_CODE, P_DESCRIPT, P.P_PRICE
FROM VENDOR AS V
RIGHT JOIN PRODUCT AS P ON V.V_CODE = P.V_CODE;
 FULL OUTER JOIN
A FULL OUTER JOIN (sometimes just called FULL JOIN) is a type of
SQL join that combines the results of both a LEFT JOIN and a RIGHT
JOIN. It returns all rows from both participating tables.

SELECT column1, column2, ...

FROM table1
FULL OUTER JOIN table2 ON table1.join_column = table2.join_column;
FULL OUTER JOIN
SELECT V.V_CODE, V.V_NAME, P.P_CODE, P.P_DESCRIPT
FROM PRODUCT P
FULL OUTER JOIN VENDOR V ON P.V_CODE = V.V_CODE;
FULL OUTER JOIN
SELECT V.V_CODE, V.V_NAME, P.P_CODE, P.P_DESCRIPT, L.LINE_UNITS,
L.LINE_PRICE
FROM VENDOR V
FULL OUTER JOIN PRODUCT P ON V.V_CODE = P.V_CODE
FULL OUTER JOIN LINE L ON P.P_CODE = L.P_CODE;
CROSS JOIN
• A CROSS JOIN is a type of join that produces the Cartesian product of the rows from the
joined tables. This means that every row from the first table is combined with every row from
the second table.
• No ON clause: Unlike INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL OUTER JOIN, a CROSS
JOIN does not have an ON clause to specify a join condition. It simply combines all possible
pairs of rows
• Result Size: The number of rows in the result of a CROSS JOIN is the product of the number
of rows in each of the joined tables. For example, if table A has 3 rows and table B has 4 rows,
their CROSS JOIN will result in 3 * 4 = 12 rows
SELECT column1_table1, column2_table1, column1_table2,
column2_table2, ...
FROM table1
CROSS JOIN table2;
CROSS JOIN

SELECT C.CUS_CODE, C.CUS_LNAME,

C.CUS_FNAME, I.INV_NUMBER, I.CUS_CODE
FROM CUSTOMER C CROSS JOIN INVOICE I;
Join type Purpose
INNER JOIN Shows only matching rows between tables
Shows all rows from the left table, plus matching
LEFT JOIN
rows from the right table
Shows all rows from the right table, plus matching
RIGHT JOIN
rows from the left table
Shows all rows from both tables, matched when
FULL JOIN
possible
CROSS JOIN Shows every possible combination of rows
RELATIONAL SET
OPERATORS
RELATIONAL SET OPERATORS
• The relational set operators in SQL (including PostgreSQL) allow
you to combine and manipulate results from multiple queries,
working on whole result sets rather than just individual rows.
• UNION, UNION ALL, INTERSECT, EXCEPT (or MINUS)
RELATIONAL SET OPERATORS
• The relational set operators in SQL (including PostgreSQL) allow
you to combine and manipulate results from multiple queries,
working on whole result sets rather than just individual rows.
• UNION, UNION ALL, INTERSECT, EXCEPT (or MINUS)
UNION
• The UNION operator in SQL is used to combine the result sets of
two or more SELECT queries. It combines the rows of the result
sets, removing duplicates by default, and returns a single result
set.
• Removes Duplicates: The UNION operator eliminates duplicate
rows in the result set. If you want to include duplicates, you can
use UNION ALL (which we will cover later).
• Column Consistency: All SELECT statements involved in the
UNION must have the same number of columns, and the
corresponding columns must have compatible data types.
UNION
• The UNION operator in SQL is used to combine the result sets of two or
more SELECT queries. It combines the rows of the result sets, removing
duplicates by default, and returns a single result set.
• Removes Duplicates: The UNION operator eliminates duplicate rows in
the result set. If you want to include duplicates, you can use UNION
ALL (which we will cover later).
• Column Consistency: All SELECT statements involved in the UNION
must have the same number of columns, and the corresponding
columns must have compatible data types.

SELECT column1, column2, ...

FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
UNION (important rules)
• Number of columns must be the same in all queries.
• Data types must be compatible (e.g., VARCHAR can match TEXT;
INTEGER can match NUMERIC with conversion).
• ORDER BY must appear after the last SELECT.
 SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
CUS_AREACODE,
UNION CUS_PHONE
FROM CUSTOMER
UNION
SELECT CUS_LNAME,
SELECT CUS_LNAME, CUS_FNAME, CUS_FNAME,
CUS_INITIAL, CUS_INITIAL,
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
CUS_AREACODE, CUS_AREACODE, CUS_AREACODE,
CUS_PHONE CUS_PHONE CUS_PHONE
FROM CUSTOMER FROM CUSTOMER_2; FROM CUSTOMER_2;
UNION ALL
• UNION ALL is a SQL set operator that combines the result sets of
two or more SELECT queries into a single result set.
• It includes all rows from all queries
• It does NOT remove duplicates — every row from every SELECT is
returned.
• It is faster and uses less memory than UNION because
PostgreSQL doesn't sort and remove duplicates.
SELECT column1, column2, ...
FROM table1
WHERE condition1
UNION ALL
SELECT column1, column2, ...
FROM table2
WHERE condition2
 SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,

UNION ALL CUS_AREACODE,

CUS_PHONE
FROM CUSTOMER
UNION ALL
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
SELECT SELECT CUS_LNAME,
CUS_LNAME, CUS_FNAME, CUS_INITIAL,CUS_FNAME, CUS_INITIAL,
CUS_AREACODE, CUS_AREACODE, CUS_AREACODE,
CUS_PHONE CUS_PHONE CUS_PHONE
FROM CUSTOMER FROM CUSTOMER_2; FROM CUSTOMER_2;
 INTERSECT
• INTERSECT is a SQL set operator that returns only the rows that are
common to two or more SELECT queries.
• It shows only the rows that exist in both (or all) query results.
• It removes duplicates automatically (just like UNION does).
• Only rows that appear exactly (same values) in both queries are returned.
SELECT column1, column2, ...
FROM tableA
WHERE conditionA

INTERSECT

SELECT column1, column2, ...

FROM tableB
WHERE conditionB

ORDER BY column;
 SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,

INTERSECT CUS_AREACODE,
CUS_PHONE
FROM CUSTOMER
INTERSECT
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
SELECT SELECT CUS_LNAME,
CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_FNAME, CUS_INITIAL,
CUS_AREACODE, CUS_AREACODE, CUS_AREACODE,
CUS_PHONE CUS_PHONE CUS_PHONE
FROM CUSTOMER FROM CUSTOMER_2; FROM CUSTOMER_2;
EXCEPT
• combines rows from two queries and returns only the rows that appear in the first
set but not in the second.
• It is basically "Query 1 - Query 2" (subtract the second from the first)
• Duplicates are automatically removed in the result (just like UNION and
INTERSECT).
• Only unique rows that exist in the first set but not in the second are shown.

SELECT column1, column2, ...

FROM table1
WHERE condition1
EXCEPT
SELECT column1, column2, ...
FROM table2
WHERE condition2;
 SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
EXCEPT CUS_AREACODE,
CUS_PHONE
FROM CUSTOMER
EXCEPT
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
CUS_AREACODE, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE,
CUS_AREACODE,
CUS_PHONE CUS_PHONE CUS_PHONE
FROM CUSTOMER FROM CUSTOMER_2; FROM CUSTOMER_2;
FUNCTION
FUNCTION
• A function in PostgreSQL is a stored program written in SQL or
procedural languages (like PL/pgSQL) that takes input
parameters, processes data, and returns a result.
• Use functions when you need to return a value or perform
computations that are used in queries.
• Functions are good for calculations, data formatting, or
manipulating values.
Key Characteristics
Feature Description
Reusable Can be called multiple times with different parameters
Returns a value Always returns a result (scalar, record, or table)
Used in SQL Can be used in SELECT, WHERE, JOIN, etc.
No transaction control Cannot use COMMIT or ROLLBACK inside
Supports logic Can include IF, LOOP, CASE, etc., especially with PL/pgSQL
Basic Syntax
CREATE FUNCTION function_name(param1 TYPE, param2 TYPE)
RETURNS return_type
LANGUAGE plpgsql
AS $$
BEGIN
-- logic here
RETURN some_value;
END;
$$;
Function that adds two numbers
CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
RETURN a + b;
END;
$$;

SELECT add_numbers(5, 10);

Limitations
• Cannot commit or roll back transactions.
• More limited than procedures when it comes to side effects (e.g.,
bulk updates).
• Should not be used when you don't need a return value — use a
procedure instead.
Types of Returns
• Scalar: e.g., INTEGER, TEXT
• Composite/Record: like a row with multiple columns
• Table: returns a set of rows, like a mini query
CALCULATION OF GROSS PAY
CREATE OR REPLACE FUNCTION get_grosspay(hours_worked NUMERIC, hourly_rate NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
gross_pay NUMERIC;
BEGIN
gross_pay := hours_worked * hourly_rate;

RETURN gross_pay;
END;
$$;

SELECT get_grosspay(50,100);
Return Full Name of a Customer
CREATE OR REPLACE FUNCTION get_customer_full_name(p_cus_code INT)
RETURNS TEXT AS $$
DECLARE
full_name TEXT;
BEGIN
SELECT CUS_FNAME || ' ' || CUS_LNAME
INTO full_name
FROM CUSTOMER
WHERE CUS_CODE = p_cus_code;

RETURN full_name;
END;
$$ LANGUAGE plpgsql;

SELECT get_customer_full_name(10010);
Calculate Total Invoice Amount
CREATE OR REPLACE FUNCTION get_invoice_total(p_inv_number INT)
RETURNS NUMERIC(10,2) AS $$
DECLARE
total NUMERIC(10,2);
BEGIN
SELECT SUM(LINE_UNITS * LINE_PRICE)
INTO total
FROM LINE
WHERE INV_NUMBER = p_inv_number;

RETURN COALESCE(total, 0.00);

END;
$$ LANGUAGE plpgsql;

SELECT get_invoice_total(1003);
List All Products by a Vendor
CREATE OR REPLACE FUNCTION get_products_by_vendor(p_v_code
INT)
RETURNS TABLE (p_code VARCHAR, p_descript VARCHAR) AS $$
BEGIN
RETURN QUERY
SELECT p.P_CODE, p.P_DESCRIPT
FROM PRODUCT AS p
WHERE V_CODE = p_v_code;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_products_by_vendor(25595);

Check If Customer Has Any Invoices
CREATE OR REPLACE FUNCTION customer_has_invoice(p_cus_code INT)
RETURNS BOOLEAN AS $$
DECLARE
has_invoice BOOLEAN;
BEGIN
SELECT EXISTS (
SELECT 1
FROM INVOICE
WHERE CUS_CODE = p_cus_code
) INTO has_invoice;

RETURN has_invoice;
END;
$$ LANGUAGE plpgsql;

SELECT customer_has_invoice(10010);
Stored Procedure
Stored Procedure
• A stored procedure is a set of SQL statements that are stored and
executed within a database.
• It is precompiled and saved, allowing you to execute the same set
of operations multiple times without needing to retype or
recompile the logic each time.
• They are primarily used for tasks like INSERT, UPDATE, DELETE,
and complex queries, as well as for implementing business logic
directly inside the database.
Key Characteristics
Feature Description
Can be executed multiple times, but only as a procedure call, not as part of a SQL
Reusable
query.

Typically does not return a value (though it can return values via OUT parameters or
No return value
RETURN QUERY).

Cannot be used directly within SELECT, WHERE, JOIN, etc., but can execute SQL
Used in SQL
commands within the procedure body.

Can manage transactions by using COMMIT, ROLLBACK, or SAVEPOINT within the

Transaction control
procedure.

Supports logic Can include control structures like IF, LOOP, CASE, etc., especially with PL/pgSQL.

Typically modifies data (e.g., UPDATE, INSERT, DELETE) and may have side effects
Side effects
on the database state.
Basic Syntax
CREATE OR REPLACE PROCEDURE procedure_name(param1 TYPE, param2
TYPE)
LANGUAGE plpgsql
AS $$
BEGIN CREATE OR REPLACE PROCEDURE update_product_price(p_code
-- logic here VARCHAR, new_price NUMERIC)
LANGUAGE
-- you can use SQL statements like INSERT, plpgsql
UPDATE, DELETE
AS $$ ROLLBACK)
-- transaction control is allowed (COMMIT,
END; BEGIN
$$; UPDATE product
SET p_price = new_price
WHERE p_code = p_code;
END;
$$;

CALL update_product_price('13-Q2/P2', 17.99);

Insert a new vendor
CREATE OR REPLACE PROCEDURE set_vendor(
p_v_code INT,
p_v_name VARCHAR,
p_v_contact VARCHAR,
p_v_areacode CHAR(3),
p_v_phone CHAR(8),
p_v_state CHAR(2),
p_v_order CHAR(1)
)
LANGUAGE plpgsql
AS $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM vendor WHERE v_code = p_v_code) THEN
INSERT INTO vendor
VALUES (p_v_code, p_v_name, p_v_contact, p_v_areacode, p_v_phone, p_v_state, p_v_order);
RAISE NOTICE 'Vendor % inserted.', p_v_name;
ELSE
RAISE NOTICE 'Vendor % already exists.', p_v_name;
END IF;
END; CALL set_vendor(400, 'ABC Tools', 'Maria Rivera', '999',
$$; '9999999', 'TX', 'Y');
Delete a vendor
CREATE OR REPLACE PROCEDURE remove_vendor(p_v_code INT)
LANGUAGE plpgsql
AS $$
BEGIN
IF EXISTS (SELECT 1 FROM vendor WHERE v_code = p_v_code) THEN
DELETE FROM vendor WHERE v_code = p_v_code;
RAISE NOTICE 'Vendor with code % removed.', p_v_code;
ELSE
RAISE NOTICE 'Vendor with code % not found.', p_v_code;
END IF;
END;
$$;

CALL remove_vendor(400);
Insert a new customer
CREATE OR REPLACE PROCEDURE set_customer(
p_cus_code INT,
p_lname VARCHAR,
p_fname VARCHAR,
p_initial CHAR(1),
p_areacode CHAR(3),
p_phone CHAR(8),
p_balance NUMERIC(9,2)
)
LANGUAGE plpgsql
AS $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM customer WHERE cus_code = p_cus_code) THEN
INSERT INTO customer
VALUES (p_cus_code, p_lname, p_fname, p_initial, p_areacode, p_phone, p_balance);
RAISE NOTICE 'Customer % % inserted.', p_fname, p_lname;
ELSE
RAISE NOTICE 'Customer % % already exists.', p_fname, p_lname;
END IF;
END;
$$; CALL set_customer(800, 'Garcia', 'Luis', 'M', '615',
'1234567', 120.00);
Update Customer Balance
Transfer Balance Between Customers
Delete Customer if No Invoices Exist
CREATE OR REPLACE PROCEDURE delete_customer_if_no_invoices(p_cus_code INT)
LANGUAGE plpgsql
AS $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM invoice WHERE cus_code = p_cus_code) THEN
DELETE FROM customer WHERE cus_code = p_cus_code;
RAISE NOTICE 'Customer % deleted as they have no invoices.', p_cus_code;
ELSE
RAISE NOTICE 'Customer % has invoices and cannot be deleted.', p_cus_code;
END IF;
END;
$$;

CALL delete_customer_if_no_invoices(800);
INSERT, UPDATE, and DELETE operations