Chapter 3: SQL

Question 1

What is a Data Definition Language (DDL)?

Answer 1

A language that operates on the structure of the database

• schema for relations
• domain values with attributes
• integrity constraints
• set of indices for each relation
• security and authorization info for relations
• physical storage structure on the disk

Question 2

What are some domain types in SQL

Answer 2

char(n) - fixed length character string with user specified length n
varchar(n) - variable length character strings wit user specified maximum length n
int - integer
smallint - small integer
numeric(p,d) - fixed point number with user specified precision of p digits, with n digits to the right of decimal point
real, double precision - floating point and double-precision floating numbers, machine dependent precision
float(n) - floating point number with user specified precision of at least n digits

Question 3

What does the CREATE TABLE command do?

Answer 3

creates a table as follows
CREATE TABLE r (A1 D1, …, AnDn,
(integrity-constraint 1), …
(integrity-constraint k)
)

Question 4

What are some integrity constraints in CREATE TABLE

Answer 4

not null
-a thing isn’t null
primary key (A1, …, An)
-declaration on an attribute automatically ensures not null (SQL-92 and onwards, needs to be explicitly declared in SQL-89)

Question 5

What does the DROP TABLE command do?

Answer 5

deletes all information about the dropped relation from the database

Question 6

What does the ALTER TABLE command do?

Answer 6

adds/drops attributes to an existing relation

• ALTER TABLE r ADD a d
• a is name of attribute being added to relation r and d is domain of a
• ALTER TABLE r DROP a
• a is the name of an attribute of relation r

Question 7

What is the basic structure of an SQL query?

Answer 7

3) SELECT A1,A2,…,An
1) FROM r1,r2, …, rn
2) WHERE P
* *where is optional**

Question 8

How do you force the elimination of duplicates/multiset in your query result?

Answer 8

SELECT DISTINCT instead of SELECT

• SQL allows multiset, and normal SELECT can return multiset
• select clause can also contain arithmetic

Question 9

What is the purpose of the WHERE clause?

Answer 9

specifies conditions that the result must satisfy

-corresponds to selection predicate in relational algebra

Question 10

How do you combine comparison results in the WHERE clause?

Answer 10

Use logical connectives AND, OR, and NOT

Question 11

How does the BETWEEN comparison operator work?

Answer 11

WHERE thing between (lower bound) and (upper bound)

-is inclusive, greater than or equal to and less than or equal to

Question 12

What is the purpose of the SELECT clause?

Answer 12

lists the attributes in the result of a query

-corresponds to the projection operation in relational algebra

Question 13

What is the purpose of the FROM clause?

Answer 13

lists the relations involved in the query

-corresponds to the cartesian product operation of the relational algebra

Question 14

What is the purpose of the rename operation and how is it done in SQL?

Answer 14

it allows for the renaming of relations and attributes within a query
-in SQL is the AS clause

Question 15

Where are temp variables defined?

Answer 15

They are defined in the from clause by use of the as clause

-makes temp variables to be referenced for the query

Question 16

What are the two string operator special characters?

Answer 16

'%'
-matches any substring
'_'
-matches any character
**used after keyword LIKE
**to get actual _ and % need \ before, \% and \_

Question 17

What are the set operators in SQL?

Answer 17

UNION, INTERSECT, and EXCEPT

-to make them multi-set use keyword ALL

Question 18

What does the having clause do?

Answer 18

Acts as a filter for the temporary output generated by the group by clause,
allows you to selectively output results from group by

Question 19

What does a SQL query with a having clause look like?

Answer 19

SELECT z, ag(a)
FROM    T1,..., Tn
WHERE  theta
GROUP BY z
HAVING  ag1(a)

Question 20

How can you check for null values in SQL?

Answer 20

use IS NULL

Question 21

What are Nested Subqueries

Answer 21

They are queries within a query

• they are a matter of preference
• any NSQ can be written as a set operation or a join, and vice-versa
• they run at O(n^2) because there is an outer and inner loop

Question 22

What does the SOME clause do?

Answer 22

checks is there is at least one thing that meets the desired situation
- (=some) = in

Question 23

How do you test for empty relations?

Answer 23

Use EXISTS and NOT EXISTS

• exists returns true if the subquery has an output
• not exists returns true if the subquery has no output

Question 24

What does UNIQUE do?

Answer 24

tests if a subquery has any duplicate tuples in its result

Question 25

What is a correlation variable?

Answer 25

Variable from outer query that is utilized in a NSQ, this is done by referencing the outer query variable within the NSQ

Question 26

What is a derived relation?

Answer 26

A relation produced by a NSQ within the from clause, the NSQ produces a relation which then becomes the relation utilized in the outer query

Question 27

What is the purpose of the WITH clause?

Answer 27

It provides a way of defining temporary tables, allowing for more complex queries

Question 28

How does referencing temporary tables made using the WITH clause work?

Answer 28

The temp tables made, and any commands within their queries, only have access to the tables created before the execution of the current line. (can’t reference tables made after current table only before)

Question 29

What is a VIEW

Answer 29

A VIEW is a QUERY WITH A NAME otherwise called a PRESERVED QUERY.
-query is stored in a system table tied to the view name so when view is called the query tied to that view is ran.

Question 30

How long does it take to create a view?

Answer 30

Since creating a view is the act of storing the view’s query in the system table, (VIEW_NAME, QUERY), it is very fast (takes no time). Utilizing and calling the view can take time though.

Question 31

How do VIEWS and WITH statements differ?

Answer 31

VIEW
-makes a temporary table that is permanent and can be accessed outside of current query
-is public
WITH
-makes temporary table that is temporary, can only be accessed within the query
-is private

Question 32

How does INSERT INTO (insertion) function?

Answer 32

• inserts new rows/tuples in table
• works row wise
• INSERT INTO table VALUES(col 1, col 2, …)
• any values that you don’t want to insert can specify as null

Question 33

How does UPDATE (updates) function?

Answer 33

• updates values in tables
• works column wise
• UPDATE table SET column value stat WHERE more stuff

Question 34

How can the CASE statement be used for conditional updates?

Answer 34

can use CASE stuff END in the set portion a an UPDATE to make if then else.
- UPDATE table SET attribute = CASE WHEN attribute condition THEN stuff ELSE stuff END

Question 35

What can be done with VIEWS and is recommended against and why?

Answer 35

You can do updates, inserts and deletes in a view but it can get very complicated so it is not recommended.