Chapter 3

Question 1

History of SQL: what year do most of the features come from

Answer 1

92

Question 2

Data-definition language: what does it contain?

Answer 2

Schema for each relation
Domain of values associated w/ each attribute
Integrity constraints
Set of indices to be maintained for each relation
Security and authorization info for each relation
Physical storage structure of each relation on disk

Question 3

Domain types in SQL: name them

Answer 3

char, varchar, int, smallint (machine-dependent subset of integer domain type)

real, double precision: flaoting pt and double-precision floating pt numbers

float (floating pt number, user-specific precision of n digits)

Question 4

Create table command: list the compoennts

Answer 4

name of table is the RELATION
A is an attribute name in the schema of relation r
D is the data type of values in domain of attribute A

Question 5

define primary key, candidate key, and foreign key

Answer 5

primary key: unique identifier (does not have to be one of one)

candidate key: think social security number. Permanent (static), and is one-of-one.

Foreign key: references primary key of another table

Question 6

List all updates to tables

Answer 6

insert, delete, drop, alter

Question 7

define alter table

Answer 7

alter table r add A D

A is the name of the attribute to be added to relation r, D is the domain

alter table r drop A:

A is the name of the attribute of relation r
dropping NOT supported by many databases

Question 8

what is a relation?

Answer 8

result of an SQL query

Question 9

select clause: define it

Answer 9

list the attributes desired in the result of a query

SQL is case INSENSITIVE

Question 10

what does an asterisk mean?

Answer 10

it means “all attributes”

Question 11

for select: how do we do arithmetic on it?

Answer 11

we can do

select ID, name, salary/12 AS monthly_salary (RENAME SALARY, AND DO MATH ON IT)

Question 12

where

Answer 12

conditoins that the result must satisfy, can use logical connctives of and, or, and NOT

Question 13

define from

Answer 13

from is the CARTESIAN PRODUCT operation; not useful w/o where-clause, or w/o natural join operation

Question 14

string operations: define % and _

Answer 14

% character matches any substring:

% AFTER substring means the term must begin; % BEFORE substring means it must fit term exactly

we can also do escape characters, in which we must say:

like ‘100\%’ escape ‘'

Question 15

how do we display order of tuples?

Answer 15

use the order by command:

desc for descending order, asc for ascending order

example:
order by name desc

OR order by dept_name, name (dept name takes precedent)

Question 16

what is a where clause predicate that we can see what is between certain things?

Answer 16

where salary BETWEEN 9000 and 100000

tuple comparison:
where (instructor.ID, dpt_name) = (teaches.ID, ‘Biology’)

Question 17

need to review relational algebra

Answer 17

REVIEW

Question 18

what are set operations?

Answer 18

union, intersect, except; NONE of which include duplicates UNLESS we do union ALL, INTERSECT ALL, etc.

the same tuple appears m times in r, and n times in s, it occurs:

m+n times in r union all s
min(m,n) times in r intersect all s
max(0, m-n) times in r except all s

Question 19

define null

Answer 19

null is UNKNOWN, NOT ZERO…any arithmetic, 5+null is NULL

Question 20

describe logic w/ null values

Answer 20

any logic operations returns null w/ null

example:
null <> null
null = null
5 < null

AND downgrades the truth value, OR upgrades the truth value

Question 21

define all aggregate functions

Answer 21

avg, min, max, sum, count

Question 22

group by: what belongs in the group by function?

Answer 22

anything that is mentioned in SELECT, EXCEPT for aggregate function values; likewise, SELECT clause without aggregate or mentioned in group by will be WRONG

Question 23

define the having clause

Answer 23

having is applied AFTER the group by clause; where is applied BEFORE the group by clause

Question 24

how can we count null values?

Answer 24

only count(*) will count null values; count itself will return 0, any other thing will return null

Question 25

subqueries: think of some examples

Answer 25

select distinct course_id
from section
where semester = ’Fall’ and year= 2009 and course_id not in (select course_id
from section
where semester = ’Spring’ and year= 2010);

select count (distinct ID)
from takes
where (course_id, sec_id, semester, year) in (select course_id, sec_id, semester, year
from teaches
where teaches.ID= 10101);

Question 26

define the some, all, and none clauses

Answer 26

select name
from instructor
where salary > some (select salary
from instructor
where dept name = ’Biology’);

=SOME is equivalent to IN, BUT not some is NOT equal to NOT in

for example: 5 is NOT EQUAL to NOT SOME of 0,5 evaluates to TRUE. That is because NOT SOME of 0 and 5 is 0, then 5

5 is NOT EQUAL to 0 is very true.

Question 27

define special cases of all and not all

Answer 27

not all is equivalent to not in
BUT all doesn’t mean IN

5 = ALL(4,5) will be false, because 4 =/= 5, so we can’t make it mean IN

Question 28

test of empty relations:

Answer 28

use the empty clause, as in the exists or the not exists clauses

select course_id
from section as S
where semester = ’Fall’ and year = 2009 and exists (select *
from section as T
where semester = ’Spring’ and year= 2010 and S.course_id = T.course_id);

exists of SELECT-ALL (gives you a tuple), will thus give you a value that is hopefully NOT NULL

Question 29

think of an example of the not exists clause

Answer 29

select distinct S.ID, S.name
from student as S
where not exists ( (select course_id
from course
where dept_name = ’Biology’)
except
(select T.course_id
from takes as T
where S.ID = T.ID));

Question 30

understand an example of the unique clause

Answer 30

select T.course_id
from course as T
where unique (select R.course_id                           from section as R                           
where T.course_id= R.course_id                                        and R.year = 2009);

Question 31

why do we put subqueries in the FROM clause?

Answer 31

select dept_name, avg_salary
from (select dept_name, avg (salary) as avg_salary from instructor
group by dept_name)
where avg_salary > 42000;

select dept_name, avg_salaryfrom (select dept_name, avg (salary)
from instructor
group by dept_name) as dept_avg (dept_name, avg_salary)
where avg_salary > 42000;

Question 32

what is a scalar subquery?

Answer 32

Scalar subquery is one which is used where a single value is expected

select dept_name,              
(select count(*)                  
from instructor                
where department.dept_name = instructor.dept_name)             as num_instructors
from department;

Here, we are renaming the subquery as num_instructors

Question 33

deletion of tuples

Answer 33

delete from instructor
where dept name in (select dept name from department where building = ’Watson’);

Question 34

insertion of tuples

Answer 34

insert into student select ID, name, dept_name, 0
from instructor

The select clause is ALWAYS evaluated BEFORE the insertion clause; same with deletion

Question 35

conditional updates: how do we do that?

Answer 35

update instructor
set salary = case
when salary 100000;

update instructor
set salary = salary * 1.05
where salary <= 100000;

CLEARLY, the case is better honestly speaking…

Question 36

how can we update w/ scalar subqueries?

Answer 36

update student S
set tot_cred = (select sum(credits) from takes natural join course where S.ID= takes.ID and takes.grade <> ’F’ and takes.grade is not null);

Instead of sum(credits), use:
case
when sum(credits) is not null then sum(credits) else 0
end

Question 37

Find the maximum across all departments of the total salary at each department
Can use the lateral clause
select name, salary, avg_salary from instructor I1, lateral (select avg(salary) as avg_salary from instructor I2 where I2.dept_name= I1.dept_name);

Answer 37

create table temp_account like account