SQL: A Commercial Database Language

Question 1

The name SQL is derived from

Answer 1

Structured Query Language

Question 2

Sequel was originally called ______.

Answer 2

SEQUEL ( for Structure English QUEry Language).

Question 3

What does it mean that SQL is a comprehensive database language?

Answer 3

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.

Question 4

The SQL language provides a high-level declarative language interface meaning

Answer 4

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.

Question 5

The terms table, row, and column are used in SQL in documents for ____, ____ and ____ respectively.

Answer 5

table = relation

row = tuple

column = attribute

Question 6

is SQL case sensitive?

Answer 6

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.

Question 7

The concept of ____ is used in SQL to group together constructs that belong to the same database application

Answer 7

schema

Question 8

A schema includes ______ such as tables constraints, view, domains, authorization grants and other constructs.

Answer 8

schema elements

Question 9

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.

Answer 9

authorization identifier ; descriptors

Question 10

Write a SQL statement that creates a schema called COMPANY owned by a user with authorization identifier JSMITH

Answer 10

CREATE SCHEMA COMPANY AUTHORIZATION JSMITH;

Question 11

The concept of a ______ is used in SQL to denote a named collection of schemas.

Answer 11

catalog

Question 12

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.

Answer 12

INFORMATION_SCHEMA

Question 13

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

Answer 13

True.

False. Integrity constraints can be defined between relations only if they exist in schemas of the same catalog.

Question 14

What data types are included in SQL?

Answer 14

numerics, character-string, bit-string, data and time.

Question 15

What are the numeric types in SQL and how are they specified?

Answer 15

Question 16

What are the character string data types in SQL and how are they specified?

Answer 16

Question 17

What are the bit string data types in SQL and how are they specified?

Answer 17

Question 18

What are the data and time data types in SQL and how are they specified?

Answer 18

Question 19

How do you create a domain in SQL on SSN_TYPE to be a character data type consisting of 9 characters?

Answer 19

CREATE DOMAIN SSN_TYPE AS CHAR(9);

Question 20

A create domain statement can have an optional _______ and an optional

_____________.

.

Answer 20

default specification ; list of constraints

Question 21

What does the CREATE TABLE command do?

Answer 21

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.

Question 22

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.

Answer 22

Question 23

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.

Answer 23

CONSTRAINT;

A) optional but is a good practice to follow.

Question 24

True or False: The names of some of the constraints within a particular can match (not unique).

Answer 24

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

Question 25

What is SQL’s default policy for any modificiations that violate the referential integrity constraint?

Answer 25

Any modifications violating the referential integrity constraints is rejected by the system.

Question 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?

Answer 26

SET NULL, CASCADE, SET DEFAULT

Question 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 ______.

.

Answer 27

ON DELETE ; ON UPDATE

Question 28

How would you specify a constraint called EMPPK that enforces for the EMPLOYEE relation’s primary key is SSN?

Answer 28

Question 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?

Answer 29

Question 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?

Answer 30

Question 31

Can the name of a schema be explicitly specified in the definition of a table?

Answer 31

Yes

CREATE TABLE COMPANY.DEPARTMENT….

Question 32

A schema can be deleted using the _______ command. It has two options: _____ and _____.

Answer 32

DROP SCHEMA; CASCADE; RESTRICT

Question 33

For DROP SCHEMA, what happens if RESTRICT is specified ?

Answer 33

The operation will fail if the schema if nonempty; otherwise it will be succeed and the schema will be destroyed.

Question 34

For DROP SCHEMA, what happens if CASCADE is specified ?

Answer 34

If CASCADE is spedicifed the operation will always succeed.

DROP SCHEMA COMPANY CASCADE;

Question 35

A table can be deleted using the _________ command. Two options: _____ and _____.

Answer 35

DROP TABLE, CASCADE, RESTRICT

Question 36

For DROP TABLE, what happens if RESTRICT is specified ?

Answer 36

If RESTRICT is specified, the operation will succeed only if the table is not referenced in any constraints or views.

Question 37

For DROP TABLE, what happens if CASCADE is specified ?

Answer 37

If CASCADE is specified, the operation will always succeed and propogate to all constraints and views that reference the table.

DROP TABLE DEPENDENT CASCADE;

Question 38

How do you add a column to relation EMPLOYEE with attribute name JOB and domain of variable char of length 12?

Answer 38

ALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12);

Question 39

ALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12) NOT NULL;

What does this command do?

Answer 39

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.

Question 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?

Answer 40

ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS CASCADE;

Question 41

When dropping a column, what is the difference between CASCADE and RESTRICT?

Answer 41

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.

Question 42

