Chapter 3 Flashcards

(37 cards)

1
Q

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

A

92

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Data-definition language: what does it contain?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Domain types in SQL: name them

A

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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Create table command: list the compoennts

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

define primary key, candidate key, and foreign key

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

List all updates to tables

A

insert, delete, drop, alter

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

define alter table

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

what is a relation?

A

result of an SQL query

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

select clause: define it

A

list the attributes desired in the result of a query

SQL is case INSENSITIVE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

what does an asterisk mean?

A

it means “all attributes”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

for select: how do we do arithmetic on it?

A

we can do

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

where

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

define from

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

string operations: define % and _

A

% 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 ‘'

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

how do we display order of tuples?

A

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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

where salary BETWEEN 9000 and 100000

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

17
Q

need to review relational algebra

18
Q

what are set operations?

A

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

19
Q

define null

A

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

20
Q

describe logic w/ null values

A

any logic operations returns null w/ null

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

AND downgrades the truth value, OR upgrades the truth value

21
Q

define all aggregate functions

A

avg, min, max, sum, count

22
Q

group by: what belongs in the group by function?

A

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

23
Q

define the having clause

A

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

24
Q

how can we count null values?

A

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

25
subqueries: think of some examples
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);
26
define the some, all, and none clauses
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.
27
define special cases of all and not all
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
28
test of empty relations:
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
29
think of an example of the not exists clause
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));
30
understand an example of the unique clause
``` 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); ```
31
why do we put subqueries in the FROM clause?
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_salary from (select dept_name, avg (salary) from instructor group by dept_name) as dept_avg (dept_name, avg_salary) where avg_salary > 42000;
32
what is a scalar subquery?
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
33
deletion of tuples
delete from instructor where dept name in (select dept name from department where building = ’Watson’);
34
insertion of tuples
insert into student select ID, name, dept_name, 0 from instructor The select clause is ALWAYS evaluated BEFORE the insertion clause; same with deletion
35
conditional updates: how do we do that?
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...
36
how can we update w/ scalar subqueries?
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
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);
create table temp_account like account