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