SQL: A Commercial Database Language Flashcards
(141 cards)
1
Q
The name SQL is derived from
A
Structured Query Language
2
Q
Sequel was originally called ______.
A
SEQUEL ( for Structure English QUEry Language).
3
Q
What does it mean that SQL is a comprehensive database language?
A
It has statements for data defintion, query and update (in this sense it is both a DDL and a DML). In addiotion, it has facitilities for defining views, for specifying security anduthorization, for defining integrity constaints, and for specifying transaction controls. It also has rules for embedding SQL statements into a general-purpose programming language.
4
Q
The SQL language provides a high-level declarative language interface meaning
A
The SQL language provides a high-level declarative language interface meaning the user specifies what the result is to be and leaves the decisions on how to execute the query to the DBMS.
5
Q
The terms table, row, and column are used in SQL in documents for ____, ____ and ____ respectively.
A
table = relation
row = tuple
column = attribute
6
Q
is SQL case sensitive?
A
No. It treats upper and lower case letters as the same letter. Only inside quotes does SQL make a distinction between upper and lower case letters.
7
Q
The concept of ____ is used in SQL to group together constructs that belong to the same database application
A
schema
8
Q
A schema includes ______ such as tables constraints, view, domains, authorization grants and other constructs.
A
schema elements
9
Q
A SQL schema is identified by a schema name and includes an _______ to indicate the user who owns the scema. In addition, it can include _______ for every element in the schema.
A
authorization identifier ; descriptors
10
Q
Write a SQL statement that creates a schema called COMPANY owned by a user with authorization identifier JSMITH
A
CREATE SCHEMA COMPANY AUTHORIZATION JSMITH;
11
Q
The concept of a ______ is used in SQL to denote a named collection of schemas.
A
catalog
12
Q
A catalog always contains a special schema called _____. This schema provides information on all the schemas in the catalog and all the element descriptors in these schemas.
A
INFORMATION_SCHEMA
13
Q
True or False: Some elements (e.g. domain definitions) can be shared by schemas in the same catalog.
Integrity constraints can be defined between relations only if they do not exist in schedmas of the same catalog
A
True.
False. Integrity constraints can be defined between relations only if they exist in schemas of the same catalog.
14
Q
What data types are included in SQL?
A
numerics, character-string, bit-string, data and time.
15
Q
What are the numeric types in SQL and how are they specified?
A
16
Q
What are the character string data types in SQL and how are they specified?
A
17
Q
What are the bit string data types in SQL and how are they specified?
A
18
Q
What are the data and time data types in SQL and how are they specified?
A
19
Q
How do you create a domain in SQL on SSN_TYPE to be a character data type consisting of 9 characters?
A
CREATE DOMAIN SSN_TYPE AS CHAR(9);
20
Q
A create domain statement can have an optional _______ and an optional
_____________.
.
A
default specification ; list of constraints
21
Q
What does the CREATE TABLE command do?
A
The CREATE TABLE command is used to specify a new relation, its attributes, their data types and constraints.
The key, entity integrity and referential integrity constraints can be specified within a CREATE TABLE statement or later using the ALTER TABLE command.
22
Q
Create a relation named department with attributes: DNAME, DNUMBER, MGRSSN, MGRSTARTDATE. The primary key is DNUMBER and the MGRSSN and EMPLOYEE are foreign keys. DNAME is a secondary key (unique). Write a command to create this relation.
A
23
Q
A constraint can be given a name following the keyword ________. Giving names to constraints, is:
A) optional but is a good practice to follow.
B) necessary and a bad practice to follow.
C) unnecessary but a bad practice to follow.
D) necessary and a good practice to follow.
A
CONSTRAINT;
A) optional but is a good practice to follow.
24
Q
True or False: The names of some of the constraints within a particular can match (not unique).
A
False. The names of all constraints withing a particular schema must be unique. A constraint name can be used to drop a constraint (and replace it later by another constraint).
25
What is SQL's default policy for any modificiations that violate the referential integrity constraint?
Any modifications violating the referential integrity constraints is rejected by the system.
26
In SQL, the schema designer can specify the action of a referenced primary key value, by attaching a referential triggered action clause to a foreign key constraint.
What are three options?
SET NULL, CASCADE, SET DEFAULT
27
In SQL, the schema designer can specify the action of a referenced primary key value, by attaching a referential triggered action clause to a foreign key constraint.
The options include SET NULL, CASCADE, SET DEFAULT.
They can be qualified on either ______ or \_\_\_\_\_\_.
.
ON DELETE ; ON UPDATE
28
How would you specify a constraint called EMPPK that enforces for the EMPLOYEE relation's primary key is SSN?
29
How would you specify a constraint called EMPSUPERFK that enforces for the EMPLOYEE relation's primary foreign key is SUPERSSN (references relation EMPLOYEE), sets the value to null on delete, and cascades the changes on update?
How would you specify a constraint called EMPDEPTFK that enforces for the EMPLOYEE foreign key references DEPARTMENT, on delete sets the value to the default value of 4, and on update cascades the changes?
30
How would you specify a constraint called EMPDEPTFK that enforces for the EMPLOYEE foreign key references DEPARTMENT, on delete sets the value to the default value of 4, and on update cascades the changes?
31
Can the name of a schema be explicitly specified in the definition of a table?
Yes
CREATE TABLE COMPANY.DEPARTMENT....
32
A schema can be deleted using the _______ command. It has two options: _____ and \_\_\_\_\_.
DROP SCHEMA; CASCADE; RESTRICT
33
For DROP SCHEMA, what happens if RESTRICT is specified ?
The operation will fail if the schema if nonempty; otherwise it will be succeed and the schema will be destroyed.
34
For DROP SCHEMA, what happens if CASCADE is specified ?
If CASCADE is spedicifed the operation will always succeed.
## Footnote
DROP SCHEMA COMPANY CASCADE;
35
A table can be deleted using the _________ command. Two options: _____ and \_\_\_\_\_.
DROP TABLE, CASCADE, RESTRICT
36
For DROP TABLE, what happens if RESTRICT is specified ?
If RESTRICT is specified, the operation will succeed only if the table is not referenced in any constraints or views.
37
For DROP TABLE, what happens if CASCADE is specified ?
If CASCADE is specified, the operation will always succeed and propogate to all constraints and views that reference the table.
DROP TABLE DEPENDENT CASCADE;
38
How do you add a column to relation EMPLOYEE with attribute name JOB and domain of variable char of length 12?
ALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12);
39
ALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12) NOT NULL;
What does this command do?
This command creates a new attribute for relation EMPLOYEE called JOB which is a character string with variable length up to 12 NOT NULL forces this attribute to have a value. A DEFAULT clause can also be specified. If no such clause is specified the new attribute will have NULLs in all the tuples of the relation after the command is executed.
40
How to drop a column called ADDRESS from relation EMPLOYEE with so that the deletion is always successful and propogates to all constraints and views that referencee the column?
ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS CASCADE;
41
When dropping a column, what is the difference between CASCADE and RESTRICT?
The **CASCADE** option: all constraints and views that reference the column are dropped from the schema.
The **RESTRICT** option. the command is successful only if no views or constraints reference the column.
42
How would you drop a default clause in a column called MGRSSN for relation DEPARTMENT in the COMPANY SCHEMA?
ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN DROP DEFAULT;
43
How would you add a default clause to a column called MGRSSN in relation DEPARTMENT in the COMPANY schema such that the default MGRSSN is now '33344555'?
**_Adding a default clause in a column:_**
ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN SET DEFAULT '33344555';
44
What must a constraint have in order to be dropped?
To be dropped a constraint must have a name.
45
Write the command that drops the constrain from relation EMPLOYEE called EMPSUPERFK such that all foreign key definitions that reference the candidate key will be dropped too.
ALTER TABLE COMPANY.EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE;
46
When dropping a CONSTRAINT, CASCADE and RESTRICT have meaning only if \_\_\_\_\_\_\_\_\_\_\_\_\_.
the constraint is specified, all the foreign key definitions that reference the candidate key will be dropped too.
47
Write a command that adds a constraint called EMPSUPERFK that enforces the foreign key SUPERSSN references an EMPLOYEE.
ALTER TABLE COMPANY.EMPLOYEE ADD CONSTRAINT EMPSUPERFK FOREIGN KEY SUPERSSN REFERENCES EMPLOYEE;
48
True or False: When adding a constraing using ALTER TABLE, the constraint to be created can be named or unnamed.
True.
49
What is the problem with these table definitions?
The problem is taht either EMPLOYEE or DEPARTMENT table has to be defined first.
In both cases, the system will issue an error message.
50
What is a possible solution to the problem in this code?
The chicken and egg problem in this code can be solves by postponing the foreign-key constraint in the first table. For instance, if CREATE TABLE EMPLOYEE is executed first, we should not have the foreign key clause FOREIGN KEY (DNO) ... in the statement.
After CREATE TABLE DEPARTMENT has been process, we can add the desired constraint to employee using the ALTER TABLE command:
51
If we want to populate our COMPANY database, we face another chicken and egg problem because insertions of EMPLOYEE's with non NULL DNO's will be rejected because it violates the foreign key constraint:
MRGSSN references EMPLOYEE(SSN). What is a solution to this problem?
One solution is to initially replace DNO component in all tuples to be inserted in the EMPLOYEE relation with NULL. Then, when DEPARTMENT is populated with appropriate tuples, we can scan relation EMPLOYEE and replace NULLs with valid department number. This solution is awkward and error-prone. A better solution is to use transactions and deferred checking of integrity constraints.
52
SQL has one basic statement for retrieving information from a database; the \_\_\_\_\_\_\_.
SELECT statement
53
The SELECT statement in SQL is (same/ not same) as the SELECT operation of relational algebra.
Not the same. The Important distinction between SQL and the formal relational model : SQL allow a table (relation) to have two or more tuples that are identical in all their attribute values.
54
A SQL relation (table) is a multi-set (sometimes called a \_\_\_\_\_) of tuples; it is not a set of tuples.
bag
55
SQL relations can be constrained to be sets by specifying PRIMARY KEY or UNIQUE attributes in a table definition or by USING _____ option in a query.
DISTINCT
56
In a SELECT-FROM-WHERE block, SELECT takes an as an argument. what does attribute list refer to ?
attibute list is a list of attribute names whose values are to be retrieved by the query
57
In a SELECT-FROM-WHERE block, FROM takes a
## Footnote
as an argument. what does table list refer to ?
table list is a list of the relation names required to process the query.
58
In a SELECT-FROM-WHERE block, WHERE takes a as an argument. What does condition refer to ?
cition is a conditional coolean expression that identifies the tuples to be retrieved by the query.
59
What is a SELECT-FROM-WHERE block?
SELECT
60
Write the SQL query that retreives the birthdate and address of the employee(s) whose name is 'John Smith'.
SELECT BDATE, ADDRESS
FROM EMPLOYEE
WHERE FNAME='John' AND
LNAME='Smith';
61
Compare SELECT-PROJECT pair of relation algebra operations to SQL equivalent.
THE SELECT-clause specifies the projection attributes and the WHERE-CLAUSE specifies the selection condition. However the result of a SQL SFW query may contain duplicate tuples.
62
Write the query that retrieves the name and address of all employees who work for the 'Research' department.
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='Research' AND DNUMBER=DNO;
The WHERE-clause specifies the selection condition (corresponding to the selection operation in relational algebra) and a join condition (corresponding to the join operation in relational algebra).
63
In SQL, we can use the same name for two (or more) attributes as long as the attributes are in \_\_\_\_\_\_\_.
different relations
64
A query that refers to two or more attributes with the same name must qualify the attrivute name with the relation name by \_\_\_\_\_\_\_\_\_\_\_\_\_.
prefixing the relation name the the attribute name.
65
Write the SQLquery that lists the department names and their locations.
**SELECT** DEPARTMENT.DNAME, DEPT\_LOCATIONS.DLOCATION
**FROM** DEPARTMENT, DEPT\_LOCATIONS
**WHERE** DEPARTMENT.DNUMBER=DEPT\_LOCATIONS.DNUMBER;
66
Some queries need refer to the same relation twice. In this case, _____ are given to the relation name.
aliases
67
Write the Query: For each employee, retrieve the employee's name, and the name of his or her immediate supervisor.
**SELECT** E.FNAME, E.LNAME, S.FNAME, S.LNAME
**FROM** EMPLOYEE **AS** E, EMPLOYEE **AS** S
**WHERE** E.SUPERSSN=S.SSN
68
It is possible to rename any attrivute that appear in the result of a query by adding the qualifier **\_\_\_\_\_** followed by the desired new name.
**AS**
69
The **\_\_\_\_\_** construct can be used to alias both attribute and relation names, and it can appear in both the SELECT and FROM clause of a query.
**AS**
70
What does a missing WHERE-clause indicate?
A missing WHERE-clause indicates no condition; hence, all tuples of the relations in the FROM-clause are selected. This is quivalent to a TRUE condition in the WHERE clause.
71
Write the SQL query: Retrieve the SSN values for all employees.
**SELECT** SSN
**FROM** EMPLOYEE
72
If more than one relations is specified in the FROM-clause and there is no join condition, then the **\_\_\_\_\_\_** of tuples is selected.
CARTESIAN PRODUCT
73
Write the SQL query that retrieves all combinations of an EMPLOYEE SSN and a DEPARTMENT name.
**SELECT** SSN, DNAME
**FROM** EMPLOYEE, DEPARTMENT
74
To retrieve all the attribute values of the selected tuples, a **\_\_\_\_** is used, which stands for all the attributes.
\*
75
Write the query that retrieves all the attribute values of the EMPLOYEE tuples for employees who work in department number 5.
**SELECT** \*
**FROM** EMPLOYEE
**WHERE** DNO=5;
76
Write the query that retrieves all the attributes of an EMPLOYEE and all the attributes of the DEPARTMENT he/she works in for every employee of the 'Research' department.
**SELECT** \*
**FROM** EMPLOYEE, DEPARTMENT
**WHERE** DNAME='Research' **AND** DNO=DNUMBER;
77
Write the query that computes the CROSS PRODUCT of the EMPLOYEE and DEPARTMENT relations.
**SELECT** \*
**FROM** EMPLOYEE, DEPARTMENT
78
To eliminate duplicate tuples in a query result, the keyword _____ in the **\_\_\_\_\_** clause is used.
**DISTINCT; SELECT**
79
a query with SELECT ______ eliminates deuplicates, whereas a query with SELECT ______ (which is equivalent to a simple SELECT) does not
DISTINCT; ALL
80
Write the query to retrieve the salary of every employee
**SELECT** SALARY
**FROM** EMPLOYEE
81
Write a query to retrieve every distinct salary value of an employee
**SELECT DISTINCT** SALARY
**FROM** EMPLOYEE
82
What are the 3 set operations that SQL includes:
**UNION** - union operator
**EXCEPT** - set difference
**INTERSECT** - set intersection
The resulting relations of these set operations are sets of typles; duplicate tuples are eliminated from the result.
83
The set operations apply only to \_\_\_\_\_\_\_\_\_.
union compatible relations
84
union compatible relations
the two relations must have the same number of attributes and corresponding attributes must be of compatible data types.
85
86
The **\_\_\_\_** comparison operator is used to compare partial strings.
**LIKE**
Two reserved characters are used:'%' replaces and arbitrary number of characters, and '\_' replaces a single arbitrary character.
87
Query: Retrieve all employee na,es whose addresss is in Houseton, Texas. here, the value of the ADDRESS attribute must contain the substring 'Houston, TX'.
**SELECT** FNAME, LNAME
**FROM** EMPLOYEE
**WHERE** ADDRESS **LIKE**'%Houston, TX%';
88
Query: Retrieve all employee names who were born during the 1950s.
**SELECT** FNAME, LNAME
**FROM** EMPLOYEE
**WHERE** BDATE **LIKE**'\_ _ \_ _ \_ _ \_ 5 _ ';
Here, 5 must be the 9th character of the string (according to our format for date), so the BDATE value is '\_ _ \_ _ \_ _ \_ 5 \_', with each underscore as a placeholder for a single arbitrary character. (no spaces between).
89
The ____ operator allows use to get around the face that each value is considered atomic and indivisible; hende, in SQL, character string attribute values are not atomic.
LIKE
90
The standard arithmetic operators ( \_\_, \_\_, \_\_, \_\_) can be applied to numeric values or attributes with numeric domains.
+, -, \*, /
91
Query: Show the effect of giving all employee salaries of employees who work on the 'ProductX' project a 10% raise.
**SELECT** FNAME, LNAME, 1.1 **\*** SALARY
**FROM** EMPLOYEE, WORKS\_ON, PROJECT
**WHERE** SSN = ESSN **AND** PNO = PNUMBER **AND** PNAME = 'ProductX';
92
For string data types, the ____________ can be used in a query to append two string values.
concatenate operator (||)
93
Query: Retrieve the names of employees in the form:
first\_name/blank\_space/last\_name
**SELECT** FNAME || ' ' || LNAME **AS** NAME
**FROM** EMPLOYEE
94
A comparison operator that can be used for convenience is
BETWEEN
95
Query: Retrieve all employees in department number 5 whose salary is between $30,000 and $40,000.
**SELECT** \*
**FROM** EMPLOYEE
**WHERE** DNO = 5 **AND** (SALARY **BETWEEN** 30000 **AND** 40000);
96
The _________ clause is used to sort the tuples in a query result based on the values of some attribute(s).
ORDER BY
97
Query: Retrieve a list of employees and the projects each works in, ordered by the employee's department name, and within each department ordered alphabetically by employee last name, first name.
**SELECT** DNAME, LNAME, FNAME, PNAME
**FROM** DEPARTMENT, EMPLOYEE, WORKS\_ON, PROJECT
**WHERE** DNUMBER=DNO **AND** SSN=ESSN **AND** PNO=PNUMBER
**ORDER BY** DNAME,LNAME,FNAME;
98
The default order is in _______ of values
ascending order
99
To specify a descending order we use the keyword \_\_\_\_\_\_.
DESC
100
The keyword ____ can be used to explicity specify ascending order, even though it is the default.
ASC
101
Query: Retrieve a list of employees and the projects each works in, ordered by the empoyee's department name in descending order, and within each department ordered alphabetically by employee last name, first name.
**SELECT** DNAME, LNAME, FNAME, PNAME
**FROM** DEPARTMENT, EMPLOYEE, WORKS\_ON, PROJECT
**WHERE** DNUMBER = DNO **AND** SSN = ESSN **AND** PNO = PNUMBER
**ORDER BY** DNAME **DESC,** LNAME **ASC,** FNAME **ASC**;
102
In SQL it is possible to use an explicit (enumerated) set of values in the Where clause of a query. Such a set is enclosed in _______ and the keyword ___ is used.
parentheses, **IN**
103
Query: Retrieve the social security number of employees who work on project number 1,2, or 3.
**SELECT** **DISTINCT** ESSN
**FROM** WORKS\_ON
**WHERE** PNO **IN** (1, 2, 3);
104
\_\_\_\_\_ in databases are used to represent a missing value.
NULLs
105
A NULL for an attribute A in a tuple t can have different meaning. List 3:
1. **The value is unknown**
2. **The value exists but is not available.**
3. **Attribute A does not apply to tuple t.**
106
SQL (does/ does not) distinguish between the different meanings of NULL.
does not. SQL considers each NULL value is distinct from other NULL values.
107
SQL allows queries that check whether a value is NULL. SQL uses ____ or ____ (instead of using = and \<\>) to compare NULLs. Since in SQL each NULL value is distinct from other NULL values, equality comparison is not appropriate.
**IS; IS NOT**
108
Query: Retrieve the names of all employees who do not have supervisors.
**SELECT** FNAME, LNAME
**FROM** EMPLOYEE
**WHERE** SUPERSSN **IS NULL;**
109
SQL uses a 3-valued logic with values \_\_\_\_\_, \_\_\_\_\_, and ______ instead of the standard 2 valued logic with values TRUE and FALSE. When a NULL is involved in an atomic comparison in SQL, the result is considered to be \_\_\_\_\_\_.
TRUE (T); FALSE (F); UNKNOWN (U); UNKNOWN (U)
110
Truth table: **AND** for 3-valued logic.
111
Truth table: **OR** for 3-valued logic.
112
Truth table: **NOT** for 3-valued logic.
113
When a join condition is specified, tuples with null values for the join attributes are (included/not included) in the result. This rule does not apploy to OUTER JOINs.
not included
114
The concept of a joined table allows users to specify a "joined relation" in the ______ of a query.
FROM-clause
115
What are some of the different types of joins a user can specify in the FROM-clause?
regular "theta" JOIN, NATURAL JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN
116
Query: Retrieve the first names, last names, and addresses of employees in the 'Research' department using joins.
**SELECT** FNAME, LNAME, ADDRESS
**FROM** (EMPLOYEE **JOIN** DEPARTMENT **ON** DNUMBER = DNO)
**WHERE** DNAME = 'Research';
117
In ______ no join condition is specified. The implicit equijoin condition involves each pair of attributes with the same names in both relations.
natural join
118
Query: Retrieve the employees' first names, last names, and addresses who work in the Research department using natural join.
**SELECT** FNAME, LNAME, ADDRESS
**FROM** (EMPLOYEE **NATURAL JOIN** DEPARTMENT **AS** DEPT(DNAME, DNO, MSSN, MSDATE))
**WHERE** DNAME='Research';
119
Query: Retrieve the names of employees and their supervisors including the employees who have no supervisor using left outer join.
**SELECT** E.FNAME, E.LNAME, S.FNAME, S.LNAME
**FROM** (EMPLOYEE E **LEFT OUTER JOIN** EMPLOYEE S **ON** E.SUPERSSN=S.SSN);
120
Query: Use nested join specifications to retrieve the addresses of the managers that manage projects in 'Stafford'.
**SELECT** ADDRESS
**FROM** ((PROJECT **JOIN** DEPARTMENT **ON** DNUM=DNUMBER) **JOIN** EMPLOYEE **ON** MGRSSN=SSN)
**WHERE** PLOCATION='Stafford';
121
A complete SELECT query, called a _________ can be specified within the WHERE-clause of another query, called the \_\_\_\_\_\_.
nested query; outer query
122
Query: Retreive the name and address of all employees who work for the 'Research' department using nested queries.
**SELECT** FNAME, LNAME, ADDRESS
**FROM** EMPLOYEE
**WHERE** DNO **IN** (
**SELECT** DNUMBER
**FROM** DEPARTMENT
**WHERE** DNAME='Research');
The nested query selects the number of the 'Research' department.
The outer query selects an EMPLOYEE typle if its DNO value is in the result of the nested query.
The comparison operator **IN** compares a value v with a set (or multi-set) of values V, and evaluates to TRUE if v is one of the elements in V.
123
Query: Make a list of all project numbers for projects that involve an employee whose last name is 'Smith' as a worker or as a manager of the department that controls the project.
**SELECT** **DISTINCT** PNUMBER
**FROM** PROJECT
**WHERE** PNUMBER **IN**
(**SELECT** PNUMBER
**FROM** PROJECT, DEPARTMENT, EMPLOYEE
**WHERE** DNUM=DNUMBER **AND** MGRSSN=SSN **AND**'Smith')
**OR** **PNUMBER IN**
**(SELECT** PNO
**FROM** WORKS\_ON, EMPLOYEE
**WHERE** ESSN=SSN **AND** LNAME='Smith');
124
To compare a tuple of values using **IN** comparison, place the values in \_\_\_\_\_.
parentheses
125
Query: Select the social security number of all employees who work the same (project, hours) combination on a project that the employee whose SSN is '123456789' works on using tuple comparison and IN.
**SELECT DISTINCT** ESSN
**FROM** WORKS\_ON
**WHERE** (PNO, HOURS) **IN**
**(SELECT** PNO, HOURS
**FROM** WORKS\_ON
**WHERE** ESSN = '123456789');
126
Query: retrieve the names of the employees whose salary is greater than the salary of all the employees in department number 5.
**SELECT** LNAME, FNAME
**FROM** EMPLOYEE
**WHERE** SALARY \> **ALL (SELECT** SALARY
**FROM** EMPLOYEE
**WHERE** DNO=5**)**;
127
Abiguity can arist among attribute names if the same attribute exists in relations in the FROM clauses of both the outer query and a nested query. What are the two rules we use to deal with this ambiguity?
1. A reference to an unqualified attribute refers to the relation declared in the innermost nested query.
2. To refer to an attribute of a relation specified in an outer query, we can specify and refer to an alias for that relation.
128
Attributes SALARY and DNO in the nested query refer to which relation EMPLOYEE ?
Attributes SALARY and DNO in the nested query refer to the EMPLOYEE relation declared in the nested query.
129
If a condition in the WHERE-clause of a nested query references an attribute of a relation declare in the outer query, the two queries are said to be \_\_\_\_\_\_.
correlated
130
The result of a correlated ______ is different for each tuple (of combination of tuples) of the relation(s) of the \_\_\_\_\_.
nested query; outer query
131
We can understand a correlated query by considering that the nested query is evalued ____ for each tuple (or combination of tuples) of the out query.
once
132
Query: Retrieve the name of each employee who has a dependent with the same first name and sex as the employee.
133
In general, can a query with nested SELECT-FROM-WHERE blocks that uses the IN comparison operator be expressed using a single query block?
Yes. Example: Retrieve the name of each employee who has a dependent with the same first name and sex as the employee.
134
The _____ function in SQL is used to check whether the result of a correlated nested query is empty (contains no tuples) or not.
EXISTS
135
Query: Retrieve the name of each employee who has a dependent with the same first name and sex as the employee using EXISTS..
136
The function ________ can also be used. EXISTS(Q) returns ___ if there is at least one tuple in the result of Q, and ____ otherwise.
\_\_\_\_\_\_ returns TRUE if there are no tuples in the result of Q, and FALSE otherwise.
NOT EXISTS; TRUE; FALSE; NOT EXISTS(Q);
137
Query: Retrieve the names of employees who have no dependents using NOT EXISTS.
**SELECT** FNAME, LNAME
**FROM** EMPLOYEE
**WHERE** **NOT EXISTS (SELECT \***
**FROM** DEPENDENT
**WHERE** SSN=ESSN);
138
Query: List the names of managers who have at least one dependent using EXISTS.
139
Query: Retrieve the names of each employee who works on all the projects using NOT EXISTS and EXCEPT.
140
The SQL function _______ returns TRUE is there are no duplicates in the result of a query Q; otherwise it returns FALSE.
UNIQUE(Q)
141
