sql Flashcards
how do i create a table
CREATE TABLE tableName (attributes);
how do i show foreign keys
FOREIGN KEY (fk) REFERENCES fkTable (fk)
how do you insert into a table
INSERT INTO relation (attributes)
VALUES (all values to be inserted)
how do you delete from a table
DELETE name
FROM relation
WHERE condition
how do you select from a table
SELECT name
FROM relation
WHERE condition
*
all
DISTINCT
when selecting distinct indicates that it should not return any duplicates
what is the cross product of a relation list
when you combine each row in one table with each row in the other
basically the cartesian product
what are the steps in the conceptual evaluation strategy
compute the cross product
discard the tuples that dont satisfy the condition
display the attributes in the attribute list
if DISTINCT eliminate the duplicate rows
when do you HAVE create a variable/copy of the table (e.g. s.id instead of student.id)
when you are joining the table with itself as there each id will be duplicated and you need to know which one youre talking about
in which two ways can you name fields in result
AS and =
LIKE
used for string matching
_
any one character
%
o or more characters
UNION
used for the union of any two union-compatible sets
basically used instead of AND but have to have 2 queries instead of 1
EXCEPT
minuses the 2 select statements
INTERSECT
getting the intersection of 2 sets
IN
returns true if the first value is in the set returned in the query
e.g. SELECT S.sname
FROM Sailors S
WHERE S.sid IN
(SELECT R.sid (ONLY ONE Colum)
FROM Reserves R
WHERE R.bid=103)
EXISTS
returns true if the set is not empty
NOT EXISTS
returns true if the set is empty
UNIQUE
returns true if the set doesn’t have any duplicates
ANY
returns true is any tuples obey the comparison
ALL
returns true if all tuples obey the comparison
how does division work in sql
combines EXISTS and EXCPET
where EXISTS is the outer set where you’re trying to find your info and EXCEPT is the specific information you wanted
COUNT
counts the number of things in you query
COUNT/SUM/AVG ([DISTINCT] A)
counts/sums/averages the different entities in your query
MAX/MIN
get the max/min value in your query
GROUP BY
arranges identical data into groups
HAVING
filters groups based on conditions after GROUP BY
(like WHERE but for GROUP BY)
