Q04 Quiz 04 - Chapter 04

__ will undo all statements up to commit?
c. Rollback
Flashback will undo all the statements and Abort will terminate the operation.
The correct answer is: Rollback

A _ consists of a sequence of query and/or update statements.
b. Transaction
Transaction is a set of operation until commit.

A transaction completes its execution is said to be
Committed
A complete transaction always commits.

Consider the following action,
TRANSACTION…..Commit;
ROLLBACK;
What does Rollback do?
a. No action
Once a transaction has executed commit work, its effects can no longer be undone by rollback work.

Consider the two relations

Instructor
ID | name |dept_name, salary
—- | —- |
1001 | Ted |Finance10000
1002 | Bob | Music20000
1003 | Ron |Physics50000

Department
dept_name, building, budget
Biology Watson , 40000
Chemistry Painter, 30000
Music, Taylor, 50000

Which of the following is used to create view for these relations together?
CREATE VIEW instructor_info AS
SELECT ID, name, building
FROM instructor, department
WHERE instructor.dept_name= department.dept_name;

CREATE TABLE course
( . . .FOREIGN KEY (dept name) REFERENCES department. . . );

Which of the following is used to delete the entries in the referenced table when the tuple is deleted in course table?
b. Delete cascade
The delete “cascades” to the course relation, deletes the tuple that refers to the department that was deleted.

CREATE TABLE Employee(
Emp_id NUMERIC NOT NULL,
Name VARCHAR(20) ,
dept_name VARCHAR(20),
Salary NUMERIC UNIQUE(Emp_id,Name));

INSERT INTO Employee VALUES(1002, Ross, CSE, 10000);
INSERT INTO Employee VALUES(1006, Ted, Finance, );
INSERT INTO Employee VALUES(1008, Ross, Sales, 20000);

What will be the result of the query?
b. Error in insert into Employee values(1008,Ross,Sales,20000);

The not null specification prohibits the insertion of a null value for the attribute.
The unique specification says that no two tuples in the relation can be equal on all the listed attributes.

Data integrity constraints are used to:
d. Improve the quality of data entered for a specific property (i.e., table column)

For the view
Create view instructor_info as
SELECT ID, name, building
FROM instructor, department
WHERE instructor.dept name= department.dept name;

If we insert tuple into the view as
insert into instructor info values (‘69987’, ‘White’, ‘Taylor’);

What will be the values of the other attributes in instructor and department relations?
Null

The values take null if there is no constraint in the attribute else it is an Erroneous statement.

How many tables may be included with a join?
d. All of the mentioned

Join can combine multiple tables.

How will you select the Names whose first letter is E ?
a. SELECT Name FROM instructor WHERE Name LIKE ‘E%’;

% matches any substring while _ matches any character.

In SQL the statement
select from R, S
is equivalent to
Select from R cross join S

Cross join (without the where clause) is similar to Cartesian product.

SELECT *
FROM student JOIN takes USING (ID);

The above query is equivalent to
SELECT *
FROM student INNER JOIN takes USING (ID);

Join can be replaced by inner join.

SELECT Name
FROM instructor
WHERE salary > SOME
(SELECT salary FROM instructor WHERE dept_name = ‘Physics’);

How many rows are selected ?
(Use the University Database from the textbook)
3

This displays the names of instructors with salary greater than that of some (at least one) instructor in the Physics department.

To include integrity constraint in an existing relation use :
c. Alter table

SYNTAX - alter table table-name add constraint, where constraint can be any constraint on the relation.

ALTER TABLE 语句用于在已有的表中添加、删除或修改列。

What is the function of the not null constraint?

Select one:
a. It prevents illegal data from being entered into the database
b. It ensures that data is entered into the database
c. It ensures that the data entered is unique
d. None of the mentioned
b. It ensures that data is entered into the database

The not null constraint ensures that data is entered into the database. It displays an error message whenever a data field mentioned is left empty.

What is the function of the unique constraint
(Note: did not mixed with unique construct)?

Select one:
a. It ensures that no two values under an attribute are identical
b. It ensures that all the attributes are perfectly unique in their data type
c. It ensures that all the relations in the database have a unique set of attributes
d. It does not have any function in SQL
a. It ensures that no two values under an attribute are identical

The purpose of the unique constraint/clause is to ensure that no two values under the same attribute are identical.
Primary keys are unique by default.
While the unique construct returns true if the argument subquery contains no duplicate tuples.

What type of join is needed when you wish to include rows that do not have matching values?

Select one:
a. Equi-join
b. Natural join
c. Outer join
d. All of the mentioned
c. Outer join

An outer join does not require each record in the two joined tables to have a matching record.
Types are inner join, left outer join, right outer join, full join, cross join.

Which of the following is not an integrity constraint?
Select one:
a. not null
b. unique
c. identical
d. check
identical

• Identical is not an allowed integrity constraint in SQL.
  - Not null prevents null values and unique only allows unique values to be entered.
  - Check checks for a given condition.

Which of the following is not an integrity constraint?
Select one:
a. Check ‘predicate’
b. Unique
c. Not null
d. Positive
Positive

Positive is a value and not a constraint.