DBMS / SQL

Réussis tes devoirs et examens dès maintenant avec Quizwiz!

To normalize a data structure, what do you apply in sequence?

normal forms

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

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

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

Nested FROM query

select from (select from) as T

The highest level at which you can grant permissions is the ________________ level.

server

Type I difference formulation for same question

specify type and IN the nested query

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

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

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

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 keyword can you use to retrieve rows in which an expression matches a string pattern called a mask?

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

Which function would you use to calculate the rank of the values in a sorted set of values as a percent?

PERCENT_RANK

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

process difference of Type I and Type II in difference formulation

Type II-process each line of outer query first Type I - processes sub query first then replaces the subquery with the result

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

where are nested queries usually

WHERE and HAVING

When you code a table-level check constraint, the constraint can refer to data in more than one

column

When you code an ORDER BY clause, you can specify a

column name, alias, expression, or column number

Which identifier can't be used in a SQL statement?

email addresses

purpose of DISTINCT -when can you use

duplicate -faculty who teach every IS 2016 course

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

outer join

excludes nonmatching rows (no cross product style)

To relate one table to another, a/an ________________ in one table is used to point to the primary key in another table.

foreign key

where can an aggregate function not be

in the where clause, need to either do having or a select list

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

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

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

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

A subquery can be coded in a WHERE, FROM, SELECT, or ______________ clause.

HAVING

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

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

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

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

division problem: •Compare the number of rows associated with a group to the total number in the subset of interest

Type I nested query in HAVING clause

other way of doing full join w/ FULL JOIN

UNION of LEFT and RIGHT

UPDATE

UPDATE table SET new=new WHERE ---

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.

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

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 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)

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

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

Although the American National Standards Institute publishes the specifications for a standard SQL language, each DBMS vendor has its own ________________ of SQL.

dialect/variant

each vendor in the Top10 table

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

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

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

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

A relational database consists of one or more what?

tables

What's the name of the system database that stores temporary tables?

tempdb

use Type I nested queries to ---on other tables

test conditions -UPDATE/DELETE

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 largest invoice amount related to that row

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

In a join, column names only need to be qualified where?

when the same names are used in both tables

If you delete a stored procedure, function, or trigger and then create it again

you delete the security permissions assigned to the object

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)

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...)

SQL statements that define the tables in a database are referred to as ________________ statements.

Data Definition Language (DDL)

Which of the following types of statements isn't an action query?

Select

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

To concatenate character strings in a string expression, which operator do you use?

Type II sub-query executes when -used for which problems

-Executes one time for each row of outer query -difference problems (NOT EXISTS) -so dont specify a something youre looking for

division operators keywords

-suppliers who supply ALL parts -faculty who teach EVERY IS course

types of outerjoin

1. full outer join (has everything matched & unmatched) 2. one sided outer join

If CustomerAddress contains " 178 E Center Street ", what will the Solution column contain when this code is executed? LEN(LTRIM(RTRIM(CustomerAddress))) AS Solution

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.

Which statement can you use to create a user-defined database role?

CREATE ROLE

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

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';

To test whether one or more rows are returned by a subquery, you can use which operator?

EXISTS

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

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

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

To log on to SQL Server using your Windows login ID, you use ________________ authentication.

Windows

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

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

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)

When you use Transact-SQL, you can store procedural code in

all of the above (stored procedures user-defined functions scripts)

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 (#)

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 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

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

A subquery that's used in a WHERE or HAVING clause is called what?

a subquery search condition

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.

division operations must be done with what method

COUNT multilayer sub-queries with NOT EXISTS (or NOT IN)

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';

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;

By default, all duplicate values are included in the aggregate calculation, unless you specify which keyword?

DISTINCT

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;

Which clause of the SELECT statement names the table that contains the data to be retrieved?

FROM

SYNTAX of one sided outer join

FROM Tab1 LEFT JOIN Tab2 ON PK=FK

SYNTAX of Full outer join

FULL JOIN ON

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?

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?

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

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 of the following is not a good guideline for deciding when to create an index for a column?

The column is frequently updated.

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

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

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.

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)

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)

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

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

What kind of constraint limits the values that can be stored in a column?

check constraint

Unless you assign a ________________, the column name in the result set is the same as the column name in the base table.

column alias

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

join

Subqueries can be ________________ within other subqueries.

nested

Type II sub-queries are similar to also known as

nested loops -correlated sub query

Type I sub-queries are known as

nested queries OR independent sub query -query inside a query

The three main hardware components of a client/server system are the clients, the server, and the ________________.

network

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

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

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

DBMS / SQL

Ensembles d'études connexes

Theater finals second half

Groove Weld Terms

La data

Chapter 11

Primerica

psych 100 chapter 1,2,3

econ quiz 2

OSHA (2)

Excel Chapter 6 Quiz

econ 2105 Exam 2 Key Notes

SOCI 1101H - Chapter 13 Review Questions

BCOM Chapters 3, 4, and 5

GOVT: Chapter 11

RA 8423

8.8 conjugation

Business Ethics True/False

MENTAL HEALTH: CHAPTER 4: Treatment Settings and Therapeutic Programs:

Ch. 2 - Debt Securities - Test Topics

States of Matter (chapter 13)

Series 65 Exam