SQL ALL QUIZZES
Code example 6-2 WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) When this query is executed, the result table will contain one row for
each vendor in the Top10 table
Code example 4-2 SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) If the LEFT keyword is replaced with the RIGHT keyword, the total number of rows that are returned must equal
none of the above
To normalize a data structure, what do you apply in sequence?
normal forms
The COALESCE function provides one way to substitute constant values for which values?
null
The GROUPING function lets you substitute another value for a/an ____________________ value when you use the ROLLUP or CUBE operator.
null
When passing a list of parameters to a stored procedure by name, you can omit optional parameters by
omitting the parameter name and value from the list
What is the most common type of relationship between two tables?
one-to-many
The most common type of relationship between two tables is called what?
one-to-many relationship
To override the order of precedence in an arithmetic expression, you can use
parentheses
The ranking functions make it easy to include a column in a result set that provides the sequential ranking number of each row within a ___________________________.
partition
Stored procedures execute faster than an equivalent SQL script because stored procedures are what?
precompiled
Data validation is the process of
preventing errors due to invalid data
The WITH CHECK OPTION clause
prevents a row in a view form being updated if that would cause the row to be excluded from the view
What uniquely identifies each row in a table?
primary key
What do you typically use to relate two tables that have a one-to-one relationship?
primary keys
The users, groups, logins, and roles that have access to a server are called ________________.
principals
After you create a schema, you can create any object within that schema by
qualifying the object name with the schema name
To apply the second normal form, you move columns that don't depend on the entire primary key to another table and establish a relationship between the two tables. This
reduces redundancy and makes maintenance easier
To maintain ________________, if you delete a row in a primary key table, you must also delete any related rows in foreign key tables.
referential integrity
When you use the Management Studio to create a foreign key constraint, you specify the relationship between two tables as well as the rules for enforcing what?
referential integrity
Management Studio allows you to back up a database. Then, if you accidentally modify or delete data, you can easily ________________ it.
restore
In a cross join, all of the rows in the first table are joined with all of the
rows in the second table
Unlike most database objects, when you invoke a user-defined function, you must always preface the name of the function with the
schema name
Whenever you use the Management Studio to create, alter, or delete database objects, you can save the ________________ that it used for doing that.
script
An index improves performance when SQL Server ______________________.
searches a table
The entities that can be secured on a server are called ________________.
securables
The highest level at which you can grant permissions is the ________________ level.
server
A SELECT statement that includes aggregate functions is often called a/an ________________ query.
summary
A common table expression (CTE) creates a temporary _____________ that can be used by a query that follows.
table
This is typically modeled after a real-world entity, such as an invoice or a vendor.
table
When you use the Check Constraints dialog box, all of the constraints are at the which level so they can refer to any of the columns in the table?
table
A relational database consists of one or more what?
tables
What's the name of the system database that stores temporary tables?
tempdb
If you define a column with a default value, that value is used whenever a row
that doesn't include a value for that column is added to the table
An at sign (@) at the beginning of an identifier indicates
that the identifier is a local variable or parameter
When you define a foreign key constraint, you can specify all but one of the following. Which one is it?
that the insertion of a row in a foreign key table that has a foreign key that isn't matched in the primary key table should be cascaded up to the primary key table
When coding a definition for a column that will contain a high percentage of null values, what attribute can you use to optimize the storage?
the SPARSE attribute
Where would you go to create, modify, or delete logins using the Management Studio?
the Security folder for the server
The statement CREATE VIEW Example4 AS SELECT * FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;
will fail because the SELECT statement returns two columns named VendorID
If you delete a stored procedure, function, or trigger and then create it again
you delete the security permissions assigned to the object
When you create a script for creating a database,
you need to create the referred to tables before you create the tables that refer to them
If ExpirationDate contains a value that's equivalent to June 2, 2016 and the GetDate function returns a value that's equivalent to July 17, 2016, what will the Solution column contain when this code is executed? DATEDIFF(day, ExpirationDate, GetDate()) AS Solution
45
SQL statements that define the tables in a database are referred to as ________________ statements.
Data Definition Language (DDL)
To test whether one or more rows are returned by a subquery, you can use which operator?
EXISTS
Which of the following isn't a common error when entering and executing SQL statements?
Forgetting to attach the required database
What statement can you use to divide a script into multiple batches?
GO
When you create a script for creating a database, which keyword do you use to signal the end of a batch and cause all the statements in the batch to be executed?
GO
A subquery can be coded in a WHERE, FROM, SELECT, or ______________ clause.
HAVING
How would you code the INSTEAD OF clause for a trigger that's fired whenever a view is deleted?
INSTEAD OF DROP_VIEW
Which of the following expressions will not compute 10% of the balance due if balance due is the invoice total minus the credit total minus the payment total?
InvoiceTotal - CreditTotal - PaymentTotal / 10
When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an InvoiceTotal value of $1000 or less?
InvoiceTotal IN (0,1000)
Which statements are true about the ROUND function?
It returns the number rounded to the specified precision
Which function would you use to retrieve data from a subsequent row in a result set? And which function would you use to retrieve data from a previous row?
LEAD, LAG
After locating the characters that separate the components of a string you wish to parse, you can use which functions to extract the individual components?
LEFT, RIGHT, SUBSTRING, and LEN
Which keyword can you use to retrieve rows in which an expression matches a string pattern called a mask?
LIKE
Which function returns the string with any leading spaces removed?
LTRIM
Write an aggregate expression to find the latest date in the InvoiceDate column
MAX(InvoiceDate)
Write an aggregate expression to find the VendorName column that's last in alphabetical order
MAX(VendorName)
Which option can you use to make SQL Server prompt the user for a new password the first time the login ID is used?
MUST_CHANGE
If you omit both NULL and NOT NULL from the list of column attributes in a CREATE TABLE statement, which is the default setting?
NULL
Which clause specifies the number of rows that should be skipped before rows are returned from the result set?
OFFSET
How would you code the ON clause for a trigger that's fired after a table is deleted from the current database?
ON DATABASE
Which keyword can you use to pass parameter from a stored procedure back to the calling program?
OUTPUT
If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery)
a subquery can't be introduced in this way
A subquery that's used in a WHERE or HAVING clause is called what?
a subquery search condition
Figure 10-1 ORDERS (OrderID, OrderDate) • | ^ ORDERLINEITEMS (OrderID, OrderSequence, ProductID) v | • PRODUCTS (ProductID, ProductName) (Refer to figure 10-1.) Which column or columns in each table should be defined as the primary key?
Orders: OrderID OrderLineItems: OrderID and OrderSequence Products: ProductID
Which function would you use to calculate the rank of the values in a sorted set of values as a percent?
PERCENT_RANK
When coded in a WHERE clause, which search condition will return invoices when PaymentDate is not null and InvoiceTotal is greater than or equal to $500?
PaymentDate IS NOT NULL AND InvoiceTotal >= 500
Within the Management Studio, you can build a SQL statement without having to write your own code by using the ________________.
Query Designer
Which of the following statements returns the value of a variable named @InvoiceCount?
RETURN @InvoiceCount;
In Management Studio, if a statement returns data, that data is displayed in the __________ tab.
Results tab
You can use the Object Dependencies dialog box of the Management Studio to do all but one of the following. Which one is it?
Review the stored procedures and views that a specific table depends on
A subquery is a/an ______________ statement that's coded within another SQL statement.
SELECT
Which statement can you use to manually raise an error within a stored procedure?
THROW (Technically that should be 'raise an exception'...)
When coded within a SELECT clause, which TOP clause will return a result set consisting of the ten largest InvoiceNumbers?
TOP 10 InvoiceNumber
Which of the following is not a good guideline for deciding when to create an index for a column?
The column is frequently updated.
The basic code structure for many SQL statements and objects can be found in which section of the SQL Server Management Studio?
Transact-SQL snippets
Code a statement that changes the database context to a database named TestDB.
USE TestDB;
All of the following statements about application roles are true except for one. Which one?
Unlike a standard database role, an application role can contain only one member.
Which of the following recommendations won't improve the readability of your SQL statements?
Use comments to describe what each statement does.
If you want to filter the result set that's returned by a SELECT statement, you must include which clause?
WHERE
Your code will be easier to read if you code the join condition in the ON expression, and the search conditions in the which clause?
WHERE
Which statement can you use to repeatedly execute a statement or set of statements?
WHILE
Code example 6-2 WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) In this query, the table named Top5 is coded as a
common table expression (CTE)
What kind of constraint limits the values that can be stored in a column?
check constraint
When you code a table-level check constraint, the constraint can refer to data in more than one
column
Unless you assign a ________________, the column name in the result set is the same as the column name in the base table.
column alias
When you code an ORDER BY clause, you can specify a
column name, alias, expression, or column number
To run a SELECT statement from an application program, you store the statement in the ________________ object for the database connection.
command
To work with the data in a SQL Server database from a .NET application, you can use ADO.NET objects like
commands, connections, and data readers
If you try to move a database file that's attached to a server, you'll get an error message that indicates the file is in use. To get around this, you need to ________________ the database from the server.
detach
A database ________________ is a schematic drawing that shows you the relationships between the tables you're working with.
diagram
Which identifier can't be used in a SQL statement?
email addresses
Although the American National Standards Institute publishes the specifications for a standard SQL language, each DBMS vendor has its own ________________ of SQL.
dialect/variant
If FirstName contains "Edward" and LastName contains "Williams", what will the Solution column contain when this code is executed? LOWER(LEFT(FirstName,1) + LEFT(LastName,7)) AS Solution
ewilliam
What is a type of nonclustered index that includes a WHERE clause?
filtered index
Users who are involved in the administration of the server are typically assigned to one of the ________________ roles that are built into SQL Server.
fixed server
To relate one table to another, a/an ________________ in one table is used to point to the primary key in another table.
foreign key
You can use the OVER clause with an aggregate function to
include the rows used to calculate the summary in the result set
When you code a union with the INTERSECT keyword to combine two result sets, the union
includes only rows that occur in both result sets
Code example 4-1 SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; (Refer to code example 4-1.) This type of join is called a/an
inner join
A correlated subquery is one that
is executed once for each row in the outer query
The CUBE operator is similar to the ROLLUP operator except that
it adds summary rows for every combination of groups
By default, what kind of index does the CREATE INDEX statement create?
non clustered
Code example 4-2 SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) If the LEFT keyword is replaced with the FULL keyword, the total number of rows that are returned must equal
none of the above
Which statement can you use to create a user-defined database role?
CREATE ROLE
Which of the following statements can be coded in a batch with other statements?
CREATE TABLE
Which of the following statements creates a database user in the current database from a TomBrown SQL Server login ID?
CREATE USER TomBrown;
___________________ names can be used when you want to assign a temporary name to a table.
Correlation
Which of the following is not a SQL DML statement?
CreateTable
When you use the Management Studio to create a database, including its tables and indexes, the Management Studio actually generates and runs the ____________ statements that are necessary to create the database.
DDL
Code a statement that creates a table variable named @TestTable.
DECLARE @TestTable table;
Each of the following column attributes is a column constraint, except
DEFAULT
To sort the records that are retrieved by a SELECT statement in descending sequence what keyword do you code at the end of the ORDER BY clause?
DESC (For some reason my professor's key says DISTINCT, but it's most certainly DESC...)
By default, all duplicate values are included in the aggregate calculation, unless you specify which keyword?
DISTINCT
If InvoiceTotal contains a value of 250.00, what will the Solution column contain when this code is executed? CASE WHEN InvoiceTotal > 500 THEN InvoiceTotal - ROUND(InvoiceTotal * .20, 2) WHEN InvoiceTotal >= 250 THEN InvoiceTotal - ROUND(InvoiceTotal * .10, 2) ELSE 0 END AS Solution
225.00
Which of the following statements creates a login ID for a Windows user named AliceJackson in a domain named Sales?
CREATE LOGIN [Sales\AliceJackson] FROM WINDOWS;
To concatenate character strings in a string expression, which operator do you use?
+
If CustomerAddress contains " 178 E Center Street ", what will the Solution column contain when this code is executed? LEN(LTRIM(RTRIM(CustomerAddress))) AS Solution
19
When you use weekday with the DATEPART function, it returns an integer that indicates the day of the week where
1=Sunday, 2=Monday, etc.
Code example 6-2 WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) When this query is executed, there will be how many rows in the result table?
5
If RegistrationDate contains a value that's equivalent to August 10, 2016, what will the Solution column contain when this code is executed? DATEPART(month, RegistrationDate) AS Solution
8
Which system function can you use to return the value of the most recently assigned identity column?
@@IDENTITY
Which statement is true about the DENY and REVOKE statements?
A denied permission can't be granted by role membership, but a revoked permission can
Each of the following statements about triggers is true except for one. Which one?
A trigger can have more than one batch.
To transfer a database object from one schema to another, you use the which statement?
ALTER SCHEMA
When you create a table using the Management Studio, the table is automatically stored in the default schema. If you want to transfer the table to a different schema, you can use the which statement?
ALTER SCHEMA
Which statement is used to modify the structure of an existing table?
ALTER TABLE
Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values
AVG(InvoiceTotal)
Which functions perform a calculation on the values of a column from selected rows?
Aggregate
Some database designers write their own SQL statements for creating a database, its tables, and its indexes instead of using the Management Studio. Why?
All of the above (They want to have complete control over how the database is created. The scripts generated by the Management Studio are harder to understand. It's easier to modify your own script if you want to use it to create the same database for another database management system later on.)
Each of the outcomes listed below is a result of executing the following script except for one. Which one? CREATE ROLE ExampleRole; ALTER ROLE db_datareader ADD MEMBER ExampleRole; GRANT INSERT,UPDATE ON Vendors TO ExampleRole; DENY INSERT ON Vendors TO ASmith; ALTER ROLE ExampleRole ADD MEMBER ASmith;
By being assigned to the role db_datareader, a user would be granted INSERT and UPDATE permission to the Vendors table and SELECT permission to all user tables.
If a string consists of one or more components, you can parse it into its individual components. To locate the characters that separate the components, you would use which function?
CHARINDEX
To locate the index of the first character of the first occurence of a substring within another string, you would use which function?
CHARINDEX
All of the aggregate functions ignore null values, except for which function?
COUNT(*)
Write an aggregate expression for the number of entries in the VendorName column, including null values
COUNT(*)
Write an aggregate expression for the number of unique values in the VendorID column
COUNT(DISTINCT VendorID)
Before you can pass a table to a stored procedure or a function as a parameter, which statement do you use to create a user-defined table type?
CREATE
Which of the following statements creates a SQL Server login ID for a user named TomBrown with the password 'abc123XYZ'.
CREATE LOGIN TomBrown WITH PASSWORD = 'abc123XYZ';
If you want to prevent users from examining the SQL code that defines a procedure, function, or trigger, you code the CREATE statement with the ________________ option
ENCRYPTION
Which of the following statements executes a stored procedure named spInvoiceCount and stores its return value in a variable named @InvoiceCount? Assume that the @InvoiceCount variable has already been declared and that the stored procedure doesn't accept any parameters.
EXEC @InvoiceCount = spInvoiceCount;
Which of the following statements calls the stored procedure and passes the values '2015-12-01' and 122 to its input parameters? CREATE PROC spInvoiceTotal1 @DateVar smalldatetime, @VendorID int AS SELECT SUM(InvoiceTotal) FROM Invoices WHERE VendorID = @VendorID AND InvoiceDate >= @DateVar; ______________________________________________
EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2015-12-01';
Which of the following statements calls the following stored procedure, passes the value '2015-12-01' to its input parameter, and stores the value of its output parameter in a variable named @MyInvoiceTotal? CREATE PROC spInvoiceTotal2 @DateVar smalldatetime, @InvoiceTotal money OUTPUT AS SELECT @InvoiceTotal = SUM(InvoiceTotal) FROM Invoices WHERE InvoiceDate >= @DateVar; (Assume that the @MyInvoiceTotal variable has already been declared, and pass the parameters by position.)
EXEC spInvoiceTotal2 '2015-12-01', @MyInvoiceTotal OUTPUT;
Code example 14-1 USE AP; DECLARE @Date1 smalldatetime; DECLARE @Date2 smalldatetime; SELECT @Date1 = MIN(InvoiceDueDate), @Date2 = MAX(InvoiceDueDate) FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0; IF @Date1 < GETDATE() IF @Date2 < GETDATE() BEGIN PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1); PRINT 'Latest past due date: ' + CONVERT(varchar, @Date2, 1); END; ELSE PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1); ELSE PRINT 'No invoices past due'; (Refer to code example 14-1.) If the current date is 03/15/16, the earliest invoice due date for invoices with unpaid balances is 02/09/16, and the latest invoice due date for invoices with unpaid balances is 03/20/16, what will be printed by this script?
Earliest past due date: 02/09/16
Code example 14-1 USE AP; DECLARE @Date1 smalldatetime; DECLARE @Date2 smalldatetime; SELECT @Date1 = MIN(InvoiceDueDate), @Date2 = MAX(InvoiceDueDate) FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0; IF @Date1 < GETDATE() IF @Date2 < GETDATE() BEGIN PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1); PRINT 'Latest past due date: ' + CONVERT(varchar, @Date2, 1); END; ELSE PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1); ELSE PRINT 'No invoices past due'; (Refer to code example 14-1.) If the current date is 04/04/16, the earliest invoice due date for invoices with unpaid balances is 02/09/16, and the latest invoice due date for invoices with unpaid balances is 03/20/16, what will be printed by this script?
Earliest past due date: 02/09/16 Latest past due date: 03/20/16
Which clause of the SELECT statement names the table that contains the data to be retrieved?
FROM
Which of the following types of statements isn't an action query?
Select
The six clauses of the SELECT statement must be coded in the following order:
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
When you code a SELECT statement, you must code the four main clauses in the following order
SELECT, FROM, WHERE, ORDER BY
To log on to SQL Server using your SQL Server login ID, you use ________________ authentication.
SQL Server
A graphical tool that you can use to start and stop the database server is called what?
SQL Server Configuration Manager
To view the code that's generated for the view, you would use what?
SQL pane
When using the Query Designer, where is the generated SQL statement displayed?
SQL pane
Which function returns the specified number of characters from the string starting at the specified position?
SUBSTRING
Code example 5-1 SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty, SUM(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE VendorState < 'e' GROUP BY VendorState, VendorCity, VendorName HAVING SUM(InvoiceTotal) > 500 ORDER BY VendorState, VendorCity, VendorName; (Please refer to code example 5-1.) Although this query runs as coded, it contains this logical error:
The column name for the fifth column in the result set doesn't match the data
Which of the statements below best describes the result set returned by this SELECT statement? SELECT VendorID, SUM(InvoiceTotal - PaymentTotal - CreditTotal) AS Column2 FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 GROUP BY VendorID;
The total unpaid balance due for each VendorID
Which of the following is not a valid column alias name?
Total Sales
What SQL dialect does Microsoft SQL Server use?
Transact-SQL
Code example 14-2 USE AP; SELECT * INTO #InvoiceCopy FROM Invoices; DECLARE @InvoiceID int, @InvoiceTotal money; DECLARE @Total money; SET @Total = 0; WHILE @Total + (SELECT TOP 1 InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC) <= 200000 BEGIN SELECT TOP 1 @InvoiceID = InvoiceID, @InvoiceTotal = InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC; IF @InvoiceTotal < 1000 BREAK; ELSE BEGIN SET @Total = @Total + @InvoiceTotal; DELETE FROM #InvoiceCopy WHERE InvoiceID = @InvoiceID; END; END; PRINT 'Total: $' + CONVERT(varchar, @Total, 1); (Refer to code example 14-2.) When does the expression on the WHILE statement in this script cause the loop to end?
When the value of the @Total variable plus the value of the largest invoice total in the #InvoiceCopy table becomes greater than 200,000
To log on to SQL Server using your Windows login ID, you use ________________ authentication.
Windows
Each of the following is a benefit provided by using views except for one. Which one?
You can create a view that simplifies data insertion by hiding a complex INSERT statement within the view.
If introduced as follows, the subquery can return which of the values listed below? WHERE InvoiceTotal > ALL (subquery)
a column of one or more rows
If introduced as follows, the subquery can return which of the values listed below? WHERE VendorID NOT IN (subquery)
a column of one or more rows
If you define a column as an identity column,
a number is generated for that column whenever a row is added to the table
What do you use to uniquely identify each row in a table?
a primary key
A join that joins a table with itself is called
a self-join
What can you use to generate a series of integer values that can be used by more than one table?
a sequence
If introduced as follows, the subquery can return which of the values listed below? SELECT (subquery)
a single value
If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery)
a single value
A user who's granted the EXECUTE object permission can execute what?
a stored procedure or function
Insert, Update, and Delete statements can be referred to as ________________ queries.
action
Which keyword lets you control the number of rows that are returned by a query?
all of the above (ALL, DISTINCT, TOP)
A union combines the rows from two or more what?
all of the above (SELECT statements, result tables, queries)
System stored procedures
all of the above (are stored in the Master database perform standard tasks on the current database can change with each version of SQL Server)
A view
all of the above (doesn't store any data itself consists only of the rows and columns specified in its CREATE VIEW statement is like a virtual table)
With the ALTER LOGIN statement, you can
all of the above (enable or disable a login ID change the name for a login ID change the default database or language)
When you identify a column as the primary key, the column
all of the above (has a clustered index created automatically for the column is forced to contain a unique value for each row is forced to be NOT NULL)
Code example 5-1 SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty, SUM(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE VendorState < 'e' GROUP BY VendorState, VendorCity, VendorName HAVING SUM(InvoiceTotal) > 500 ORDER BY VendorState, VendorCity, VendorName; The GROUPING SETS operator works like the ROLLUP and CUBE operators, but it
all of the above (includes summary rows adds summary rows for specified groups allows you to use additional sets of parentheses to create composite groups)
The dbcreator role
all of the above (is intended for those users who need to be able to work with database objects lets members create, alter, and drop databases allows new members to be added to the role)
You use data definition language (DDL) to create, modify, and delete the ________________ of a database.
all of the above (objects, sequences, tables)
Which of the following statements about the SPARSE attribute is true?
all of the above (optimizes the storage of null values for a column requires more overhead to retrieve non-null values you should only use it when a column contains a high percentage of null values)
The WITH SCHEMABINDING clause of the CREATE VIEW statement
all of the above (prevents the tables that the view is based on from being modified in a way that affects the view prevents the tables that the view is based on from being deleted protects the view by binding it to the database schema)
When you use Transact-SQL, you can store procedural code in
all of the above (stored procedures user-defined functions scripts)
You can use the GRANT statement to give users permission to use each of the following items except for one. Which one?
all the objects in a database
A combination of column names and operators that evaluate to a single value is called
an expression
You can create a database diagram for
any combination of the tables in a database
The first character of an identifier must be
any of the above
You can invoke a table-valued user-defined function
anywhere you'd refer to a table or a view
If you have the files for an existing SQL Server database, the easiest way to create the database is to ________________ those files to the database server.
attach
The processing that's done by the DBMS is typically referred to as
back-end processing
What kind of constraint enforces referential integrity between tables?
both a and b (reference constraint, foreign key constraint)
Check constraints you create using DDL can be defined at the
both a and b (table level, column level)
(Refer to code example 4-1.) The name "v" is known as a?
both b and c
(Refer to code example 4-1.) This join is coded using what syntax?
both b and c
An identifier
can contain a number sign (#)
A user-defined function
can return a single scalar value or a single table value
Expressions coded in the HAVING clause
can use either aggregate search conditions or non-aggregate search conditions
Expressions coded in the WHERE clause
can use non-aggregate search conditions but can't use aggregate search conditions
The SELECT statement for a view
can use the ORDER BY clause if it also uses the TOP clause
One way to examine the system objects that define a database is to use which views?
catalog
One limitation of the Query Designer is that you can't use it for
certain types of complex queries
In Management Studio, the Query Editor uses the IntelliSense feature to automatically display ________________ lists that you can use to enter parts of the SQL statement.
completion
The IIF function determines the value it returns based on what type of expression?
conditional
When you use the CREATE TABLE statement to create a table, you can also define the attributes and ______________ for the columns.
constraints
A user who's granted the REFERENCES object permission can do what?
create objects that refer to the object
The CREATE TABLE statement
creates a new table in the current database
In a join, column names only need to be qualified where?
when the same names are used in both tables
A view is a SELECT statement that is stored with the ________________.
database
To store and manage the databases of the client/server system, each server requires what?
database management system (DBMS)
To make a parameter for a stored procedure optional, what do you assign to it?
default value
Before you can delete a server role, you must
delete all of its members
Code example 6-2 WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) In this query, the table named Top5 is used as part of a
join
In many cases, a subquery can be restated as a/an ______________.
join
What can you use to combine data from two or more tables into a single result set?
join
Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for inner joins? The explicit syntax
lets you combine the join and seearch conditions
A SQL Server database consists of two files: a database file and a ___________.
log file
The CREATE DATABASE statement creates two files on the hard drive of the server: a data file and a
log file
To allow users to log on using either type of authentication, you need to set the SQL Server authentication to ________________ mode.
mixed
For each type of action query, a table can have
multiple AFTER triggers and one INSTEAD OF trigger
Subqueries can be ________________ within other subqueries.
nested
The three main hardware components of a client/server system are the clients, the server, and the ________________.
network
Code example 6-2 WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) When this query is executed, each row in the result table will show
the largest invoice amount related to that row
Code example 4-2 SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) The total number of rows returned by this query must equal
the number of rows in the Invoices table
When you identify the data elements in a new database, you typically subdivide data elements into
the smallest practical components
If ZipCode is a varchar column that contains the value 93702, what will the Solution column evaludate to? ISNUMERIC(ZipCode) AS Solution
true
In a/an ________________, a table can contain information about two or more entities.
unnormalized data structure
When you use the Management Studio to create a check constraint, you can specify whether you want the constraint enforced for insert or ________________ operations.
update
Which of the following is not a valid way to avoid search problems when you want to search for rows that have a specific date in a column that's defined with the datetime data type and which might include time values?
use the DatePart function to extract just the date from each datetime value
When you use Windows authentication to connect to a database, SQL Server
uses the login name and password that you use for your PC to authorize your connection