Exam 9: Advanced Query Formulation With SQL

Figuer: VOTE Name votel vote2 Bill 20 15 Mary 10 25 Jill 15 0 Bob 30 -SELECT * FROM VOTE WHERE Vote2 <10 The execution of this query produces the following number of rows:

The SQL keyword(s)____________ in a SELECT query generate a result table that contains the matching rows and the non-matching rows of the left table.

To evaluate the result of a compound condition,SQL:1999 uses _________________,which show how combinations of the values true,false and null combine with Boolean operators.

One of the reasons Type II nested queries are not used for join problems is that their repetitive execution makes them inefficient for this purpose.

Null values affect:

A SELECT statement that appears inside another query is called a(n)___________________.

The following SQL statement is an example of a Type II nested query: SELECT StdSSN,StdFirstName,StdLastName,StdMajor FROM Student WHERE EXISTS (SELECT StdSSN FROM Enrollment WHERE Student.StdSSN = Enrollment.StdSSN AND EnrGrade > 3.5)

A full outer join generates a table with the matching rows plus the nonmatching rows from both tables.

In SQL statements,________________ conditions involve a column or column expression,a comparison operator,and another column,column expression or constant.

Figuer: VOTE Name votel vote2 Bill 20 15 Mary 10 25 Jill 15 0 Bob 30 -SELECT SUM(VOTE1)AS S1,SUM(VOTE2)AS S2, SUM(VOTE1)-SUM(VOTE2)AS S3,SUM(VOTE1-VOTE2)AS S4 FROM VOTE The execution of this query produces the following result (shown in order of occurrence in the query):

A(n)_________________ nested query executes one time and produces a result table.

In the following SQL statement,which column would be blank if null values were present in one or more of the rows? _______________ SELECT FacSSN,COUNT(*)AS NumRows FROM Offering GROUP BY FacSSN

The following SQL statement would return only the non-matching rows of the left table: SELECT FacSSN,LastName FROM Faculty LEFT JOIN Student ON Faculty.SSN = Student.SSN WHERE Student.StdSSN IS NULL

Figuer: CUSTOMER CID CNAME AGE RESID\_CITY BIRTHPLACE 10 BLACK 40 ERIE TAMPA 20 GREEN 25 CARY ERIE 30 JONES 30 HEMET TAMPA 40 MARTIN 35 HEMET TAMPA 50 SIMON 22 ERIE ERIE 60 VERNON 60 CARY CARY In table CUSTOMER, CID is the primary key (Customer ID). RENTALS CID MAKE DATE\_OUT PICKUP RETURN RTN 10 FORD 10-0ct-1994 CARY CARY 1 10 GM 01-Nov-1995 TAMPA CARY 2 10 FORD 01-Jan-1995 ERIE ERIE 3 20 NISSAN 07-Ju1-1994 TAMPA TAMPA 4 30 FORD 01-Jul-1995 CARY ERIE 5 30 GM 01-Aug-1995 ERIE ERIE 6 40 FORD 01-Aug-1994 CARY ERIE 7 50 GM 01-Sep-1995 ERIE CARY 8 70 TOYOTA 02-Sep-1995 RENO RENO 9 In the table RENTALS, RTN provides the rental number (the primary key), CID is the customer's unique id, PICKUP is the city where the car was picked up, and Return is the city where the car was returned. RENTCOST MAKE COST FORD 30 GM 40 NISSAN 30 TOYOTA 20 VOLVO 50 RENTCOST shows the base cost of renting a given MAKE for one day. CITYADJ CITY FACTOR CARY 1 ERIE 1.1 RENO 0.9 TAMPA 0.8 If the return city of table RENTALS is the one listed in table CITYADJ, the cost of the rental is multiplied by FACTOR and by DAYS shown in table RENTLENGTH below. RENTLENGTH RTN DAYS 1 1 2 3 3 2 4 2 5 4 6 2 7 3 8 1 RENTLENGTH shows the number of days for the rental number (RTN) shown in table RENTALS. In a database used in reality, this table would be merged with the RENTALS table. -SELECT CID,CNAME FROM CUSTOMER HERE EXISTS (SELECT CID FROM RENTALS WHERE CUSTOMER.CID = RENTALS.CID AND PICKUP = 'CARY') The execution of this query lists the following CNAMEs:

If a problem narrative specifies a requirement to list the values of one column that match all of the values in another column,this problem would require the use of a(n)_________________ operation.

Outer join problems cannot be formulated in SQL at all using the cross product style.

Typically,a full outer join is used to combine two tables that are similar but are not union compatible.

Figuer: VOTE Name votel vote2 Bill 20 15 Mary 10 25 Jill 15 0 Bob 30 -SELECT * FROM VOTE WHERE Vote1>10 AND VOTE2 >10 The execution of this query produces the following number of rows:

When null values exist in one of the columns in a simple condition,it is possible that those rows will not appear in the result of either the simple condition or its negation.

The SQL keyword(s)____________ in a SELECT query generate a result table that contains the matching rows and the non-matching rows of the right table.