Chapter 7 Introduction to Structured Query Language (SQL) Learning Objectives • After completing this chapter, you will be able to: • • • • • • • Retrieve specified columns of data from a database Join multiple tables in a single SQL query Restrict data retrievals to rows that match complex criteria Aggregate data across groups of rows Create subqueries to preprocess data for inclusion in other queries Identify and use a variety of SQL functions for string, numeric, and date manipulation Explain the key principles in crafting a SELECT query © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 2 Introduction to SQL (1 of 4) • Categories of SQL functions • • • • Data definition language (DDL) Data manipulation language (DML) Transaction control language (TCL) Data control language (DCL) • SQL is relatively easy to learn • Nonprocedural language with basic command vocabulary set of less than 100 words • Differences in SQL dialects are minor © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 3 Introduction to SQL (2 of 4) Table 7.2 SQL Data Definition Commands Command or Option Description Covered CREATE SCHEMA AUTHORIZATION Creates a database schema Chapter 8 CREATE TABLE Creates a new table in the user’s database schema Chapter 8 NOT NULL Ensures that a column will not have null values Chapter 8 UNIQUE Ensures that a column will not have duplicate values Chapter 8 PRIMARY KEY Defines a primary key for a table Chapter 8 FOREIGN KEY Defines a foreign key for a table Chapter 8 DEFAULT Defines a default value for a column (when no value is given) Chapter 8 CHECK Validates data in an attribute Chapter 8 CREATE INDEX Creates an index for a table Chapter 8 CREATE VIEW Creates a dynamic subset of rows and columns from one or more tables Chapter 8 ALTER TABLE Modifies a table’s definition (adds, modifies, or deletes attributes or constraints) Chapter 8 CREATE TABLE AS Creates a new table based on a query in the user’s database schema Chapter 8 DROP TABLE Permanently deletes a table (and its data) Chapter 8 DROP INDEX Permanently deletes an index Chapter 8 DROP VIEW Permanently deletes a view Chapter 8 © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 4 Introduction to SQL (3 of 4) Table 7.3 Other SQL Commands Command or Option Description Covered COMMIT Permanently saves data changes Chapter 8 ROLLBACK Restores data to its original values Chapter 8 GRANT Gives a user permission to take a system action or access a data object Chapter 16 REVOKE Removes a previously granted permission from a user Chapter 16 Transaction Control Language Data Control Language © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 5 Introduction to SQL (4 of 4) • Data type: specification about the kinds of data that can be stored in an attribute • Influence queries that retrieve data • Fundamental types of data • Character data • Numeric data • Date data • At the heart of SQL is the query • Covers both questions and actions © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 6 The Database Model © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 7 Basic SELECT Queries • Each clause in a SELECT query performs a specific function • • • • SELECT: specifies the attributes to be returned by the query FROM: specifies the table(s) from which the data will be retrieved WHERE: filters the rows of data based on provided criteria GROUP BY: groups the rows of data into collections based on sharing the same values in one or more attributes • HAVING: filters the groups formed in the GROUP BY clause based on provided criteria • ORDER BY: sorts the final query result rows in ascending or descending order based on the values of one or more attributes • SQL commands can be grouped together on a single line • Complex command sequences are best shown on separate lines, with space between the SQL command and the command’s components © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 8 SELECT Statement Options (1 of 7) • The SELECT query specifies the columns to be retrieved as a column list • Syntax: SELECT columnlist FROM tablelist; • The columnlist represents one or more attributes, separated by commas • A wildcard character is a symbol that can be used as a general substitute for other characters or commands • Using column aliases • Alternative name for a column or table in a SQL statement • Using computed columns • Computed column (also called a calculated column) represents a derived attribute • Arithmetic operators: the rule of precedence • Rules that establish the order in which computations are completed © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 9 SELECT Statement Options (2 of 7) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 10 SELECT Statement Options (3 of 7) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 11 SELECT Statement Options (4 of 7) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 12 SELECT Statement Options (5 of 7) Table 7.4: The Arithmetic Operators Operator Description + Add - Subtract * Multiply / Divide ^ Raise to the power of (some applications use ** instead of ^) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 13 SELECT Statement Options (6 of 7) • Date arithmetic • Values are stored as a number of days; it is possible to perform date arithmetic in a query • Listing unique values • SQL’s DISTINCT clause produces a list of only those values that are different from one another • Command example: SELECT FROM DISTINCT V_CODE PRODUCT; © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 14 SELECT Statement Options (7 of 7) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 15 FROM Clause Options (1 of 6) • FROM clause of the query specifies the table or tables from which the data is to be retrieved • Inner joins return only rows from the tables that match on a common value • Outer joins return the same matched rows as the inner join, plus unmatched rows from one table or the other • Natural join returns all rows with matching values in the matching columns and eliminates duplicate columns • Determines the common attribute(s) by looking for attributes with identical names and compatible data types • Selects only the rows with common values in the common attribute(s) • If there are no common attributes, returns the relational product of the two tables • Syntax: SELECT column-list FROM table1 NATURAL JOIN table2 © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 16 FROM Clause Options (2 of 6) Table 7.5 Creating Links through Foreign Keys Table Attributes To Be Shown Linking Attribute PRODUCT P_DESCRIPT, P_PRICE V_CODE VENDOR V_NAME, V_CONTACT, V_AREACODE, V_PHONE V_CODE © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 17 FROM Clause Options (3 of 6) • JOIN USING syntax • Returns only the rows with matching values in the column indicated in the USING clause—and that column must exist in both tables • Syntax: SELECT column-list FROM table1 JOIN table2 USING (common-column) • JOIN ON syntax • Express a join when the tables have no common attribute names • Query returns only the rows that meet the indicated join condition • Syntax: SELECT column-list FROM table1 JOIN table2 ON join-condition • Common attribute names • Most common cause of duplicate column names is the existence of a foreign key © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 18 FROM Clause Options (4 of 6) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 19 FROM Clause Options (5 of 6) • Outer joins • Returns not only the rows matching the join condition (rows with matching values in the common columns) and returns the rows with unmatched values • ANSI standard defines three types of outer joins: left, right, and full • Cross join • Performs a relational product (also known as the Cartesian product) of two tables • Joining tables with an alias • An alias may be used to identify the source table from which the data is taken • The ability to specify a table alias is very useful - Using a table alias allows the database programmer to improve the maintainability of the code by using a table alias that is descriptive of what data the table is providing within the query • Recursive joins • Recursive query: joins a table to itself © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 20 FROM Clause Options (6 of 6) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 21 ORDER BY Clause Options (1 of 2) • ORDER BY clause is especially useful when the listing order is important • Syntax: SELECT columnlist FROM tablelist [ORDER BY columnlist [ASC|DESC] ]; • Cascading order sequence - 1. ORDER BY last name - 2. Within matching last names, ORDER BY first name - 3. Within matching first and last names, ORDER BY middle initial © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 22 ORDER BY Clause Options (2 of 2) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 23 WHERE Clause Options (1 of 4) • Selecting rows with conditional restrictions • WHERE clause is used to add conditional restrictions to the SELECT statement that limit the rows returned by the query • Syntax: SELECT FROM [WHERE [ORDER BY columnlist tablelist conditionlist ] columnlist [ASC | DESC] ]; • Using comparison operators on character attributes • May be used to place restrictions on character-based attributes • Using comparison operators on dates • Date procedures are often more software-specific than other SQL procedures © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 24 WHERE Clause Options (2 of 4) Table 7.6 Comparison Operators Symbol Meaning = Equal to < Less than Greater than >= Greater than or equal to or != Not equal to © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 25 WHERE Clause Options (3 of 4) • Selecting rows with conditional restrictions • WHERE clause is used to add conditional restrictions to the SELECT statement that limit the rows returned by the query • Syntax: SELECT FROM [WHERE [ORDER BY columnlist tablelist conditionlist ] columnlist [ASC | DESC] ]; • Using comparison operators on character attributes • May be used to place restrictions on character-based attributes • Using comparison operators on dates • Date procedures are often more software-specific than other SQL procedures © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 26 WHERE Clause Options (4 of 4) • Logical operators: AND, OR, and NOT • SQL allows you to include multiple conditions in a query through the use of these logical operators • Boolean algebra is dedicated to the use of logical operators • Old-style joins • Generally not recommended - Make complex queries more difficult to maintain - Susceptible to undetected errors • Special operators • • • • • BETWEEN IN LIKE IS NULL NOT © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 27 Aggregate Processing (1 of 3) • Takes a collection of rows and reduces it to a single row • SQL provides useful aggregate functions that count, find minimum and maximum values, calculate averages, etc. • Aggregate functions • Count • MIN and MAX • SUM and AVG • Grouping data • GROUP BY clause syntax: SELECT FROM [WHERE [GROUP BY [ORDER BY columnlist tablelist conditionlist ] columnlist ] columnlist [ASC | DESC] ]; © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 28 Aggregate Processing (2 of 3) Table 7.7 Some Basic SQL Aggregate Functions Function Output COUNT The number of rows containing non-null values MIN The minimum attribute value encountered in a given column MAX The maximum attribute value encountered in a given column SUM The sum of all values for a given column AVG The arithmetic mean (average) for a specified column © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 29 Aggregate Processing (3 of 3) • HAVING clause • Operates very much like the WHERE clause in the SELECT statement • HAVING clause is applied to the output of a GROUP BY operation • Syntax: SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY columnlist tablelist conditionlist ] columnlist ] conditionlist ] columnlist [ASC | DESC] ]; © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 30 Subqueries (1 of 3) • Key characteristics • • • • • • • A subquery is a query (SELECT statement) inside another query A subquery is normally expressed inside parentheses The first query in the SQL statement is known as the outer query The query inside the SQL statement is known as the inner query The inner query is executed first The output of an inner query is used as the input for the outer query The entire SQL statement is sometimes referred to as a nested query • Subquery can return one or more values • One single value (one column and one row) • A list of values (one column and multiple rows) • A virtual table (multicolumn, multirow set of values) © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 31 Subqueries (2 of 3) • WHERE subqueries • Most common type of subquery uses an inner SELECT subquery on the right side of a WHERE comparison expression • IN subqueries • IN operator: used to compare a single attribute to a list of values • IN subquery: values are not known beforehand, but can be derived using a query • HAVING subqueries • HAVING clause: used to restrict the output of a GROUP BY query by applying conditional criteria to the grouped rows • Multirow subquery operators: ALL and ANY • ALL operator compares a single value with a list of values returned by the first subquery using a comparison operator other than equals • ANY operator compares a single value to a list of values and select only the rows greater than or less than any value in the list © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 32 Subqueries (3 of 3) • FROM subqueries • FROM clause specifies the table(s) from which the data will be drawn • Attribute list subqueries • Inline subquery: subquery expression - Example: can be used to list the difference between each product’s price and the average product price • Correlated subquery • Executes once for each row in the outer query • Inner query is related to the outer query; the inner query references a column of the outer subquery • Can also be used with the EXISTS special operator - Can be used whenever there is a requirement to execute a command based on the result of another query - Can be used with uncorrelated subqueries, but it is almost always used with correlated subqueries © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 33 SQL Functions • SQL functions are very useful tools • Many types • Date and time functions • All date functions take one parameter of a date or character data type and return a value; refer to Table 7.10 • Numeric functions • Can be grouped in many different ways, such as algebraic, trigonometric, and logarithmic; refer to Table 7.11 • String functions • Among the most-used functions in programming; refer to Table 7.12 • Conversion functions • Allow you to take a value of a given data type and convert it to the equivalent value in another data type; refer to Table 7.13 © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 34 Relational Set Operators (1 of 2) • UNION • Combines rows from two or more queries without including duplicate rows • Syntax: query UNION query • UNION ALL • Used to produce a relation that retains the duplicate rows • Used to unite more than just two queries • INTERSECT • Can be used to combine rows from two queries, returning only the rows that appear in both sets • Syntax: query INTERSECT query © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 35 Relational Set Operators (2 of 2) • EXCEPT (MINUS) • Combines rows from two queries and returns only the rows that appear in the first set but not in the second • Syntax: query EXCEPT query and query MINUS query • Syntax alternatives • Alternative syntax used to achieve the same output © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 36 Crafting SELECT Queries (1 of 2) • Know your data • The importance of understanding the data model that you are working in cannot be overstated • Real-world databases are messy; most database systems remain in service in an organization for decades • Know the problem • Understand the question you are attempting to answer • Information reporting requests will come from a range of sources; may be one-time events or ongoing operations within an application © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 37 Crafting SELECT Queries (2 of 2) • Build one clause at a time • • • • • • FROM WHERE GROUP BY HAVING SELECT ORDER BY © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 38 Summary (1 of 2) • SQL commands can be divided into two overall categories: data definition language (DDL) commands and data manipulation language (DML) commands • The ANSI standard data types are supported by all RDBMS vendors in different ways • The basic data types are NUMBER, NUMERIC, INTEGER, CHAR, VARCHAR, and DATE • The SELECT statement is the main data retrieval command in SQL • The column list represents one or more column names separated by commas • Operations that join tables can be classified as inner joins and outer joins • A natural join returns all rows with matching values in the matching columns and eliminates duplicate columns • Joins may use keywords such as USING and ON • The ORDER BY clause is used to sort the output of a SELECT statement © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 39 Summary (2 of 2) • The WHERE clause can be used with the SELECT, UPDATE, and DELETE statements to restrict the rows affected by the DDL command • Aggregate functions (COUNT, MIN, MAX, and AVG) are special functions that perform arithmetic computations over a set of rows • Subqueries and correlated queries are used when it is necessary to process data based on other processed data • Most subqueries are executed in a serial fashion • SQL functions are used to extract or transform data • SQL provides relational set operators to combine the output of two queries to generate a new relation • Crafting effective and efficient SQL queries requires a great deal of skill © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 40 
Purchase answer to see full attachment