Chapter 7

Question 1

What is the SQL Query to check if an attribute value is NULL?

Answer 1

Where {attribute name} IS NULL
or
Where {attribute name} IS NOT NULL

Question 2

Which comparison operator compares values v with a set cor multiset) of values V and evaluates TRUE if v is one of the elements in V?

Answer 2

Comparison operator IN

Question 3

___________ are complete select-from-where blocks within WHERE clause of another query. It includes an outer query and nested subqueries.

Answer 3

Nested queries

Question 4

Which operator returns TRUE if the value v is equal to some value in the set V and is hence equivalent to IN?

Answer 4

= ANY
or
= SOME

Question 5

__________ expresses all values in the database.

Answer 5

ALL

Question 6

T/F: A correlated nested query is evaluated once for each tuple in the outer query.

Answer 6

true

Question 7

________ and ________ function checks whether the result of a correlated nested query is empty or not. They are Boolean functions that return TRUE or FALSE result.

Answer 7

EXISTS, NOT EXISTS

Question 8

T/F: EXISTS and NOT EXISTS functions are typically used in conjunction with a correlated nested query.

Answer 8

True.

Question 9

________ function returns TRUE if there are no duplicate tuples in the result of query Q.

Answer 9

UNIQUE (Q)

Question 10

T/F: we use explicit set of values in SELECT clause.

Answer 10

False, in WHERE clause.
(Ex:
Select distinct Essn
From WORKS_ON
where Pno IN (1,2,3); )

Question 11

Use qualifier ________ rolled by desired new name to rename any attribute that appears in the result of a query.

Answer 11

AS

Question 12

________ permits users to specify a table resulting from a join operation in the FROM clause of a query.

Answer 12

Joined table

Question 13

JOIN may also be
called ________.

Answer 13

INNRT JOIN

Question 14

__________ JOIN on two relations R and S has no join condition specified.

Answer 14

NATURAL

Question 15

_________ JOIN is equivalent to an implicit EQUIJOIN condition for each pair of attributes with same name from R and S.

Answer 15

NATURAL
[NOTE: An equi-join is a join based on equality or matching column values.]

Question 16

T/F: It is not possible to use NATURAL JOIN on two relations that have the same attribute names.

Answer 16

false, you can rename the attributes of one relation so it can be joined with another using NATURAL JOIN.
(EX:
SELECT Fname, Lname, Address
FROM (EMPLOYEE NATURAL JOIN
(DEPARTMENT AS DEPT (Dname, Dno, Mssn,
Msdate)))
WHERE Dname=‘Research’; )

Question 17

Rewrite the following query using an implicit join condition:
SELECT Fname, Lname, Address
FROM (EMPLOYEE NATURAL JOIN
(DEPARTMENT AS DEPT (Dname, Dno, Mssn,
Msdate)))
WHERE Dname=‘Research’;

Answer 17

EMPLOYEE.Dno = DEPT.Dno
[Note: remember that natural join is equivalent to an implicit join condition.]

Question 18

_________ is the default type of join in a joined table.

Answer 18

INNER JOIN

Question 19

In a _________ JOIN, a tuple is included in the result onlu if a matching tuple exists in the other relation.

Answer 19

INNER

Question 20

In a __________ JOIN, every tuple in the left table must appear in the result.

Answer 20

LEFT OUTER

Question 21

T/F: If there are no matching tuples in an LEFT OUTER JOIN then the query will not execute.

Answer 21

False, if there are no matching tuples, the right table will be padded with NULL values for attributes. This applies for both RIGHT and LEFT OUTER JOIN.

Question 22

In a ________ JOIN, every tuple in the right table must appear in the result.

Answer 22

RIGHT OUTER

Question 23

If there are no matching tuples in a RIGHT OUTER JOIM, the (right/left) table attributes will be padded with ________ values.

Answer 23

Left, NULL.

Question 24

__________ are used to summarize information from multiple tuples into a single-tuple summary.

Answer 24

aggregate functions

Question 25

Built-in aggregate functions include _____, _____, ____, _____, and _____.

Answer 25

COUNT, SUM, MAX, MIN, AVG.

Question 26

________ create subgroups of tuples before summarizing.

Answer 26

Grouping

Question 27

To select entire groups, a _________ clause is used.

Answer 27

HAVING

Question 28

Aggregate functions can be used in the ______ clause or in a _______ clause.

Answer 28

SELECT, HAVING.

Question 29

What does the following query return?

SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG
(Salary)
FROM EMPLOYEE;

Answer 29

a single row of computed values from EMPLOYEE table

Question 30

NULL values are ________ when aggregate functions are applied to a particular column.

Answer 30

discarded

Question 31

Write an alternate syntax for the following without using LEFT OUTER JOIN:

SELECT E.Lname AS Employee_Name
S.Lname AS Supervisor_Name
FROM Employee AS E LEFT OUTER JOIN EMPLOYEE AS S
ON E.Super_ssn = S.Ssn)

Answer 31

SELECT E.Lname , S.Lname
FROM EMPLOYEE E, EMPLOYEE S
WHERE E.Super_ssn + = S.Ssn

Question 32

T/F: Tuples with NULL Values are counted when using COUNT (DISTINCT {attribute name}).

Answer 32

False, any tuples with NULL will not be counted. Such as duplicate values will be eliminated.

Question 33

When an aggregate function is applied to a collection of values, and the collection becomes empty because all values are NULL, the aggregate function will return ________.

