Chapter 6 Flashcards
Basic SQL.
SQL schema Identified by a _________ Includes an authorization _________ and ___________ for each element.
schema name, identifier, descriptors.
Each statement in SQL ends with a _________.
semicolon
T/F: Schema elements include tables, constraints, views, domains, and other constructs.
true.
___________ is the named collection of schemas in an SQL environment.
catalog
T/F: SQL also has the concept of a cluster of catalogs.
true.
What is the schema creation statement?
CREATE SCHEMA <schema>;</schema>
In Create table statement, you need to specify the _______, their _______, and _________.
attributes, types, intial constraints.
______________ are created through the CREATE VIEW statement. Do not correspond to any physical file.
Virtual relations (views)
___________ where the relation and its tuples are actually created and stored as a file by the DBMS.
Base tables (base relations)
T/F: There can be an attribute referencing another attribute within the same table.
True. EX: Super_ssn is referencing Ssn.
T/F: There can exist a table in which all of it’s attributes consist of a primary key and a foreign key.
true.
Write a creation statemement for: Table Employee with employee’s first name, sex, salary, birth date, department number, ssn, and super ssn.
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) );
Name 2 reason why a foreign key might cause an error.
1- Circular references
2- They refer to a table that has not yet been created.
Name the 5 basic attribute data types in SQL.
Numeric, character-string, bit-string, boolean, date.
Name the 2 numeric data types in SQL.
Interger numbers, Floating-point(real) numbers.
Name the 2 character-string data types.
fixed length, varying length.
INT, INTEGER, and _________ are examples of integer data types.
SMALLINT
FLOAT (or _____) and DOUBLE PRECISION are examples of floating-point number data types.
REAL
T/F: CHAR (n), and CHARACTER (n) are examples of varying length character-string data types.
f, fixed.
VARCHAR (n), CHAR VARYING (n), and CHARACTER VARYING (n) are examples of ____________ data types.
character-string.
T/F: Bit-string data types can be fixed or varying length.
T, EX: BIT(n) is fixed length, and BIT VARYING (n) is varying length.
Boolean data types have values of TRUE or FALSE or _______ for NULL.
UNKNOWN.
The DATE data type has ____ positions.
ten
T/F: The DATE data type components are YEAR, MONTH, and DAY in the form YYYY-DD-MM.
False, it is in the form YYYY-MM-DD.
T/F: There are multiple mapping functions available in RDBMSs to change date formats for the DATE data type.
true.
Timestamp date type include the ______ and _____ fields.
DATE, TIME.
T/F: The timestamp data type comes with an additional minimum of six positions for decimal fractions of seconds.
true.
T/F: The timestamp data type has an optional WITH TIME ZONE qualifier.
true.
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
DATE, TIME, Timestamp, INTERVAL data types can be _______ or converted to _______ formats for comparison.
cast, string.
T/F: The domain name is used with the attribute specification
true. EX: CREATE DOMAIN Ssn_types AS CHAR(9);
T/F: The domain makes it easier to change the data type for a domain that is used by numerous attributes.
true.
The domain improves schema ______.
readability.
T/F: User Defined Types (UDTs) are supported for object-oriented applications.
True.
Name the relational model’s 3 basic contraint types that are supported in SQL.
Key constraint, entity integrity constraint, and referential integrity.
The ______ constraint is where a primary key cannot be duplicated.
key
The _______ contraint is where a primary key value cannot be null.
entity integrity
The referential constraint is where the foreign key must have a value that is already present as a primary key or may be ______.
null.
Name the attribute constraint that is used on the attribute domain to assign a default value for an attribute.
DEFAULT <value|>
(ignore the line | it is only there because of a technical issue)
_________ is a attribute contraint in which NULL is not permitted for a particular attribute.
NOT NULL.
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]
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;
The _________ clause specifies alternate (secondary) keys.
UNIQUE. ex: Dname VARCHAR(15) UNIQUE;
______ keys are the alternate (secondary) keys in the relational model.
candidate
The FOREIGN KEY clause’s default operation is _____ update on violation.
reject
The FOREIGN KEY CLAUSE can come attached with a referential triggered action clause such as: _______, ________, and _________.
SET NULL, SET DEFAULT, SET CASCADE.
Actions taken by the DBMS for SET NULL or SET DEFAULT is the same for both ON ______ and ON ______.
DELETE, UPDATE.
Which referential triggered action is the most suitable option for “relationship” relations?
CASCADE.
T/F: Using the keyword CONSTRAINT is useful for later altering.
true. Ex: CONSTRAINT EmpPK PRIMARY KEY (Ssn);
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;
Additional Constraints on individual tuples within a relation are also possible using ______.
CHECK. ex: CHECK (Dept_create_date <=
Mgr_start_date);
T/F: CHECK clauses at the end of a CREATE TABLE statement apply to each tuple INDIVIDUALLY.
true.
_________ statement is the one basic statement for retrieving information from a database.
SELECT
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.
T/F: Relational model is strictly set-theory based.
true.
Write the basic form of the SELECT statement:
SELECT <attribute>
FROM <table list>
WHERE <condition>;</condition></attribute>
In the SELECT statement, __________ is a list of the relation names required to process the query.
<table list|>
(ignore the line it’s just a technical issue from the flash cards)
In the SELECT statement, __________ is a boolean expression that identifies the tuples to be retrieved by the query.
<condition|>
(ignore the line it’s just a technical issue from the flash cards)
<> is a __________ operator.
logical comparison
Projection attributes are the attributes whose values are to be ___________.
Retrieved
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.
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.
T/F: You must qualify the attribute name with the relation name to prevent ambiguity.
true.
_________ or ____________ declare alternative relation names E and S to refer to the EMPLOYEE relation twice in a query
Aliases, tuple variables
T/F: The “AS” may be dropped in most SQL implementations.
True.
Missing _______ clause indicates no condition on tuple selection.
WHERE
In the selection statement, the effect is a _______________.
cross product (or Cartesian product)
When you specify an _________, it will retrieve all the attribute values of the selected tuples.
asterisk (*)
T/F: SQL automatically eliminate duplicate tuples in query results.
False, it does not.
T/F: For aggregate operations, duplicates must be accounted for.
true
Using the keyword ______ in the SELECT clause will have only distinct tuples remain in the result.
DISTINCT
Write the query for retrieving the salary of every employee.
SELECT ALL Salary FROM EMPLOYEE;
Write the query for retrieving all distinct salary values.
SELECT DISTINCT Salary FROM EMPLOYEE;
Set operations in SQL include: _______, _______, __________.
UNION, EXCEPT(difference), INTERSECT.
Corresponding multiset operations: UNION ALL, EXCEPT ALL, INTERSECT ALL.
________ compatibility is needed for set operations to be valid.
Type
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).
________ comparsion operator is used for string pattern matching.
LIKE
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’;
If an apostrophe (’) is needed, it is represented as __________ so that it will not be interpreted as ending the string.
two consecutive apostrophes (”)
‘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’
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;
________ 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.
Write the basic SQL retrival query block with the ORDER BY clause.
SELECT <attribute list|>
FROM <table list|>
WHERE <condition|>
ORDER BY <attribute list|>;
(ignore the line it’s only a technical issue from the flashcards)
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.
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.
Write the basic INSERT command form.
INSERT INTO <table name|>
VALUES (<new attribute value list|>):
(ignore the line | it is only there because of a technical issue)
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 <table name|> (<attribute list|>)
VALUES (<new attribute values list|>);
(ignore the line | it is only there because of a technical issue)
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.)
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.
Tuples are deleted from only one table at a time (unless _________ is specified on a referential integrity constraint).
CASCADE
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.)
T/F: The number of tuples deleted depends on the number of tuples in the relation that satisfy the WHERE-clause.
True.
Write the basic form of a DELETE statement.
DELETE FROM <table name>
WHERE <condition|>;
or
DELETE FROM <table name>
(ignore the line | it is only there because of a technical issue)
T/F: Referential integrity specified as part of DDL specification is enforced when using the UPDATE command.
True
Write the basic form of the UPDATE statement.
UPDATE <table name|>
SET <attribute = new value|>
WHERE <condition|>;
(ignore the line | it is only there because of a technical issue)
T/F: Each UPDATE command modifies tuples in the same relation.
true.
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.
_______ is a comprehensive language for relational database management.
SQL
