Select, insert, update, join. Master the vocabulary of SQL queries that pull exactly the data you need.
These terms were extracted from Database Design – 2nd Edition by Adrienne Watt, eCampusOntario (originally published by BCcampus) which is licensed under a Creative Commons Attribution 4.0 International License.
Download this book for free here
returns summary values
It is similar to the WHERE condition except HAVING can include the aggregate function; the WHERE cannot do this.
ascending order
Use ASC to sort the results in ascending order and DESC to sort the results in descending order.
transforms one data type to another
The conversion function transforms one data type to another.
a product combining two tables
All the different joins such as inner, left (outer), right (outer), and cross join will be described below.
displays information about dates and times
The date function produces a date by adding an interval to a specified date.
removes rows from a record set
The DELETE statement removes rows from a record set.
descending order
Use ASC to sort the results in ascending order and DESC to sort the results in descending order.
specifies that if a row from either table does not match the selection criteria
A full outer join includes rows from either table that do not match the selection criteria, setting the missing columns to NULL.
used to create one output row per each group and produces summary values for the selected columns
The GROUP BY clause is used to create one output row per each group and produces summary values for the selected columns, as shown below.
connects two tables on a column with the same data type
An inner join connects two tables on a column with the same data type.
adds rows to a table
Use the INSERT statement to add a new row to a table.
specifies that all left outer rows be returned
A left outer join specifies that all left outer rows be returned.
performs operations on numeric data
Mathematical functions perform operations on numeric data.
includes all rows from the right table that did not meet the condition specified
A right outer join includes, in its result set, all rows from the right table that did not meet the condition specified.
used to query data in the database
The SELECT statement, or command, allows the user to extract data from tables, based on specific criteria.
performs operations on character strings, binary data or expressions
A string function such as UPPER converts a customer's last name to capital letters.
returns a special piece of information from the database
A system function such as USER_NAME returns the name of the user currently connected to the database.
performs operations on text and image data
Text and image functions let you work with large text and image data stored in a table.
changes data in existing rows either by adding new data or modifying existing data
Use the UPDATE statement to standardize the country field to Canada.
allows the user to match fields that contain certain letters.
A wildcard allows the user to match fields that contain certain letters.