Brainscape's Knowledge GenomeTM

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


See full index

Database Design > Advanced SQL > Flashcards

Advanced SQL Flashcards

1
Q

Set

A

each table in a database or the result of a query

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

Empty set

A

query that returns no results

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

Member

A

row within a set

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

3 common set operators

A
  1. Intersection (INTERSECT)
  2. Difference (EXCEPT)
  3. Union
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

How to join tables?

A

with ON or USING

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

Embedded Select is also called

A

Derived table, cannot use order by, requires an alias

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

What is a correlated sub query?

A

Sub query that references a parent column

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

What types of fields can you join on?

A

Character, number or dates

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

What is a row subquery?

A

embedded select that returns more than 1 column and 0-1 rows

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

What is a table subquery?

A

returns 1 or more columns and 0 or more rows

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

What is a scalar subquery?

A

returns 1 column and 0-1 rows

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

What are the special predicates for sub queries?

A

IN (membership), ALL/ANY/SOME (quantified), EXISTS (existance)

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

How would you use a quantified predicate?

A

WHERE expression (=, , etc) [ANY, ALL, SOME] (subquery)

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

What are the SQL aggregate functions?

A

MIN, MAX, AVG, SUM, COUNT, COUNT distinct

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

Do aggregate functions ignore NULL values?

A

Yes except for COUNT(*)

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

What will an aggregate function return if all values are NULL?

A

NULL

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

T/F: You can embed one aggregate function with another

A

FALSE

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

T/F: you can use an aggregate function in a WHERE clause

A

TRUE, if you use the function in a subquery

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

How do you update a record?

A

UPDATE table_name
SET column_name = value
WHERE condition

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

How do you insert a record?

A

INSERT INTO table_name
(column names)
VALUES (values list)

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

How do you delete a record?

A 
DELETE FROM table_name
WHERE exp (where is optional)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What is a stored program?

A

1 or more SQL statements stored in DB for later use.

23
Q

What is procedural code?

A

Style of programming that contains procedures/functions completed by software program. Controls the flow of execution

24
Q

What are controlled constructs?

A

Used for selection (if-else, switch, case) and looping (while, do-while, for)

25
Q

What are exception handling?

A

Managing known error conditions (try, catch blocks)

26
Q

What are SQL control flows?

A

IF…ELSEIF…ELSE

CASE…WHEN…ELSE

27
Q

What are SQL repeat conditions?

A

WHILE…DO…LOOP

REPEAT…UNTIL…END REPEAT

28
Q

What is a cursor?

A

Used to work with data in a result set 1 row at a time (like streaming)

29
Q

What is a handler?

A

Error handling (condition handler). Syntax is DECLARE [continue or exit] HANDLER FOR [error code]

30
Q

What are the 3 built in named conditions for error codes?

A
  1. NOT FOUND, 2. SQLEXCEPTION, 3. SQLWARNING
31
Q

How do you use outbound parameters?

A

In sproc, specify OUT param_name. When call procedure, use @var_name to store the out parameter

32
Q

How do you delete a stored procedure?

A

DROP PROCEDURE [IF EXISTS] proc_name

33
Q

T/F: a function can update, delete or insert data

A

FALSE

34
Q

What is a trigger?

A

Named block of code that executes automatically when insert, update, or delete happens. Ex: database logs.

35
Q

T/F: a trigger can be modified

A

FALSE

36
Q

T/F: a trigger cannot be disabled

A

TRUE

37
Q

What is an event?

A

Block of code that runs according to event scheduler. By default event scheduler is off

38
Q

How do you turn the event scheduler on?

A

SET event_scheduler = [ON or OFF]

39
Q

T/F: turning the event_scheduler on will be permanent

A

FALSE

40
Q

What is a transaction?

A

Group of SQL statements that combine into single logical unit of work

41
Q

How do you start a transaction?

A

START TRANSACTION
COMMIT (perm changes DB) OR
ROLLBACK (cancels changes)

42
Q

When would you use a transaction?

A
  1. when using 2 or more insert/update/delete statements
  2. moving rows from 1 table to another
  3. when failure of a change statement would violate data integrity
43
Q

What is a savepoint?

A

Used to rollback transaction to beginning or at a specific save point. SAVEPOINT name
ROLLBACK TO SAVEPOINT name

44
Q

What is concurrency?

A

When 2 or more users have access to the same database and are trying to update/change data at the same time

45
Q

What are the 4 problems that result from concurrency?

A
  1. Lost updates (changes over write)
  2. Dirty Reads (select without latest updates)
  3. Non repeatable reads
  4. Phantom reads
46
Q

How do you prevent concurrency issues?

A

Setting/changing the transaction isolation level

47
Q

What is the default transaction isolation level?

A

REPEATABLE READ (allows inserts, phantom reads can still occur)

48
Q

What concurrency problems are allowed with READ UNCOMMITTED?

A

All errors allows, limited lock

49
Q

What concurrency problems are allowed with READ COMMITTED?

A

prevents lost updates, can still get dirty reads, non repeatable or phantom reads

50
Q

What concurrency problems are allowed with REPEATABLE READ?

A

All problems prevented except phantom reads

51
Q

What concurrency problems are allowed with SERIALIZATION?

A

all problems are prevented, most secure level

52
Q

What is a deadlock?

A

Occurs with neither transaction can be committed because each has a lock needed by the other transaction

53
Q

What are the 4 ways to prevent a deadlock?

A
  1. don’t allow transactions to stay open long
  2. don’t use a transaction isolation level higher than necessary
  3. make large changes at slow times
  4. consider locking when coding transactions

Database Design (1 decks)

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