Chapter 3 Flashcards
(37 cards)
History of SQL: what year do most of the features come from
92
Data-definition language: what does it contain?
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
Domain types in SQL: name them
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)
Create table command: list the compoennts
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
define primary key, candidate key, and foreign key
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
List all updates to tables
insert, delete, drop, alter
define alter table
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
what is a relation?
result of an SQL query
select clause: define it
list the attributes desired in the result of a query
SQL is case INSENSITIVE
what does an asterisk mean?
it means “all attributes”
for select: how do we do arithmetic on it?
we can do
select ID, name, salary/12 AS monthly_salary (RENAME SALARY, AND DO MATH ON IT)
where
conditoins that the result must satisfy, can use logical connctives of and, or, and NOT
define from
from is the CARTESIAN PRODUCT operation; not useful w/o where-clause, or w/o natural join operation
string operations: define % and _
% 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 do we display order of tuples?
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)
what is a where clause predicate that we can see what is between certain things?
where salary BETWEEN 9000 and 100000
tuple comparison:
where (instructor.ID, dpt_name) = (teaches.ID, ‘Biology’)
need to review relational algebra
REVIEW
what are set operations?
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
define null
null is UNKNOWN, NOT ZERO…any arithmetic, 5+null is NULL
describe logic w/ null values
any logic operations returns null w/ null
example:
null <> null
null = null
5 < null
AND downgrades the truth value, OR upgrades the truth value
define all aggregate functions
avg, min, max, sum, count
group by: what belongs in the group by function?
anything that is mentioned in SELECT, EXCEPT for aggregate function values; likewise, SELECT clause without aggregate or mentioned in group by will be WRONG
define the having clause
having is applied AFTER the group by clause; where is applied BEFORE the group by clause
how can we count null values?
only count(*) will count null values; count itself will return 0, any other thing will return null