adv db Quiz 5-8 ~ College Brighten

CHAPTER 5

Question 1

What type of conversion is performed by the following statement:

SELECT LENGTH(45,629.788) FROM DUAL;

Implicit

Explicit

TO_NUMBER

No conversion is done.

Question 2

What type of conversion is performed by the following statement:

SELECT TO_CHAR(45629.788, '$999,999') FROM DUAL;

Implicit

Explicit

TO_NUMBER

No conversion is done.

Question 3

Choose any statements that are true regarding conversion functions. Choose all that apply.

TO_CHAR may convert date items to character items.

TO_DATE may convert character items to date items.

TO_CHAR may convert numbers to character items.

TO_NUMBER may convert character items to numbers.

Question 4

What value is returned after executing the following statement:

SELECT TO_CHAR(45.38, '99.9') FROM DUAL;

45.3

45.4

It will return a syntax error.

None of the choices.

Question 5

What is returned by the following statement:

SELECT TO_NUMBER(123.56, '999.9') FROM DUAL;

123.5

123.6

An error is returned.

None of the choices.

Question 6

If today's date is February 19, 2012, then what is returned by this statement:

SELECT TO_CHAR(SYSDATE, 'fmMonth, Year') FROM DUAL;

February, 2012

February, Twenty Twelve

FEBRUARY, TWENTY TWELVE

An error will result.

Question 7

If today's date is February 19, 2012, then what is returned by this statement:

SELECT TO_CHAR(SYSDATE, 'fmddth, Year') FROM DUAL;

February 19th, 2012

February 19th, Twenty Twelve

19th, Twenty Twelve

An error will result.

Question 8

If today's date is 12-JULY-09, then what is returned by this statement:

SELECT TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'DD'), 'DD'), 'YEAR')

FROM DUAL;

2009

TWO THOUSAND NINE

An error will result.

Question 9

What is the result of the execution of the following query:

SELECT NVL(1234) FROM DUAL;

1234

NULL

Syntax error

Question 10

What is the result of the execution of the following query:

SELECT NVL(SUBSTR('abc', 4), 'nothing') FROM DUAL;

abc

nothing

Syntax error

Question 11

What is the result of the execution of the following query:

SELECT NVL2(SUBSTR('abc', 2), 'nothing', 'something') FROM DUAL;

nothing

Syntax error

something

Question 12

What is the result of the execution of the following query:

SELECT NVL2(1234, 5678, 'nothing') FROM DUAL;

1234

NULL

Syntax error

5678

Question 13

What is the result of the execution of the following query:

SELECT NULLIF(1234, 1234) FROM DUAL;

1234

NULL

Syntax error

Question 14

What is the result of the execution of the following query:

SELECT COALESCE(null, null, 1234) FROM DUAL;

1234

NULL

Syntax error

Question 15

What is the result of the execution of the following query:

SELECT COALESCE(null, null, 1234, 'abc') FROM DUAL;

1234

NULL

Syntax error

Question 16

What is the result of the execution of the following query:

SELECT COALESCE(null, null, 'def', 'abc') FROM DUAL;

abc

NULL

Syntax error

def

CHAPTER 6

Question 1

Aggregate functions return one value for every set of zero or more rows considered within a SELECT statement.

True

False

Question 2

Aggregate and scalar data cannot be included in the SELECT statement's select list, unless the aggregate data is also included in a GROUP BY clause.

True

False

Question 3

The MIN and MAX functions can operate on date, character, or numeric data.

True

False

Question 4

The GROUP BY can specify columns in a table, which will have the effect of gouping rows with common values.

True

False

Question 5

Whatever columns you specify in the GROUP BY must also be included in the SELECT statement's select list.

True

False

Question 6

You cannot have a HAVING clause without a GROUP BY clause.

True

False

Question 7

If GROUP BY and HAVING clauses are present in a statement, they must follow the WHERE clause and precede the ORDER BY clause.

True

False

Question 8

While GROUP BY typically precedes HAVING in common practice, it is not required, and the HAVING may precede the GROUP BY.

True

False

Question 9

Aggregate functions: (choose two)

Return one value for each group of rows specified in a SELECT statement

Are also called group functions

Must be used in SELECT statements that select multiple rows.

Can only operate with numeric data.

Question 10

Review the following columns that are contained in a tabled named Cruise_Orders:

Cruise_Order_ID NUMBER (PRIMARY KEY)

Cruise_Date DATE

What can be said of this SQL statement:

SELECT Cruise_Order_ID, COUNT(Cruise_date)

FROM Cruise_Orders;

It will fail to execute because Cruise_Date is a date datatype.

It will fail to execute because it mixes scalar and aggregate data in the select list.

It will execute successfully, but not produce any meaningful output.

There is nothing wrong with the statement.

Question 11

Which of the following may be used on character data? (Choose two)

COUNT

MIN

MEDIAN

AVG

Question 12

Review the following columns that are contained in a tabled named Cruise_Orders:

Cruise_Order_ID NUMBER (PRIMARY KEY)

Cruise_Date DATE

What can be said of this SQL statement:

SELECT AVG(Cruise_Order_ID), MIN(Cruise_date)

FROM Cruise_Orders;

It will fail to execute because the AVG function cannot be used on a primary key column.

It will fail to execute if the table contains only one row.

It will fail to execute because the min function is not capitalized.

There is nothing wrong with the statement. It will execute and perform as intended.

Question 13

Review the following data listing from a table SCORES:

SCORE_ID TEST_SCORE

------------ ------------------

1 95

3 85

Now consider the following query:

SELECT TO_CHAR(AVERAGE(TEST_SCORE), '999,999.99')

FROM SCORES;

It will result in a syntax error.

It will execute but the TO_CHAR will not be applied.

90.00

60.00

Question 14

Review the following data listing from a table SCORES:

SCORE_ID TEST_SCORE

------------ ------------------

1 95

3 85

Now consider the following query:

SELECT TO_CHAR(AVG(TEST_SCORE), '999,999.99')

FROM SCORES;

It will result in a syntax error.

It will execute but the TO_CHAR will not be applied.

90.00

60.00

Question 15

An aggregate function can be called from: (Choose all that apply.)

The ORDER BY clause

The WHERE clause

the select list

the HAVING clause

Question 16

Which of the following SELECT statements lists the highest retail price of all books in the Family category?

SELECT MAX(retail)

FROM books

WHERE category = 'Family';

SELECT MAX(retail)

FROM books

HAVING category = 'Family';

SELECT retail

FROM books

WHERE category = 'Family'

HAVING MAX(retail);

None of the choices.

Question 17

Which of the following statements are true about HAVING? (Choose two.)

It can be used only in the SELECT statement

It must occur after the GROUP BY clause.

It must occur after the WHERE clause.

It cannot reference an expression unless that expression is first referenced in the GROUP BY clause.

Question 18

Consider the PROJECTS table and SELECT statement (line numbers added):

PROJECT_ID NUMBER

SHIP_ID NUMBER

PURPOSE VARCHAR2(30)

PROJECT_NAME VARCHAR2(30)

PROJECT_COST NUMBER

DAYS NUMBER

1 SELECT SHIP_ID, MAX(DAYS)

2 FROM PROJECTS

3 GROUP BY SHIP_ID

4 HAVING AVG(PROJECT_COST) < 500000;

Which of the following statements is true for this statement?

It will fail to execute due to a syntax error on line 4.

It will include only those rows with a PROJECT_COST value of less than 500000

It will include only those groups of rows for a given SHIP_ID with an average value of PROJECT_COST less than 500000

It will fail to execute due to a syntax error on line 3.

CHAPTER 7

Question 1

What can be said about the following statement (line numbers added):

1 SELECT P.PORT_NAME, S.SHIP_NAME, SC.ROOM_NUMBER

2 FROM PORTS P JOIN SHIPS S

3 ON P.PORT_ID = S.PORT_ID

4 FROM SHIP_CABINS SC

5 ON S.SHIP_ID = SC.SHIP_ID;

It will successfully execute as expected.

There will be a runtime error.

There is a syntax error on line 4.

There is a syntax error on line 3.

Question 2

Which one of the keywords in this statement is optional?

SELECT V.VENDOR_ID, INV.INVOICE_DATE

FROM VENDORS V INNER JOIN INVOICES INV

ON V.VENDOR_ID = INV.VENDOR_ID;

JOIN

INNER

ALL KEYWORDS ARE REQUIRED.

Question 3

You have two tables, one named CUSTOMERS and the other called PURCHASES, which lists customer transactions. Your goal is to create a SELECT statement that will show all customers by last name in alphabetical order, along with any purchases they may have made in the last two weeks. It's possible that some customers have made no purchases in the last two weeks, but you still want them included in the output. Both tables contain a column called CUSTOMER_ID. Which of the following will be true of the SELECT statement you'll need to create? (Choose two.)

It will be an inner join.

It will be an outer join.

It will be a cross-join.

