Chapter 7 Introduction to Structured Query Language (SQL)

Overview of Structured Query Language (SQL)

  • Database Systems: Design, Implementation, and Management, 14e defines SQL as a nonprocedural language with a command vocabulary of less than 100100 words.

  • SQL functionalities are categorized into several sub-languages:

    • Data Manipulation Language (DML)

    • Data Definition Language (DDL)

    • Transaction Control Language (TCL) for managing logical units of work.

    • Data Control Language (DCL)

SQL Data Definition and Querying

  • Data Definition (DDL) commands manage database structures:

    • CREATE TABLECREATE\,TABLE: Builds new tables with constraints like NOT NULLNOT\,NULL, UNIQUEUNIQUE, PRIMARY KEYPRIMARY\,KEY, and FOREIGN KEYFOREIGN\,KEY.

    • ALTER TABLEALTER\,TABLE: Modifies existing table definitions.

    • DROP TABLEDROP\,TABLE: Deletes tables and their data.

    • CREATE INDEXCREATE\,INDEX, CREATE VIEWCREATE\,VIEW, and CREATE SCHEMA AUTHORIZATIONCREATE\,SCHEMA\,AUTHORIZATION.

  • Data Retrieval is performed via the SELECTSELECT statement, which is set-oriented, acting on groups of rows across columns.

Components of the SELECT Query

  • The query structure follows a specific logical order for construction:

    • FROMFROM: Identifies table sources.

    • WHEREWHERE: Filters rows using comparison operators (=,<,<=,>,>=,<>,!==, <, <=, >, >=, <>, !=) and logical operators (AND,OR,NOTAND, OR, NOT).

    • GROUP BYGROUP\,BY: Organizes rows into collections for aggregation.

    • HAVINGHAVING: Filters groups created by GROUP BYGROUP\,BY.

    • SELECTSELECT: Specifies attributes to return.

    • ORDER BYORDER\,BY: Sorts results in ASCASC (ascending) or DESCDESC (descending) sequence.

  • Specialized operators include BETWEENBETWEEN (range checks), ININ (value lists), LIKELIKE (pattern matching), and IS NULLIS\,NULL.

  • The DISTINCTDISTINCT clause is used to eliminate duplicate values in a result set.

JOIN Operations

  • Joins combine data from multiple tables:

    • Inner Join: Returns only matching rows based on a common value (includes NATURAL JOINNATURAL\,JOIN, JOIN USINGJOIN\,USING, and JOIN ONJOIN\,ON).

    • Outer Join: Includes matched rows plus unmatched rows from specific tables (LEFTLEFT, RIGHTRIGHT, or FULLFULL).

    • Cross Join: Produces a Cartesian product (relational product) of two tables.

    • Recursive Join: A query where a table is joined to itself.

Aggregate Functions and Subqueries

  • Aggregates reduce row collections to a single summary row:

    • COUNTCOUNT: Tallies non-null values.

    • MINMIN and MAXMAX: Finds lowest and highest values.

    • SUMSUM and AVGAVG: Calculates totals and arithmetic means.

  • Subqueries (Nested Queries) involve an inner query executed first, with its output serving as input for the outer query.

    • Types include WHERE, IN, HAVING, and FROM subqueries.

    • Correlated Subqueries: Execute once for each row in the outer query and often utilize the EXISTSEXISTS operator.

Relational Set Operators and SQL Functions

  • Set operators combine results from multiple queries:

    • UNIONUNION: Combines rows and removes duplicates.

    • UNION ALLUNION\,ALL: Combines rows including duplicates.

    • INTERSECTINTERSECT: Returns rows common to both queries.

    • EXCEPT (MINUS)EXCEPT\,(MINUS): Returns rows in the first query not present in the second.

  • Built-in functions assist in data manipulation:

    • Date and Time: Arithmetic based on a stored number of days.

    • Numeric: Algebraic, trigonometric, and logarithmic operations.

    • String and Conversion: Manipulating text and changing data types.

Questions & Discussion

  • Question 7-1: What is the difference between an ORDER BYORDER\,BY clause and a GROUP BYGROUP\,BY clause?

  • Answer: ORDER BYORDER\,BY simply sorts the rows returned without changing which rows appear. GROUP BYGROUP\,BY gathers rows into collections to be processed by aggregate functions, directly impacting the result set structure.

  • Question 7-2: What three join types are included in the OUTER JOINOUTER\,JOIN classification?

  • Answer: The SQL standard defines three types: LEFT [OUTER] JOINLEFT\,[OUTER]\,JOIN, RIGHT [OUTER] JOINRIGHT\,[OUTER]\,JOIN, and FULL [OUTER] JOINFULL\,[OUTER]\,JOIN.