How would you drop a default clause in a column called MGRSSN for relation DEPARTMENT in the COMPANY SCHEMA?

Answer 42

ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN DROP DEFAULT;

Question 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’?

Answer 43

Adding a default clause in a column:

ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN SET DEFAULT ‘33344555’;

Question 44

What must a constraint have in order to be dropped?

Answer 44

To be dropped a constraint must have a name.

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

Answer 45

ALTER TABLE COMPANY.EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE;

Question 46

When dropping a CONSTRAINT, CASCADE and RESTRICT have meaning only if _____________.

Answer 46

the constraint is specified, all the foreign key definitions that reference the candidate key will be dropped too.

Question 47

Write a command that adds a constraint called EMPSUPERFK that enforces the foreign key SUPERSSN references an EMPLOYEE.

Answer 47

ALTER TABLE COMPANY.EMPLOYEE ADD CONSTRAINT EMPSUPERFK FOREIGN KEY SUPERSSN REFERENCES EMPLOYEE;

Question 48

True or False: When adding a constraing using ALTER TABLE, the constraint to be created can be named or unnamed.

Answer 48

True.

Question 49

What is the problem with these table definitions?

Answer 49

The problem is taht either EMPLOYEE or DEPARTMENT table has to be defined first.

In both cases, the system will issue an error message.

Question 50

What is a possible solution to the problem in this code?

Answer 50

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:

Question 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?

Answer 51

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.

Question 52

SQL has one basic statement for retrieving information from a database; the _______.

Answer 52

SELECT statement

Question 53

The SELECT statement in SQL is (same/ not same) as the SELECT operation of relational algebra.

Answer 53

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.

Question 54

A SQL relation (table) is a multi-set (sometimes called a _____) of tuples; it is not a set of tuples.

Answer 54

bag

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

Answer 55

DISTINCT

Question 56

In a SELECT-FROM-WHERE block, SELECT takes an <attribute> as an argument. what does attribute list refer to ?</attribute>

Answer 56

attibute list is a list of attribute names whose values are to be retrieved by the query

Question 57

In a SELECT-FROM-WHERE block, FROM takes a

as an argument. what does table list refer to ?

Answer 57

table list is a list of the relation names required to process the query.

Question 58

In a SELECT-FROM-WHERE block, WHERE takes a as an argument. What does condition refer to ?

Answer 58

cition is a conditional coolean expression that identifies the tuples to be retrieved by the query.

Question 59

What is a SELECT-FROM-WHERE block?

Answer 59

SELECT <attribute></attribute>

FROM

WHERE <condition></condition>

Question 60

Write the SQL query that retreives the birthdate and address of the employee(s) whose name is ‘John Smith’.

Answer 60

SELECT BDATE, ADDRESS

FROM EMPLOYEE

WHERE FNAME=’John’ AND

LNAME=’Smith’;

Question 61

Compare SELECT-PROJECT pair of relation algebra operations to SQL equivalent.

Answer 61

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.

Question 62

Write the query that retrieves the name and address of all employees who work for the ‘Research’ department.

Answer 62

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

Question 63

In SQL, we can use the same name for two (or more) attributes as long as the attributes are in _______.

Answer 63

different relations

Question 64

A query that refers to two or more attributes with the same name must qualify the attrivute name with the relation name by _____________.

Answer 64

prefixing the relation name the the attribute name.

Question 65

Write the SQLquery that lists the department names and their locations.

Answer 65

SELECT DEPARTMENT.DNAME, DEPT_LOCATIONS.DLOCATION

FROM DEPARTMENT, DEPT_LOCATIONS

WHERE DEPARTMENT.DNUMBER=DEPT_LOCATIONS.DNUMBER;

Question 66

Some queries need refer to the same relation twice. In this case, _____ are given to the relation name.

Answer 66

aliases

Question 67

Write the Query: For each employee, retrieve the employee’s name, and the name of his or her immediate supervisor.

Answer 67

SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME

FROM EMPLOYEE AS E, EMPLOYEE AS S

WHERE E.SUPERSSN=S.SSN

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

Answer 68

AS

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

Answer 69

AS

Question 70

What does a missing WHERE-clause indicate?

Answer 70

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.

Question 71

Write the SQL query: Retrieve the SSN values for all employees.

Answer 71

SELECT SSN

FROM EMPLOYEE

Question 72

If more than one relations is specified in the FROM-clause and there is no join condition, then the ______ of tuples is selected.

Answer 72

CARTESIAN PRODUCT

Question 73

Write the SQL query that retrieves all combinations of an EMPLOYEE SSN and a DEPARTMENT name.

Answer 73

SELECT SSN, DNAME

FROM EMPLOYEE, DEPARTMENT

Question 74