It will be an equijoin.

Question 4

Consider the partial SELECT statement below:

SELECT vendor_name, invoice_date

FROM

ON vendors.vendor_id = invoices.vendor_id;

Which of the following correctly completes the FROM clause (choose two).

vendors join inner invoices

vendors outer join invoices

vendors right outer join invoices

vendors inner join invoices

Question 5

Review the following SQL statment (line numbers added):

01 SELECT vendor_id, invoice_date, total_price

02 FROM vendors JOIN invoices

03 USING (vendor_id);

Which of the following is true for the statement?

It will execute successfully.

It will fail with a syntax error because there is no ON clause.

It will fail with a syntax error on line 1 because VENDOR_ID is ambiguous.

It will fail with a syntax error on line 3 because of the parenthesis around VENDOR_ID.

Question 6

How many tables can be included in a JOIN?

Only two.

As many as you like, provided that tey are all constrained with PRIMARY KEY and FOREIGN KEY constraints to ensure that the join conditions will work.

two, three, or more

No more than seven.

Question 7

The difference between an INNER and an OUTER join is:

The INNER join relates a table to itself; the OUTER join relates a table to other tables.

The INNER join displays rows that match in all joined tables; the OUTER join shows data that doesn't necessarily match.

The OUTER join relates a table to tables in other user accounts; the INNER join does not.

The INNER join runs on data inside the table; the OUTER join runs on data outside of the table.

Question 8

Which of the following statements accurately describe the SQL statement below? (Choose two.)

SELECT A.EMPLOYEE_ID, B.POSITION

FROM PAY_HISTORY A JOIN POSITIONS B

ON A.SALARY <= B.MAX_SALARY;

It contains a syntax error on line 1.

It is an inner join.

It is a non-equijoin.

It contains a syntax error on line 2.

Question 9

Review the following SQL statement:

SELECT *

FROM INSTRUCTORS CROSS JOIN SCORES;

The INSTRUCTORS table contains a total of 3 rows. The SCORES table contains a total of 4 rows. How many rows will the statement above return?

There is not enough information to determine the answer.

Question 10

If there are two discrete tables that have a column with the same name, then the tables must have a primary key/foreign key relationship with each other.

True

False

Question 11

Qualifying a column with dot notation only helps to read and understand a query better; it does not have any performance benefits.

True

False

Question 12

The syntax diagram for the natural JOIN USING clause is:

SELECT table1.column, table2.column, ...

FROM JOIN table1 ON table2

USING (join_column1, join_column2 ...);

FROM table1 JOIN table2

USING (join_column1 = join_column2);

FROM table1, table2

USING (join_column1, join_column2 ...);

FROM table1 JOIN table2

USING (join_column1, join_column2 ...);

Question 13

Select which clauses below are categorized as "Natural Joins" (Choose all that apply):

NATURAL JOIN

OUTER JOIN

JOIN...USING

JOIN...ON

Question 14

The data in two tables you wish to join is related but does not share any identically named collumns. Therefore it is not possible to join these two tables.

True

False

Question 15

Which join is performed by the following query?

SELECT E.JOB_ID, J.JOB_ID

FROM EMPLOYEES E JOIN JOBS J

ON (E.SALARY BETWEEN J.MIN_SAL and J.MAX_SAL);

Equijoin

Nonequijoin

Cross join

Outer join

Question 16

The EMPLOYEES and DEPARTMENTS tables have two identically named columns, department_id and manager_id. Which clause(s) join these tables based on both column values? (Choose all that apply.)

FROM employees NATURAL JOIN departments;

FROM employees JOIN departments

USING (department_id, manager_id);

FROM employees e JOIN departments d

ON e.department_id = d.department_id AND e.manager_id = d.manager_id;

FROM employees NATURAL JOIN departments

ON (department_id, manager_id);

Question 17

How many rows will be returned by this SQL query on the Regions table?

SELECT * FROM Regions R1 JOIN Regions R2 ON (R1.Region_ID = LENGTH(R2.Region_name)/2);

Region_ID Region_Name

------------ ---------------

1 Europe

2 Americas

3 Asia

4 Middle East

CHAPTER 8

Question 1

When will the subquery be executed in the following statement:

SELECT o.customer_id, o.customer_Name

FROM customers o

WHERE o.credit_limit > (SELECT AVG(i.credit_limit)

FROM customers i

WHERE o.category = i.category);

It will be executed prior to the main query

It will be executed after the main query

It will be executed concurrently with the main query

It will be executed once for every candidate row in the main query

