CSE 180 Final Latest Study Questions And Answers
With Verified Solutions
Primary Key ANS Uniquely identifies a row. Must be non-null and can only appear once.
Unique Key ANS Uniquely identifies a row. Values can be null and can have multiple unique statements.
SELECT Clause ANS Where you format outputs & assign attributes. Can also have expressions
DISTINCT ANS Removes duplicates. Only use if truly necessary
FROM Clause ANS The clause that indicates the table from which to retrieve the specified columns
WHERE Clause ANS Contains the bulk of the conditions, deciding tuples the query will return. Can
contain multiple subqueries, but cannot have aggregate values.
DESC ANS Used by ORDER BY to sort from High to Low.
ASC ANS Used by ORDER BY to sort from Low to High
ORDER BY ANS Sorts output by first attribute listed. If two tuples have the same value, then it is by the
second. Defaults to ASC.
Matches Zero or more ANS LIKE '%'
Matches exactly one ANS LIKE '_'
Three value Logic ANS TRUE, FALSE, UNKNOWN
ACID ANS Atomicity, Consistency, Isolation, Durability
,Isolation ANS Different transactions shouldn't step over each other. Ex: Protect against multiple
transactions at the same time from the same bank account.
Relation Schema ANS Set of attributes addressing a single value
Consistency ANS Transaction must change affected data in only the allowed ways. Ex: Student & course
exist if the student is enrolled in a course.
First Normal Form (1NF) ANS Every type of attribute is atomic. (Every other Normal Form assumes
1NF). It only has one value defined for each attribute (no nested attributes)
Relations: ANS Structures with rows and columns subset of cartesian products
Count(A) ANS The number of tuples with non-NULL values
Count(*) ANS The number of rows, including NULL
Alternate Key ANS A non-primary key that can be used to identify only one row in a table
Declarative programs ANS Specifies what's the expected outcome, not how it's obtained. Ex: Scheme,
Ocaml . SQL is declarative but it is not fully declarative.
Procedural Program ANS Program specified as a sequence of operations to get the desired outcome. (How
the outcome is obtained) Ex. Java, C.
Hierarchical Data Model ANS Organizes data into a tree-based structure. Ex: XML, JSON
Durability ANS If transaction occurs, it should exist.
Key ANS Can't be in two different tuples in an instance of R for all K, but the same uniquely identifies
current relations
, Super Key ANS Any key that uniquely identifies each row (always a key, but not vice versa)
HAVING ANS Choose groups based on some aggregate property of the group itself (WHERE applies
conditions to each group. Removing any that don't satisfy.
MIN, MAX ANS Non-Aggregate columns in the select clause must come from attributes in GROUP BY.
Also cannot be in WHERE.
INSERT INTO ANS Tuple is inserted into Relation R, where attribute a_i = V_i and default values are
NULL for any not entered/missing.
GROUP BY ANS A SQL clause used to create frequency distributions when combined with any of the
aggregate functions in a SELECT statement. Does not ignore NULL
Atomicity ANS All or nothing.
VARCHAR(a) ANS Character type that is not padded when stored
UPDATE ANS Specifies single table update. Can have FROM, WHERE clause describing which values to
update.
SQL Views ANS Relations that defined in terms of other relations
Decimal(n,d) ANS n = decimal digit, d = digits after decimal point
Transactions ANS A group of operations that should be executed atomically all or nothing
Serialization ANS Shows us the illusion that every transaction is executed one by one. Related to the
concept of isolation.
START TRANSACTION ANS Beginning of transaction followed by one or more SQL statements
With Verified Solutions
Primary Key ANS Uniquely identifies a row. Must be non-null and can only appear once.
Unique Key ANS Uniquely identifies a row. Values can be null and can have multiple unique statements.
SELECT Clause ANS Where you format outputs & assign attributes. Can also have expressions
DISTINCT ANS Removes duplicates. Only use if truly necessary
FROM Clause ANS The clause that indicates the table from which to retrieve the specified columns
WHERE Clause ANS Contains the bulk of the conditions, deciding tuples the query will return. Can
contain multiple subqueries, but cannot have aggregate values.
DESC ANS Used by ORDER BY to sort from High to Low.
ASC ANS Used by ORDER BY to sort from Low to High
ORDER BY ANS Sorts output by first attribute listed. If two tuples have the same value, then it is by the
second. Defaults to ASC.
Matches Zero or more ANS LIKE '%'
Matches exactly one ANS LIKE '_'
Three value Logic ANS TRUE, FALSE, UNKNOWN
ACID ANS Atomicity, Consistency, Isolation, Durability
,Isolation ANS Different transactions shouldn't step over each other. Ex: Protect against multiple
transactions at the same time from the same bank account.
Relation Schema ANS Set of attributes addressing a single value
Consistency ANS Transaction must change affected data in only the allowed ways. Ex: Student & course
exist if the student is enrolled in a course.
First Normal Form (1NF) ANS Every type of attribute is atomic. (Every other Normal Form assumes
1NF). It only has one value defined for each attribute (no nested attributes)
Relations: ANS Structures with rows and columns subset of cartesian products
Count(A) ANS The number of tuples with non-NULL values
Count(*) ANS The number of rows, including NULL
Alternate Key ANS A non-primary key that can be used to identify only one row in a table
Declarative programs ANS Specifies what's the expected outcome, not how it's obtained. Ex: Scheme,
Ocaml . SQL is declarative but it is not fully declarative.
Procedural Program ANS Program specified as a sequence of operations to get the desired outcome. (How
the outcome is obtained) Ex. Java, C.
Hierarchical Data Model ANS Organizes data into a tree-based structure. Ex: XML, JSON
Durability ANS If transaction occurs, it should exist.
Key ANS Can't be in two different tuples in an instance of R for all K, but the same uniquely identifies
current relations
, Super Key ANS Any key that uniquely identifies each row (always a key, but not vice versa)
HAVING ANS Choose groups based on some aggregate property of the group itself (WHERE applies
conditions to each group. Removing any that don't satisfy.
MIN, MAX ANS Non-Aggregate columns in the select clause must come from attributes in GROUP BY.
Also cannot be in WHERE.
INSERT INTO ANS Tuple is inserted into Relation R, where attribute a_i = V_i and default values are
NULL for any not entered/missing.
GROUP BY ANS A SQL clause used to create frequency distributions when combined with any of the
aggregate functions in a SELECT statement. Does not ignore NULL
Atomicity ANS All or nothing.
VARCHAR(a) ANS Character type that is not padded when stored
UPDATE ANS Specifies single table update. Can have FROM, WHERE clause describing which values to
update.
SQL Views ANS Relations that defined in terms of other relations
Decimal(n,d) ANS n = decimal digit, d = digits after decimal point
Transactions ANS A group of operations that should be executed atomically all or nothing
Serialization ANS Shows us the illusion that every transaction is executed one by one. Related to the
concept of isolation.
START TRANSACTION ANS Beginning of transaction followed by one or more SQL statements
