Comprehensive SQL Lecture Notes

SQL OVERVIEW

  • Structured Query Language (SQL) = standard language for Relational DataBase Management Systems (RDBMSs)
    • Used to create, manipulate, and control relational databases
    • Runs on mainframes, servers, PCs, mobile and cloud platforms
    • May be invisible to end-users (hidden behind GUIs, forms, or QBE interfaces)
  • Historical milestones
    • 1970 : E. F. Codd proposes the relational model
    • 1974{-}1979 : IBM System R proves relational feasibility ➜ internal query language “Sequel” ⇒ SQL
    • 1979 : Oracle ships the first commercial DBMS supporting SQL
    • 1981 : IBM SQL/DS released
    • 1986 : ANSI / ISO ratify first standard – SQL/86 (a.k.a. SQL-1)
    • Later revisions: SQL/89, SQL/92, SQL:1999, SQL:2003, SQL:2008, SQL:2011, SQL:2016

MOTIVATION FOR A FORMAL STANDARD

  • Purposes
    • Define common syntax & semantics
    • Specify minimum/complete feature sets for portability
    • Provide basis for enhancements
  • Advantages
    • Lower training costs (one language)
    • Developer productivity & skill longevity
    • Application portability/longevity
    • Reduced vendor lock-in ➜ healthy competition & lower TCO
    • Cross-system communication (heterogeneous DB links)
  • Disadvantages / criticisms
    • May stifle innovation; hard to change (vested vendor interests)
    • Vendors add proprietary extensions ➜ portability suffers
    • Early versions lacked referential integrity & several relational operators

MARKET LANDSCAPE & EMERGING TECHNOLOGIES

  • Mature relational market; dominant products (mid-2020): Oracle, MySQL, Microsoft SQL Server
  • Key open-source RDBMSs: MySQL, PostgreSQL, MariaDB
  • NoSQL & Hadoop ecosystems rising, but many still expose SQL or SQL-like layers (Hive QL, Spark SQL)

THE SQL ENVIRONMENT

  • Components of an SQL-based application
    • User interface (form, report, GUI, web page…)
    • Database tables (base tables)
    • RDBMS executing SQL engine
  • Catalog hierarchy
    • Catalog ⇒ Collection of schemas ⇒ Objects (tables, views, indexes, constraints…)
    • Information Schema = meta-catalog of all objects (tables, attributes, privileges…)
  • SQL command families
    • DDL (Data Definition) – CREATE, ALTER, DROP
    • DML (Data Manipulation) – SELECT, INSERT, UPDATE, DELETE, MERGE
    • DCL (Data Control) – GRANT, REVOKE; plus transaction control (COMMIT, ROLLBACK)

SQL DATA TYPES

  • Numeric: INTEGER, SMALLINT, BIGINT, NUMERIC(p,s), DECIMAL(p,s), DECFLOAT
  • Character
    • Fixed length: CHARACTER(n) / CHAR(n)
    • Variable: CHARACTER VARYING(n) / VARCHAR(n) (Oracle VARCHAR2)
  • Binary Large Objects: BLOB, CLOB, NCLOB, BFILE
  • Temporal: DATE, TIME, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE
  • Boolean: BOOLEAN (TRUE/FALSE/UNKNOWN)
  • Advanced (SQL:1999-2016): XML, MULTISET, JSON (vendor), spatial, graphic, image
  • Good practice
    • Choose data type by intended operations (math ⇒ numeric, searching ⇒ proper length, date arithmetic ⇒ temporal)
    • Use domains & CHECK constraints for semantic validation

DEFINING DATABASE STRUCTURES (DDL)

  • Creating a schema: CREATE SCHEMA schema_name AUTHORIZATION owner_userid;
  • Command summary
    • CREATE TABLE – base or derived (from AS SELECT)
    • CREATE VIEW – logical table; may be updateable with restrictions
    • CREATE INDEX – performance aid (primary / secondary, single / composite)
    • CREATE DOMAIN, CREATE CHARACTER SET, CREATE COLLATION, CREATE TRANSLATION, CREATE ASSERTION
  • Reversing objects – DROP TABLE | VIEW | INDEX … [RESTRICT | CASCADE]
  • Modifying structures – ALTER TABLE (ADD column, ALTER column, DROP column, ADD constraint)