Answer 33

NULL (except in the case of
COUNT, where it will return 0 for an empty collection of values).

Question 34

(SOME/ALL) returns true if at least one element in the
collection is TRUE (similar to OR)

Answer 34

SOME

Question 35

ALL returns _____ if all of the elements in the
collection are TRUE (similar to AND)

Answer 35

TRUE

Question 36

COUNT (*) with a GROUP BY clause counts the number of _______ in the group.

Answer 36

rows

Question 37

________ partitions a relation into subsets of tuples based on grouping attribute(s).

Answer 37

Grouping (GROUP BY clause)

Question 38

If the grouping attribute in a GROUP BY clause has NULL as a possible value,
then a separate group is created for the _____ value.

Answer 38

null

Question 39

T/F: The grouping attribute must appear in the SELECT
clause.

Answer 39

true.
(Ex: Dno is the grouping attribute in the following..

SELECT Dno, COUNT (*), AVG (Salary)
FROM EMPLOYEE
GROUP BY Dno;
)

Question 40

the _________ clause provides a condition to select or reject an entire group.

Answer 40

HAVING

Question 41

the (WHERE/HAVING) clause applies tuple by tuple, whereas the (WHERE/HAVING) clause applies to entire group of tuples.

Answer 41

WHERE, HAVING

Question 42

The _______ clause allows a user to define a table that will only be used in a particular query (notavailable in all SQL implementations).

Answer 42

WITH

Question 43

T/F: The WITH clause is used for convenience to create a temporary
“View” and use that immediately in a query.

Answer 43

true

Question 44

SQL also has a ______ construct that is used when a value can be different based on certain conditions.

Answer 44

CASE

Question 45

T/F: A CASE construct can be used in any part of an SQL query where a value is expected.

Answer 45

true.

Question 46

T/F: The CASE construct is applicable when querying, inserting or deleting tuples.

Answer 46

false, or updating not deleting tuples

Question 47

Enter the missing clause:

UPDATE EMPLOYEE
SET Salary =
_____ WHEN Dno = 5THEN Salary + 2000
WHEN Dno = 4THEN Salary + 1500
WHEN Dno = 1THEN Salary + 3000
ELSE Salary + 0 ;

Answer 47

CASE

Question 48

T/F: Semantic constraints are beyond the scope of the EER and relational model.

Answer 48

true.

Question 49

CREATE _________ : Specifies automatic actions that database system will perform when certain events and conditions occur.

Answer 49

TRIGGER

Question 50

CREATE _______: specifies additional types of constraints outsidescope of built-in relational model constraints.

Answer 50

ASSERTION

Question 51

T/F: CREATE TRIGGER specifies a query that selects any tuples that violate
the desired condition.

Answer 51

false, CREATE ASSERTION.

Question 52

T/F: A trigger is used to monitor the database.

Answer 52

true.

Question 53

Typical trigger has three components which makeit a rule for an “active database”: Event(s), ________, and action.

Answer 53

condition

Question 54

A _______ is a single table derived from other tables called the defining tables.

Answer 54

Views (virtual tables)

Question 55

T/F: A view is considered to be a virtual table that is NOT necessarily populated.

Answer 55

true.

Question 56

T/F: Update not permitted on aggregate views.

Answer 56

true.
(ex:
UPDATE DEPT_INFO
SET Total_sal=100000
WHERE Dname=‘Research’;
cannot be processed because Total_sal is a computed value in the view
definition)

Question 57

T/F: A user cannot always issue a retrieval query against any view.

Answer 57

false, can always issue a retrival query.

Question 58

T/F: Issuing an INSERT, DELETE, or UPDATE command on a view table is in many cases not possible.

Answer 58

true.

Question 59

T/F: Views can be used to hide certain attributes or tuples from unauthorized users.

Answer 59

True.
(ex: For a user who is only allowed to see
employee information for those who work fordepartment 5, he may only access the view..
DEPT5EMP:
CREATE VIEW DEPT5EMP AS
SELECT *
FROM EMPLOYEE
WHERE Dno = 5;
)

Question 60

T/F: Changing the schema while the database is operational does not require recompilation of the database schema.

Answer 60

true.

Question 62

________ command is used to drop named schema elements, such as tables, domains, or constraint.

Answer 62

DROP

Question 63

Drop behavior options include: ______ and ________.

Answer 63

CASCADE, RESTRICT.
(EX: DROP SCHEMA COMPANY CASCADE;)

Question 64

What is the result of the following query?

DROP SCHEMA COMPANY CASCADE;

Answer 64

This removes the schema and all its elements including tables, views, constraints, etc.

Question 65

T/F: Alter table actions include: Adding or dropping a column (attribute), changing a column definition, adding or dropping table constraints.

Answer 65

true.
(EX: ALTER TABLE COMPANY.EMPLOYEE ADD COLUMN Job VARCHAR(12);
or
ALTER TABLE COMPANY.EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE;
)

Question 66

T/F: Column with RESTRICT is possible if no views refer to it.

Answer 66

true.

Question 67

T/F: Default values cannot be dropped and altered.

Answer 67

false, they can.
(EX:
ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn
DROP DEFAULT;
ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn
SET DEFAULT ‘333445555’;
)

Question 68

_________ nested queries, joined tables (in the FROM clause), outer joins, aggregate functions, grouping.

Answer 68

Complex SQL