EXAM Flashcards
What is an IDENTITY clause?
Tells SQL Server to autogenerate numbers
What is the IDENTITY clause syntax?
IDENTITY(start_num, increment_num) ex IDENTITY(1, 1) starts at 1 and increments by 1
What are 3 ways the UPDATE statement is used?
- Update multiple columns on one row
- Update one column on multiple rows
- Update multiple columns on multiple rows
What are the ACID principles?
Atomicity
Consistency
Isolation
Durability
What is meant by atomicity?
All or nothing
What do you use transactions?
When doing multiple updates to a database
What is T-SQL?
Transact SQL
How do you see open transactions?
@@TRANSCOUNT
What kind of join returns results if there is NULL data?
Outer Join
What is a LEFT OUTER JOIN?
If the first table listed has no corresponding rows on the second table listed, display NULL
What is the syntax for an OUTER join?
LEFT or RIGHT [OUTER] JOIN
[OUTER] is optional
What is the syntax for SUBSTRING?
SUBSTRING(string, start pos, length)
What does CHARINDEX return?
An integer
What is the syntax for CHARINDEX?
CHARINDEX(string to find, string, [start position])
What is the syntax for REPLACE?
REPLACE(string, old string, new string)
What is the LEN function?
Length, returns the length of a String
How do you substitute a NULL in a SELECT statement?
ISNULL(column, ‘replacement string’)
What is the syntax to format currency?
FORMAT(column, ‘C’, ‘en-us)
What is the syntax for formatting dates?
FORMAT(column, ‘MMM-dd-yy’)
What are the “Fab 5” aggregate functions?
- SUM
- AVG
- COUNT
- MAX
- MIN
What must be included if you are selecting multiple columns and using an aggregate function?
GROUP BY clause
What is like the WHERE clause for aggregate functions?
HAVING
What type of function is best if you have multiple columns with an aggregate function?
Window Functions
What is the syntax of a window function?
FUNCTION( ) OVER (PARTITION BY column)
What is a sub-window called?
A Partition
How do you specify you want the 2 results with the largest return?
TOP 2
What is a scalar sub-query?
A subquery that returns only one result
What must be included for a multiple-row sub-query?
IN, ANY, ALL or EXISTS operator
What is a view?
Show a results table that looks like a real table
What are the 2 purposes of using a view?
- Simplify having to issue complex queries
2. Restrict access to certain columns
What is the preferred prefix for user defined procedures?
usp_
How do you declare variables?
DECLARE @variablename datatype = initial_value
How do you run a stored procedure?
EXEC usp_name [ARGUMENT]
What is a cursor?
An object that traverses over the rows of a result set, one at a time
What is the syntax for declaring a cursor?
DECLARE c_cursorname CURSOR FOR
SELECT …;
What 2 statements are needed at the end of the cursor loop?
CLOSE c_cursorname
DEALLOCATE c_cursorname
What is the loop continuation condition for a cursor?
WHILE @@FETCH_STATUS = 0
What is the syntax for a cursor to fetch the next line of data?
FETCH NEXT FROM c_cursorname INTO @variable
What keywords are used with loops and conditionals (similar to a block of code)?
BEGIN, END
FUNCTION/PROCEDURE: Do not need it to return a value
Procedure
FUNCTION/PROCEDURE: Cannot support output parameters
Function
FUNCTION/PROCEDURE: Can use transactions
Procedure
FUNCTION/PROCEDURE: Cannot be called from a SELECT statement.
Procedure
What line of code comes after defining a function?
RETURNS statement
RETURNS DATATYPE AS
How do you call a function?
dbo.ufn_functionname
What is the syntax of a TRY/CATCH?
BEGIN TRY
END TRY
BEGIN CATCH
END CATCH
How do you throw an exception?
; THROW 50000+, ‘Error Message’
How do you specify a return parameter?
@variable DATATYPE OUTPUT
What is the syntax for a case?
(CASE WHEN condition THEN output WHEN condition THEN output ELSE output END) "column name"
