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 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:
: Builds new tables with constraints like , , , and .
: Modifies existing table definitions.
: Deletes tables and their data.
, , and .
Data Retrieval is performed via the 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:
: Identifies table sources.
: Filters rows using comparison operators () and logical operators ().
: Organizes rows into collections for aggregation.
: Filters groups created by .
: Specifies attributes to return.
: Sorts results in (ascending) or (descending) sequence.
Specialized operators include (range checks), (value lists), (pattern matching), and .
The 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 , , and ).
Outer Join: Includes matched rows plus unmatched rows from specific tables (, , or ).
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:
: Tallies non-null values.
and : Finds lowest and highest values.
and : 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 operator.
Relational Set Operators and SQL Functions
Set operators combine results from multiple queries:
: Combines rows and removes duplicates.
: Combines rows including duplicates.
: Returns rows common to both queries.
: 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 clause and a clause?
Answer: simply sorts the rows returned without changing which rows appear. 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 classification?
Answer: The SQL standard defines three types: , , and .