SQL

Question 1

Why do we use SQL?

Answer 1

Flexible
Breaks into relational algebra
Very high level

Question 2

What are the 3 types of SQL?

Answer 2

DDL - schema/constraint data
DML - insert/delete/modify data
DQL - find/join/aggregate data

Question 3

How do we order SQL statements?

Answer 3

SELECT desired attributes
FROM one or more tables
WHERE condition about table rows

Question 4

How do we do selection and projection in SQL?

Answer 4

Selection = WHERE
Projection = SELECT all attributes of relation below

Question 5

How do we rename columns in SQL?

Answer 5

AS

Renames attribute

Question 6

How do we do computed attributes in SQL?

Answer 6

Use a calculation

Part of SELECT clause

Question 7

How are NULL values used in SQL?

Answer 7

Tuples can be NULL

Any value compared with NULL = truth value unknown

Question 8

How do we do 3 valued logic in SQL?

Answer 8

True = 1
Also = 0
Unknown = 0.5

Question 9

How do we do multi-relation queries in SQL?

Answer 9

Combine data from multiple queries

List all in FROM clause

Question 10

How do we do subqueries/nested queries in SQL?

Answer 10

SELECT-FROM-WHERE statements

Question 11

How do we do HAVING statements in SQL?

Answer 11

HAVING

Follows GROUP BY clause

Question 12

What are the 3 formal semantics of SQL?

Answer 12

Product of relations in FROM clause
Apply selection condition from WHERE clause
Project onto list of attributes/expressions in SELECT clause

Question 13

How do we do explicit tuple variables in SQL?

Answer 13

Query needs to use 2 copies of same relation

Distinguish by putting relation name then tuple name in FROM clause

Question 14

How do we do the EXISTS operator in SQL?

Answer 14

EXISTS

True if and only if is not empty

Question 15

How do we do union/intersection/difference in SQL?

Answer 15

Default set semantics (duplicates deleted)

Question 16

How do we do the different types of joins in SQL?

Answer 16

Natural join: R natural join S
Product: R cross join S
Theta join: R join S on
Outer join: R outerjoin S

Question 17

How do we do the ANY operator in SQL?

Answer 17

X = ANY

Boolean true if x equals at least 1 tuple in relation

Question 18

How do we do the ALL operator in SQL?

Answer 18

X<>ALL

True for every tuple t in relation: (x not equal to t)

Question 19

How do we do aggregations in SQL?

Answer 19

SUM, AVG, COUNT, MIN, MAX applied to column in SELECT clause
To eliminate duplicates use DISTINCT
Ignore NULLS
To restrict on SELECT each element must be aggregated or attribute on GROUP BY list