Lecture 9

Question 1

How to do union with SQL?

Answer 1

(SELECT * FROM R) UNION (SELECT * FROM R);

If duplicates then use UNION ALL

Question 2

How to insert in SQL?

Answer 2

INSERT INTO R(A1, …, An) VALUES (.., …) … ;

Question 3

How to delete in SQL?

Answer 3

DELETE FROM R WHERE C;

Question 4

How to update in SQL?

Answer 4

UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

You can also change using || and including other values.

Question 5

What is the goal of a transaction?

Answer 5

To make sure that certain queries are processed in a serializable way. It also makes sure that a transaction is either completed or not.

Question 6

How to start and end a transaction?

Answer 6

Start: START TRANSACTION
Abort: ROLLBACK;
Finish: COMMIT;

Question 7

How to set read-only transaction?

Answer 7

SET TRANSACTION READ ONLY;

Question 8

What is a Dirty read?

Answer 8

Uses data that may be lost in the end because of ROLLBACK;

Question 9

What are the 4 isolation levels?

Answer 9

1. Read Uncommited, 2. Read committed, 3. Repeatable read, 4. Serializable

Question 10

What can each isolation level do?

Answer 10

Read uncommitted: dirty reads, non-repeatable, phantoms. Read committed: non-repeatable, phantoms. Repeatable read: phantoms. Serializable: None.

Phantom: Read new rows

Question 11

What is non-repeatable?

Answer 11

New data may enter in the transaction

Question 12

What is serialiazable?

Answer 12

New data cannot be inserted, or updated until transaction is completed.

Question 13

What is GREATEST?

Answer 13

Just maximum for multiple rows. Similar to LEAST() for min.

Question 14

How to do switch case in SQL?

Answer 14

e.g. CASE A

WHEN A = ‘a’ THEN ‘b’

Question 15

How to use LIMIT?

Answer 15

LIMIT returns at most the number of tuples that is placed after it.

and
LIMIT 10, 100 skips the first 10 then returns the next 100.

Question 16

How to randomly select data.

Answer 16

SELECT * FROM R WHERE RAND() < 0.1;

0.1 is example, thus selects 10% of all values.