SQL Conventions and Capabilities

SQL: The Complete Reference examines SQL features found in popular database management systems (DBMS) and those defined by ANSI/ISO SQL standards.

The book aims for syntax that works across various SQL dialects. When differences exist, they are highlighted.

Technical terms are italicized when first introduced and explained.

SQL keywords, table and column names, and sample SQL statements are presented in uppercase monospace font.

SQL API function names are displayed in lowercase monospace font.

Program listings use specific case conventions based on the programming language (e.g., uppercase for COBOL).

The book emphasizes readability; SQL implementations typically accept uppercase or lowercase.

Many examples include query results shown immediately after the SQL statement, simulating an interactive session.

A type of SQL statement that retrieves data from a database.

A table that holds information about sales representatives, including their name, office location, sales figures, and quotas.

A column in the SALESREPS table that stores the name of each sales representative.

A calculated result obtained by subtracting the quota from the sales figure.

A condition that specifies which rows to include in the result set.

A condition that compares a column value to a specific value or expression.

A type of comparison operator that checks if two values are equal.

A type of comparison operator that checks if two values are not equal.

A SQL statement that selects specific data from a table based on a mathematical comparison. For example, you can use it to find sales reps who have sales less than their quota.

A SQL statement that sorts the results of a query based on a specific column. For example, you can use it to display orders in descending order of their amounts.

A SQL function that calculates the average value of a specific column in a table. For example, you can use it to find the average order amount.

A SQL function that calculates the sum of values in a specific column. For example, you can use it to find the total amount of orders placed by a customer.

A SQL statement that groups rows based on a specific column and then applies an aggregate function to each group. For example, you can use it to group orders by customer and then calculate the total order amount for each customer.

A SQL statement that inserts new data into a database table. For example, you can use it to add a new customer to your customers table.

The process of extracting useful information from data stored in a database.

The process of summarizing data by grouping related information and applying aggregate functions.

A SQL statement that removes data from a database.

A SQL clause used to specify the conditions that must be met for a row to be deleted.

A SQL clause that specifies the columns to be retrieved in a SELECT statement.

A SQL clause that specifies conditions that must be met for rows to be included in the results of a SELECT statement.

A type of data that stores a date and time value.

A SQL statement that modifies data in a database.

A data type that stores text characters, such as letters, numbers, and special characters.

An operator that combines two strings into one, usually represented by two vertical bars (||).

Built-in functions are pre-written code that performs specific tasks, available for use within SQL queries. They simplify complex operations and improve code readability.

A function that extracts the month from a date or timestamp value, returning an integer representing the month.

A function that extracts the year from a date or timestamp value, returning an integer representing the year.

A function that converts a value from one data type to another. For example, it can convert a date to a string with a specific format.

Functions that take a date value and format it according to a given specification, returning a formatted string representation.

Functions that can be used anywhere in a SQL expression where a constant of the same data type could be used.

SQL statements use expressions that combine columns, constants, and functions using arithmetic and other operators, allowing complex calculations.

A special value in SQL that represents missing or inapplicable data.

SQL statements can be run on different database systems, and different systems may have variations in data types, constants, and functions, making 'portable' SQL statements challenging.

A simpler SQL query retrieves data from a single table within the database without requiring complex joins or subqueries.