Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

IS230 > Chapter 7 > Flashcards

Chapter 7 Flashcards

don't forget to make missing slides 57 - 63 flashcards.

1
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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?

A

Comparison operator IN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

Nested queries

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

= ANY
or
= SOME

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

__________ expresses all values in the database.

A

ALL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

true

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

________ 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.

A

EXISTS, NOT EXISTS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

True.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

UNIQUE (Q)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A

AS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

Joined table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

JOIN may also be
called ________.

A

INNRT JOIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

NATURAL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

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’; )

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

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’;

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

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

A

INNER JOIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

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

A

INNER

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

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

A

LEFT OUTER

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

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

A

RIGHT OUTER

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

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

A

Left, NULL.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

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

A

aggregate functions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

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

A

COUNT, SUM, MAX, MIN, AVG.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

________ create subgroups of tuples before summarizing.

A

Grouping

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

To select entire groups, a _________ clause is used.

A

HAVING

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

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

A

SELECT, HAVING.

29
Q

What does the following query return?

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

A

a single row of computed values from EMPLOYEE table

30
Q

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

A

discarded

31
Q

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)

A

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

32
Q

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

A

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

33
Q

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 ________.

A

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

34
Q

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

A

SOME

35
Q

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

A

TRUE

36
Q

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

A

rows

37
Q

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

A

Grouping (GROUP BY clause)

38
Q

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

A

null

39
Q

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

A

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

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

40
Q

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

A

HAVING

41
Q

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

A

WHERE, HAVING

42
Q

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

A

WITH

43
Q

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

A

true

44
Q

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

A

CASE

45
Q

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

A

true.

46
Q

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

A

false, or updating not deleting tuples

47
Q

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 ;

A

CASE

48
Q

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

A

true.

49
Q

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

A

TRIGGER

50
Q

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

A

ASSERTION

51
Q

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

A

false, CREATE ASSERTION.

52
Q

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

A

true.

53
Q

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

A

condition

54
Q

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

A

Views (virtual tables)

55
Q

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

A

true.

56
Q

T/F: Update not permitted on aggregate views.

A

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)

57
Q

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

A

false, can always issue a retrival query.

58
Q

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

A

true.

59
Q

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

A

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;
)

60
Q

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

A

true.

61
Q
A
62
Q

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

A

DROP

63
Q

Drop behavior options include: ______ and ________.

A

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

64
Q

What is the result of the following query?

DROP SCHEMA COMPANY CASCADE;

A

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

65
Q

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

A

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

66
Q

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

A

true.

67
Q

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

A

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’;
)

68
Q

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

A

Complex SQL

69
Q
A

IS230 (13 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions