CS 292 CH 4
Chapter 4 - Intermediate SQL MCQ Question: Which one is an allowed integrity constraints? A. add B. create table C. not null D. alter table
Ans: C Reference: page 146
MCQ Question: What is the The process of keeping the materialized view up-to-date called? A. maintenance material B. up-to date materialized view C. materialized view maintenance D. none of the above
Ans: C Reference: pg 140, section 4.2.3 Materialized Views
MCQ Question: What specific generalized super class can be used to the following: car and truck A. TRANSPORTATION B. PROJECT C. VEHICLE D. CARS
Ans: C Reference: pg. 112 - 113
Short Question: What is Integrity constraints?
Ans: It ensures that changes made to the database by authorized users do not result in a loss of data consistency. Reference: 4.4 p.145
Short Question: What each of these types of authorization is call in query language
Ans: It is call Privilege. Reference: Page 165
T/F Question: The outer join operation preserves the tuples that would be lost in a join by creating tuples in the result containing null values.
Ans: TRUE Reference: 4.1 - Join Expressions
T/F Question: When a referential-integrity constraint is violated, the normal procedure is to reject the action that caused the violation.
Ans: TRUE Reference: Chapter slides
T/F Question: The "natural join" operation operates on two relations and produces a relation as the result
Ans: TRUE Reference: Page 127
T/F Question: No two instructors can have the same instructor ID integrity constraints.
Ans: TRUE Reference: Page 145
T/F Question: The from clause has only one database relation
Ans: TRUE Reference: Pg 141, section 4.2.4 update of a view
T/F Question: the form of the create view command is: create view v as <query expression>;
Ans: TRUE Reference: page 138
T/F Question: Application that uses a view frequently may benefit if the view is materialized
Ans: TRUE Reference: page 140
T/F Question: An assertion is a predicate expressing a condition that we wish the database always satisfy
Ans: TRUE Reference: page 152
T/F Question: An important concept associated with sub classes (sub types) is that of type inheritance.
Ans: TRUE Reference: pg. 110
T/F Question: Specialization is the process of defining a set of subclass of an entity type.
Ans: TRUE Reference: pg. 110
T/F Question: A common use of the check clause is to ensure that attribute values satisfy specified conditions, in effect creating a powerful type system.
Ans: TRUE Reference: Ch. 4.4.4 Page 147
T/F Question: The right outer join is symmetric to the left outer join
Ans: TRUE Reference: Chapter 4. Section 4.1.3. Page 132
T/F Question: The full outer join is a combination of the left and right outer-join types.
Ans: TRUE Reference: Chapter 4. Section 4.1.3. Page 133
T/F Question: A transaction consists of a sequence of query and/or update statements.
Ans: TRUE Reference: Pg 171
T/F Question: Integrity constraints ensure that changes made to the database by authorized users do not result in a loss of data consistency.
Ans: TRUE Reference: Pg 173
T/F Question: The outer - join operation preserves topics that would be lost in a join by creating tuples in the result containing null values.
Ans: TRUE Reference: Pg. 131 - Join expressions
T/F Question: A database language enables the user to create database and table structures to perform basic data management chores.
Ans: TRUE Reference: Textbook
T/F Question: An SQL query that implements an outer join will return rows that do not have matching values in common columns.
Ans: TRUE Reference: Textbook
T/F Question: Transactions are a series queries and updates that work together in order to complete a task
Ans: TRUE Reference: chapter 4 page 151
T/F Question: The left outer join preserves tuples only in the relation named before (to the left of) the left outer join operation.
Ans: TRUE Reference: chapter 4, page 133
T/F Question: There are two types of User-defined types that SQL supports?
Ans: TRUE Reference: section 4.4.5 pg 158
Short Question: What is natural join operation?
Ans: The natural join operation operates on two relations and produces a relation as the result. . Reference: Pg 155
Short Question: How many forms of outer join are there and explain each form
Ans: There are three forms, the left outer join, which preserves tuples only in the relation named before (to the left of) the left outer join operation, so the left would be linked to the left side. The right outer join preserves tuples only in the relation named after (to the right of) the right outer join operation, the right is linked to the ride side, and the full outer join preserves tuples in both relation, which is both left and right. Reference: Pg 131-132 section 4.1.3 outer joins
Short Question: What is a check clause and what is it used for?
Ans: When applied to a relation declaration, the clause check(P) specifies a predicate P that must be satisfied by every tuple in a relation. A common use of the check clause is to ensure that attribute values satisfy specified conditions, in effect creating a powerful type system. Reference: Chapter 4 Page 147
Short Question: What are Integrity constraints?
Ans: ensure that changes made to the database by authorized users do not result in a loss of data consistency. Reference: Chapter 4 - pg 145
Short Question: Give an example of how to express natural joins through the use of the "join...on" clause.
Ans: select * from student join takes on student.ID=takes.ID; Reference: Page 130
MCQ Question: How many forms of outer join is there? A. 1 B. 7 C. 3 D. 4
Ans: C Reference: 4.1 - Join Expressions
Short Question: Define an index.
Ans: An index on an attribute of a relation is a data structure that allows the database system to find those tuples in the relation that have a specified value for that attribute efficiently, without scanning through all the tuples of the relation. Reference: Page 164
Short Question: Name some example of integrity constraints.
Ans: An instructors name cannot be null, no two instructors can havethe same instructor ID, every department name in the sourse relation must have matching department name in the department relation and the budget of a department must be greater than $0.00. Reference: page 145
Short Question: What is the role of authorization in SQL?
Ans: Authorization in SQL, gives the ability to input new data, update data, read data, and delete data. The different types of authorization are called privileges. These can be used for specific database objects or SQL actions such as a relation or view. Reference: section 4.7 pg 165
Short Question: A user is able to authorize parts of the database that includes what?
Ans: Authorization to read data, insert new data, update data and delete data. Reference: 4.7
Short Question: Which types of authorizations is called privilege ?
Ans: Authorization to read, insert, update and to delete data Reference: 4.7 Authorization. page 165
MCQ Question: Which clause contains only attribute names of the relation and does not have any expressions, aggregates, or distinct specifications? A. From B. Select C. on D. where
Ans: B Reference: 4.2.4 p.142
MCQ Question: The ______ clause can be used to define new types. A. new type B. create type C. begin type D. assign type
Ans: B Reference: Chapter 4 - pg 158
T/F Question: The SQL standard includes the privileges select, insert, delete, but not update
Ans: FALSE Reference: chapter 4 page 143
T/F Question: The keyword work is mandatory in commit and rollback work statements.
Ans: FALSE Reference: page 143
MCQ Question: What are the two forms of user-defined data types that SQL supports? A. Distinct and Domain types B. Distinct and Structure Data types C. Domain and Query Data types D. Create and Drop types
Ans: B Reference: Chapter 4 Page 158
MCQ Question: The process of keeping the materialized view up-to-date is called A. View Update B. materialized view maintenance C. View Check D. Materialized Perspective Maintenance
Ans: B Reference: Page 140
MCQ Question: SQL supports which of the following user-defined data type? A. Unstructured data type B. Structured data type C. Abstract data type D. None of the above
Ans: B Reference: Section: 4.5.5 Page: 158
MCQ Question: The "____ outer join" preserves the tuple only in the relation names before the operation. A. Left B. Right C. Full D. None of the above
Ans: C Reference: Pg 131
MCQ Question: The grant statement is used to confer A. Deletion B. Insertion C. Authorization D. Creation
Ans: C Reference: Section: 4.7.1 Page: 166
MCQ Question: SQL supports two forms of user defined data types, one is called structured data types and the other is called A. unknown types B. complex types C. distinct types D. reading types
Ans: C Reference: chapter 4 page 138
MCQ Question: How can we do natural join in SQL? A. select B. join C. Natural Join D. none
Ans: C Reference: chapter 4, page 129
T/F Question: The passing of a general authorization from one user to another can be represented by an authorization graph.
Ans: FALSE Reference: pg. 171, section 4.7.5 Transfer of Privileges
T/F Question: With clause allows us to to assign a name to a subquery for use as often as desired, but in one particular query only.
Ans: TRUE Reference: 4.2 p.137
T/F Question: A check clause is satisfied if it is not false, so clauses that evaluate to unknown are not violations.
Ans: TRUE Reference: 4.4.4
MCQ Question: Which join operation returns rows meeting criteria. A. Outer Join B. inner Join C. Cross Join D. Natural join
Ans: B Reference: Chaper slides
MCQ Question: Which one is a constraint that can be included in the create table command A. set default B. not null C. create view D. unique
Ans: B Reference: chapter 4 page 129
MCQ Question: Which view defines a view in SQL? A. new view B. materialized view C. create view D. regular view
Ans: C Reference: 4.2.1
MCQ Question: There are three forms of outer join, circle the one that isn't an outer join. A. Left Outer Join B. Right Outer Join C. Empty Outer Join D. Full Outer Join
Ans: C Reference: Chapter 4 Pages 131-132
MCQ Question: A transaction consists of sequence of query: A. Commit work B. rollback work C. Answer A & B D. None of the above
Ans: C Reference: Page # 143, section 4.3
MCQ Question: What operation operates on two relations and produces a relation as the result? A. natural join B. where C. joined D. join conditions
Ans: A Reference: 4.1 - Join Expressions
MCQ Question: What is the process of keeping the materialized view up-to-date? A. Materialized view maintenance B. Create view C. View definition D. Update of a view
Ans: A Reference: 4.2.3 p.140
MCQ Question: Which one is incorrect ? A. An instructor name can be null B. No two instructors can have the same instructors ID C. The budget of a department must be greater than $0 D. A & B
Ans: A Reference: 4.4 Integrity constraints , Page 145
MCQ Question: These are the three forms of outer join EXCEPT A. up outer join B. left outer join C. right outer join D. full outer join.
Ans: A Reference: Chapter 4 Section 4.1.3. Page 132
MCQ Question: The left outer join preserves tuples only in the relation named A. Before the left outer join operation B. After the right outer join operation C. Both of these D. None of the above
Ans: A Reference: Page 131
MCQ Question: What is Rollback Work? A. Rollback does the undoes all the updates perform by the SQL transaction. B. Commit C. Alter D. Feedback
Ans: A Reference: Page 143
MCQ Question: What is commit work for ? A. After commit, transaction become permanent. B. After commit transaction delete C. Before commit transaction become permanent. D. Commit is false
Ans: A Reference: Page 143
MCQ Question: We define a view in SQL by using which command? A. create view B. select C. view as D. from
Ans: A Reference: Pg 138 section 4.2.1 view definition
MCQ Question: Which process keeps the materialized view up-to-date? A. View maintenance B. Materialized views C. View D. None of the above
Ans: A Reference: Pg. 140 - Views
Chapter 4 - Intermediate SQL MCQ Question: The right outer join preserves tuples only in... A. the relation named after (to the right of) the right outer join operation B. the relation named after (to the left of) the left outer join operation C. the relation named after (to the middle) the middle outer join operation D. None of the above
Ans: A Reference: Pg.160
MCQ Question: In authorization graph, node representing database administrator, is known to be A. Root node B. Child node C. Leaf node D. Neighboring node
Ans: A Reference: Textbook
MCQ Question: What symbol is used, that would stand for the union operation (meaning what symbols connects multiples entities). A. U symbol B. N symbol C. ∩ symbol D. #NAME?
Ans: A Reference: pg. 120 - 121
Short Question: What is the common use of the check clause?
Ans: A common use of the check clause is to ensure that attribute values satisfy specified conditions. Reference: Chapter 4. Section 4.4.4. Page 147
Short Question: what is a join clause?
Ans: A join clause can thus specify inner join instead of outer join to specify that a normal join is to be used. Reference: chapter 4, page 139
Short Question: What is the difference between a specialization hierarchy and a specialization lattice?
Ans: A specialization hierarchy has the constraint that every sub class has only one parent, which results in a tree structure or strict hierarchy. Whereas, a specialization lattice a sub class can be in more than one class/sub class relationship. Reference: pg. 116
Short Question: What is a transaction consists of?
Ans: A transaction consists of a sequence of query and/or update statements. Reference: Section: 4.3 Page: 143
Short Question: what is Virtual private database
Ans: A virtual private database or VPD masks data in a larger database so that only a subset of the data appears to exist, without actually segregating data into different tables, schemas or databases. Reference: Textbook
Short Question: Explain assertion and give example constraints which can be expressed using assertion?
Ans: An assertion is a predicate expressing a condition that we wish the database always to satisfy. Consider the following constraints, which can be expressed using assertions. • For each tuple in the student relation, the value of the attribute tot cred must equal the sum of credits of courses that the student has completed successfully. • An instructor cannot teach in two different classrooms in a semester in the same time slot.9 Reference: Ch4.4.8 Page 152
Short Question: What are the following SQL statements that must happen to end the transaction?
Ans: Commit work commits the current transaction; that is, it makes the updates performed by the transaction become permanent in the database. After the transaction is committed, a new transaction is automatically started. Rollback work causes the current transaction to be rolled back; that is, it undoes all the updates performed by the SQL statements in the transaction. Thus, the database state is restored to what it was before the first statement of the transaction was executed. Reference: 4.3 - Transactions
MCQ Question: The select clause contains only attribute names of the relation and does not have any; A. aggregates B. expressions C. distinct specifications D. all of the above
Ans: D Reference: 4.2.4
MCQ Question: The allowed integrity constraints include A. not null B. unique C. check D. All of above
Ans: D Reference: 4.4.1 Constraints on a single relation, page 146
MCQ Question: Which is not one of the examples of integrity constraints? A. An instructor name cannot be null B. No two instructors can have the same instructor ID C. Every department name in the course relation must have a matchin department in the department relation D. the budget of a department must be less than $0.00
Ans: D Reference: Ch. 4.4 Page 145
MCQ Question: What are the types of authorizations called? A. Rules B. Regulations C. Primary Authorization D. Privilege
Ans: D Reference: Chapter 4 - pg 165
MCQ Question: What are some example of integrity constraints? A. An instructor name cannot be null. B. No two instructors can have the same instructor ID. C. Every department name in the course relation must have a matching department name in the department relation. D. All of the Above
Ans: D Reference: Chapter 4. Section 4.4. Page 145
MCQ Question: what does check clause specifies ? A. it appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation. B. It expresses a condition that we wish the database always to satisfy. C. It specifies that a transaction begins implicitly when an SQL statement is executed. D. It specifies a predicate P that must be satisfied by every tuple in a relation.
Ans: D Reference: Chapter slides
MCQ Question: In intermediate SQL the join conditions are: A. outer join B. left outer join C. right outer join D. all of the above
Ans: D Reference: Page # 131, section 4.1.3
MCQ Question: Examples of integrity constraints are: A. An instructor name cannot be null. B. No two instructors can have the same instructor ID. C. Every department name in the course relation must have a matching department name in the department relation. D. All of the above
Ans: D Reference: Pg 173
MCQ Question: What consists of a sequence of query and/or update statements? A. Commit work B. Rollback work C. Work D. Transaction
Ans: D Reference: Pg. 143 - Transactions
MCQ Question: Which SQL statement is used to extract data from a database? A. GET B. SELECT C. OPEN D. EXTRACT
Ans: D Reference: TEXTBOOK
MCQ Question: In general, an SQL view is said to be updatable if the following conditions are all satisï¬ed by the query deï¬ning the view: A. The from clause has only one database relation. B. The select clause contains only attribute names of the relation and does not have any expressions, aggregates, or distinct speciï¬cation. C. Any attribute not listed in the select clause can be set to null; that is, it does not have a not null constraint and is not part of a primary key. D. All The Above
Ans: D Reference: page 141
MCQ Question: We may assign a user several forms of authorizations on parts of the database. Authorizations on data include: A. Authorization to read data. B. Authorization to insert new data. C. Authorization to update data. D. All the Above
Ans: D Reference: page 165
MCQ Question: What privileges are included in the SQL standards? A. grant, insert, update and select B. all privileges. C. update, on, grant, and select D. select, insert, update and delete.
Ans: D Reference: page 166
Chapter 4 - Intermediate SQL MCQ Question: what are/is null value from below> A. and B. or C. not D. A B and C
Ans: D Reference: page 89
MCQ Question: What are the different outer join conditions in SQL? A. left-join, upper-join, lower-join B. left, right, up, down C. A and B D. left outer join, right outer join, full outer join, inner join
Ans: D Reference: section 4.1.3 pg 131-132
MCQ Question: What is the view definition in SQL? A. To see the different types of information on the data tables B. To create a table C. The view definition is used to create view command D. A and C
Ans: D Reference: section 4.2.1 pg 138
Short Question: How does Referential Integrity work?
Ans: Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation. Reference: Chapter slides
T/F Question: Is the way to keep an materialized view up-to-date called updating?
Ans: FALSE Reference: 4.1 - Views
T/F Question: The not null constraint allows for the insertion of a null value for an attrubute
Ans: FALSE Reference: 4.4.2 pg 146
T/F Question: A calendar date containing year, month,day and hour
Ans: FALSE Reference: 4.5.1 Date & time types in SQL ,page 154
T/F Question: SQL Supports only one form of user-defined data types.
Ans: FALSE Reference: 4.5.5 p.158
T/F Question: The inspect condition verifies that the time slot id in each tuple in the section relation is actually the identifier of a time slot in the time slot relation.
Ans: FALSE Reference: Chapter 4 - pg 152
T/F Question: Authorization in SQL means is playing music.
Ans: FALSE Reference: Page 165
T/F Question: The natural join operation operates on three relations and produces a relation as the result.
Ans: FALSE Reference: Pg. 127 - Join expressions
T/F Question: To distinguish normal joins from inner joins, normal joins are called outer joins in SQL.
Ans: FALSE Reference: Pg. 136