To retrieve all the attribute values of the selected tuples, a ____ is used, which stands for all the attributes.

Answer 74

*

Question 75

Write the query that retrieves all the attribute values of the EMPLOYEE tuples for employees who work in department number 5.

Answer 75

SELECT *

FROM EMPLOYEE

WHERE DNO=5;

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

Answer 76

SELECT *

FROM EMPLOYEE, DEPARTMENT

WHERE DNAME=’Research’ AND DNO=DNUMBER;

Question 77

Write the query that computes the CROSS PRODUCT of the EMPLOYEE and DEPARTMENT relations.

Answer 77

SELECT *

FROM EMPLOYEE, DEPARTMENT

Question 78

To eliminate duplicate tuples in a query result, the keyword _____ in the _____ clause is used.

Answer 78

DISTINCT; SELECT

Question 79

a query with SELECT ______ eliminates deuplicates, whereas a query with SELECT ______ (which is equivalent to a simple SELECT) does not

Answer 79

DISTINCT; ALL

Question 80

Write the query to retrieve the salary of every employee

Answer 80

SELECT SALARY

FROM EMPLOYEE

Question 81

Write a query to retrieve every distinct salary value of an employee

Answer 81

SELECT DISTINCT SALARY

FROM EMPLOYEE

Question 82

What are the 3 set operations that SQL includes:

Answer 82

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.

Question 83

The set operations apply only to _________.

Answer 83

union compatible relations

Question 84

union compatible relations

Answer 84

the two relations must have the same number of attributes and corresponding attributes must be of compatible data types.

Question 86

The ____ comparison operator is used to compare partial strings.

Answer 86

LIKE

Two reserved characters are used:’%’ replaces and arbitrary number of characters, and ‘_’ replaces a single arbitrary character.

Question 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’.

Answer 87

SELECT FNAME, LNAME

FROM EMPLOYEE

WHERE ADDRESS LIKE‘%Houston, TX%’;

Question 88

Query: Retrieve all employee names who were born during the 1950s.

Answer 88

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

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

Answer 89

LIKE

Question 90

The standard arithmetic operators ( __, __, __, __) can be applied to numeric values or attributes with numeric domains.

Answer 90

+, -, *, /

Question 91

Query: Show the effect of giving all employee salaries of employees who work on the ‘ProductX’ project a 10% raise.

Answer 91

SELECT FNAME, LNAME, 1.1 * SALARY

FROM EMPLOYEE, WORKS_ON, PROJECT

WHERE SSN = ESSN AND PNO = PNUMBER AND PNAME = ‘ProductX’;

Question 92

For string data types, the ____________ can be used in a query to append two string values.

Answer 92

concatenate operator (||)

Question 93

Query: Retrieve the names of employees in the form:

first_name/blank_space/last_name

Answer 93

SELECT FNAME || ‘ ‘ || LNAME AS NAME

FROM EMPLOYEE

Question 94

A comparison operator that can be used for convenience is

Answer 94

BETWEEN

Question 95

Query: Retrieve all employees in department number 5 whose salary is between $30,000 and $40,000.

Answer 95

SELECT *

FROM EMPLOYEE

WHERE DNO = 5 AND (SALARY BETWEEN 30000 AND 40000);

Question 96

The _________ clause is used to sort the tuples in a query result based on the values of some attribute(s).

Answer 96

ORDER BY

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

Answer 97

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;

Question 98

The default order is in _______ of values

Answer 98

ascending order

Question 99

To specify a descending order we use the keyword ______.

Answer 99

DESC

Question 100

The keyword ____ can be used to explicity specify ascending order, even though it is the default.

Answer 100

ASC

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

Answer 101

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;

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

Answer 102

parentheses, IN

Question 103

Query: Retrieve the social security number of employees who work on project number 1,2, or 3.

Answer 103

SELECT DISTINCT ESSN

FROM WORKS_ON

WHERE PNO IN (1, 2, 3);

Question 104

_____ in databases are used to represent a missing value.

Answer 104

NULLs

Question 105

A NULL for an attribute A in a tuple t can have different meaning. List 3:

Answer 105

1. The value is unknown
2. The value exists but is not available.
3. Attribute A does not apply to tuple t.

Question 106

SQL (does/ does not) distinguish between the different meanings of NULL.

Answer 106

does not. SQL considers each NULL value is distinct from other NULL values.

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

Answer 107

IS; IS NOT

Question 108

Query: Retrieve the names of all employees who do not have supervisors.

Answer 108

SELECT FNAME, LNAME

FROM EMPLOYEE

WHERE SUPERSSN IS NULL;

Question 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 ______.

Answer 109

