Brainscape's Knowledge GenomeTM

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


See full index

IS230 > Chapter 6 > Flashcards

Chapter 6 Flashcards

Basic SQL.

1
Q

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

A

schema name, identifier, descriptors.

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

Each statement in SQL ends with a _________.

A

semicolon

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

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

A

true.

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

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

A

catalog

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

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

A

true.

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

What is the schema creation statement?

A

CREATE SCHEMA <schema>;</schema>

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

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

A

attributes, types, intial constraints.

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

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

A

Virtual relations (views)

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

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

A

Base tables (base relations)

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

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

A

True. EX: Super_ssn is referencing Ssn.

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

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

A

true.

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

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

A

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

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

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

A

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

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

Name the 5 basic attribute data types in SQL.

A

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

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

Name the 2 numeric data types in SQL.

A

Interger numbers, Floating-point(real) numbers.

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

Name the 2 character-string data types.

A

fixed length, varying length.

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

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

A

SMALLINT

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

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

A

REAL

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

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

A

f, fixed.

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

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

A

character-string.

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

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

A

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

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

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

A

UNKNOWN.

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

The DATE data type has ____ positions.

A

ten

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

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

A

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

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

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

A

true.

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

Timestamp date type include the ______ and _____ fields.

A

DATE, TIME.

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

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

A

true.

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

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

A

true.

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

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

A

interval

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

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

A

cast, string.

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

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

A

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

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

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

A

true.

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

The domain improves schema ______.

A

readability.

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

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

A

True.

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

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

A

Key constraint, entity integrity constraint, and referential integrity.

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

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

A

key

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

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

A

entity integrity

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

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

A

null.

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

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

A

DEFAULT <value|>

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

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

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

A

NOT NULL.

41
Q

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

A

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

42
Q

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

A

true. ex: Dnumber INT PRIMARY KEY;

43
Q

The _________ clause specifies alternate (secondary) keys.

A

UNIQUE. ex: Dname VARCHAR(15) UNIQUE;

44
Q

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

A

candidate

45
Q

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

A

reject

46
Q

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

A

SET NULL, SET DEFAULT, SET CASCADE.

47
Q

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

A

DELETE, UPDATE.

48
Q

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

A

CASCADE.

49
Q

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

A

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

50
Q

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

A

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

51
Q

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

A

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

52
Q

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

A

true.

53
Q

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

A

SELECT

54
Q

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

A

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

55
Q

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

A

true.

56
Q

Write the basic form of the SELECT statement:

A

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

57
Q

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

A

<table list|>

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

58
Q

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

A

<condition|>

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

59
Q

<> is a __________ operator.

A

logical comparison

60
Q

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

A

Retrieved

61
Q

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.

A

true.

62
Q

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

A

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

63
Q

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

A

true.

64
Q

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

A

Aliases, tuple variables

65
Q

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

A

True.

66
Q

Missing _______ clause indicates no condition on tuple selection.

A

WHERE

67
Q

In the selection statement, the effect is a _______________.

A

cross product (or Cartesian product)

68
Q

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

A

asterisk (*)

69
Q

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

A

False, it does not.

70
Q

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

A

true

71
Q

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

A

DISTINCT

72
Q

Write the query for retrieving the salary of every employee.

A

SELECT ALL Salary FROM EMPLOYEE;

73
Q

Write the query for retrieving all distinct salary values.

A

SELECT DISTINCT Salary FROM EMPLOYEE;

74
Q

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

A

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

75
Q

________ compatibility is needed for set operations to be valid.

A

Type

76
Q

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

A
77
Q

________ comparsion operator is used for string pattern matching.

A

LIKE

78
Q

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

A

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

79
Q

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

A

two consecutive apostrophes (”)

80
Q

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

A

‘AB_CD%EF’

81
Q

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

A

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

82
Q

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

A

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

83
Q

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

A

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

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

84
Q

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.

A

INSERT, UPDATE, DELETE.

85
Q

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

A

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

86
Q

Write the basic INSERT command form.

A

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

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

87
Q

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

A

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

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

88
Q

What does the following statement do?

A

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

89
Q

What does the following statement do?

A

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.

90
Q

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

A

CASCADE

91
Q

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

A

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

92
Q

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

A

True.

93
Q

Write the basic form of a DELETE statement.

A

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

or

DELETE FROM <table name>

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

94
Q

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

A

True

95
Q

Write the basic form of the UPDATE statement.

A

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

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

96
Q

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

A

true.

97
Q

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.

A

WHERE-clause, SET-clause.

98
Q

_______ is a comprehensive language for relational database management.

A

SQL

IS230 (13 decks)

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