Relational Database Test 1

The INSERT, UPDATE, and DELETE SQL statements are sometimes called action queries because they perform an action on the database.

True

The SQL Server Configuration Manager is a graphical tool that you can use to start and stop the database server.

True

The SQL Server Management Studio can be used to work directly with database objects and to develop and test SQL statements.

True

The SQL Server Management Studio is the primary graphical tool that a developer uses to work with a SQL Server 2016 database.

True

To execute the SQL statement (s) in the query editor press the F5 key or click the Execute button.

True

When the query editor window is open you can use the Available Databases combo box on the toolbar to select the database that you want to work with.

True

When you start the Management Studio, if you select SQL Server authentication, you'll need to enter an appropriate login name and password.

True

When you start the Management Studio, if you select Windows authentication, SQL Server will use the login name and password that you use for your computer to verify that you are authorized to connect to the database server.

True

Pre SQL92 join Syntax.

Implicit Syntax

Arithmetic Operators

( * / % + - )

An expression that evaluates to True or False.

Boolean Expression or Predicate

The bringing together of rows from two tables.

Join

Concatenation Operator

+ (plus sign)

What will the the result of running the following query be? SELECT LastName, Phone FROM Employee WHERE State = 'SC' UNION SELECT CustLastName, Phone FROM Customer WHERE State = 'SC'

A single two column result set that contains the name and phone values from both tables where the State value is SC.

When an ORDER BY clause does not specify ASC or DESC what is the sort order?

ASC

When you start the Management Studio, it displays a dialog box that allows you to specify the information that's needed to connect to the _______________________.

Appropriate database server

Combining character columns and / or literal values.

Concatenation

The intersection of a row and column is sometimes called a ______.

Cell

A different name specified for a column (normally through use of the AS clause).

Column alias

Temporary table names assigned in the FROM clause.

Correlation Name or Table Alias

Each row from table A is joined (concatenated) with each row from table B.

Cross Join (Cartesian Product)

The _____________ SQL statements let you work with the objects in the database.

DDL

The _______________ SQL statements let you work with the data in the database.

DML

The join syntax introduced in the SQL-92 standard.

Explicit Syntax

In a query what is the keyword that goes immediately in front of the name of the table that you are selecting data from?

FROM

By default, remote connections are enabled for SQL Server 2016.

False

Oracle, DB2, SQL Server, and MySQL all use exactly the same syntax for all of their SQL statements.

False

The SQL Server Configuration Manager is the primary tool used to develop and test SQL Statements.

False

When you start the Management Studio, if you select SQL Server authentication, SQL Server will use the login name and password that you use for your computer to verify that you are authorized to connect to the database server.

False

All of the rows in both joined tables appear in the join results regardless of whether they have a match in the join condition.

Full Outer Join

Something that performs an operation and returns a result.

Function

A __________ column is a numeric column whose value is generated automatically when a row is added to the table.

Identity

For an inner join (default type) only rows from table A and table B that satisfy the join condition are placed on the table C. If rows exist in table A and/or table B that do not satisfy the join condition, these rows will not be present in table C.

Inner Join

What type of JOIN is the following code sample: SELECT VendorName, InvoiceNumber, InvoiceDate FROM Vendors LEFT JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 ORDER BY VendorName

It is a LEFT OUTER JOIN.

What type of JOIN is the following code sample: SELECT VendorName, InvoiceNumber, InvoiceDate FROM Vendors RIGHT JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 ORDER BY VendorName

It is a RIGHT OUTER JOIN.

On a SQL Server database diagram the "one" side of a relationship line has a key symbol and the "many" side has an infinity symbol.

True

What will the following JOIN show in the result set? SELECT VendorName, InvoiceNumber, InvoiceDate FROM Vendors FULL JOIN Invoices ON Vendors.VendorID = Invoices.VendorID ORDER BY VendorName

It is a join that will show you all the rows from both tables regardless of whether they have a match in the join condition.

What will the following JOIN show in the result set? SELECT VendorName, InvoiceNumber, InvoiceDate FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID ORDER BY VendorName

It is a join that will show you the data from the rows that match on the VendorID. If there are rows on either table that do not have a matching VendorID value on the other table they will not show in the result set.

