Introduction to SQL Flashcards
(113 cards)
1
Q
Data
A
raw facts that need to be processed to become meaningful and useful
2
Q
Database Management System
A
Software that handles storage, retrieval and updating of data
3
Q
Database
A
Collection of related data that is organized so that it can be easily accessed, managed, and updated
4
Q
Data Integrity
A
Assurance of the accuracy and consistency of data
5
Q
Information
A
Data that has been processed and has meaning and context
6
Q
Relational database
A
Data that is stored in a table with rows and columns
7
Q
Column
A
One data item that is a field in the table
8
Q
Field
A
The data in the column of a table
9
Q
Foreign Key
A
A key that is used to connect two tables
10
Q
Primary Key
A
Column or columns that uniquely identify each row in a table
11
Q
Null
A
Field with no value
12
Q
Record
A
Row in a database table
13
Q
Relationship
A
Link or connection that is created between tables
14
Q
Row
A
One record in a table
15
Q
Table
A
Collection of data about one person, place or thing
16
Q
Data redundancy
A
Data that is duplicated in the database
17
Q
Relational Database Management System (RDMS)
A
Database Management System designed specifically for relational databases
18
Q
Structured Query Language
A
Standard database management language
19
Q
Query
A
Request for information from the database
20
Q
SQL Server Management Studio (SSMS)
A
The integrated environment used to manage the SQL infrastructure
21
Q
Relational Database Management System (RDMS)
A
A database management system designed specifically for relational databases
22
Q
New Query button
A
Opens a new query window for a SQL query
23
Q
Execute Button
A
to run the SQL query
24
Q
Object Explorer
A
In SSMS, the window where the list of databases and other objects are located
25
Query Editor Window
In the SSMS, where the SQL query commands are written and executed
26
SQL Script File
A file that contains SQL code that can be copied into the SSMS Query Editor window and executed
27
Comments
Comments in code are ignored by the computer. They are information for the programmer
28
The asterisk *
A wildcard that means ALL
For Example: SELECT * FROM Customers means to select all of the rows in the Customers table
29
SQL Servers primary purpose is to _________ used by other applications
store and retrieve data
30
SSMS is used to _____, ______,
and ______ the database.
Query, design, and manage
31
SQL Server Express is the _____ version of SQL Server
free
32
To make use of a practice database, you need to define _____ and _____
Tables and data
33
What are scripts?
Pre-written SQL code that has been saved as a text file
34
A table will be made up of several _______ and _______
Columns and Rows
35
Keywords
A word that is considered a reserved SQL word that is used in a database operation
36
SELECT
A SQL keyword that is used in a query statement to retrieve data rows from database tables
37
FROM
A SQL keyword that used before the table name to identify the table
38
WHERE Clause
the WHERE clause is used to filter rows and only select data that meets the condition
39
Column Names
names of the columns that were defined when the table was created
40
Filter Rows
uses the WHERE clause to retrieve only the rows that meet the conditions
41
Limit Columns
A list of column names to retrieve in the SQL query instead of retrieving all of the columns
42
Execute
Running a query statement to retrieve a result-set
43
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.
44
Result-set
The columns and rows of data retrieved by running a SQL query
45
ORDER BY
the SQL clause used to sort the data in ascending or descending order
46
DESC / ASC
SQL keywords used with ORDER BY to indicate sorting order
47
CREATE TABLE
A SQL statement to create a new table in a database
48
ALTER TABLE
A SQL statement to alter an existing table in a database. It can also be used to add constraints.
49
Constraint
A rule for data in the database
50
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.
51
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.
52
Data Types
The data type of a column defines what value the column can store
53
nvarchar
a data type that can hold a string. Also a variable width that can hold unicode or multilingual data
54
int
a data type that holds whole numbers
55
decimal (p, s)
A data type that holds numbers with decimals. The default is 18 numbers precision (p) and 0 decimals scale (s)
56
date
a data type that only holds dates
57
NULL
a constraint that the data can hold null values or that the column is empty of data
58
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
59
DROP TABLE
A SQL statement to remove a table and all of the data rows from the database
60
debugging
making corrections to remove errors in the code
61
NOT NULL
A SQL Server constraint that ensures a column cannot have a NULL or empty value
62
UNIQUE
A SQL Server constraint that ensures all values are different
63
PRIMARY KEY
SQL Server constraint that uniquely identifies each row in a table
64
FOREIGN KEY
SQL Server constraint: used to create the link to another table
65
CHECK
SQL Server constraint: ensures that all values in a column satisfy a specific condition
66
DEFAULT
SQL Server constraint: sets a default value for a column when no value is specified
67
INDEX
SQL Server constraint: used to create and retrieve data from the database very quickly
68
INSERT INTO ... VALUES
SQL statement to add rows to the table
69
Duplicate Key
a key value that is being added to the database that violates the PRIMARY KEY constraint
70
subquery
a query embedded in another query
71
SELECT subquery
a SELECT query embedded inside of another query
72
outer query
the main query that has another query embedded inside
73
inner query
the subquery that is embedded inside the outer query
74
IN
the IN conditional operator is shorthand for multiple OR statements. IN is used to filter records
75
NOT
the NOT conditional operator displays the rows where the condition is not true
76
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
77
OR
conditional operator that only require one condition to be true
78
LIKE
used in the WHERE statement to search for a pattern. Usually uses a wildcard
79
%
Wildcard: represents zero or more characters
80
_
Wildcard: represents a single character
81
Data Integrity
Accuracy and consistency of data
82
Accuracy
Data that is correct
83
Consistency
When changes are made to the data, there are rules that are followed
84
ALTER TABLE
SQL Statement to add, delete, or modify columns or constraints on an existing table
85
UPDATE ... SET
SQL Statement to modify existing rows in a table
86
DELETE
SQL Statement to delete rows in a table
87
BETWEEN ... AND
An operator to select values within a given range, including the beginning and end values
88
IS NULL
SQL Condition to test if a column has null values
89
UPDATE ... DELETE
SQL statement to delete rows. Requires a filter in the statement.
90
Aggregate Function
A function that combines the values of multiple rows to return a single value of significant meaning
91
Built-in Function
pre-defined function
92
COUNT()
returns the number of rows that match the criteria
93
SUM()
returns the sum of a numeric column
94
AVG()
returns the average value of a numeric column
95
MIN()
returns the minimum value of a numeric column
96
MAX()
returns the maximum value of a numeric column
97
Column Alias (SELECT ... AS ...)
a temporary name given to a column to make a result-set more readable
98
SELECT DISTINCT
returns only the different values
99
View
a virtual table that displays data from the underlying tables.
100
virtual table
a table with columns and rows that does not store data
101
base table
the underlying table that stores the data
102
database security
measures put in place to protect a database from unauthorized users
103
CREATE VIEW
SQL statement to create view
104
CREATE / ALTER VIEW
SQL statement to create or alter a view
105
DROP VIEW
SQL statement to delete a view
106
Constraints
Defined rules specified for the data in a database table
107
DEFAULT Constraint
Used to provide a default value for a column
108
CHECK Column Constraint
Used to limit the value range that can be placed in a column.
109
NULL Constraint
the column default is to allow null values. a row can be added to the table without a value in the column.
110
CHECK Table Constraint
a CHECK constraint on a table limits the values based on the values in other columns in the row
111
NOT NULL Constraint
a rule that enforces a column to not accept NULL VALUES. A row cannot be added without a value.
112
Primary Key Constraint
unique column in a table
113
index
used to help the query process performance when retrieving data
