Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SQL 70-761 > Create Transact-SQL SELECT queries > Flashcards

Create Transact-SQL SELECT queries Flashcards

1
Q

Ways to assign alias in SELECT statement

A
  1. column AS alias
  2. column alias
  3. alias = column
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

When do you need to delimit identifiers?

A

When it doesn’t comply with the rules:

  • First character must be a letter, underscore, @, or #.
  • Subsequent characters can include letters, decimal numbers, @, #, $, or underscore.
  • Cannot be a reserved keyword in T-SQL
  • Cannot have embedded spaces
  • Must not include supplementary characters
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Two ways to delimit identifiers

A
  1. “2017”

2. [2017]

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Why shouldn’t you use = when looking for a NULL? What should you use instead?

A

Because nothing is considered equal to a NULL–not even another NULL.

You should use IS NULL or IS NOT NULL operators

e.g.
WHERE region IS NULL;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is the order of operations for AND, OR, and NOT?

A

NOT, AND, OR

Note: can use parentheses to specify order

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

How should you identify Unicode character strings?

A

N’string’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Syntax for pattern matching

What do these wildcards mean?
%, _, [characters], [character-range], [^characters]

A

column LIKE pattern

% : matches any string including empty one
_ : a single character
[characters] : a single character from a list of characters
[character-range] : a single character from a range
[^characters] : a single character that is not in the list or range

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

How to look for a character that is considered a wildcard?

A
  1. Use escape character: col1 LIKE ‘!_%’ ESCAPE ‘!’ looks for strings that start with an underscore
  2. Use square brackets: col1 LIKE ‘[_]%’
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Which date form is considered language-neutral for all date and time types?

A

‘YYYYMMDD’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

You’re trying to find orders placed in April. What is wrong with the following and what should you do instead?

SELECT orderid, orderdate, empid
FROM Sales.Orders2
WHERE orderdate BETWEEN ‘20160401’ AND ‘20160430 23:59:59.999’

A

The value is rounded up to the next millisecond. This also returns orders placed in May 1, 2016.

SELECT orderid, orderdate, empid
FROM Sales.Orders2
WHERE orderdate >= ‘20160401’ AND orderdate < ‘20160501’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Can you order by columns that are not in the SELECT statement?

When would this fail?

A

Yes

It can fail when DISTINCT is used because since duplicates are removed, result rows don’t necessarily map to source rows in a one-to-one manner

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

In what order does SQL process the components of a query?

A 
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Syntax for TOP for both number of rows and percent

Does TOP PERCENT round up or down the number of rows?

A

SELECT TOP (4) col1, col2
FROM Sales.Orders
ORDER BY orderdate DESC;

Note: Can also specify number of rows without parentheses, but correct syntax is with
Note: Can use expressions as inputs

SELECT TOP (4) PERCENT col1, col2
FROM Sales.Orders
ORDER BY orderdate DESC;

Note: TOP PERCENT rounds up for number of rows

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What does OFFSET do?

Syntax for OFFSET-FETCH

Which clause is required for OFFSET-FETCH?

Do you always need to use OFFSET-FETCH together?

What if want to filter a certain number of rows in arbitrary order?

After which statement is OFFSET-FETCH processed in a query?

A

It skips rows

SELECT col1, col2
FROM Sales.Orders
ORDER BY orderdate
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

ORDER BY is required to use OFFSET-FETCH
FETCH clause required OFFSET, but OFFSET doesn’t require FETCH

Note: If skipping 0 rows, use FETCH FIRST instead of NEXT
Note: can use expressions as inputs

For arbitrary order use ORDER BY (SELECT NULL)

OFFSET-FETCH is processed after SELECT. Think of it as an extension of the ORDER BY clause

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Between TOP and OFFSET-FETCH, which one is standard and which one is not?

A

