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