SQL: Structured Query Language

SQL: Structured Query Language

Overview

SQL Definition

• Pronunciation:

  • "S-Q-L" or "Sequel"

• Meaning: Structured Query Language

• Purpose: Language used for creating, manipulating, and maintaining a relational database.

• Standard:

  • Official standard accepted by ANSI and ISO.

  • De facto standard in the database world.

SQL Data Sub-Language Types

• SQL encompasses three types of data sub-languages used in database management systems (DBMS):

  1. Data Definition Language (DDL):

  • Commands that define a database structure, including:

    • Creating tables

    • Altering tables

    • Dropping tables

    • Establishing constraints.

  • Key statements include: CREATE, ALTER.

  1. Data Control Language (DCL):

  • Commands that control database access, including:

    • Administering privileges

    • Committing data.

  • Key statements include: GRANT, REVOKE.

  1. Data Manipulation Language (DML):

  • Commands for maintaining and querying a database.

  • Key statements include: SELECT, INSERT, DELETE, UPDATE.

History of SQL

• Origin:

  • SEQUEL: Structured English Query Language

  • A 23-page research report in 1974 from IBM's San Jose Research Labs.

  • Lead to the System R prototype.

• SQL Launch:

  • SQL rebranded from SEQUEL.

  • Adopted by IBM's DB2 in 1983.

• Standardization:

  • Adopted as ANSI standard in 1986 and ISO in 1987.

  • Underwent many revisions; the latest version can be referenced on https://en.wikipedia.org/wiki/ISO/IEC_9075.

Relationship with DBMS Products

• Most relational DBMSs support a subset of the SQL standard.

• Differences in SQL syntax implementations across various DBMS platforms require rewriting SQL queries and commands when porting across platforms.

SQL Fundamentals Outline

• Components:

  • Single table operations.

  • Filtering (selection) of output rows.

  • Sorting results.

  • Queries involving multiple tables and joins.

  • Aggregation.

Retrieving Table Data: SELECT & FROM

• Basic SELECT Statement Syntax:

  • SELECT column_list FROM table_list;

• Requirement:

  • Every SQL query must contain at least a SELECT and a FROM clause.

• Example to find vendor names:

  • SELECT VName FROM VENDORS; (This query is termed a projection query.)

• Query Termination:

  • End SQL queries in Oracle SQL Developer with a semicolon (;).

• Table Names:

  • Table names should be prefixed with the owner's name unless the user owns the table.

  • Example: SELECT * FROM MIS331CLASS.VENDORS;

Handling Duplicates in Selection Queries

• Using DISTINCT to eliminate duplicates:

  • Example to list unique cities where vendors are located:

  • SELECT DISTINCT City FROM VENDORS;

  • Example to project distinct vendor names and cities:

  • SELECT DISTINCT VName, City FROM VENDORS;

• Using Wildcards in SELECT Statements:

  • SELECT * FROM VENDORS; to retrieve entire table.

  • You can also use DISTINCT with * to eliminate duplicates: SELECT DISTINCT * FROM VENDORS;.

Aliases for Columns and Tables

• Renaming Columns in SELECT Clause:

  • A column can be renamed in the SELECT clause using the AS keyword:

  • Example: SELECT DISTINCT City AS "Towns" FROM VENDORS;

• Using Aliases for Tables in FROM Clause:

  • Tables can be given aliases:

  • Example: SELECT V.City AS "Towns" FROM VENDORS V;

• NOTE: Once an alias is set, it must be used consistently in the SQL statement.

WHERE Clause for Conditional Row Selection

• Using WHERE Clause:

  • To choose rows conditional on specified conditions. Conditions can include comparisons to constant values, column values, or results of a SELECT query:

  • Example to find vendors living in Tucson:
    SELECT VName, City FROM VENDORS WHERE City = 'Tucson';

• Use of DISTINCT with WHERE:

  • If you add DISTINCT to the previous query, it eliminates duplicate rows based on the filter conditions.

Core SQL Clauses Introduction

• Clauses Introduced to Date Include:

  1. SELECT: Outputs a column list.

  2. FROM: Determines the origin of data.

  3. WHERE: Specifies conditions for rows in output.

  4. GROUP BY: Breaks tables into groups of rows for aggregation.

  5. HAVING: Chooses groups to include in output.

  6. ORDER BY: Sorts output rows by specified criteria.

Logical Operators in WHERE Clause

• AND, OR Logical Operators Usage in WHERE Clause for Compound Conditions:

  • Example: List products priced over $40 or under $10:
    SELECT PName, ListPrice FROM PRODUCTS WHERE ListPrice < 10 OR ListPrice > 40;

• Using NOT Operator:

  • Negates conditions in the WHERE clause:

  • Example: List products not priced over $40 or under $10:
    SELECT PName, ListPrice FROM PRODUCTS WHERE NOT (ListPrice < 10 OR ListPrice > 40);

Ordering Results with ORDER BY Clause

• Using ORDER BY to Sort Results:

  • To sort results based on a specific column:

  • Example:
    SELECT PName, ListPrice FROM PRODUCTS WHERE ListPrice >= 25 ORDER BY ListPrice;

  • Descending Order:

  • Change to descending order using DESC keyword:
    ORDER BY ListPrice DESC;

• Sorting Results by Multiple Columns:

  • Example to sort by category and price:
    SELECT Kind AS category, PName, ListPrice FROM PRODUCTS ORDER BY category, ListPrice DESC, PName;

Multiple Tables and Joins

• FROM Clause with Multiple Tables:

  • A JOIN is required for correlating data across different tables.

  • Joining Products with Cansupply:

  • Example of a join query:
    SELECT PName FROM PRODUCTS JOIN CANSUPPLY ON PRODUCTS.ProdID = CANSUPPLY.ProdID WHERE VCode='V70';

• Cartesian Product Explained:

  • A query involving two tables without specifying a join can generate a Cartesian product:

  • Results present all combinations of rows.

  • Example:
    SELECT PName, VCode FROM PRODUCTS, CANSUPPLY;

Aggregation Functions and GROUP BY Clause

• Using Aggregates:

  • Aggregates perform calculations on a set of values in a column:

  • Keywords include:

    • SUM: Sum of values.

    • COUNT: Number of values.

    • AVG: Average.

    • MAX: Maximum.

    • MIN: Minimum.

• Using GROUP BY Keyword:

  • To categorize results for aggregate functions:

  • Example to count products per kind:
    SELECT Kind, COUNT(*) FROM PRODUCTS GROUP BY Kind;

• **HAVING Clause Usage: **

  • HAVING can filter the results of grouped data:

  • Example:
    SELECT Kind, AVG(SupplyPrice) FROM PRODUCTS p JOIN CANSUPPLY c ON p.ProdID = c.ProdID GROUP BY Kind HAVING AVG(SupplyPrice) > 50;

Example SQL Statements and Practices

• **Practice SQL Questions: **

  1. List vendors supplying less than 4 products:
     SELECT VCode, COUNT(*) FROM CANSUPPLY GROUP BY VCode HAVING COUNT(*) < 4;

  2. For each city in Arizona (AZ):
     SELECT City, COUNT(*) FROM VENDORS WHERE State='AZ' GROUP BY City;

Table Structures Familiarity

• Table Definitions:

  • PRODUCTS: (ProdID, PName, Kind, ListPrice)

  • VENDORS: (VCode, VName, City, State, VSince)

  • CANSUPPLY: (ProdID, VCode, SupplyPrice)