The special operator used to check whether an attribute value matches a given string pattern is __________.
BETWEEN
IS NULL
LIKE
IN
The SQL aggrergate function that gives the number of rows containing non-null values for a given column is ____________.
MIN
MAX
SUM
COUNT
Which one of the following will be useful if there is a need to step through rows forward and backward?
Stored Procedure
Cursor
Trigger
Index
Which one of the following is essentially virtual tables?
View
You can use a stored procedure that returns information from base tables that you do not have permission on.
The WITH ENCRYPTION clause of the CREATE VIEW statement
prevents users from modifying the view.
prevents users from seeing the code the defines the view.
prevents users from using the view without the appropriate authorization.
causes the data that's returned by the view to be encrypted.
The statement
CREATE VIEW Example4 AS SELECT * FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;
will create an updatable view
will create a read-only view
will create a view through which you can update or insert rows, but not delete rows
will fail because the SELECT statement returns two columns named VendorID
A table that's used to create a view is called
a view table
a temporary table
a base table
an OFFSET table
Each of the following is a benefit provided by using views except for one. Which one is it?
You can simplify data retrieval by hiding multiple join conditions.
You can provide secure access to data by creating views that provided access only to certain columns and rows.
You can create a view that simplifies data insertion by hiding a complex INSERT statement within the view.
You can create custom views to accommodate different needs.
A view
is like a virtual table
consists only of the rows and columns specified in its CREATE VIEW statement
doesn't store any data itself
All of the above (a, b, and c)
Only statements a and b are correct
Only statements a and c are correct
CREATE VIEW Example1 AS SELECT VendorName, SUM(InvoiceTotal) AS SumOFInvoices FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID GROUP BY VendorName ORDER BY VendorName;
will fail because the GROUP BY clause isn't allowed in this view
will fail because the column alias SumOfInvoices is invalid
will fail because the ORDER BY clause isn't allowed in this view
will succeed
If you delete a stored procedure, function, or trigger and then create it again
you delete the tables on which the object is based
you disable access to the tables on which the object is based
you delete the security permissions assigned to the object
none of the above
Each of the following statements about triggers is true except for one. Which one?
A trigger can't be directly called or invoked.
A trigger doesn't accept input or return output parameters.
A trigger can have more than one batch.
The code of a trigger can execute in place of the action query to which it's assigned.
Which statement can you use to manually raise an error within a stored procedure?
THROW
ERROR
TRY
RAISE
Which of the following statements calls the stored procedure and passes the values '2019-10-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;
SELECT spInvoiceTotal1 @VendorID = 122, @DateVar = '2019-10-01';
CREATE spInvoiceTotal1 @VendorID = 122, @DateVar = '2019-10-01';
EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2019-10-01';
A user-defined function
can return a single scalar value or a single table value
can return multiple scalar values or a single table value
can return multiple scalar values or a multiple table values
can't accept input parameters
Which statement can you use to control the flow of execution based on a true/false condition?
IF...ELSE
BEGIN...END
TRY...CATCH
EXEC
Given the following statements that declare a local variable and set its value, which of the following will cause an error?
DECLARE @Example1 varchar(128); SET @Example1 = 'Invoices';
IF @Example1 = 'Invoices' SELECT* FROM Invoices
PRINT 'Table name is: ' + @Example1;
SELECT * FROM @Example1;
SELECT * FROM sys.tables WHERE name = @Example1;
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
inserting an extra comma
declaring the optional parameters after the required parameters
using the OUTPUT keyword
A cursor that is sensitive to updates and deletes to the source data, but insensitive to insertions:
dynamic
static
forward-only
keyset-driven
