SQL Fundamentals: Joins, Subqueries, Transactions, and Triggers

Posted on Aug 6, 2024 in Computers

Chapter 8: SQL Fundamentals

Joins

JOIN – brings together data from two or more related tables into one resultant table.

INNER JOIN – also known as equi-join; the joining condition is based on equality between values in the common columns.

OUTER JOIN – rows that do not have matching values in common columns are also included in the result table.

For each customer who placed an order, what are the names of all customers who have placed orders?

SELECT Customer_T.Customer_ID, Order_T.Customer_ID,Customer_T.Customer_Name, Order_T.Order_ID
FROM Customer_T INNER JOIN Order_T ON Customer_T.Customer_ID = Order_T.Customer_ID;

Only 10 rows will be returned from this INNER JOIN.

List the customer name, ID number, and order number for all customers.

SELECT Customer_T.Customer_ID, Customer_Name, Order_ID
FROM  Customer_T LEFT OUTER JOIN Order_T
ON Customer_T.Customer_ID = Order_T.Customer_ID;

Unlike INNER join, the results of this query include customer information even for customers that do not have an order.

Write a SQL query to display customer ID, name, and order date for order number 1006.

SELECT Customer_T.Customer_ID,Customer_Name, Order_Date
FROM Customer_T, Order_T
WHERE Customer_T.Customer_ID=Order_T.Customer_ID
AND  Order_ID=1006;               

Subqueries

Subquery – placing an inner query (SELECT statement) inside an outer query.

Subquery can be nested multiple times.

Inner query can be placed:

  • In a condition of the WHERE clause;
  • As a “table” of the FROM clause;
  • Within the HAVING clause.

Write a SQL query to display customer name, address, city, state and postal code for the customer who made the order with an order ID of 1008.

SELECT Customer_Name, Customer_Address,  Customer_City, Customer_State, Postal_Code
FROM  Customer_T WHERE Customer_t.Customer_ID = (SELECT Customer_ID FROM Order_t WHERE Order_ID=1008);

Noncorrelated subqueries:

  • Do not depend on data from the outer query.
  • Execute once for the entire outer query.

Correlated subqueries:

  • Make use of data from the outer query.
  • Execute once for each row of the outer query.

CASE clause establishes IF-THEN-ELSE logical processing.

Use CASE clause to address “What products are included in Product_Line 1?”

SELECT (CASE WHEN Product_Line_ID = 1
THEN Product_Description
ELSE ‘####’ END) AS Product_Description
FROM Product_T; PRODUCT_DESCRIPTION

Transactions

Transaction is a discrete unit of work that must be completely processed or not processed at all.

May involve multiple updates.

If any update fails, then all other updates must be cancelled.

SQL commands for transactions:

  • BEGIN TRANSACTION/END TRANSACTION – marks boundaries of a transaction.
  • COMMIT – Makes all updates permanent.
  • ROLLBACK – Cancels updates since the last COMMIT.

Triggers

Triggers are a special type of stored procedure and run only in response to an INSERT, UPDATE, or DELETE command.

Triggers have three parts: the event, the condition, and the action.

CREATE TRIGGER Standard_Price_Update AFTER
UPDATE OF Standard_Price ON Product_T
FOR EACH ROW INSERT INTO Price_Updates_T VALUES
(‘Product_Description’, SYSDATE, Standard_Price);

Routines

Routines are program modules (functions or procedures) that execute on demand.

Functions return one value and has only input parameters.

Procedures do not return values and may have both input and output parameters.

CREATE PROCEDURE Product_Line_Sale AS BEGIN
UPDATE Product_T SET Standard_Price = 0.90*Standard_Price
WHERE Standard_Price >= 400;
UPDATE Product_T SET Standard_Price = 0.85*Standard_Price
WHERE Standard_Price <>

END;

Embedded SQL

Including hard-coded SQL statements in a program written in another language such as C or Java.

Dynamic SQL

Ability for an application program to generate SQL code on the fly, as the application is running.

Chapter 9: Database Architecture

Presentation Logic

GUI interface

Formatting and presenting data

Input and Output

Processing Logic

procedures, function, algorithms

data processing

Applying business rules

Data management

Storage Logic

DBMS activities

Data storage and retrieval

Two-Tier Architecture

  • -All processing is done at the PC that requests the service.
  • -Entire files are transferred from the server to the client for processing.

Problems:

  • -Huge amount of data transfer on the network.
  • -Each client must contain full DBMS.
  • -Heavy resource demand on clients.

Three-Tier Architecture

Client is responsible for

  • I/O processing logic.
  • Some business rules logic.

Database server performs all database storage, access, and processing.   

DBMS is only on database server

  • -Performance improves for compiled SQL statements.
  • -Network traffic greatly reduces.
  • -Improved data integrity since it is all processed centrally.
  • -Data integrity improves as applications access the same stored procedures.
  • Scalability – more scalable than two-tier architecture.
  • Technological flexibility – easy to change DBMS engines.
  • Long-term cost reduction.
  • Better match of systems to business needs.
  • Improved customer service – multiple interfaces on different clients.
  • Competitive advantage.
  • Reduced risk – implement and combine small modules of code with vendors’.