TABLE DESIGN & CONSTRAINTS

  • Column definition clause: column_name data_type [NOT NULL] [DEFAULT expr] [constraint]
  • Key constraints
    • PRIMARY KEY (col [, …]) – unique + not null
    • UNIQUE (col [, …]) – alternate candidate keys
    • FOREIGN KEY … REFERENCES parent(col) – referential integrity
  • Referential integrity actions
    • ON UPDATE / ON DELETE { RESTRICT | CASCADE | SET NULL | SET DEFAULT }
  • Additional constraints
    • CHECK (predicate) – enforce domain/business rule
    • Named constraints strongly recommended: CONSTRAINT Order_PK PRIMARY KEY (OrderID)
  • Identity / auto-incrementing primary keys (SQL:2008):
  CustomerID INTEGER GENERATED ALWAYS AS IDENTITY
      (START WITH 1 INCREMENT BY 1)
  • Copy structure: CREATE TABLE New_T LIKE Old_T; (SQL:2008 allows copying constraints)
  • Dual table (Oracle/MySQL): dummy one-row table for selecting expressions (SELECT SYSDATE FROM DUAL;)

CHANGING & REMOVING TABLES

  • ALTER TABLE … usage examples
    • Add column: ALTER TABLE Customer_T ADD CustomerType VARCHAR2(10) DEFAULT 'Commercial';
    • Drop column: ALTER TABLE … DROP COLUMN colname;
    • Add / drop constraints as needed (often disabled temporarily for bulk loads)
  • Removing data structures
    • DROP TABLE TableName; (plus RESTRICT / CASCADE)
    • TRUNCATE TABLE – delete all rows but keep structure (non-logged, fast)

INSERTING, UPDATING & DELETING DATA (DML)

  • INSERT
    • Full-row: INSERT INTO Customer_T VALUES (001,'Contemporary Casuals',…);
    • Partial-row: INSERT INTO Product_T (ProductID, ProductDescription, …) VALUES (1,'End Table',…);
    • Copy rows: INSERT INTO CaCustomer_T SELECT * FROM Customer_T WHERE CustomerState = 'CA';
  • Batch loaders
    • Oracle SQL*Loader, SQL Server BULK INSERT, MySQL LOAD DATA INFILE
  • DELETE
    • Conditional: DELETE FROM Customer_T WHERE CustomerState = 'HI';
    • All rows: DELETE FROM Customer_T; (or TRUNCATE TABLE)
    • Always test with SELECT … first to preview
  • UPDATE
    • Example: UPDATE Product_T SET ProductStandardPrice = 775 WHERE ProductID = 7;
    • Set to NULL: SET column = NULL
    • MERGE (UPSERT) SQL:2008 syntax combines INSERT & UPDATE (see transcript example)

INTERNAL SCHEMA & PERFORMANCE OPTIONS

  • Indexing guidelines
    • Create indexes on primary & frequently-searched secondary keys ➜ faster SELECT, slower INSERT/UPDATE
    • Types: unique / non-unique / full-text / spatial / hash (MySQL), ascending/descending
    • Oracle syntax: CREATE INDEX Name_IDX ON Customer_T (CustomerName);
    • Drop when bulk-loading or if seldom used: DROP INDEX Name_IDX;
  • Physical file organisation
    • Choose clustering, partitioning, sorted files to match workload
    • Allocate extra space for growth; maintain statistics for optimiser

QUERYING SINGLE TABLES – SELECT STATEMENT

  • Basic clause order to write: SELECT … FROM … WHERE … ORDER BY …;
  • Actual processing order: \text{FROM} \rightarrow \text{WHERE} \rightarrow \text{GROUP BY} \rightarrow \text{HAVING} \rightarrow \text{SELECT} \rightarrow \text{ORDER BY}
  • Key clauses
    • SELECT list – columns / expressions / DISTINCT / *
    • FROM – base or derived tables, views
    • WHERE – row selection joins, predicates, subqueries
    • ORDER BY – final sort (ASC default / DESC)
    • GROUP BY – form groups for aggregates; columns in SELECT must appear in GROUP BY unless aggregated
    • HAVING – filter groups (aggregate predicates)
  • Aliases improve readability: SELECT CUST.CustomerName AS Name … FROM Customer_T CUST …