Question 2

Which comparison operator cannot be used with multiple-row subqueries? (Choose the best answer.)

<= ALL

> ANY

=ANY

=ALL

Question 3

What are the distinguishing characterstics of a scalar subquery. (Choose two.)

It cannot be used in a correlated subquery

It returns a single row

It cannot be used in the SELECT LIST of a the main query

It returns a single column.

Question 4

What is wrong with the following statement:

SELECT last_name, (SELECT count(*) FROM departments)

FROM employees

WHERE salary = (SELECT salary FROM employees);

Nothing, it will run without any errors.

The statement will fail because the subquery in the SELECT list references a table that is not in the FROM clause on the main query

The statement will fail if the second subquery returns more than one value.

The statement will run but will be extremely inefficient because of the need to run the second subquery once for every candidate row in the employees table.

Question 5

A subquery that includes references back to the parent (or main) query, and thus cannot execute as a standalone query is a ____ subquery.

scalar

correlated

multi-row

multi-column

Question 6

Which of the following is true about a multiple-row subquery?

Only two values may be returned by the subquery

IN is the only comparison operator that may be used.

The comparison operators which may be used are: IN, NOT IN, ANY, and ALL.

A multiple-row subquery can also be a scalar subquery

Question 7

A subquery must be a complete query in itself, in other words, it must have at least a SELECT and a FROM clause.

True

False

Question 8

A subquery cannot have an ORDER BY clause.

True

False

Question 9

Although it is convention to enclose a subquery in parenthesis to separate it from the outer query, the parenthesis are not required.

True

False

Question 10

If you place a subquery in the outer query's WHERE or HAVING clause, you can only do so on the RIGHT HAND SIDE of the comparison operator.

True

False

Question 11

Given the BOOKS table, which contains the following columns:

title VARCHAR2(40)

cost NUMBER

retail NUMBER -- retail price

pub_id CHAR(4)

You write the following query. What will happen when you execute it?

SELECT title, retail

FROM books

WHERE retail = (SELECT MAX(retail) FROM books GROUP BY pub_id);

It will run and select any books that have the highest retail price.

It will run but produce no rows.

It will generate an error due to the multiple row subquery.

It will generate an error because the subquery itself has a group function error.

Question 12

Consider the BOOKS table, which contains the following columns:

title VARCHAR2(40)

cost NUMBER

retail NUMBER

pub_id CHAR(4)

What can be said of this query? (Choose two.)

SELECT title FROM books

WHERE pub_id IN

(SELECT pub_id

FROM books

WHERE title LIKE '%GAMES%') AND

retail > (SELECT AVG(retail)

FROM books);

It will generate an error since the first subquery is not really necessary.

It will execute without any errors.

It will generate an error because you cannot have two subqueries in the same SELECT statement that are not nested.

It will return the title of all books published by publishers who have published a title that contains the word 'GAMES', where the retail price is above the average retail price.

Question 13

Which query identifies customers living in the same state as the customer named LISA SMITH?

SELECT customer# FROM customers

WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH');

SELECT customer# FROM customers

WHERE state = (SELECT state FROM customers WHERE firstname = 'LISA' OR lastname = 'SMITH');

SELECT customer# FROM customers

WHERE state = (SELECT state

FROM ustomers WHERE firstname = 'LISA' AND lastname = 'SMITH');

SELECT customer# FROM customers

WHERE state = (SELECT state FROM customers

WHERE firstname = 'LISA' AND lastname = 'SMITH');

Question 14

Which one of the following operators is considered a single-row operation?

ALL

>ALL

Question 15

Which of the following operators is used to find all values greater than the highest value returned by a value?

>ALL

<ALL

>ANY

<ANY

Question 16

Which of the following statements is true?

The IN operator cannot be used with a subquery that returns only one row of results.

The = operator cannot be used with a subquery that returns more than one row of results.

In an uncorrelated subquery, statements in the outer query are executed first, then statements in the inner query are executed.

A subquery can be nested only in the outer query's WHERE clause.

Question 17

A subquery must be placed in the outer query's HAVING clause if:

The inner query needs to reference the value returned to the outer query.

The value returned by the inner query is to be compared to grouped data in the outer query.

The subquery returns more than one value to the outer query.

None of the above. Subqueries can't be used in the outer query's HAVING clause.

College Brighten

adv db Quiz 5-8

No comments:

Post a Comment

Popular Posts

College Brighten

adv db Quiz 5-8

No comments:

Post a Comment

Social Profiles

Popular Posts