OFFSET-FETCH is standard

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Guidelines for set operators: UNION, UNION ALL, INTERSECT, and EXCEPT

  1. About number of columns and column types
  2. Set operators use distinctness-based comparison rather than equality-based. What does this mean?
  3. These operators are set operators and not cursor operators. What restriction does this impose on the individual queries?
  4. Where can you have an ORDER BY clause?
  5. How are column names of the result columns determined?
A
  1. Number of columns between the queries has to be the same and the column types of corresponding columns need to be compatible (implicitly convertible)
  2. A comparison between two NULLs yields true. Comparison between NULL and non-NULL yields false.
  3. Individual queries cannot have an ORDER BY clause
  4. You can add an ORDER BY clause at the end of the query so that it acts on the result of the set operator
  5. Columns names are determined by the first query.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What does UNION do?

Syntax for UNION

When do you use UNION ALL?

If the sets you’re unifying don’t have duplicate rows, should you use UNION or UNION ALL?

A

Combines results of queries vertically

SELECT country, region
FROM HR.Employees

UNION

SELECT country, region
FROM Sales.Customers;

Use UNION ALL when you want to keep duplicate rows (They are excluded if just use UNION)

If no duplicates, use UNION ALL so that SQL doesn’t have to check for duplicates and thus save computation cost

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What does INTERSECT do?

Syntax for INTERSECT

A

Returns distinct rows that are in both queries

SELECT country, region, city
FROM HR.Employees

INTERSECT

SELECT country, region, city
FROM Sales.Customers;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What does EXCEPT do?

Syntax for EXCEPT

A

Returns rows that are in the first query but not in the second.

SELECT country, region, city
FROM HR.Employees

EXCEPT

SELECT country, region, city
FROM Sales.Customers;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What is the order of operations for the set operators?

A

INTERSECT precedes UNION and EXCEPT

UNION and EXCEPT are evaluated left to right based on their position in the expression.

Note: can use parenthesis to specify order

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What is a CROSS JOIN?

CROSS JOIN syntax

A

It produces the Cartesian product of the two tables

SELECT D.n AS theday, S.n AS shiftno
FROM dbo.Nums AS D
CROSS JOIN dbo.Nums AS S

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Consider this query:

SELECT D.n AS theday, S.n AS shiftno
FROM dbo.Nums AS D
    CROSS JOIN dbo.Nums AS S
WHERE D.n <= 7
   AND S.n <= 3
ORDER BY theday, shiftno;

SQL Server performs optimization technique called predicate pushdown. What does this mean?

A

SQL Server knows that with a cross join followed by a filter it can evaluate the filters first.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is an INNER JOIN?

INNER JOIN syntax

What’s equivalent to INNER JOIN?

A

Combines tables horizontally based on matching values in both tables.

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

JOIN is equivalent to INNER JOIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

For inner joins, what’s the difference between ON and WHERE clauses?

What about outer joins?

A

There’s no difference. Instead of using a WHERE clause, you can simply put the expression on the ON clause

In outer joins, ON and WHERE play different roles. ON matches while WHERE filters.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What is a left join?

What is a right join?

Syntax

A

Left join returns all records from the left table (table1), and the matched records from the right table (table2).

Right join returns all records from the right table (table2), and the matched records from the left table (table1)

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Note: can also say LEFT OUTER JOIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

What is a full join?

Syntax

A

A full join returns matched rows plus all the unmatched rows from the left and right tables.

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Note: can also just say FULL JOIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Between a unique constraint and a primary key constraint, which allows NULLs?

A

Only the unique constraint allows NULLs

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What does the ISNULL function do?

Syntax

A

The ISNULL function checks whether an expression is NULL and if so, replaces it by the value specified. Otherwise, it returns the expression

ISNULL(expression, value)

e.g.
ISNULL(col1, N’N/A’)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

This table has a clustered index. It also has NULLs in the ‘region’ column. What’s the problem with the following query (Two cases: when SQL Server uses nested loops and when it uses merge for join)? What should you do instead?