EXPRESSIONS & FUNCTIONS

  • Operators: +, –, *, /, % (modulo)
  • Example: price plus 10 %: ProductStandardPrice*1.1 AS Plus10Percent
  • Precedence: Parentheses → * / → + -
  • Scalar functions
    • Math: ROUND, TRUNC, MOD, POWER, SQRT, LN, EXP, FLOOR, CEILING
    • Aggregate: COUNT, SUM, AVG, MIN, MAX
    • String: LOWER, UPPER, INITCAP, CONCAT, SUBSTR, COALESCE
    • Date: NEXT_DAY, ADD_MONTHS, MONTHS_BETWEEN
    • Analytical (vendor): TOP, WIDTH_BUCKET, window functions (not detailed here)

COMPARISON, LOGIC & PATTERN MATCHING

  • Comparison operators: =, <>, !=, >, >=, <, <=
  • Wildcards with LIKE
    • % any string; _ single char
    • LIKE '%Desk' ➜ ends with ‘Desk’
    • LIKE '_-drawer' ➜ pattern such as “3-drawer”
  • Boolean operators
    • NOT evaluated first ➜ AND ➜ OR (use parentheses to override)
  • Ranges & lists
    • BETWEEN low AND high / NOT BETWEEN
    • IN (… list …) / subquery lists; NOT IN
  • DISTINCT removes duplicate rows in final projection (only once per query)
  • NULL predicates: IS NULL, IS NOT NULL (NULL ≠ zero or blank)

SORTING & PAGINATION

  • ORDER BY column1 [, column2] [DESC] (position numbers allowed: ORDER BY 3,1)
  • LIMIT / OFFSET clauses (MySQL) or FETCH FIRST n ROWS ONLY (Oracle 12c):
  ORDER BY 3,1 LIMIT 30,5; -- skip 30, fetch 5
  • NULL sort order implementation-dependent (some allow NULLS FIRST|LAST)

GROUPING & AGGREGATION

  • Scalar aggregate = single result, vector aggregate = multiple per group
  • Rule: every SELECT column must appear in GROUP BY unless it is an aggregate argument
  • HAVING filters groups using aggregates
  • Example workflow
    1. FROM – build working set
    2. WHERE – row filter
    3. GROUP BY – create groups
    4. HAVING – group filter
    5. SELECT – projection & calculations
    6. ORDER BY – final sort
  • Debug tip: build query progressively in processing order, check intermediate results

TYPICAL EXAMPLES IN CONTEXT

  • Find products costing 
  SELECT ProductDescription, ProductStandardPrice
  FROM   Product_T
  WHERE  ProductStandardPrice < 275;
  • Count order lines for Order 1004: SELECT COUNT(*) FROM OrderLine_T WHERE OrderID = 1004;
  • Customers per state, only states with >1 customer:
  SELECT CustomerState, COUNT(*)
  FROM   Customer_T
  GROUP  BY CustomerState
  HAVING COUNT(*) > 1;

ETHICAL / PRACTICAL IMPLICATIONS & BEST PRACTICES

  • Referential integrity prevents orphaned data → quality & trust in analytics
  • Proper typing & constraints reduce application-level validation code
  • Over-indexing harms write performance; balance speed vs. cost
  • Standardisation fosters skill portability but watch proprietary features
  • Always test DML on small sets or within transactions; rollback if unexpected
  • Provide meaningful names for objects & constraints to ease maintenance

RECAP & KEY TAKEAWAYS

  • SQL serves three roles: DDL, DML, DCL
  • Standard evolution continues; SQL:2016 adds JSON, temporal tables, row pattern matching
  • Catalog → Schema → Objects hierarchy central to metadata management
  • Designing tables involves choosing right data types, primary/foreign keys, constraints & indexes
  • CRUD operations (INSERT, UPDATE, DELETE) obey referential constraints; MERGE simplifies upserts
  • SELECT is declarative & set-oriented; mastering WHERE, GROUP BY, HAVING, ORDER BY, functions & subqueries enables powerful analytics
  • Performance tuning rests on indexing, physical design, statistics, and query rewriting
  • Always consider null semantics, Boolean logic precedence, and standard vs. vendor-specific syntax during development