SQL Certification Exam 461 Flashcards
(135 cards)
1
Q
What three things make a nonrelational query relational?
A
- Every element has a name
- There are no duplicates
- There is no order
2
Q
Name two approximate numeric data types
A
1) Real
2) Float
3
Q
How many bytes is a float
A
8
4
Q
How many bytes is a real?
A
4
5
Q
What function generates a sequential DEFAULT identifier for a UNIQUEIDENTIFIER field?
A
NEWSEQUENTIALID()
6
Q
What column attribute requires it contain a Unique identifier?
A
UNIQUEIDENTIFIER
7
Q
What function returns the current date?
A
GetDate() returns DATETIME
8
Q
What year range does DATETIME support?
A
1753 - 9999
9
Q
What function returns the most accurate DateTime value?
A
SYSDATETIME returns DATETIME2
10
Q
What is the year range for DATETIME2?
A
year 0001 to 9999
11
Q
What function will return a part of a date (month, day, or year) as a scalar value?
A
DATEPART(month, string date)
12
Q
What function returns a string value for a part of a date - and the month name spelled out?
A
DATENAME(month, string date)
13
Q
What function adds a number of days, months, or years to a date?
A
DATEADD(month, 1, string date)
14
Q
What function returns the difference between dates in days, months, or years?
A
DATEDIFF(month, start date, end date )
15
Q
What function returns a substring?
A
SUBSTRING(string date, starting, length)
16
Q
What function returns the string length in characters?
A
LEN
17
Q
What function returns the string length in bytes?
A
DATALENGTH
18
Q
What function replaces all occurrences of characters in a string?
A
REPLACE(string, ‘x’, ‘y’) replaces x’s with y’s
19
Q
What function produces a string repeated n times?
A
REPLICATE(‘xyz’, 10) repeats xyz 10 times
20
Q
What function replaces a series of characters with a new string?
A
STUFF(string, starting, length, string)
21
Q
Show a simple form of CASE statement.
A
CASE scalar-expression WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' ELSE 'Unknown' END AS alias
22
Q
Show the advanced form of CASE statement.
A
CASE WHEN unitPrice < 20.00 THEN 'Low" WHEN unitPrice < 40.00 THEN 'Medium' ELSE 'High' END AS priceRange
23
Q
What function returns the first of its arguments that is not NULL?
A
COALESCE(a, b, c, …)
24
Q
What function performs and IF-THEN-ELSE?
A
IFF(expression, true, false)
25
What function takes a scalar and uses that to index into its argument list to select a value to return?
CHOOSE(index, arg1, arg2, arg3, ...)
26
In expressions is AND processed BEFORE or AFTER the OR operator?
Before
27
LIKE wildcards: %
0 or more of any character
28
LIKE wildcards: _ (underscore)
any single character
29
LIKE wildcards: [ABCDE]
any one character in the list
30
LIKE wildcards: [0-9]
any one character in the range
31
LIKE wildcards ^ caret
NOT the following argument
32
OFFSET-FETCH requires what clause?
ORDER BY
33
example OFFSET-FETCH
after order by
| OFFSET n ROWS FETCH NEXT m ROWS ONLY;
34
What does a CROSS JOIN produce?
Each row in Left paired with Each row in right
35
What does an INNER JOIN produce?
Produces pairing of Left and Right rows that match a predicate
36
What does a LEFT OUTER JOIN produce?
ALL rows from left and pairing those on the right that match a predicate
37
What does a RIGHT OUTER JOIN product?
ALL rows from the right and pairing those on the left that match a predicate.
38
What defines a self-contained subquery?
It has no dependency (references) on the outer containing query.
39
What is a correlated subquery?
One that references a column from the outer query or table.
40
List four types of Table Expressions.
1) Derived Tables
2) Common Table Expressions (CTEs)
3) Views
4) Inline table-valued functions
41
How is a derived table defined?
From (Select ... returning a table of rows) as T
42
Show CTE form.
```
WITH myCTE
AS
(
Inner query
)
Select ... FROM myCTE
```
43
Show View form.
CREATE VIEW myView
AS
SELECT ... FROM table;
44
Show inline table-valued function form.
CREATE FUNCTION name(args) RETURNS TABLE
AS
RETURN
SELECT statement returning table;
45
Which function UNION or UNION ALL returns only DISTINCT pairs?
UNION... UNION ALL keeps all duplicates
46
What operator returns only rows that are DISTINCT between two sets.
query1 INTERSECT query2
47
What operator returns rows in the first that are not in the second set.
query1 EXCEPT query2
48
What date function returns the End of the Month value.
EOM(date)
49
Show the form of a GROUPING SET
```
GROUP BY GROUPING SETS
(
( colA, colB, YEAR(colC) ),
( colA ),
( YEAR(colC) ),
( )
)
```
50
What's a CUBE GROUPING SET?
```
Defines all possible combinations of its argument:
GROUP BY CUBE( A, B );
produces sets:
( A, B )
( A )
( B )
( )
```
51
What does a ROLLUP GROUPING SET product?
```
Given an argument such as:
GROUP BY ROLLUP ( country, state, city)
it produces hierarchical sets:
(country, state, city),
(country, state),
(country),
( )
```
52
Pivoting: rows are also known as _____
grouping element
53
Pivoting: columns are also known as _____
spreading element
54
Pivoting: data is also known as ______
aggregation element
55
Show the form of a PIVOT.
```
WITH PivotData AS
(
Select
custid, -- grouping - rows
shipperid, -- spreading - columns
freight -- aggregation - data
From Sales.Orders
)
SELECT custid, [1]. [2]. [3]
FROM PivotData
PIVOT(SUM(freight))
FOR shipperid IN ([1], [2], [3])
AS P;
```
56
What clause is added to aggregates to determine a range to apply the aggregate to using values in the CURRENT row.
OVER (PARTITION BY column)
57
Show example of partitioning and bounding.
SELECT custid, ordered, orderdate, val,
SUM(val) OVER ( PARTITION BY custid
ORDER BY orderdate, ordered
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) as runningtotal
FROM Sales.OrderValues;
58
List the four window offset functions.
LEAD, LAG, FIRST_VALUE, LAST_VALUE
59
XML replacement text for & (ampersand)
ampersand then amp;
60
XML replacement text for " (double quote)
ampersand then quot;
61
XML replacement text for < (less than)
ampersand then lt;
62
XML replacement text for > (greater than)
ampersand then gt;
63
XML replacement text for ' (apostrophe)
ampersand then apos;
64
Show XML CDATA section syntax
< ! [ CDATA[ the text here ] ] >
65
What clause specifies XML to be produced one line per row called "row" and all fields as attributes.
FOR XML RAW
66
Show clause for defining XML namespaces.
```
Before Select:
WITH XMLNAMESPACES('my name space' AS alias)
```
67
Show clause of XML standard output.
FOR XML AUTO, ELEMENTS, ROOT('my root')
68
FOR XML comes after what clause
ORDER BY
69
What does ELEMENTS do in FOR XML?
The columns are shown as sub-elements rather than attributes of the first column of the select statement.
70
XML: How can you force a column to appear as an attribute?
Give it an alias name with @: "Customer AS '@custid'" ... FOR XML PATH
71
Show XML PATH example.
```
SELECT EmployeeID "@EmpID",
FirstName "EmpName/First",
MiddleName "EmpName/Middle",
LastName "EmpName/Last" ...
FOR XML PATH('Employee'), Root('AllEmps')
```
72
Converting XML to a table is called _____
Shredding
73
List three options that can be added to a Create View statement.
WITH ENCRYPTION
WITH SCHEMABINDING
WITH VIEW_METADATA
74
List four restrictions of views.
1) they cannot be ordered (no ORDER BY allowed)
2) you can't pass parameters to them
3) they can't build tables either permanent or temporary
4) they can only reference permanent tables
75
Boolean operators: True or False
DECLARE @X as N' ';
SELECT @x.query('(1, 2, 3) = (2, 4)')
True
76
Boolean operators: True or False
DECLARE @X as N' ';
SELECT @x.query('(1, 2, 3) != (1)')
True
77
How do you move a Categories table from schema A to schema B?
ALTER SCHEMA B TRANSFER A.Categories;
78
What's the difference between DECIMAL and NUMERIC?
None
79
Which is the most accurate: REAL, FLOAT, or DECIMAL
DECIMAL
80
Give the format of a primary key constraint.
CONSTRAINT name PRIMARY KEY(column)
81
Give the format of a unique constraint.
CONSTRAINT name UNIQUE(column)
82
Must a Unique constraint column be NOT NULL?
No, but it can only have one row with that column containing NULL.
83
Show format of Foreign Key constraint.
ALTER TABLE xyz WITH CHECK
ADD CONSTRAINT fk_xyz FOREIGN KEY(xyzID)
REFERENCES foreignTable(primaryOrUnique)
84
What statement allows you to insert a value into an identity column?
SET IDENTITY_INSERT tablename ON;
85
Rather than using a non-deterministic UPDATE, what can you use instead to generate an error if multiple rows match a join?
MERGE
86
What function returns the current (last used) identity value
IDENTITY_CURRENT
87
DELETE requires what permissions?
DELETE
88
TRUNCATE requires what permissions?
ALTER
89
Which resets the identity counter (DELETE or TRUNCATE)?
TRUNCATE
90
Which (DELETE OR TRUNCATE) can be used on a table with an index?
DELETE only
91
What is the rule for using DELETE or TRUNCATE on a table that has foreign keys pointing to it?
DELETE will work as long as no foreign key references match the deleting records. Truncate is disallowed in all cases.
92
What does SCOPE_IDENTITY generate?
The identity value last generated in the current scope and session.
93
What does the @@IDENTITY variable generate?
The identity value last generate in the current session regardless of scope.
94
What does IDENTITY_CURRENT generate?
The last value generated in the table regardless of scope or session.
95
Show statement to reseed the identity value.
DBCC CHECKIDENT('table', RESEED, value)
96
Show statement to create a sequence.
```
Create SEQUENCE mySeq
minvalue 1
maxvalue 100
start with 5
increment by 2
cycle
```
97
Show how to acquire the next sequence number.
SELECT NEXT VALUE FOR mySeq;
98
Statement to cause a sequence to restart at another value.
ALTER SEQUENCE mySeq RESTART WITH 1;
99
Give the form of the MERGE statement.
```
MERGE INTO target_table AS TGT
USING source_table AS SRC
ON predicate
WHEN MATCHED [AND predicate]
THEN action
WHEN NOT MATCHED [BY TARGET] [AND predicate]
THEN action
WHEN NOT MATCHED BY SOURCE [AND predicate]
THEN action;
```
100
Using MERGE, what database object identifier indicates the action performed.
OUTPUT $action, ...
101
List five types of locks.
1) Shared
2) Exclusive
3) Update
4) Intent
5) Schema
102
List 3 types of transaction main isolation levels.
1) Read Committed
2) Read Uncommitted
3) Read Committed Snapshot - same as Read Committed but not blocking on saved records.
103
List 3 other transaction isolation levels.
1) Repeatable Read (phantom possible)
2) Snapshot (no phantom)
3) Serializable (strongest) - all reads repeatable and news rows not allowed in underlying tables.
104
What is the default isolation level for a session?
Read Committed
105
What is the default isolation level for a Windows Azure Database?
Read Committed Snapshot
106
Show a safe way to convert data.
SELECT TRY_CONVERT(DateTime, '1752-12-31') (returns NULL because DATETIME begins at 1/1/1753
107
Show an example of parsing a string to a data type.
SELECT TRY_PARSE('1' AS INTEGER);
108
List the functions that can provide error information in a catch block.
1) ERROR_NUMBER
2) ERROR_MESSAGE
3) ERROR_SEVERITY
4) ERROR_LINE
5) ERROR_PROCEDURE
6) ERROR_STATE
109
Show format of the sp_executesql statement.
EXEC sp_executesql @statement, @params, @ param1, ...
110
Name three kinds of WAITFOR
WAITFOR DELAY,
WAITFOR TIME,
WAITFOR RECEIVE
111
Show example of using sp_executesql.
set @command = N'Select * from catalog where SalesId = @Id;'
set @parameterType = N'@Id INT';
set @parameterValue = 123;
sp_executesql @command, @parameterType, @parameterValue;
112
When and how should you test to see if an Insert, Update, or Delete affected any rows?
Test @@ROWCOUNT = 0 very first row of the trigger. Must be first row.
113
What two kinds of triggers are there?
After and Instead of
114
Are result sets allowed in Triggers?
It is deprecated.
115
INSTEAD OF triggers are typically used on Views. Why?
Because you can only update one table in a view. The trigger would provide the capability to make a coherent update of multiple tables possible.
116
Besides @@ROWCOUNT, what two functions can you use in a trigger to get useful information.
UPDATE() - determines is a particular column was updated
| COLUMNS_UPDATED() - gives a
117
show format of a table-valued function.
```
CREATE FUNCTION fn
(
@parm1 as int,
@parm2 as varchar(10)
)
RETURNS TABLE AS RETURN
(
SELECT ....
);
```
118
Show form of a multistatement Table-Valued UDF (under defined function)
```
CREATE FUNCTION fn
(
@parm1 as int,
@parm2 as char(4)
)
RETURNS @returntable TABLE
(
c1 int,
c2 char(4)
)
AS
BEGIN
INSERT @returntable
SELECT @parm1, @parm2
RETURN
END;
```
119
List five UDF Options.
```
Encryption
schemabinding
returns null on null input
called on null input
execute as
```
120
Define three characteristics of ISOLATION LEVEL SERIALIZABLE
Ensures transaction statement:
1 - cannot read data altered but not committed,
2 - cannot modify data READ by the current transaction until transaction commits,
3 - cannot insert rows with keys in the range read by the current transaction until it completes.
121
Define READ UNCOMMITTED isolation level
I can read rows that have been altered by other transactions even though they haven't been committed yet.
122
Define READ COMMITTED isolation level
I cannot read rows that have been altered by other transactions before they have been committed.
123
Define REPEATABLE READ isolation level
1) Cannot read data altered by other transactions but not yet committed
2) Other transactions cannot modify data I have read (making my read "repeatably consistent")
3) Other transactions can insert rows however.
124
Define SNAPSHOT isolation level
All data I read will be consistent with the state it was in at the beginning of my transaction.
125
Describe the use of NTILE
NTILE(5) OVER (Order by Rate Desc), ...
| divides the partitions into tiles or groups or approximately equal sizes.
126
Describe RANK versus DENSE RANK
RANK orders the entities and assigns the same rank to those that are a match - though the rank at all times means: "how many items are less than me." DenseRank means "how many RANKS are less than me."
127
List some style parameters for Convert for dates.
101 - US month/day/year
103 - British/French day/month/year
104 - German day.month.year
105 - Italian day-month-year
128
What is the value range for tinyint
0 to 255 -- 1 byte
129
What is the value range for smallint
-32,768 to 32,767 --- 2 bytes
130
What is the value range for int
2 billion -- 4 bytes
131
How many bytes are used for bigint
8 bytes
132
What is the equivalent to COMMIT WORK
COMMIT TRANSACTION
133
How is a ROLLBACK WORK different from a ROLLBACK TRANSACTION.
ROLLBACK WORK can only rollback to the beginning of the transaction - not to a save point.
134
What is the effect of SET XACT_ABORT ON or OFF?
ON requires a complete rollback for any error while OFF allows low-severity errors to continue.
135
What function can you use to generate a RANDOM identifier for a UNIQUEIDENTIFIER field either in the declaration or in a query?
NEWID()