What type of JOIN is the following code sample: SELECT VendorName, InvoiceNumber, InvoiceDate FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 ORDER BY VendorName

It is an INNER JOIN.

What is wrong with the following code sample: SELECT VendorName, InvoiceNumber, InvoiceDate FROM Vendors, Invoices WHERE Vendors.VendorID = Invoices.VendorID AND InvoiceTotal - PaymentTotal - CreditTotal > 0 ORDER BY VendorName

It uses implicit join syntax. The syntax is okay, but it is best not to use implicit join syntax for new join query code.

Specifies how the rows of table A are to be brought together with the rows of table B.

Join Condition

A word that has a predefined meaning.

Keyword

All of the rows in the left table appear in the join results, regardless of whether they have a match in the join condition.

Left Outer Join

Something enclosed in single quotation marks.

Literal value

Oracle, DB2, and SQL Server all meet at least the entry level ANSI SQL 92 standard.

True

One sort nested within another sort.

Nested Sort

A __________ represents a value that's unknown, unavailable, or not applicable.

Null

Represents a value that is unknown, unavailable, or not applicable. It is not the same as a zero, a blank space, or an empty string.

Null Value

In a query what is the clause that is used to specify how the rows in the result set will be sorted?

ORDER BY

The three types of relationships that can exist between two tables are: _________, ___________, ___________.

One-one, One-many, Many-many

A join in which all the rows of a table appear in the join results, regardless of whether they have a match in the join condition.

Outer Join

A value passed to a function.

Parameter or argument

Most tables have a ____________ that uniquely identifies each row in the table.

Primary Key

A fully qualified name consists of four parts: a server name, a database name, a schema name, and the name of the object itself. At times it will be necessary to qualify a column name (either partially or fully) to tell SQL server the source of the column.

Qualified Column Name

All of the rows in the right table appear in the join results, regardless of whether they have a match in the join condition.

Right Outer Join

What is the keyword used to start a query?

SELECT

Which code sample selects each row from the Vendors table that has a state values of 'CA' or 'PA'?

SELECT * FROM Vendors WHERE VendorState IN ('CA', 'PA');

Which code sample selects the five rows from the Invoices table that have the oldest InvoiceDate values?

SELECT TOP 5 VendorID, InvoiceDate FROM Invoices ORDER BY InvoiceDate ASC;

Which SELECT code sample correctly joins the Vendor table to the Invoices table using the Explicit join syntax?

SELECT VendorName, InvoiceNumber, InvoiceDate FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 ORDER BY VendorName

Which code sample selects three columns from a table name Vendors and presents the selected columns in ascending order by VendorName?

SELECT VendorName, VendorCity, VendorState FROM Vendors ORDER BY VendorName;

What is the correct order for the four clauses shown?

SELECT, FROM, WHERE, ORDER BY

A join where a table is joined to itself.

Self-Join

A combination of one or more character columns and literal values.

String Expression

What is wrong with the following code sample: SELECT VendorName, InvoiceNumber, InvoiceDate FROM Vendors V JOIN Invoices I ON Vendors.VendorID = I.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 ORDER BY VendorName

The Correlation name V was defined for the Vendors table, but it was not used in the JOIN clause. If you specify a correlation name (table Alias) you must use it within your query. You can't use the original table name.

What is wrong with the following code sample: SELECT VendorName, InvoiceNumber, InvoiceDate FROM Vendors, Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 ORDER BY VendorName

The JOIN condition was omitted so it is an implicit cross join. Accidently producing a cross join in a production system can have a negative impact on system performance.

A relational database contains tables that consist of rows and columns.

True

By default, the Object Explorer window is displayed on the left side of the Management Studio Window. If it isn't displayed, you can use the View menu to display it.

True

Database diagrams can be used to illustrate the relationships between tables in a database.

True

In SQL Server, a schema is a container that holds objects. The default schema is dbo.

True

Combining the results of two or more SELECTS that return the same number of corresponding columns.

Union

In a query what is the keyword that is used to start the clause that specifies the conditions that must be met for a row to be included in the results set?

WHERE