SELECT EL.country, EL.region, EL.city, EL.numemps, CL.numcusts
FROM dbo.EmpLocations AS EL
INNER JOIN dbo.CustLocations AS CL
ON EL.country = CL.country
AND ISNULL(EL.region, N’N/A’) = ISNULL(CL.region, N’N/A’)
AND EL.city = CL.city;

A

The problem is that once you manipulate a column with the ISNULL function, SQL Server cannot trust that the result values preserve the same ordering as the original values.
For nested loops, it will make the region and city predicates appear as residual predicates rather than as seek predicates. This increases computational cost.

For merge joins, after scanning the clustered index, SQL Server has to sort the results before merging. Also increases computational cost.

The following query does the same but is order-preserving:

SELECT EL.country, EL.region, EL.city, EL.numemps, CL.numcusts
FROM dbo.EmpLocations AS EL
INNER JOIN dbo.CustLocations AS CL
ON EL.country = CL.country
AND(EL.region = CL.region OR (EL.region IS NULL AND CL.region IS NULL))
AND EL.city = CL.city;

Then all predicates will appear under seek predicates.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Consider the following query. Is it order-preserving or not?

Explain what happens in the ON clause

What is the advantage of using INTERSECT instead of = here?

SELECT EL.country, EL.region, EL.city, EL.numemps, CL.numcusts
FROM dbo.EmpLocations AS EL
INNER JOIN dbo.CustLocations AS CL
ON EXISTS (SELECT EL.country, EL.region, EL.city
INTERSECT
SELECT CL.country, CL.region, CL.city);

A

Yes, it is order-preserving.

For each row evaluated by the join, the set operator performs an intersection of the employee location attributes and the customer location attributes using FROM-less SELECT statements, each producing one row. If the locations intersect, the result is one row, in which case the EXISTS predicate returns true. If the locations don’t intersect, the result is an empty set, in which case the EXISTS predicate returns false.

This take advantage of the distinctness-based comparison of set operators. This query will return rows where NULLs match between the two tables.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

In what order does SQL Server evaluate multi-join queries?

A

From left to right, so the result of one join is used as the left input for the next join

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

Assume that not all suppliers have products and therefore no categoryid. What is the problem with this query?

SELECT companyname AS supplier, S.country, P.productid, P.productname, P.unitprice, C.categoryname
FROM Production.Suppliers AS S
LEFT OUTER JOIN Production.Products AS P
ON S.supplierid = P.supplierid
INNER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid
WHERE S.country = N’Japan’;

Provide two solutions to the problem. One using outer joins and one separating joins into their own independent logical phase.

A

The inner join that followed the outer join nullified the outer part of the join. The first join returns all suppliers, including NULLs for those without products. Then the inner join compared the NULLs in the categoryid column in the outer rows to categoryid values in Production.Categories and discarded those rows.

Solution 1: outer joins

SELECT companyname AS supplier, S.country, P.productid, P.productname, P.unitprice, C.categoryname
FROM Production.Suppliers AS S
LEFT OUTER JOIN Production.Products AS P
ON S.supplierid = P.supplierid
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid
WHERE S.country = N’Japan’;

Solution 2: separating into own phase

SELECT companyname AS supplier, S.country, P.productid, P.productname, P.unitprice, C.categoryname
FROM Production.Suppliers AS S
LEFT OUTER JOIN
(Production.Products as P
INNER JOIN Production.Categories as C
ON C.categoryid = P.categoryid)
ON S.supplierid = P.supplierid
WHERE S.country = N’Japan’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

CAST function syntax

CONVERT function syntax

Which one is standard and which one is proprietary in T-SQL?

When is it useful to use CONVERT over CAST?

A

CAST(source_expression AS target_type)
e.g. CAST(‘100’ AS INT)

CONVERT(target_type, source_expression [,style_number])
e.g. CONVERT(DATE, ‘01/02/2018’, 101)

CAST is standard and CONVERT is proprietary in T-SQL.

It’s useful to use CONVERT when you need to specify a style for the conversion.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

PARSE function syntax

How is it different from CONVERT?

