Chapter 6

Question 1

SQL schema Identified by a _________ Includes an authorization _________ and ___________ for each element.

Answer 1

schema name, identifier, descriptors.

Question 2

Each statement in SQL ends with a _________.

Answer 2

semicolon

Question 3

T/F: Schema elements include tables, constraints, views, domains, and other constructs.

Answer 3

true.

Question 4

___________ is the named collection of schemas in an SQL environment.

Answer 4

catalog

Question 5

T/F: SQL also has the concept of a cluster of catalogs.

Answer 5

true.

Question 6

What is the schema creation statement?

Answer 6

CREATE SCHEMA <schema>;</schema>

Question 7

In Create table statement, you need to specify the _______, their _______, and _________.

Answer 7

attributes, types, intial constraints.

Question 8

______________ are created through the CREATE VIEW statement. Do not correspond to any physical file.

Answer 8

Virtual relations (views)

Question 9

___________ where the relation and its tuples are actually created and stored as a file by the DBMS.

Answer 9

Base tables (base relations)

Question 10

T/F: There can be an attribute referencing another attribute within the same table.

Answer 10

True. EX: Super_ssn is referencing Ssn.

Question 11

T/F: There can exist a table in which all of it’s attributes consist of a primary key and a foreign key.

Answer 11

true.

Question 12

Write a creation statemement for: Table Employee with employee’s first name, sex, salary, birth date, department number, ssn, and super ssn.

Answer 12

CREATE TABLE Employee (
Fname VARCHAR(15) NOT NULL,
Sex CHAR,
Salary Decimal(10, 2),
Ssn CHAR(9),
Super_ssn CHAR(9),
Bdate DATE,
Dno INT NOT NULL,
PRIMARY KEY (Ssn) );

Question 13

Name 2 reason why a foreign key might cause an error.

Answer 13

1- Circular references
2- They refer to a table that has not yet been created.

Question 14

Name the 5 basic attribute data types in SQL.

Answer 14

Numeric, character-string, bit-string, boolean, date.

Question 15

Name the 2 numeric data types in SQL.

Answer 15

Interger numbers, Floating-point(real) numbers.

Question 16

Name the 2 character-string data types.

Answer 16

fixed length, varying length.

Question 17

INT, INTEGER, and _________ are examples of integer data types.

Answer 17

SMALLINT

Question 18

FLOAT (or _____) and DOUBLE PRECISION are examples of floating-point number data types.

Answer 18

REAL

Question 19

T/F: CHAR (n), and CHARACTER (n) are examples of varying length character-string data types.

Answer 19

f, fixed.

Question 20

VARCHAR (n), CHAR VARYING (n), and CHARACTER VARYING (n) are examples of ____________ data types.

Answer 20

character-string.

Question 21

T/F: Bit-string data types can be fixed or varying length.

Answer 21

T, EX: BIT(n) is fixed length, and BIT VARYING (n) is varying length.

Question 22

Boolean data types have values of TRUE or FALSE or _______ for NULL.

Answer 22

UNKNOWN.

Question 23

The DATE data type has ____ positions.

Answer 23

ten

Question 24

T/F: The DATE data type components are YEAR, MONTH, and DAY in the form YYYY-DD-MM.

Answer 24

False, it is in the form YYYY-MM-DD.

Question 25

T/F: There are multiple mapping functions available in RDBMSs to change date formats for the DATE data type.

Answer 25

true.

Question 26

Timestamp date type include the ______ and _____ fields.

Answer 26

DATE, TIME.

Question 27

T/F: The timestamp data type comes with an additional minimum of six positions for decimal fractions of seconds.

Answer 27

true.

Question 28

T/F: The timestamp data type has an optional WITH TIME ZONE qualifier.

Answer 28

true.

Question 29

The ______ data type specifies a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp

Answer 29

interval

Question 30

DATE, TIME, Timestamp, INTERVAL data types can be _______ or converted to _______ formats for comparison.

Answer 30

cast, string.

Question 31

T/F: The domain name is used with the attribute specification

Answer 31

true. EX: CREATE DOMAIN Ssn_types AS CHAR(9);

Question 32

T/F: The domain makes it easier to change the data type for a domain that is used by numerous attributes.

Answer 32

true.

Question 33

The domain improves schema ______.

Answer 33

readability.

Question 34

T/F: User Defined Types (UDTs) are supported for object-oriented applications.

Answer 34

True.

Question 35

Name the relational model’s 3 basic contraint types that are supported in SQL.

Answer 35

Key constraint, entity integrity constraint, and referential integrity.

Question 36

The ______ constraint is where a primary key cannot be duplicated.

Answer 36

key

Question 37

The _______ contraint is where a primary key value cannot be null.

Answer 37

entity integrity

Question 38

The referential constraint is where the foreign key must have a value that is already present as a primary key or may be ______.

Answer 38

null.

Question 39

Name the attribute constraint that is used on the attribute domain to assign a default value for an attribute.

