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