DATA

Data Table

Structured container to hold information.

SQL Server Management Studio

The IDE (Integrated Development Environment) used to interact with Microsoft's commercial database management system SQL Server.

Normalization

the process of following a standard set of rules to separate data into logical related groups.

Table Alias

A "nickname" for a table that can be used instead of spelling out the entire table name.

Count()

Built-in aggregate function used to return the total number of values in a column (will ignore null values)

Min()

Built-in aggregate functions used to find the smallest column value in a result set

Coalesce

Built-in function used to convert a null value to some other specified value in the result set.

NullIf

Built-in function used to convert a specified value to a null in the result set.

Aggregate Functions

Built-in functions used to summarize data such as finding the average or sum of a column of values

Derived Column

A column that is created as a result of an arithmetic calculation or the use of a function.

Table

A container in a database made up of columns and rows to hold data

Foreign Key

A field in a table that references the primary key in another table

SQL (Structured Query Language)

A general language used to interact with relational databases

Combination Key

A key made up of two or more non-unique fields that when combined are a unique identifier for that row

DML (Data Manipulation Language)

A part of SQL that is used query, insert, update and remove data from a database

DDL (Data Definition Language)

A part of SQL that is used to create and modify objects of a database such as tables, views, functions and stored procedures

TCL (Transactional Control Language)

A part of SQL that is used to manage different transactions occurring within a database

DCL (Data Control Language)

A part of SQL that is used to manage permissions to objects in a database

Clause

A portion of an SQL command that begins with a keyword

Subquery

A query that is nested inside another query (that could run independently of the outer query).

Correlated Subquery

A query that is nested inside another query and is dependent on information from the outer query in order to execute.

Row

A record of related data in a database

Statement

A set of SQL clauses that make up a single command set

Column

A set of data having all the same datatype in a table spanning all rows (records)

Primary Key

A unique identifier for a row

Lookup Table

An ID & single value.

Schema

An overall view that represents the tables and relationships of an entire database.

Null

An unknown value (null is not 0 nor is null an empty string - null does NOT equal null)

Avg()

Built-in aggregate function used to calculate the average column value in a result set

Sum()

Built-in aggregate function used to calculate the total value of a column in a result set

Max()

Built-in aggregate function used to find the largest column value in a result set

Count(*)

Built-in aggregate function used to return the total number of records in a table (will NOT ignore null values in columns)

Linking Table

Contains two ID values and optionally additional information.

Subset Table

Filtered and/or combined table(s).

GUID

Globally Unique Identifier, a 32 character hexadecimal string with over 1.2 septillion unique values (as in hundreds, thousands, millions, billions, trillions, quadrillions, quintillions, sextillions, septillions).

= < > <= =>

Just like in math... equal, less than, greater then, less than or equal to, greater than or equal to

Set

Keyword used in an update clause to indicate which column(s) to update to which value(s).

Insert

Keyword used to add records to a database.

Or

Keyword used to combine filtering criteria in which at least one of the criteria must evaluate to true in order for the record to be included in the result set

And

Keyword used to combine filtering criteria in which both of the criteria must evaluate to true in order for the record to be included in the result set

Order By

Keyword used to designate the sort order of a result set

Select

Keyword used to designate which columns to be returned in a query

From

Keyword used to designate which table to query

Having

Keyword used to filter the results returned based on a group

Where

Keyword used to filter the results returned in an SQL statement

Between

Keyword used to find values that fall inside a designated range

Group By

Keyword used to indicate a column or set of columns in which to bundle values together (and must be used when combining aggregate and non-aggregate values in a select clause)

In

Keyword used to indicate a specified set of values to be compared against

Union

Keyword used to join two or more result sets that have the same number and data type of all columns.

Like

Keyword used to perform a "fuzzy" search and gives us the ability to use wildcards in our search criteria

Delete

Keyword used to remove rows from a table.

As

Keyword used to rename a column in a result set

Intersect

Keyword used to return all records that are common to two or more result sets.

Distinct

Keyword used to return only unique values in a result set

Top

Keyword used to return the first set number or percentage of rows in a result set.

Except

Keyword used to return values from the first table that are not present in the second table.

Update

Keyword used with set to modify or change values in a table.

Is Null

Keywords used to select rows where the specified column contains a null value

Is Not Null

Keywords used to select rows where the specified column does not contain a null value

<> OR !=

Not equal to... <> is ANSI SQL while != is SQL Server specific

One-to-One

One record in a table relates to only one record in another table.

DBMS (Database Management System)

Software used to store and organize data in a logical and easily retrievable manner.

Full Outer Join

Retrieves all rows from 2 tables and associates any related records

Left Outer Join

Retrieves all rows from the left table and any related rows from the right table.

Right Outer Join

Retrieves all rows from the right table and any related rows from the left table.

Inner Join

Retrieves only related rows from 2 tables.

Cross Join

Returns all possible combinations of all rows (also known as a Cartesian Product).

One-to-Many

The most common relationship type where one record in a table relates to many records in another table.

Self Join

The process of linking a table to itself using table aliases. Useful in finding duplicate values in a single table.

Many-to-Many

When many records in one table can relate to multiple records in another table which requires a linking table.

Keywords

Words that have reserved meanings in SQL

Identifiers

Words used to reference database objects such as tables, columns, etc