Advanced SQL

Question 1

Set

Answer 1

each table in a database or the result of a query

Question 2

Empty set

Answer 2

query that returns no results

Question 3

Member

Answer 3

row within a set

Question 4

3 common set operators

Answer 4

1. Intersection (INTERSECT)
2. Difference (EXCEPT)
3. Union

Question 5

How to join tables?

Answer 5

with ON or USING

Question 6

Embedded Select is also called

Answer 6

Derived table, cannot use order by, requires an alias

Question 7

What is a correlated sub query?

Answer 7

Sub query that references a parent column

Question 8

What types of fields can you join on?

Answer 8

Character, number or dates

Question 9

What is a row subquery?

Answer 9

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

Question 10

What is a table subquery?

Answer 10

returns 1 or more columns and 0 or more rows

Question 11

What is a scalar subquery?

Answer 11

returns 1 column and 0-1 rows

Question 12

What are the special predicates for sub queries?

Answer 12

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

Question 13

How would you use a quantified predicate?

Answer 13

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

Question 14

What are the SQL aggregate functions?

Answer 14

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

Question 15

Do aggregate functions ignore NULL values?

Answer 15

Yes except for COUNT(*)

Question 16

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

Answer 16

NULL

Question 17

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

Answer 17

FALSE

Question 18

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

Answer 18

TRUE, if you use the function in a subquery

Question 19

How do you update a record?

Answer 19

UPDATE table_name
SET column_name = value
WHERE condition

Question 20

How do you insert a record?

Answer 20

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

Question 21

How do you delete a record?

Answer 21

DELETE FROM table_name
WHERE exp (where is optional)

Question 22

What is a stored program?

Answer 22

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

Question 23

What is procedural code?

Answer 23

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

Question 24

What are controlled constructs?

Answer 24

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

Question 25

What are exception handling?

Answer 25

Managing known error conditions (try, catch blocks)

Question 26

What are SQL control flows?

Answer 26

IF...ELSEIF...ELSE | CASE...WHEN...ELSE

Question 27

What are SQL repeat conditions?

Answer 27

WHILE...DO...LOOP | REPEAT...UNTIL...END REPEAT

Question 28

What is a cursor?

Answer 28

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

Question 29

What is a handler?

Answer 29

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

Question 30

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

Answer 30

1. NOT FOUND, 2. SQLEXCEPTION, 3. SQLWARNING

Question 31

How do you use outbound parameters?

Answer 31

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

Question 32

How do you delete a stored procedure?

Answer 32

DROP PROCEDURE [IF EXISTS] proc_name

Question 33

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

Answer 33

FALSE

Question 34

What is a trigger?

Answer 34

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

Question 35

T/F: a trigger can be modified

Answer 35

FALSE

Question 36

T/F: a trigger cannot be disabled

Answer 36

TRUE

Question 37

What is an event?

Answer 37

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

Question 38

How do you turn the event scheduler on?

Answer 38

SET event_scheduler = [ON or OFF]

Question 39

T/F: turning the event_scheduler on will be permanent

Answer 39

FALSE

Question 40

What is a transaction?

Answer 40

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

Question 41

How do you start a transaction?

Answer 41

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

Question 42

When would you use a transaction?

Answer 42

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

Question 43

What is a savepoint?

Answer 43

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

Question 44

What is concurrency?

Answer 44

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

Question 45

What are the 4 problems that result from concurrency?

Answer 45

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

Question 46

How do you prevent concurrency issues?

Answer 46

Setting/changing the transaction isolation level

Question 47

What is the default transaction isolation level?

Answer 47

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

Question 48

What concurrency problems are allowed with READ UNCOMMITTED?

Answer 48

All errors allows, limited lock

Question 49

What concurrency problems are allowed with READ COMMITTED?

Answer 49

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

Question 50

What concurrency problems are allowed with REPEATABLE READ?

Answer 50

All problems prevented except phantom reads

Question 51

What concurrency problems are allowed with SERIALIZATION?

Answer 51

all problems are prevented, most secure level

Question 52

What is a deadlock?

Answer 52

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

Question 53

What are the 4 ways to prevent a deadlock?

Answer 53

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