Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

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
T/F: There are multiple mapping functions available in RDBMSs to change date formats for the DATE data type.
true.
26
Timestamp date type include the ______ and _____ fields.
DATE, TIME.
27
T/F: The timestamp data type comes with an additional minimum of six positions for decimal fractions of seconds.
true.
28
T/F: The timestamp data type has an optional WITH TIME ZONE qualifier.
true.
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
interval
30
DATE, TIME, Timestamp, INTERVAL data types can be _______ or converted to _______ formats for comparison.
cast, string.
31
T/F: The domain name is used with the attribute specification
true. EX: CREATE DOMAIN Ssn_types AS CHAR(9);
32
T/F: The domain makes it easier to change the data type for a domain that is used by numerous attributes.
true.
33
The domain improves schema ______.
readability.
34
T/F: User Defined Types (UDTs) are supported for object-oriented applications.
True.
35
Name the relational model's 3 basic contraint types that are supported in SQL.
Key constraint, entity integrity constraint, and referential integrity.
36
The ______ constraint is where a primary key cannot be duplicated.
key
37
The _______ contraint is where a primary key value cannot be null.
entity integrity
38
The referential constraint is where the foreign key must have a value that is already present as a primary key or may be ______.
null.
39
Name the attribute constraint that is used on the attribute domain to assign a default value for an attribute.
DEFAULT ## Footnote (ignore the line | it is only there because of a technical issue)
40
_________ is a attribute contraint in which NULL is not permitted for a particular attribute.
NOT NULL.
41
T/F: more than one constraint can be used in an attribute's domain.
True, ex: Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21); [the CHECK clause and NOT NULL were used]
42
T/F: The PRIMARY KEY clause specifies one **OR MORE** attributes that make up the primary key of a relation.
true. ex: Dnumber INT PRIMARY KEY;
43
The _________ clause specifies alternate (secondary) keys.
UNIQUE. ex: Dname VARCHAR(15) UNIQUE;
44
______ keys are the alternate (secondary) keys in the relational model.
candidate
45
The FOREIGN KEY clause's default operation is _____ update on violation.
reject
46
The FOREIGN KEY CLAUSE can come attached with a referential triggered action clause such as: _______, ________, and _________.
SET NULL, SET DEFAULT, SET CASCADE.
47
Actions taken by the DBMS for SET NULL or SET DEFAULT is the same for both ON ______ and ON ______.
DELETE, UPDATE.
48
Which referential triggered action is the most suitable option for "relationship" relations?
CASCADE.
49
T/F: Using the keyword CONSTRAINT is useful for later altering.
true. Ex: CONSTRAINT EmpPK PRIMARY KEY (Ssn);
50
Write a constraint statement in which the foreign key super ssn is null when deleted and cascaded when updated.
CONSTRAINT EmpSuperFK FOREIGN KEY (Super_Ssn) REFERENCES Employee(Ssn) ON DELETE SET NULL ON UPDATE CASCADE;
51
Additional Constraints on individual tuples within a relation are also possible using ______.
CHECK. ex: CHECK (Dept_create_date <= Mgr_start_date);
52
T/F: CHECK clauses at the end of a CREATE TABLE statement apply to each tuple **INDIVIDUALLY**.
true.
53
_________ statement is the one basic statement for retrieving information from a database.
SELECT
54
T/F: SQL never allows a table to have two or more tuples that are identical in all their attribute values.
False. Unlike relational model, SQL allows it. Tuple-id may be used as a key. It has multiset or bag behavior.
55
T/F: Relational model is strictly set-theory based.
true.
56
Write the basic form of the SELECT statement:
SELECT FROM WHERE ;
57
In the SELECT statement, __________ is a list of the relation names required to process the query.
(ignore the line it’s just a technical issue from the flash cards)
58
In the SELECT statement, __________ is a boolean expression that identifies the tuples to be retrieved by the query.
(ignore the line it’s just a technical issue from the flash cards)
59
<> is a __________ operator.
logical comparison
60
Projection attributes are the attributes whose values are to be ___________.
Retrieved
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.
true.
62
T/F: The same name can be used for two (or more) attributes in different relations.
true, as long as the attributes are in different relations.
63
T/F: You must qualify the attribute name with the relation name to prevent ambiguity.
true.
64
_________ or ____________ declare alternative relation names E and S to refer to the EMPLOYEE relation twice in a query
Aliases, tuple variables
65
T/F: The “AS” may be dropped in most SQL implementations.
True.
66
Missing _______ clause indicates no condition on tuple selection.
WHERE
67
In the selection statement, the effect is a _______________.
cross product (or Cartesian product)
68
When you specify an _________, it will retrieve all the attribute values of the selected tuples.
asterisk (*)
69
T/F: SQL automatically eliminate duplicate tuples in query results.
False, it does not.
70
T/F: For aggregate operations, duplicates must be accounted for.
true
71
Using the keyword ______ in the SELECT clause will have only distinct tuples remain in the result.
DISTINCT
72
Write the query for retrieving the salary of every employee.
SELECT ALL Salary FROM EMPLOYEE;
73
Write the query for retrieving all distinct salary values.
SELECT DISTINCT Salary FROM EMPLOYEE;
74
Set operations in SQL include: _______, _______, __________.
UNION, EXCEPT(difference), INTERSECT. Corresponding multiset operations: UNION ALL, EXCEPT ALL, INTERSECT ALL.
75
________ compatibility is needed for set operations to be valid.
Type
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).
77
________ comparsion operator is used for string pattern matching.
LIKE
78
In the LIKE comparison operator, _____ replaces an arbitrary number of zeros or more characters, and ____ replaces a single character.
%, underscore(_). Examples: WHERE Address LIKE ‘%Houston,TX%’; WHERE Ssn LIKE ‘_ _ 1_ _ 8901’;
79
If an apostrophe (’) is needed, it is represented as __________ so that it will not be interpreted as ending the string.
two consecutive apostrophes (”)
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.
‘AB_CD%EF’
81
Use the comparison operator instead for the following query: WHERE (Salary >= 30000 AND Salary <= 40000) AND Dno = 5;
WHERE(Salary BETWEEN 30000 AND 40000) AND Dno = 5;
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.
ORDER BY, DESC, ASC, end. Example: ORDER BY D.Dname DESC, E.Lname ASC, E.Fname ASC.
83
Write the basic SQL retrival query block with the ORDER BY clause.
SELECT FROM
WHERE ORDER BY ; ## Footnote (ignore the line it's only a technical issue from the flashcards)
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.
INSERT, UPDATE, DELETE.
85
T/F: Constraints on data types must be observed manually when using the INSERT command.
F, they are observed automatically. Any integrity constraints as a part of the DDL specification are enforced.
86
Write the basic INSERT command form.
INSERT INTO
VALUES (): ## Footnote (ignore the line | it is only there because of a technical issue)
87
Write the 2nd form of INSERT command statement that allows the user to specify explicit attribute names that correspond to the values provided.
INSERT INTO
() VALUES (); ## Footnote (ignore the line | it is only there because of a technical issue)
88
What does the following statement do?
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
What does the following statement do?
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
Tuples are deleted from only one table at a time (unless _________ is specified on a referential integrity constraint).
CASCADE
91
In what situation do all the tuples in the relation become deleted and the table becomes an empty table?
A missing WHERE-clause in the DELETE statement. (a WHERE-clause must be used to select the tuples to be deleted.)
92
T/F: The number of tuples deleted depends on the number of tuples in the relation that satisfy the WHERE-clause.
True.
93
Write the basic form of a DELETE statement.
DELETE FROM
WHERE ; or DELETE FROM
## Footnote (ignore the line | it is only there because of a technical issue)
94
T/F: Referential integrity specified as part of DDL specification is enforced when using the UPDATE command.
True
95
Write the basic form of the UPDATE statement.
UPDATE
SET WHERE ; ## Footnote (ignore the line | it is only there because of a technical issue)
96
T/F: Each UPDATE command modifies tuples in the same relation.
true.
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.
WHERE-clause, SET-clause.
98
_______ is a comprehensive language for relational database management.
SQL

IS230 (13 decks)

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