TRUE (T); FALSE (F); UNKNOWN (U); UNKNOWN (U)

Question 110

Truth table: AND for 3-valued logic.

Answer 110

Question 111

Truth table: OR for 3-valued logic.

Answer 111

Question 112

Truth table: NOT for 3-valued logic.

Answer 112

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

Answer 113

not included

Question 114

The concept of a joined table allows users to specify a “joined relation” in the ______ of a query.

Answer 114

FROM-clause

Question 115

What are some of the different types of joins a user can specify in the FROM-clause?

Answer 115

regular “theta” JOIN, NATURAL JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN

Question 116

Query: Retrieve the first names, last names, and addresses of employees in the ‘Research’ department using joins.

Answer 116

SELECT FNAME, LNAME, ADDRESS

FROM (EMPLOYEE JOIN DEPARTMENT ON DNUMBER = DNO)

WHERE DNAME = ‘Research’;

Question 117

In ______ no join condition is specified. The implicit equijoin condition involves each pair of attributes with the same names in both relations.

Answer 117

natural join

Question 118

Query: Retrieve the employees’ first names, last names, and addresses who work in the Research department using natural join.

Answer 118

SELECT FNAME, LNAME, ADDRESS

FROM (EMPLOYEE NATURAL JOIN DEPARTMENT AS DEPT(DNAME, DNO, MSSN, MSDATE))

WHERE DNAME=’Research’;

Question 119

Query: Retrieve the names of employees and their supervisors including the employees who have no supervisor using left outer join.

Answer 119

SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME

FROM (EMPLOYEE E LEFT OUTER JOIN EMPLOYEE S ON E.SUPERSSN=S.SSN);

Question 120

Query: Use nested join specifications to retrieve the addresses of the managers that manage projects in ‘Stafford’.

Answer 120

SELECT ADDRESS

FROM ((PROJECT JOIN DEPARTMENT ON DNUM=DNUMBER) JOIN EMPLOYEE ON MGRSSN=SSN)

WHERE PLOCATION=’Stafford’;

Question 121

A complete SELECT query, called a _________ can be specified within the WHERE-clause of another query, called the ______.

Answer 121

nested query; outer query

Question 122

Query: Retreive the name and address of all employees who work for the ‘Research’ department using nested queries.

Answer 122

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.

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

Answer 123

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

Question 124

To compare a tuple of values using IN comparison, place the values in _____.

Answer 124

parentheses

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

Answer 125

SELECT DISTINCT ESSN

FROM WORKS_ON

WHERE (PNO, HOURS) IN

(SELECT PNO, HOURS

FROM WORKS_ON

WHERE ESSN = ‘123456789’);

Question 126

Query: retrieve the names of the employees whose salary is greater than the salary of all the employees in department number 5.

Answer 126

SELECT LNAME, FNAME

FROM EMPLOYEE

WHERE SALARY > ALL (SELECT SALARY

FROM EMPLOYEE

WHERE DNO=5);

Question 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?

Answer 127

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.

Question 128

Attributes SALARY and DNO in the nested query refer to which relation EMPLOYEE ?

Answer 128

Attributes SALARY and DNO in the nested query refer to the EMPLOYEE relation declared in the nested query.

Question 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 ______.

Answer 129

correlated

Question 130

The result of a correlated ______ is different for each tuple (of combination of tuples) of the relation(s) of the _____.

Answer 130

nested query; outer query

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

Answer 131

once

Question 132

Query: Retrieve the name of each employee who has a dependent with the same first name and sex as the employee.

Answer 132

Question 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?

Answer 133

Yes. Example: Retrieve the name of each employee who has a dependent with the same first name and sex as the employee.

Question 134

The _____ function in SQL is used to check whether the result of a correlated nested query is empty (contains no tuples) or not.

Answer 134

EXISTS

Question 135

Query: Retrieve the name of each employee who has a dependent with the same first name and sex as the employee using EXISTS..

Answer 135

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

Answer 136

NOT EXISTS; TRUE; FALSE; NOT EXISTS(Q);

Question 137

Query: Retrieve the names of employees who have no dependents using NOT EXISTS.

Answer 137

SELECT FNAME, LNAME

FROM EMPLOYEE

WHERE NOT EXISTS (SELECT *

FROM DEPENDENT

WHERE SSN=ESSN);

Question 138

Query: List the names of managers who have at least one dependent using EXISTS.

Answer 138

Question 139

Query: Retrieve the names of each employee who works on all the projects using NOT EXISTS and EXCEPT.

Answer 139

Question 140

The SQL function _______ returns TRUE is there are no duplicates in the result of a query Q; otherwise it returns FALSE.

Answer 140

UNIQUE(Q)