What is one drawback of this function?

A

PARSE(string_input AS target_type [USING style]

Instead of style numbers, it uses the more user-friendly .NET culture names.

e.g. PARSE(‘01/02/2019’ AS DATE USING ‘en-US’)

A drawback is that it’s significantly slower than CONVERT.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

What is a problem with CAST, CONVERT, and PARSE?

What can you do about it?

A

If the function fails to convert a value within a query, the whole query fails and stops processing.

Instead, can use TRY_CAST, TRY_CONVERT, and TRY_PARSE, which return a NULL when the conversion isn’t valid.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

FORMAT function syntax

A

FORMAT(value, format)

e.g. FORMAT(SYSDATETIME(), ‘yyyy-MM-dd’)

Uses a .NET format string

Note: this function is pretty slow

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Which functions return the current date and time?

In what format do they give you the date and time?

Which one is T-SQL and which one is standard?

A

GETDATE() and CURRENT_TIMESTAMP

Note: no parenthesis for CURRENT_TIMESTAMP

DATETIME format

GETDATE() is T-SQL and CURRENT_TIMESTAMP is standard.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

Which date functions return the current date and time in the more precise DATETIME2 and DATETIMEOFFSET data types?

A

SYSDATETIME()

SYSDATETIMEOFFSET()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

Which functions return the current date and time in UTC terms?

In what format do they give you the date and time?

A

GETUTCDATE() and SYSUTCDATETIME()

DATETIME and DATETIME2, respectively.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

How do you get only the current time or only the current date?

A

Use cast on the SYSDATETIME function to DATE or TIME

CAST(SYSDATETIME() as DATE)

41
Q

Which 2 functions allow you to extract a date or time part from a date?

Syntax for extract year from ‘01/02/2019’

What’s the difference between the 2 functions?

A

DATEPART and DATENAME

DATEPART(year, ‘01/02/2019’)

DATEPART returns integers and DATENAME returns strings.

Note: T-SQL provides YEAR, MONTH, and DAY functions as abbreviations to DATEPART.

42
Q

What are six functions that construct a desired date and time value from its numeric parts.

Syntax for function with DATE format

A

DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, SMALLDATETIMEFROMPARTS, TIMEFROMPARTS.

DATEFROMPARTS(year, month, day)

43
Q

Which function calculates the end of month date for the input date?

Syntax for function

A

EOMONTH(‘01/02/2015’)

44
Q

Which function can you use to add a specified number of date units to a date and time value?

Syntax

A

DATEADD(year, 1, ‘20170212’) adds one year to the date.

45
Q

Which function returns the difference in terms of a requested part between two date and time values?

Syntax

If we were looking for the difference in years between 01/03/2015 and 03/03/2016, would we get a decimal value?

A

DATEDIFF(day, ‘20150212’, ‘20160212’)

No, this function only looks at the requested part and above in the date and time hierarchy

46
Q

What should you use instead of DATEDIFF if the difference doesn’t fit in a four-byte integer?

A

Use DATEDIFF_BIG instead. It returns the result as a BIGINT type.

47
Q

SWITCHOFFSET function syntax

What does it do?

A

SWITCHOFFSET(SYSDATETIMEOFFSET(), ‘-08:00’)

It takes a DATETIMEOFFSET value and adjusts it to a requested offset. For example, if the system’s offset is ‘-05:00’, the function compensates for this by subtracting 3 hours from the input value.

48
Q

TODATETIMEOFFSET function syntax

What does it do?

A

TODATETIMEOFFSET(‘DATETIME2 value’, ‘-08:00’)

It takes a DATETIME2 value and translates it into a DATETIMEOFFSET value. It basically puts the datetime and offset together.

49
Q

Which T-SQL function can you use instead of SWITCHOFFSET and TODATETIMEOFFSET?

Function syntax

What does it do?

A

inputdate AT TIME ZONE timezone

e.g. SYSDATETIME() AT TIME ZONE ‘Pacific Standard Time’

It works 2 ways. When the input is of a DATETIMEOFFSET type, it works like SWITCHOFFSET. When the input is not an offset-aware value, it works like TODATETIMEOFFSET.

50
Q

What are two ways to concatenate a string?

What is the difference between them?

A

Can use + operator or CONCAT function.

eg. 
SELECT country + N', ' + region + N', ' + city AS location
SELECT CONCAT('US', ' ', 'TX', ' ', 'Dallas')

When any input is NULL, the default behavior of the + operator is to return a NULL. The CONCAT function substitutes NULLs with empty strings.

51
Q

Which function allows you to extract a substring from a string?

Syntax

A

SUBSTRING(‘string’, start, length)

where start is index of the starting position and length is the length of the string you want extracted.

52
Q

Which functions allow you to extract a requested number of characters from the left and right ends of the input string?

Syntax

A

LEFT and RIGHT functions

LEFT(‘string’, num)
where num is the number of characters to extract.

53
Q

What does CHARINDEX do?

Syntax

A

It returns the position of the first occurrence of a substring within a string.

CHARINDEX(‘substring’, ‘stringtosearch’)

54
Q

What does PATINDEX do?

Syntax

A

It locates the first position of a pattern within a string.

PATINDEX(‘pattern’, ‘stringtosearch’)

55
Q

What are two functions that measure the length of a string?

Syntax

What’s the difference between them?

A

LEN and DATALENGTH

LEN(‘string’)

LEN returns length in terms of the number of characters, and it removes trailing spaces. DATALENGTH returns length in terms of number of bytes, and it does not remove trailing spaces.

Note: for unicode characters, DATALENGTH counts 2 bytes per character/space. Regular characters are 1 byte.

56
Q

What does the REPLACE function do?

Syntax

A

Within the string provided as the first argument, it replaces all occurrences of string provided in second argument with the string provided as the third argument

REPLACE(‘stringtosearch’, ‘substringtoreplace’, ‘replacement’)

57
Q

Which function allows you to replicate an input string a certain number of times?

Syntax

A

REPLICATE(‘string’, num)

where num is number of times to replicate

58
Q

What does the STUFF function do?

Syntax

A

Searches string provided in first argument. Then, from the character position indicated as the second argument, deletes the number of characters indicated by the third argument. Then it inserts in that position the string specified as the fourth argument.

STUFF(‘stringtosearch’, start, num, ‘stringtoinsert’)

59
Q

Which functions remove leading or trailing spaces?

How do you remove both leading and trailing spaces?

A

LTRIM and RTRIM

RTRIM(LTRIM(‘string’))

60
Q

What table-valued function can be used to split strings?

Syntax

What does it return?

A

STRING_SPLIT(‘stringtosplit’, ‘delimiter’)
where the stringtosplit will be split at the delimiter.

It returns a column named “value” with the split strings.

61
Q

Syntax for simple form of CASE expression.

Syntax for searched form of CASE expression.

A 
SELECT col1, col2, 
    CASE col1
        WHEN expression THEN expression
        WHEN expression THEN expression
        ELSE expression
    END AS newcol
FROM table;
SELECT col1, col2,
    CASE 
        WHEN predicate THEN expression
        WHEN predicate THEN expression
        ELSE expression
    END AS newcol
FROM table;

predicate could be col1 < 20

Note: The first predicate that evaluates to true determines which when expression is returned.

62
Q

What does the COALESCE function do?

Which function is it similar to? Which one is standard and which one is non-standard?

3 differences between these functions

A

It accepts a list of expressions as input and returns the first that is not NULL, or NULL if all are NULLs. Can use it to substitute a NULL with something else, e.g. COALESCE(region, ‘ ‘) returns region if not NULL and returns an empty string if NULL.

It’s similar to ISNULL function. COALESCE is standard, and ISNULL is T-SQL.

Differences:

  1. ISNULL can only take 2 inputs
  2. The type of output for COALESCE is determined by the returned element, but for ISNULL, it’s determined by the first input
  3. When you use the SELECT INTO statement, if col1 is defined as NOT NULL, both COALESCE(col1, 0) and ISNULL(col1, 0) will produce output defined as NOT NULL. If col1 is defined as allowing NULLs, COALESCE will create output that allows NULLs, but ISNULL will create one that disallows NULLs.
63
Q

How does the NULLIF function work?

A

It accepts 2 input expressions. If they are equal, it returns NULL. Returns the first input if not equal.

64
Q

What does the @@ROWCOUNT function do?

What if the row count exceeds the max INT value?

A

It returns the number of rows affected by the last statement that you executed.

If row count exceeds the max INT value, use ROWCOUNT_BIG function.

65
Q

In what format is the result of the COMPRESS function?

A

The result is a binary string

66
Q

What does the NEWID() function do?

A

It can be used to generate a key that is globally unique as a UNIQUEIDENTIFIER typed value

67
Q

What does the NEWSEQUENTIALID system function do?

A

Generates GUIDs that always increase within the machine

68
Q

What is a search argument, or SARG?

What are two requirements of a sargable filter?

A

It is a filter predicate that enables the optimizer to rely on index order.

Such a filter is sargable if:

  1. You don’t apply manipulation to the filtered columns
  2. The operator identifies a consecutive range of qualifying rows in the index.
69
Q

What are the 3 main categories of function determinism?

A
  1. Functions that are always deterministic, i.e. the function is guaranteed to return the same result given the same set of input values, e.g. all string functions, COALESCE, ISNULL, ABS, SQRT
  2. Functions that are deterministic or not depending on how they are used, e.g. CAST function might return different values when converting from a string to a date based on the login’s language. Also, CONVERT, RAND (seed vs no seed).
  3. Functions that are always nondeterministic, e.g. SYSDATETIME and NEWID.
70
Q

Most nondeterministic functions are invoked once per query, what is an exception?

A

NEWID gets invoked once per row

71
Q

Suppose you want to return a random set of 3 employees, and you use the following query:

SELECT TOP (3) empid, firstname, lastname
FROM HR.Employees
ORDER BY RAND();

What is the problem with this query? How would you solve it?

A

If you run this code repeatedly, you would keep getting the same result because the RAND function returns the same value in all rows.

Solution:

SELECT TOP (3) empid, firstname, lastname
FROM HR.Employees
ORDER BY CHECKSUM(NEWID());

72
Q

When creating a table, what does IDENTITY do?

orderid INT NOT NULL IDENTITY(1, 1)

A

This is the identity property with a seed 1 and an increment 1. It will generate the values in this column automatically when rows are inserted.

73
Q

Syntax for inserting values into a table with INSERT INTO

What happens if you don’t specify a value for a column?

A

INSERT INTO table(col1, col2, col3) VALUES
(val1, val2, val3),
(val4, val5, val6),
(val7, val8, val9);

If you don’t specify a value for a column, SQL Server first checks whether the column gets its value automatically, e.g. from an identity property or a default constraint. If that’s not the case, it checks whether the column allows NULLs, in which case it assumes a NULL. If that’s not the case, SQL Server generates an error.

74
Q

Syntax for inserting values into a table with INSERT SELECT statement

A

INSERT INTO table(col1, col2, col3)
SELECT col1, col2, col3
FROM othertable
WHERE condition;

This inserts the result of the query into the table.

75
Q

How do you insert your own values into a column with an identity property?

A

SET IDENTITY_INSERT tablename ON;

INSERT ….

SET IDENTITY_INSERT tablename OFF;

76
Q

INSERT EXEC helps you insert the result set of what into a table?

Syntax

A

It helps you insert the result set returned by a dynamic batch or a stored procedure.

INSERT INTO table(col1, col2, col3)
EXEC procedurename
@variable = expression

where the procedure is something like:

CREATE PROC procedurename
@variable AS NVARCHAR(15)
AS

SELECT col1, col2, col3
FROM othertable
WHERE col3 = @variable;
GO

77
Q

Syntax for creating a new table and populate it with values using SELECT INTO statement.

Which things are copied from the original table. Which things are not?

A

SELECT col1, col2, col3
INTO NewTable
FROM OtherTable
WHERE condition

Things copied: column names, types, nullability, and identity property

Things not copied: indexes, constraints, triggers, permissions, and others.

78
Q

UPDATE statement syntax

Does standard SQL or T-SQL support using joins in UPDATE statements?

A

UPDATE target-table
SET col1 = expression
WHERE predicate;

Only T-SQL supports using joins in UPDATE statements

79
Q

What are two ways to delete rows from a table?

Syntax for each one

How are they different?

A

DELETE and TRUNCATE TABLE

DELETE FROM tablename
WHERE predicate;

TRUNCATE TABLE tablename;

TRUNCATE TABLE doesn’t support a filter. It also uses an optimized logging mode that makes it way faster than DELETE.

Use TRUNCATE TABLE when you need to delete all rows from a table or a partition but leave the table definition in place.

80
Q

Syntax for DELETE statement when using a join

A 
DELETE FROM alias1
FROM table1 AS alias1
    INNER JOIN table2 AS alias2
        ON alias1.col1 = alias2.col1
WHERE predicate;
81
Q

MERGE statement - what does it do and syntax

A

It allows you to merge data from a source table into a target table

MERGE INTO targettable as TGT
USING sourcetable AS SRC
    ON mergepredicate
WHEN MATCHED (AND predicate)
    THEN action*
WHEN NOT MATCHED (BY TARGET) (AND predicate)
    THEN INSERT ...
WHEN NOT MATCHED BY SOURCE (AND predicate)
    THEN action*

*action can be either UPDATE or DELETE. Can repeat clause to use one with UPDATE and another with DELETE.

82
Q

INSERT with OUTPUT

Syntax when you just want to return info

Syntax when you want to store the result in a table

A 
INSERT INTO tablename(col1, col2, col3)
    OUTPUT
        inserted.col1, inserted.col2, inserted.col3
    SELECT col1, col2, col3
    FROM anothertable
    WHERE predicate;

To store result in a table:

OUTPUT
    inserted.col1, inserted.col2, inserted.col3
    INTO sometable(col1, col2, col3)
83
Q

DELETE with OUTPUT syntax

A

DELETE FROM tablename
OUTPUT deleted.col1
WHERE empid=1;

84
Q

UPDATE with OUTPUT syntax

A 
UPDATE tablename
    SET col2 = expression
    OUTPUT
        inserted.col1, deleted.col2, inserted.col2
WHERE predicate;

Note: can use inserted or deleted to prefix col names with an UPDATE statement

85
Q

What does the $action function do? Which statement would you use it with?

A

It returns a string (insert, update, or delete) indicating the action on a row. You can use in with a MERGE statement.

86
Q

Where would OUTPUT go relative to a MERGE statement?

Use the $action function and OUTPUT to return the ID of an affected row and how it was affected.

A

After the MERGE statement

OUTPUT
$action AS the_action
COALESCE(inserted.id, deleted.id) as ID;

Note: Rows affected by an INSERT action have values in the inserted row and NULLs in the deleted row. Rows affected by a DELETE action have NULLs in the inserted row and values in the deleted row. Rows affected by an UPDATE action have values in both. So using coalesce helps you return the id of the affected row.

87
Q

Considering the INSERT, UPDATE, DELETE, and MERGE statements, in which ones can you refer to columns from both the target and the source tables in the OUTPUT clause?

Suppose you want the OUTPUT clause to return the both the source and target keys from a multi-row insert. How would you achieve this?

A

You can only refer to source table columns in the MERGE statement.

You would need to use the MERGE statement instead of INSERT. Because an INSERT action is only allowed when the merge predicate is false, simply use a condition that is always false e.g.

MERGE INTO targettable AS TGT
USING sourcetable AS SRC
ON 1 = 2
WHEN NOT MATCHED THEN
INSERT(col1, col2, col3) VALUES(col1, col2, col3)
OUTPUT
SRC.key AS srckey, inserted.key AS tgtkey,
inserted.col1, inserted.col2, inserted.col3

88
Q

What is the following query doing? What is it an example of?

INSERT INTO sometable(orderid, custid, empid, orderdate)
SELECT orderid, custid, empid, orderdate
FROM (MERGE INTO Sales.MyOrders AS TGT
USING sourcetable as SRC
ON SRC.orderid = TGT.orderid
WHEN MATCHED AND EXISTS( SELECT SRC.* EXCEPT SELECT TGT.* ) THEN
UPDATE SET TGT.custid = SRC.custid
TGT.empi = SRC.empid
TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN
INSERT VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT
$action AS the_action, inserted.*) AS D
WHERE the_action = ‘INSERT’;

A

This is an example of nested DML. With the MERGE and OUTPUT clause, you’re creating something that looks like a derived table. Then the INSERT SELECT statement is filtering the output rows from the derived table.

Basically use this when want to capture the output from a modification statement, but you’re only interested in a subset of the output rows.

89
Q

How do you add a column to a table?

Can you add a column that doesn’t allow NULLs to any table?

A

ALTER TABLE tablename
ADD newcol datatype
(CONSTRAINT) (WITH VALUES)

No, table must be empty to add column that doesn’t allow nulls, or the column must get values automatically.

Note: WITH VALUES is used when want to apply a default constraint to new rows and column defined as nullable.

90
Q

How do you drop a column?

When does dropping a column fail?

A

ALTER TABLE tablename
DROP col1;

It fails when the column is used in an index, is used in a default, check, foreign key, unique, or primary key constraint, or is bound to a default object or a rule

91
Q

How do you alter a column?

A

ALTER TABLE tablename

ALTER COLUMN col1 datatype;

92
Q

How do you add a default constraint to an existing column in a table?

How do you add a new column with a default constraint to an existing table?

How do you drop a constraint?

A

ALTER TABLE tablename
ADD CONSTRAINT DF_tablename_col1
DEFAULT defaultvalue FOR col1

ALTER TABLE tablename
ADD newcol datatype NULL/NOT NULL
CONSTRAINT DF_tablename_newcol DEFAULT defaultvalue

ALTER TABLE tablename
DROP constraint constraintname

93
Q

What is a CHECK constraint?

Syntax to add a check constraint

A

CHECK constraint is used to limit the range of values that can be entered for a column.

ALTER TABLE tablename
ADD CONSTRAINT CK_tablename_col CHECK (boolean expression with col)

94
Q

What is an identity column?

Syntax to create a table with an identity column

How do you explicitly provide a value for an identity column?

If you have deleted all the rows in a table, how do you reset the identity column so that it starts at the beginning value again?

A

An identity column has its value automatically generated.

CREATE TABLE tablename
(col1 datatype IDENTITY(startnum, incrementby) PRIMARY KEY,
col2…)

To explicitly provide a value for an identity column, you turn on identity insert:
SET IDENTITY_INSERT tablename ON
And in the insert query, make sure to specify the column list.

To reset identity column: 
DBCC CHECKIDENT('tablename', RESEED, 0)
95
Q

Syntax for adding a unique key constraint on an existing table

A

ALTER TABLE tablename

ADD CONSTRAINT UQ_tablename_col UNIQUE (col)

96
Q

Create a new, empty table having the same set of columns as an existing table

A

SELECT *
INTO newtable
FROM oldtable
WHERE 1 = 0

Use the INTO clause with a WHERE clause so that it returns no rows.

97
Q

True or False: If you insert rows into a view, the rows will also be inserted into the underlying table

A

TRUE

98
Q

How do you embed a quote mark within string literals? e.g. Susan’s

A

Use 2 quotes:

‘Susan’’s’

SQL 70-761 (3 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions