SQL
A _____ is a wildcard used with LIKE.
%
What SQL query statement is used to create a constraint on an existing table?
ALTER TABLE
ALTER TABLE
ALTER TABLE is the SQL statement that is used to alter or change the structure of an existing database table. This statement can be used to add constraints.
The ______ function will return a value that is calculated by dividing the sum by the count.
AVG
What command is used to modify a view?
CREATE OR ALTER VIEW
CREATE TABLE
CREATE TABLE is a SQL statement that is used to create a new table in a database.
The ____ constraint will provide a set value if no value is entered.
DEFAULT
The sort order default is in descending order.
False
WHERE FirstName LIKE 'a_%_%'
Finds any values that starts with "a" and are at least 3 characters in length
Query Editor Window
In the SSMS, the window where SQL query commands are written and executed.
Why is database access important?
It limits access to just authorized users.
% Wildcard
Represents zero or more characters.
Foreign Key (FK)
The FOREIGN KEY is a key that is used to connect two tables. The FOREIGN KEY is a column or columns in one table that refers to the PRIMARY KEY column in another table.
CREATE VIEW
The SQL statement used to create a view.
Inner Query
This is a subquery; the query that is embedded inside another query.
What is the ALTER TABLE SQL statement used for in a query?
To add, delete, or modify columns on existing databases.
What are CHECK and DEFAULT constraints used for?
To ensure data integrity rules are followed.
Which of the following describes the data to be inserted into the table?
VALUES
When you want to ____ rows, use the ____ clause.
filter, WHERE
The ______________ key creates the relationship between tables.
foreign
Data is considered _______ facts.
raw
The SQL __________ defines rules and guidelines to follow when writing SQL queries.
syntax
INSERT INTO ____________ is the first part of this SQL statement.
table name
The ____ list is the data to be added to the table.
value
It is important to have the ______ in the right order if the _______ is not included in the query statement.
value list, column list
A ______ is what a view is considered.
virtual table
What is an aggregate function?
Built-in function
The _____ constraint defines a range of values.
CHECK
What aggregate function statement will return how many rows there are?
SELECT COUNT(*)...
Inner Join
The INNER JOIN keyword selects rows that have matching values in both tables. The INNER JOIN is the default join.
NULL Constraint
The column default is to allow NULL values. A row can be added to the table without a value in the column.
Why are views important?
Views limit data access and secure the data.
A _____ is a rule in the database.
constraint
The LIKE operator finds ______ matches.
pattern
What is used in SQL statements to retrieve all data rows?
*
CHECK Table Constraint
A CHECK constraint on a table limits the values based on values in other columns in the row.
Join
A JOIN clause is used to combine rows from two or more tables, based on a related column between the tables. The default join statement is an INNER JOIN.
SELECT Subquery
A SELECT subquery is embedded as another SELECT statement inside of a query.
IS NULL
A SQL condition that will test a column to see if it is NULL (empty).
Constraint
A SQL constraint is a rule for data in the database.
DELETE
A SQL statement used to delete existing rows in a table.
INSERT INTO... VALUES
A SQL statement used to insert data rows in a database table.
UPDATE...SET
A SQL statement used to modify existing rows in a table.
nvarchar
A data type that can hold string data. Also, a variable width that also holds unicode data or multilingual data.
decimal (p, s)
A data type that holds numbers with decimals. The default size is 18 numbers (precision) and 0 decimals (scale).
int
A data type that holds whole numbers.
date
A data type that stores dates only.
Built-in Function
A function (pre-defined program) that is already available for use in SQL Server (and many other software programs).
AVG()
A function that returns the average value of a numeric column.
MAX()
A function that returns the largest value of the selected column.
COUNT()
A function that returns the number of rows that match a specified criteria.
MIN()
A function that returns the smallest value of the selected column.
SUM()
A function that returns the total sum of a numeric column.
Aggregate Function
A function where the values of multiple rows are combined to use in a calculation to return a single value of significant meaning.
Duplicate key
A key value that is being added to the database table that violates the PRIMARY KEY constraint.
Limit Columns
A list of column names to retrieve in the SQL query instead of retrieving all the columns in a table.
What is a built-in function?
A pre-defined program like =SUM in Excel
Accuracy
A quality of data that it must be correct. Accuracy is one of the elements of data integrity.
Consistency
A quality of data that, when changes take place in the database, they follow defined rules. Consistency is one of the elements of data integrity.
What is a subquery?
A query in a query.
Query
A request for information from a database table or tables.
Record
A row in a database table. It is a collection of data fields about one item.
NOT NULL Constraint
A rule that enforces a column to not accept NULL values. A row cannot be added to the table without a value in the column.
SELECT DISTINCT
A statement used to return only different values.
Subquery
A subquery is a query embedded in another query.
Temporary (Temp) Table
A table that is available only temporarily for a certain time. It then goes away as if it never existed.
Virtual Table
A table with columns and rows that does not store data.
Column Alias
A temporary name given to a column to make the column name in the result-set more readable.
Available Databases Text Box
A text box that shows the name of the current database that is being used. You can change this option with the down arrow.
View
A virtual table consisting of rows and columns. A view does not store the data and will display selected data from the underlying base table(s).
What is a view considered?
A virtual table that does not store data.
Keywords
A word that is considered a reserved SQL word that is used in a database operation.
What statement is used to drop a column from a table?
ALTER TABLE
To ____ is used to add an additional condition that must be met.
AND
What is the difference in the AND and the OR conditional operators used in filtering statements?
AND both sides have to be True; OR only one side is True.
AND
AND is a conditional operator for two or more conditions but both or all the conditions must be true for the condition to be true.
Sorting can retrieve a result-set in a(n) ___________ order, which is an A-Z order.
ASC
What does the UPDATE...SET statement do?
Adds or modifies data in a column.
Where is the filter statement written in the query?
After the JOIN condition.
What is a base table?
An underlying table where data is saved.
BETWEEN
Between a certain range
The ______ function will provide a total of the number of rows.
COUNT
What aggregate functions, when in the same query, are used for comparison purposes?
COUNT, MIN, MAX, AVG
What SQL query statement is used to design an index for a database table?
CREATE INDEX
The_______ command is used to define and make a view.
CREATE VIEW
( _____________) VALUES (___________) is the second part of this SQL statement.
Column list, value list
How often should database maintenance be performed?
Continuously.
Sorting can retrieve a result-set in a(n) ___________ order, which is a Z-A order.
DESC
What SQL query statement is used to remove an index from a database table?
DROP INDEX
DROP TABLE
DROP TABLE is a SQL statement to remove a table and all its data rows from the database.
What command is used to delete a view?
DROP VIEW
What does the SQL subquery written in the WHERE clause provide?
Data result-set to use as criteria in the outer query.
Information
Data that has been processed and has meaning and context; it can be used to make decisions.
Data Redundancy
Data that is duplicated in the database. This should be minimized by the design of the tables.
Constraints
Defined rules specified for the data in a database table.
UNIQUE
Ensures that all values in a column are different
CHECK
Ensures that all values in a column satisfy a specific condition
A SQL INSERT INTO statement will always include the column list.
False
A SQL script file is predefined in SQL.
False
A base table is the same as a view.
False
A column alias is a permanent name assigned to a column value.
False
A decimal data type holds whole number data.
False
A one-to-one relationship will retrieve several related records for one of the tables.
False
A relational database is usually made up of only one table.
False
A relational database stores its data in a file system.
False
A subquery is two queries in a row.
False
A temp table is available permanently.
False
A virtual table is different than a view.
False
ASC is the keyword abbreviation for descending order.
False
An aggregate function will always return multiple values.
False
Ascending order is numerically 10 to 1.
False
Data integrity is optional when designing a database.
False
Descending order is alphabetical order, A to Z.
False
It is not important to understand how the database is designed to learn SQL.
False
SUM is the only aggregate function that does not ignores NULL values.
False
Table relationships in the database are not very important.
False
The ALTER TABLE statement is used to add data to a column.
False
The DELETE statement is used to delete columns in a table.
False
The FROM clause is used to filter data rows.
False
The Foreign Key is a unique column.
False
The JOIN is the only join clause available.
False
The OR must have both conditions true
False
The UPDATE...SET statement is used to add a new column to a table.
False
The data type definition is not important.
False
The inner query is evaluated last.
False
There is one method to add data rows to the table.
False
Several _______ make up one record.
Fields
In what statement is a conditional operator used?
Filter column statement
WHERE FirstName LIKE '%a'
Finds any values that end with "a"
WHERE FirstName LIKE '%or%'
Finds any values that have "or" in any position
WHERE FirstName LIKE '_r%'
Finds any values that have "r" in the second position
WHERE FirstName LIKE 'a%'
Finds any values that start with "a"
WHERE FirstName LIKE 'a%o'
Finds any values that start with "a" and end with "o"
Table relationships are considered _______ in the RDBMS design.
Foundational
The ______ operator is a shorthand for multiple OR operators.
IN
The _______ tablename is the statement used for adding rows to a table.
INSERT INTO
What SQL query statement is used to test a constraint to ensure it is working correctly?
INSERT INTO
When you JOIN tables with a filter, is the result-set retrieved from table2 affected?
If it affects the result-set from table1, then the result-set from table2 is affected.
Object Explorer
In the SSMS, the window where the list of databases and other objects are located.
A database collects data to be transformed into ____________ for making decisions.
Information
___________ is/are organized data that has meaning.
Information
Data ______ determines the quality of the data.
Integrity
What does data quality mean?
Integrity, accuracy, and consistency.
What is the purpose of an aggregate function?
It calculates data rows and values for analytical purposes.
What does adding a filter to the aggregate function do?
It limits the rows that are calculated.
Why is data integrity important in the database?
It means the data is honest, correct, and always reliable.
A subquery can be used in place of a __________ statement.
JOIN
Which of the following is the correct JOIN statement in a query?
JOIN Table2 ON Table1.pk= Table2.FK
What clause is used to combine rows from two or more tables?
JOIN clause
The ________ functions return the lowest and highest values in the column.
MIN and MAX
What aggregate functions don't add, average, or count data rows or values?
MIN, MAX
Which of the following is an example of a RDBMS?
MS SQL Server
Debugging
Making corrections to remove bugs or errors in the code.
The SSMS is downloaded from the ______________ website.
Microsoft
NOT IN
NOT IN combines the NOT and IN conditional operators. It will filter the result-set and retrieve rows that are NOT equal to the listed IN criteria.
A ____ constraint ensures that data is always entered into a column.
NOT NULL
The PK columns will be automatically set to what constraint?
NOT NULL
NOT NULL
NOT NULL is a constraint rule that ensures there is always data entered in a column. A new row cannot be added without data in the column that is defined as NOT NULL.
The _____ constraint will leave the column empty if a value isn't defined.
NULL
<>
Not equal. Note: In some versions of SQL this operator may be written as !=
The ____________ SQL clause is used to specify a sort order.
ORDER BY
The _______ clause used is used to specify a sort order by last name.
ORDER BY LastName
Which of the following is the statement that sorts by the city column in ascending and then the last name column in descending order?
ORDER BY LastName DESC, City
Complete the following command to sort the query by last name in descending order.SELECT FirstName, LastNameFROM Customers
ORDER BY LastName Desc
Column
One data item that is a field in a table. For example: name, gender, birthdate.
Row
One record in a table made up of several columns.
New Query button
Opens a New Query window for a SQL query.
OR
Or is a conditional operator where there are two or more conditions but only one must be true for the condition to be true.
To create the relationship, a ________ in the first table is related to the _______ in the second table.
PK, FK
Which of the following is an example of a database of collected related data that is organized and easily accessible?
Phone book
The _____ field uniquely identifies each record in the table.
Primary key
Structured Query Language (SQL)
Pronounced "sequel" or S-Q-L; it is the standard database management language that communicates with a relational database.
An _____ is a database management system specifically for relational databases.
RDBMS
Data
Raw facts that need to be processed to become meaningful and useful.
Several _____ make up a table.
Records
What is used to connect two tables?
Related columns
The PK is used to create the _______ between tables.
Relationship
-
Represents a range of characters
_
Represents a single character
_ Wildcard
Represents a single character.
^
Represents any character not in the brackets
[]
Represents any single character within the brackets
%
Represents zero or more characters
Execute
Running a query statement to retrieve a result-set.
The subquery is usually a ___________ statement embedded in another query.
SELECT
Which of the following is a keyword that begins a query statement written to retrieve data?
SELECT
After a view is created, which command is used to see the view?
SELECT * FROM view_name
Complete the following command to list the first name and cell phone number for the customers.
SELECT FirstName, CellPhone FROM Customers
Complete the following command to list the name and email for the customers who live in Dalton.
SELECT LastName, Email FROM Customers WHERE City='Dalton'
What do relational database management systems use to create, access, and manage data?
SQL
ORDER BY
SQL clause used to sort the result-set in either ascending or descending order.
ASC
SQL keyword used with the ORDER BY clause to indicate ascending order. For example, 1, 2, 3, 4, 5 or A, B, C, D, E.
DESC
SQL keyword used with the ORDER BY clause to indicate descending order. For example, 5, 4, 3, 2, 1 or E, D, C, B, A.
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, but the majority of SQL query statements are standard, which means that, as you learn SQL in this course, it can be applied to other database systems.
The ____ is the management tool used with SQL Server.
SSMS
The ______ function will return a value that is the total of the values in the column.
SUM
LIKE
Search for a pattern
A nvarchar data type holds what kind of data?
String data
What completes the CREATE ____________ tablename (column list definition) SQL statement?
TABLE
Table Relationships
Tables are linked or related through the PK and FK to create the relationship between two or more tables.
Why do some of the PrimaryAdd data columns have a "Y" and others have an "N" value? L6 C3
The "Y" value is for those rows where the data row is their primary address. The "N" is for the rows where an order is being shipped to someone besides the customer.
The * (Asterisk) in a SQL Command
The * (asterisk) is considered a wildcard and means "all." For example, SELECT * (asterisk) FROM Customers means to select all the rows from the Customers table.
IN
The IN conditional operator is a shorthand for multiple OR conditions. IN is used to filter records.
LIKE
The LIKE operator is used in the WHERE clause to find a specified pattern in a column. It is usually used with a wildcard.
NOT
The NOT conditional operator is used to display a row if the condition is not true.
One-to-many Relationship
The PK column in table1 can relate to the FK column in many rows in table2.
One-to-one Relationship
The PK column in table1 relates to the FK column in one row in table2.
DROP VIEW
The SQL statement used to delete a view.
CREATE OR ALTER VIEW
The SQL statement used to update a view.
Why is there a ShipID and a CustID in the ShippingInfo table? L6 C3
The ShipID is the PK for the table. The CustID is the FK that connects to the Customers table PK.
WHERE Clause
The WHERE clause is used to filter rows and only retrieve those that meet a specified condition.
Data Integrity
The assurance of the accuracy and consistency of the data.
Execute Button
The button to run the SQL query.
Field
The column in a database table. It is one type of data. Field is an item, the individual pieces of data collected.
Column Names
The column names that were defined when the table was created.
Result-set
The data columns and rows retrieved when a SQL query is executed.
Data Types
The data type of a column defines what value the column can store.
Value list
The data values that will be added to the table columns in the INSERT INTO SQL statement.
Default
The default is a setting that is done automatically. The default can be changed by being specific in the SQL query statement. Sets a default value for a column when no value is specified; for example, set a default for state if most customers are from FL
SQL Server Management Studio (SSMS)
The integrated environment used to manage the SQL infrastructure. It is used in this course.
Column list
The list of columns that are in the table structure. These are the table columns that will be added in the INSERT INTO SQL statement.
Outer Query
The main query that has an inner query embedded inside it.
Database Security
The measures taken to protect and secure a database and the data from unauthorized users.
Primary Key (PK)
The primary key is a column or columns that uniquely identifies each row in the table. It cannot be left blank. A PK cannot be NULL, and there can only be one PK in each table.
Sort or Sorting
The process of organizing data in a particular order allowing for information to be found more easily. For example, sorting names in alphabetical order.
What is the result-set of an INNER JOIN clause and a JOIN clause when executed?
The same result-set.
Database Management System
The software that handles the storage, retrieval, and updating of the data in the computer system.
Base Table
The underlying table that stores the data in a database.
Primary Key (PK) Constraint
The unique column in a table. The PK column cannot be left blank or cannot be NULL. The PK is used to create relations between two or more tables.
Database Access
The users that are authorized will be given permission to have access to certain parts of the database. Each user may have a different access depending on their data needs and responsibility in the organization.
Integrity
This is a quality of being honest. In database terms, when data is used, it is intact and unchanged. Accuracy and consistency are elements of data integrity.
IN
To specify multiple possible values for a column. The IN conditional operator is a shorthand for multiple OR conditions. IN is used to filter records.
A SELECT DISTINCT is used to retrieve different values.
True
A SQL INSERT INTO statement will always include the value list.
True
A keyword can be used as a table name.
True
A one-to-many relationship means one customer can have several orders.
True
A view is used for database security and access.
True
A wildcard represents one or more characters.
True
COUNT is the aggregate function that calculates on a non-numeric column.
True
DESC is the keyword abbreviation for descending order.
True
Database security and access protects the database.
True
SQL is used by many RDBMS platforms.
True
SQL syntax indicates that keywords are not case sensitive.
True
The AND must have both conditions true.
True
The AND used in a WHERE conditional statement says both sides must be true.
True
The BETWEEN is an operator to look at a range of values.
True
The DELETE statement is used to delete existing rows in a table.
True
The INNER JOIN is considered the default JOIN.
True
The Primary Key is a unique column.
True
The column name list can be in any order in the SELECT statement.
True
The int data type holds whole number data.
True
You can add multiple rows of data to the database at one time.
True
For what SQL maintenance query statement is it critical to have a filter clause?
UPDATE, DELETE
What is database maintenance?
Updating, deleting, and adding data and modifying the database structure when appropriate.
INDEX
Used to create and retrieve data from the database very quickly. An index is used to help the query process performance when retrieving data.
CHECK Column Constraint
Used to limit the value range that can be placed in a column. Only certain values can be added to the column.
DEFAULT Constraint
Used to provide a default value for a column. If no other value is specified, the default value will be added to the column in a new row.
Filter Rows
Uses the WHERE clause to retrieve only the rows that meet a specified condition instead of all the data rows.
What does an inner subquery look like?
WHERE CId NOT IN (SELECT CustID FROM Customer)
Which of the following is the SELECT statement to filter and sort by the CITY = 'Dalton' column?
WHERE City='Dalton' ORDER BY City
What does a subquery with an ORDER BY look like?
WHERE... (SELECT CID FROM Customer) ORDER BY City
Comments
Written words in the code that the computer ignores. Comments are information for the programmer.
Why is there a value in the AltName for some rows and not the others? L6C3
You must look at the PrimaryAdd to understand this column. Since the "Y" in the PrimaryAdd column means it is their primary address, there isn't a value in the AltName or alternate name column. The PrimaryAdd "N" value does have an AltName or a person whom this order is being shipped to.
Database _____ controls who can see what in a database.
access
Data that is viewed as having ______ means that the data is correct.
accuracy
All SQL result-sets will be in _________ order as the default
ascending
The _____ is the table from which the view data is retrieved.
base table
The ____ list is defined when the table is created.
column
Without the ___________ in the INSERT INTO statement, the __________ must be in the right order.
column list, value list
To limit the data columns, the _____________ can be in any order.
column name list
The column list definition includes the ________, ________, and ______.
column name, data type, data size
A table will be made up of several ______________ and ___________.
columns, rows
Data values viewed with ______ are the same for all instances.
consistency
A(n) ____________ is a defined rule on the data in a table.
constraint
It is __________ to add multiple data rows at one time.
easy
A _____________ of one table is related to the ___________ of another table.
foreign key, primary key
The __________ of the one table is related to the __________ of the other table.
foreign key, primary key
SQL Server Express is the ___________ version of SQL Server.
free
A(n) _______ is defined to help the query process performance.
index
A(n) _____ query is executed first.
inner
A(n) _____ data type will have whole numbers.
int
Which of the following is a numeric data type?
int
Data _________ is enforced by using PKs and FKs in designing the relational database.
integrity
When a database has ______ it can be depended as honest.
integrity
The ORDER BY clause will be the _____ statement in the SQL query. A
last
To ___ the data to specific columns, use just the column names needed.
limit
A table relationship is created to __________ two columns in the tables.
link
The WHERE clause uses _____________ in the conditional statement.
mathematical operators
When you click the ____ , it opens up a _____ where you write SQL queries.sep
new query button, query editor window
A(n) ____ data type is used for data that might have international symbols.
nvarchar
The ____is where you see the _____ and other objects.
object explorer, databases
A basic SELECT aggregate function, without adding other statements, will return ________ value(s).
one
A query can retrieve ____________ data columns.
one or more
A data script is ________ to add multiple rows.
one way
The ___ of the columns will display the columns the way you want them.
order
Scripts are just ____________.
pre-written SQL code that has been saved
The _______________ key is unique and one-of-a-kind.
primary
Data that is used to make important and critical organizational decisions must have the highest _______.
quality
The SSMS is used to ________, ________, and ________ the database.
query, design, manage
Breaking the ______ usually causes problems with ________.
rules, data integrity
A ____ can be reused by another person to add the same _____ to a database.
script file, table
Database _____ protects the data from unauthorized users.
security
The column list is _____________ required in the INSERT row structure.
sometimes
SQL Server's primary purpose is to ______________ used by other applications.
store and retrieve data
The ____ sign is used when comparing two data values in a condition.
=
FROM
A SQL keyword that is used in a query statement before the table name that identifies the table.
SELECT
A SQL keyword that is used in a query statement to retrieve data rows from database tables.
Table
A collection of data about one person, place, or thing. It is made up of columns (fields) and rows (records).
Database
A collection of related data that is organized so that it can be easily accessed, managed, and updated. A file cabinet could be considered a paper database. This SQL course is about electronic databases.
Relational Database Management System (RDBMS)
A database management system designed specifically for relational databases.
Relational Database
A database where the data is stored in a table format with rows and columns. Each table is related to another table through a primary key.
Null
A field with no value. It is left blank. A constraint that indicates the data can hold null values or that the column can be empty of data.
SQL Script File
A file that contains SQL code that can be copied into the SSMS Query Editor window to be executed.
Relationship
The link that connects relational database tables.
A ___ should be after every column name except for the lastone in the list.
comma
A ____ in code is _____ by the computer.
comment, ignored
The WHERE clause is a ______________ that must be met.
conditional statement
To create a practice database, you first have to ___________.
create the database
A __________ is a container used to collect data.
database
The _____ of the database you are using will show in the _______.
database name, available database text box
Right-click _____ to create a new database.
databases
Data ______ means there is _______ data in the table.
redundancy, duplicated
To make use of a practice database, you need to define ______________.
tables and data
When you start to write a query, use ______________ to ensure the result-set returns what is needed.
the defined requirements