Introduction to SQL Flashcards
Data
raw facts that need to be processed to become meaningful and useful
Database Management System
Software that handles storage, retrieval and updating of data
Database
Collection of related data that is organized so that it can be easily accessed, managed, and updated
Data Integrity
Assurance of the accuracy and consistency of data
Information
Data that has been processed and has meaning and context
Relational database
Data that is stored in a table with rows and columns
Column
One data item that is a field in the table
Field
The data in the column of a table
Foreign Key
A key that is used to connect two tables
Primary Key
Column or columns that uniquely identify each row in a table
Null
Field with no value
Record
Row in a database table
Relationship
Link or connection that is created between tables
Row
One record in a table
Table
Collection of data about one person, place or thing
Data redundancy
Data that is duplicated in the database
Relational Database Management System (RDMS)
Database Management System designed specifically for relational databases
Structured Query Language
Standard database management language
Query
Request for information from the database
SQL Server Management Studio (SSMS)
The integrated environment used to manage the SQL infrastructure
Relational Database Management System (RDMS)
A database management system designed specifically for relational databases
New Query button
Opens a new query window for a SQL query
Execute Button
to run the SQL query
Object Explorer
In SSMS, the window where the list of databases and other objects are located
Query Editor Window
In the SSMS, where the SQL query commands are written and executed
SQL Script File
A file that contains SQL code that can be copied into the SSMS Query Editor window and executed
Comments
Comments in code are ignored by the computer. They are information for the programmer
The asterisk *
A wildcard that means ALL
For Example: SELECT * FROM Customers means to select all of the rows in the Customers table
SQL Servers primary purpose is to _________ used by other applications
store and retrieve data
SSMS is used to _____, ______,
and ______ the database.
Query, design, and manage
SQL Server Express is the _____ version of SQL Server
free
To make use of a practice database, you need to define _____ and _____
Tables and data
What are scripts?
Pre-written SQL code that has been saved as a text file
A table will be made up of several _______ and _______
Columns and Rows
Keywords
A word that is considered a reserved SQL word that is used in a database operation
SELECT
A SQL keyword that is used in a query statement to retrieve data rows from database tables
FROM
A SQL keyword that used before the table name to identify the table
WHERE Clause
the WHERE clause is used to filter rows and only select data that meets the condition
Column Names
names of the columns that were defined when the table was created
Filter Rows
uses the WHERE clause to retrieve only the rows that meet the conditions
Limit Columns
A list of column names to retrieve in the SQL query instead of retrieving all of the columns
Execute
Running a query statement to retrieve a result-set
SQL Syntax
SQL syntax refers to the rules and guidelines that must be followed as SQL queries are written. The punctuation, spaces, mathematical operators, and special characters have a special meaning when used inside of SQL query statements. Different database systems will use different syntax rules.
Result-set
The columns and rows of data retrieved by running a SQL query
ORDER BY
the SQL clause used to sort the data in ascending or descending order
DESC / ASC
SQL keywords used with ORDER BY to indicate sorting order
CREATE TABLE
A SQL statement to create a new table in a database
ALTER TABLE
A SQL statement to alter an existing table in a database. It can also be used to add constraints.
Constraint
A rule for data in the database
Primary Key (PK)
A primary key is a constraint. It is a column that uniquely identifies the data in the table. It cannot be NULL. There can only be one PK in each table.
Foreign Key (FK)
A foreign key is used to link two tables together. The PK is one column in the table that refers to the FK in another table.
Data Types
The data type of a column defines what value the column can store
nvarchar
a data type that can hold a string. Also a variable width that can hold unicode or multilingual data
int
a data type that holds whole numbers
decimal (p, s)
A data type that holds numbers with decimals. The default is 18 numbers precision (p) and 0 decimals scale (s)
date
a data type that only holds dates
NULL
a constraint that the data can hold null values or that the column is empty of data
NOT NULL
a constraint that there is always data in a column. A new row cannot be added with a NULL value in a column defined as NOT NULL
DROP TABLE
A SQL statement to remove a table and all of the data rows from the database
debugging
making corrections to remove errors in the code
NOT NULL
A SQL Server constraint that ensures a column cannot have a NULL or empty value
UNIQUE
A SQL Server constraint that ensures all values are different
PRIMARY KEY
SQL Server constraint that uniquely identifies each row in a table
FOREIGN KEY
SQL Server constraint: used to create the link to another table
CHECK
SQL Server constraint: ensures that all values in a column satisfy a specific condition
DEFAULT
SQL Server constraint: sets a default value for a column when no value is specified
INDEX
SQL Server constraint: used to create and retrieve data from the database very quickly
INSERT INTO … VALUES
SQL statement to add rows to the table
Duplicate Key
a key value that is being added to the database that violates the PRIMARY KEY constraint
subquery
a query embedded in another query
SELECT subquery
a SELECT query embedded inside of another query
outer query
the main query that has another query embedded inside
inner query
the subquery that is embedded inside the outer query
IN
the IN conditional operator is shorthand for multiple OR statements. IN is used to filter records
NOT
the NOT conditional operator displays the rows where the condition is not true
NOT IN
combines the NOT and the IN conditions, returning a result-set with records that are NOT equal to the conditions of the IN statement
OR
conditional operator that only require one condition to be true
LIKE
used in the WHERE statement to search for a pattern. Usually uses a wildcard
%
Wildcard: represents zero or more characters
_
Wildcard: represents a single character
Data Integrity
Accuracy and consistency of data
Accuracy
Data that is correct
Consistency
When changes are made to the data, there are rules that are followed
ALTER TABLE
SQL Statement to add, delete, or modify columns or constraints on an existing table
UPDATE … SET
SQL Statement to modify existing rows in a table
DELETE
SQL Statement to delete rows in a table
BETWEEN … AND
An operator to select values within a given range, including the beginning and end values
IS NULL
SQL Condition to test if a column has null values
UPDATE … DELETE
SQL statement to delete rows. Requires a filter in the statement.
Aggregate Function
A function that combines the values of multiple rows to return a single value of significant meaning
Built-in Function
pre-defined function
COUNT()
returns the number of rows that match the criteria
SUM()
returns the sum of a numeric column
AVG()
returns the average value of a numeric column
MIN()
returns the minimum value of a numeric column
MAX()
returns the maximum value of a numeric column
Column Alias (SELECT … AS …)
a temporary name given to a column to make a result-set more readable
SELECT DISTINCT
returns only the different values
View
a virtual table that displays data from the underlying tables.
virtual table
a table with columns and rows that does not store data
base table
the underlying table that stores the data
database security
measures put in place to protect a database from unauthorized users
CREATE VIEW
SQL statement to create view
CREATE / ALTER VIEW
SQL statement to create or alter a view
DROP VIEW
SQL statement to delete a view
Constraints
Defined rules specified for the data in a database table
DEFAULT Constraint
Used to provide a default value for a column
CHECK Column Constraint
Used to limit the value range that can be placed in a column.
NULL Constraint
the column default is to allow null values. a row can be added to the table without a value in the column.
CHECK Table Constraint
a CHECK constraint on a table limits the values based on the values in other columns in the row
NOT NULL Constraint
a rule that enforces a column to not accept NULL VALUES. A row cannot be added without a value.
Primary Key Constraint
unique column in a table
index
used to help the query process performance when retrieving data