Answer 39

DEFAULT <value|>

(ignore the line | it is only there because of a technical issue)

Question 40

_________ is a attribute contraint in which NULL is not permitted for a particular attribute.

Answer 40

NOT NULL.

Question 41

T/F: more than one constraint can be used in an attribute’s domain.

Answer 41

True, ex: Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);
[the CHECK clause and NOT NULL were used]

Question 42

T/F: The PRIMARY KEY clause specifies one OR MORE attributes that make up the primary key of a relation.

Answer 42

true. ex: Dnumber INT PRIMARY KEY;

Question 43

The _________ clause specifies alternate (secondary) keys.

Answer 43

UNIQUE. ex: Dname VARCHAR(15) UNIQUE;

Question 44

______ keys are the alternate (secondary) keys in the relational model.

Answer 44

candidate

Question 45

The FOREIGN KEY clause’s default operation is _____ update on violation.

Answer 45

reject

Question 46

The FOREIGN KEY CLAUSE can come attached with a referential triggered action clause such as: _______, ________, and _________.

Answer 46

SET NULL, SET DEFAULT, SET CASCADE.

Question 47

Actions taken by the DBMS for SET NULL or SET DEFAULT is the same for both ON ______ and ON ______.

Answer 47

DELETE, UPDATE.

Question 48

Which referential triggered action is the most suitable option for “relationship” relations?

Answer 48

CASCADE.

Question 49

T/F: Using the keyword CONSTRAINT is useful for later altering.

Answer 49

true. Ex: CONSTRAINT EmpPK PRIMARY KEY (Ssn);

Question 50

Write a constraint statement in which the foreign key super ssn is null when deleted and cascaded when updated.

Answer 50

CONSTRAINT EmpSuperFK FOREIGN KEY (Super_Ssn) REFERENCES Employee(Ssn)
ON DELETE SET NULL ON UPDATE CASCADE;

Question 51

Additional Constraints on individual tuples within a relation are also possible using ______.

Answer 51

CHECK. ex: CHECK (Dept_create_date <=
Mgr_start_date);

Question 52

T/F: CHECK clauses at the end of a CREATE TABLE statement apply to each tuple INDIVIDUALLY.

Answer 52

true.

Question 53

_________ statement is the one basic statement for retrieving information from a database.

Answer 53

SELECT

Question 54

T/F: SQL never allows a table to have two or more tuples that are identical in all their attribute values.

Answer 54

False. Unlike relational model, SQL allows it. Tuple-id may be used as a key. It has multiset or bag behavior.

Question 55

T/F: Relational model is strictly set-theory based.

Answer 55

true.

Question 56

Write the basic form of the SELECT statement:

Answer 56

SELECT <attribute>
FROM <table list>
WHERE <condition>;</condition></attribute>

Question 57

In the SELECT statement, __________ is a list of the relation names required to process the query.

Answer 57

<table list|>

(ignore the line it’s just a technical issue from the flash cards)

Question 58

In the SELECT statement, __________ is a boolean expression that identifies the tuples to be retrieved by the query.

Answer 58

<condition|>

(ignore the line it’s just a technical issue from the flash cards)

Question 59

<> is a __________ operator.

Answer 59

logical comparison

Question 60

Projection attributes are the attributes whose values are to be ___________.

Answer 60

Retrieved

Question 61

T/F: Selection condition is a boolean condition that must be true for any retrieved tuple. They also include join conditions when multiple relations are involved.

Answer 61

true.

Question 62

T/F: The same name can be used for two (or more) attributes in different relations.

Answer 62

true, as long as the attributes are in different relations.

Question 63

T/F: You must qualify the attribute name with the relation name to prevent ambiguity.

Answer 63

true.

Question 64

_________ or ____________ declare alternative relation names E and S to refer to the EMPLOYEE relation twice in a query

Answer 64

Aliases, tuple variables

Question 65

T/F: The “AS” may be dropped in most SQL implementations.

Answer 65

True.

Question 66

Missing _______ clause indicates no condition on tuple selection.

Answer 66

WHERE

Question 67

In the selection statement, the effect is a _______________.

Answer 67

cross product (or Cartesian product)

Question 68

When you specify an _________, it will retrieve all the attribute values of the selected tuples.

Answer 68

asterisk (*)

Question 69

T/F: SQL automatically eliminate duplicate tuples in query results.

Answer 69

False, it does not.

Question 70

T/F: For aggregate operations, duplicates must be accounted for.

Answer 70

true

Question 71

Using the keyword ______ in the SELECT clause will have only distinct tuples remain in the result.

Answer 71

DISTINCT

Question 72

Write the query for retrieving the salary of every employee.

Answer 72

SELECT ALL Salary FROM EMPLOYEE;

Question 73

Write the query for retrieving all distinct salary values.

Answer 73

SELECT DISTINCT Salary FROM EMPLOYEE;

Question 74

Set operations in SQL include: _______, _______, __________.

