EXAM

Question 1

What is an IDENTITY clause?

Answer 1

Tells SQL Server to autogenerate numbers

Question 2

What is the IDENTITY clause syntax?

Answer 2

IDENTITY(start_num, increment_num)
ex IDENTITY(1, 1) starts at 1 and increments by 1

Question 3

What are 3 ways the UPDATE statement is used?

Answer 3

1. Update multiple columns on one row
2. Update one column on multiple rows
3. Update multiple columns on multiple rows

Question 4

What are the ACID principles?

Answer 4

Atomicity
Consistency
Isolation
Durability

Question 5

What is meant by atomicity?

Answer 5

All or nothing

Question 6

What do you use transactions?

Answer 6

When doing multiple updates to a database

Question 7

What is T-SQL?

Answer 7

Transact SQL

Question 8

How do you see open transactions?

Answer 8

@@TRANSCOUNT

Question 9

What kind of join returns results if there is NULL data?

Answer 9

Outer Join

Question 10

What is a LEFT OUTER JOIN?

Answer 10

If the first table listed has no corresponding rows on the second table listed, display NULL

Question 11

What is the syntax for an OUTER join?

Answer 11

LEFT or RIGHT [OUTER] JOIN

[OUTER] is optional

Question 12

What is the syntax for SUBSTRING?

Answer 12

SUBSTRING(string, start pos, length)

Question 13

What does CHARINDEX return?

Answer 13

An integer

Question 14

What is the syntax for CHARINDEX?

Answer 14

CHARINDEX(string to find, string, [start position])

Question 15

What is the syntax for REPLACE?

Answer 15

REPLACE(string, old string, new string)

Question 16

What is the LEN function?

Answer 16

Length, returns the length of a String

Question 17

How do you substitute a NULL in a SELECT statement?

Answer 17

ISNULL(column, ‘replacement string’)

Question 18

What is the syntax to format currency?

Answer 18

FORMAT(column, ‘C’, ‘en-us)

Question 19

What is the syntax for formatting dates?

Answer 19

FORMAT(column, ‘MMM-dd-yy’)

Question 20

What are the “Fab 5” aggregate functions?

Answer 20

1. SUM
2. AVG
3. COUNT
4. MAX
5. MIN

Question 21

What must be included if you are selecting multiple columns and using an aggregate function?

Answer 21

GROUP BY clause

Question 22

What is like the WHERE clause for aggregate functions?

Answer 22

HAVING

Question 23

What type of function is best if you have multiple columns with an aggregate function?

Answer 23

Window Functions

Question 24

What is the syntax of a window function?

Answer 24

FUNCTION( ) OVER (PARTITION BY column)

Question 25

What is a sub-window called?

Answer 25

A Partition

Question 26

How do you specify you want the 2 results with the largest return?

Answer 26

TOP 2

Question 27

What is a scalar sub-query?

Answer 27

A subquery that returns only one result

Question 28

What must be included for a multiple-row sub-query?

Answer 28

IN, ANY, ALL or EXISTS operator

Question 29

What is a view?

Answer 29

Show a results table that looks like a real table

Question 30

What are the 2 purposes of using a view?

Answer 30

1. Simplify having to issue complex queries | 2. Restrict access to certain columns

Question 31

What is the preferred prefix for user defined procedures?

Answer 31

usp_

Question 32

How do you declare variables?

Answer 32

DECLARE @variablename datatype = initial_value

Question 33

How do you run a stored procedure?

Answer 33

EXEC usp_name [ARGUMENT]

Question 34

What is a cursor?

Answer 34

An object that traverses over the rows of a result set, one at a time

Question 35

What is the syntax for declaring a cursor?

Answer 35

DECLARE c_cursorname CURSOR FOR | SELECT ...;

Question 36

What 2 statements are needed at the end of the cursor loop?

Answer 36

CLOSE c_cursorname | DEALLOCATE c_cursorname

Question 37

What is the loop continuation condition for a cursor?

Answer 37

WHILE @@FETCH_STATUS = 0

Question 38

What is the syntax for a cursor to fetch the next line of data?

Answer 38

FETCH NEXT FROM c_cursorname INTO @variable

Question 39

What keywords are used with loops and conditionals (similar to a block of code)?

Answer 39

BEGIN, END

Question 40

FUNCTION/PROCEDURE: Do not need it to return a value

Answer 40

Procedure

Question 41

FUNCTION/PROCEDURE: Cannot support output parameters

Answer 41

Function

Question 42

FUNCTION/PROCEDURE: Can use transactions

Answer 42

Procedure

Question 43

FUNCTION/PROCEDURE: Cannot be called from a SELECT statement.

Answer 43

Procedure

Question 44

What line of code comes after defining a function?

Answer 44

RETURNS statement | RETURNS DATATYPE AS

Question 45

How do you call a function?

Answer 45

dbo.ufn_functionname

Question 46

What is the syntax of a TRY/CATCH?

Answer 46

BEGIN TRY END TRY BEGIN CATCH END CATCH

Question 47

How do you throw an exception?

Answer 47

; THROW 50000+, 'Error Message'

Question 48

How do you specify a return parameter?

Answer 48

@variable DATATYPE OUTPUT

Question 49

What is the syntax for a case?

Answer 49

``` (CASE WHEN condition THEN output WHEN condition THEN output ELSE output END) "column name" ```