Brainscape's Knowledge GenomeTM

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


See full index

Databases > EXAM > Flashcards

EXAM Flashcards

1
Q

What is an IDENTITY clause?

A

Tells SQL Server to autogenerate numbers

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

What is the IDENTITY clause syntax?

A 
IDENTITY(start_num, increment_num)
ex IDENTITY(1, 1) starts at 1 and increments by 1
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What are 3 ways the UPDATE statement is used?

A
  1. Update multiple columns on one row
  2. Update one column on multiple rows
  3. Update multiple columns on multiple rows
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What are the ACID principles?

A

Atomicity
Consistency
Isolation
Durability

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

What is meant by atomicity?

A

All or nothing

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

What do you use transactions?

A

When doing multiple updates to a database

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

What is T-SQL?

A

Transact SQL

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

How do you see open transactions?

A

@@TRANSCOUNT

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

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

A

Outer Join

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

What is a LEFT OUTER JOIN?

A

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

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

What is the syntax for an OUTER join?

A

LEFT or RIGHT [OUTER] JOIN

[OUTER] is optional

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

What is the syntax for SUBSTRING?

A

SUBSTRING(string, start pos, length)

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

What does CHARINDEX return?

A

An integer

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

What is the syntax for CHARINDEX?

A

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

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

What is the syntax for REPLACE?

A

REPLACE(string, old string, new string)

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

What is the LEN function?

A

Length, returns the length of a String

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

How do you substitute a NULL in a SELECT statement?

A

ISNULL(column, ‘replacement string’)

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

What is the syntax to format currency?

A

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

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

What is the syntax for formatting dates?

A

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

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

What are the “Fab 5” aggregate functions?

A
  1. SUM
  2. AVG
  3. COUNT
  4. MAX
  5. MIN
21
Q

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

A

GROUP BY clause

22
Q

What is like the WHERE clause for aggregate functions?

A

HAVING

23
Q

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

A

Window Functions

24
Q

What is the syntax of a window function?

A

FUNCTION( ) OVER (PARTITION BY column)

25
Q

What is a sub-window called?

A

A Partition

26
Q

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

A

TOP 2

27
Q

What is a scalar sub-query?

A

A subquery that returns only one result

28
Q

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

A

IN, ANY, ALL or EXISTS operator

29
Q

What is a view?

A

Show a results table that looks like a real table

30
Q

What are the 2 purposes of using a view?

A
  1. Simplify having to issue complex queries

2. Restrict access to certain columns

31
Q

What is the preferred prefix for user defined procedures?

A

usp_

32
Q

How do you declare variables?

A

DECLARE @variablename datatype = initial_value

33
Q

How do you run a stored procedure?

A

EXEC usp_name [ARGUMENT]

34
Q

What is a cursor?

A

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

35
Q

What is the syntax for declaring a cursor?

A

DECLARE c_cursorname CURSOR FOR

SELECT …;

36
Q

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

A

CLOSE c_cursorname

DEALLOCATE c_cursorname

37
Q

What is the loop continuation condition for a cursor?

A

WHILE @@FETCH_STATUS = 0

38
Q

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

A

FETCH NEXT FROM c_cursorname INTO @variable

39
Q

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

A

BEGIN, END

40
Q

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

A

Procedure

41
Q

FUNCTION/PROCEDURE: Cannot support output parameters

A

Function

42
Q

FUNCTION/PROCEDURE: Can use transactions

A

Procedure

43
Q

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

A

Procedure

44
Q

What line of code comes after defining a function?

A

RETURNS statement

RETURNS DATATYPE AS

45
Q

How do you call a function?

A

dbo.ufn_functionname

46
Q

What is the syntax of a TRY/CATCH?

A

BEGIN TRY
END TRY
BEGIN CATCH
END CATCH

47
Q

How do you throw an exception?

A

; THROW 50000+, ‘Error Message’

48
Q

How do you specify a return parameter?

A

@variable DATATYPE OUTPUT

49
Q

What is the syntax for a case?

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

Databases (4 decks)

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