Answer 74

UNION, EXCEPT(difference), INTERSECT.
Corresponding multiset operations: UNION ALL, EXCEPT ALL, INTERSECT ALL.

Question 75

________ compatibility is needed for set operations to be valid.

Answer 75

Type

Question 76

Write the result of the following:
(1) R(A) UNION ALL S(A).
(2) R(A) EXCEPT ALL S(A).
(3) R(A) INTERSECT ALL S(A).

Answer 76

Question 77

________ comparsion operator is used for string pattern matching.

Answer 77

LIKE

Question 78

In the LIKE comparison operator, _____ replaces an arbitrary number of zeros or more characters, and ____ replaces a single character.

Answer 78

%, underscore().
Examples:
WHERE Address LIKE ‘%Houston,TX%’;
WHERE Ssn LIKE ‘ _ 1_ _ 8901’;

Question 79

If an apostrophe (’) is needed, it is represented as __________ so that it will not be interpreted as ending the string.

Answer 79

two consecutive apostrophes (”)

Question 80

‘AB_CD\%EF’ ESCAPE ‘\’ represents the
literal string _________because \ is specified as the escape character. Any character not used in the string can be chosen as the escape character.

Answer 80

‘AB_CD%EF’

Question 81

Use the comparison operator instead for the following query:
WHERE (Salary >= 30000 AND Salary <= 40000) AND Dno = 5;

Answer 81

WHERE(Salary BETWEEN 30000 AND 40000) AND Dno = 5;

Question 82

________ clause is used with _____ to see the result in a descending order, or ______ to specify ascending order explicitly. It is typically placed at the _____ of the query.

Answer 82

ORDER BY, DESC, ASC, end.
Example: ORDER BY D.Dname DESC, E.Lname ASC, E.Fname ASC.

Question 83

Write the basic SQL retrival query block with the ORDER BY clause.

Answer 83

SELECT <attribute list|>
FROM <table list|>
WHERE <condition|>
ORDER BY <attribute list|>;

(ignore the line it’s only a technical issue from the flashcards)

Question 84

The three commands used to modify the database: ___________ typically inserts a tuple (row) in a relation (table). ____________ may update a number of tuples (rows) in a relation (table) that satisfy the condition. And ____________ may also delete a number of tuples
(rows) in a relation (table) that satisfy the condition.

Answer 84

INSERT, UPDATE, DELETE.

Question 85

T/F: Constraints on data types must be observed manually when using the INSERT command.

Answer 85

F, they are observed automatically. Any integrity constraints as a part of the DDL specification are enforced.

Question 86

Write the basic INSERT command form.

Answer 86

INSERT INTO <table name|>
VALUES (<new attribute value list|>):

(ignore the line | it is only there because of a technical issue)

Question 87

Write the 2nd form of INSERT command statement that allows the user to specify explicit attribute names that correspond to the values provided.

Answer 87

INSERT INTO <table name|> (<attribute list|>)
VALUES (<new attribute values list|>);

(ignore the line | it is only there because of a technical issue)

Question 88

What does the following statement do?

Answer 88

inserts multiple tuples where
a new table is loaded values from the result of a query.
(In other words, we used the SELECT statement as a way to fill in new values in combination with the INSERT command.)

Question 89

What does the following statement do?

Answer 89

Another variation of INSERT is used for bulk-loading of serveral tuples into the table…
A new table D5EMPS is created with the same attributes as EMPLOYEE and using LIKE and DATA in the syntax, it was loaded with its entire data.

Question 90

Tuples are deleted from only one table at a time (unless _________ is specified on a referential integrity constraint).

Answer 90

CASCADE

Question 91

In what situation do all the tuples in the relation become deleted and the table becomes an empty table?

Answer 91

A missing WHERE-clause in the DELETE statement. (a WHERE-clause must be used to select the tuples to be deleted.)

Question 92

T/F: The number of tuples deleted depends on the number of tuples in the relation that satisfy the WHERE-clause.

Answer 92

True.

Question 93

Write the basic form of a DELETE statement.

Answer 93

DELETE FROM <table name>
WHERE <condition|>;

or

DELETE FROM <table name>

(ignore the line | it is only there because of a technical issue)

Question 94

T/F: Referential integrity specified as part of DDL specification is enforced when using the UPDATE command.

Answer 94

True

Question 95

Write the basic form of the UPDATE statement.

Answer 95

UPDATE <table name|>
SET <attribute = new value|>
WHERE <condition|>;

(ignore the line | it is only there because of a technical issue)

Question 96

T/F: Each UPDATE command modifies tuples in the same relation.

Answer 96

true.

Question 97

In the UPDATE statement, a (WHERE-clause/SET-clause) selects the tuples to be modified, and an additional (WHERE-clause/SET-clause) specifies the attributes to be modified and their new values.

Answer 97

WHERE-clause, SET-clause.

Question 98

_______ is a comprehensive language for relational database management.

Answer 98

SQL