Adv_Database Quizes ~ College Brighten

Chapter1

Question 1

A database system is "relational" if it does which of the following? (Select all that apply.)

Includes "keys" to relate records in one table to records in another table

Uses SQL

Stores data

All databases are relational

Question 2

The most commonly used form of database normalization for relational schema is which of the following?

1NF

2NF

3NF

4NF

Question 3

A table consists of (choose the single best answer):

Names and statements

Rows and columns

Relations and columns

Primary and foreign keys

Question 4

The language used to create objects in an Oracle database is called:

RDBMS

SQL

Oracle

CREATE

Question 5

Choose all of the following statements that are true:

Oracle SQL is completely identical to standard SQL

Oracle is the only manufacturer of SQL-compliant database management systems

SQL can be used to add data to a table, but not to change that data.

SQL can be used to add data to a table, and also to change that data.

Question 6

Which of the following Oracle tools can be used to execute SQL statements against a database?

SQL Developer

SQL*Plus command line

SQL Language Reference Manual

All of the choices are correct

Question 7

SQL is a set-oriented language. Which of these features best describes the consequence of this? (select the best answer.)

Each row must have a unique identifier

Sets of users may be grouped into roles

SQL statements can be grouped into set of statements that are called "scripts"

One statement can affect a set of records or rows

Question 8

There are 16 SQL commands, separated into 5 commonly identified groups.

True False

Question 9

Which of the following are part of the Oracle Server Technologies (select all that apply):

Oracle Database

Oracle Application Server

Oracle Enterprise Manager

Application development tools, such as PL/SQL, Oracle Forms Developer, etc.

Question 10

An entity-relationship diagram shows data modeled into ____.

Two-dimensional tables

multi-dimensional tables

hierarchical structures

object-oriented structures

Question 12

Which of the following are large-scale commercial DBMS today? (Select all that are correct.)

Oracle RDBMS

Microsoft Access

Microsoft SQL Server

IBM DB2

Question 13

Which of the following provides a platform for running client/server or web applications?

Oracle Database

Enterprise Manager

Application Server

Application Development Tools

Question 14

Using Oracle Entity-Relationship notation, which of the following represents the primary key of a table?

Question 15

SQL is now an international standard language that is managed by committees from NATO and ISO.

True False

Question 16

SQL is a set-oriented language capable of nothing other than data access and manipulation.

True False

Question 17

Which of the following constructs is NOT part of the SQL language? Select all that apply.

DO...WHILE loop

FOR...NEXT loop

IF..Then..Else selection

ROLLBACK

COMMIT

Chapter2

Question 1

You are tasked with creating a SELECT statement to retrieve data from a database table named PORTS. The PORTS table has two columns: PORT_ID, and PORT_NAME. Which of the following is a valid SELECT statement? (Choose all that apply.)

SELECT PORT_ID FROM PORTS:

SELECT * FROM PORTS;

SELECT PORT_NAME, PORT_ID FROM PORTS;

SELECT PORT_ID, PORT_NAME FROM PORTS;

Question 2

Review the following data for a table called ONLINE_ORDERS:

UNIT_PRICE SHIPPING TAX_MULT

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

3.00 4.00 1.10

What is the result of the following SELECT statement:

SELECT UNIT_PRICE + SHIPPING * TAX_MULT

FROM ONLINE_ORDERS;

7.70

7.40

.70

12.12

Question 3

A SELECT statement that draws data from two or more tables by relating common information between them is said to be doing which of the following?

Selecting

Joining

Projection

Linking

Question 4

Consider the following rows in a table called CUSTOMERS:

CUST_ID FIRST_NAME MIDDLE LAST_NAME

1 Bianca M. Canales

2 Chua A. Nguyen

3 Bianca M. Jackson

4 Maya R. Canales

5 Bianca S. Canales

What will be the result of executing the following statement: (one answer)

SELECT DISTINCT LAST_NAME, FIRST_NAME FROM CUSTOMERS;

It will execute and display 3 rows of data.

It will execute and display 4 rows of data.

It will fail with a syntax error because you cannot use DISTINCT with more than one column.

It will execute and display 5 rows of data.

Question 5

What will be the result of executing the following:

SELECT PORT_ID, * FROM PORTS;

It will display the PORT_ID, and ignore the asterisk (*)

It will display the PORT_ID for each port,followed by the values for all of the other columns in the table

It will fail with a syntax error because you cannot use a column name and the asterisk (*) together in a single list.

Question 6

Consider the following table structure:

DESC ENGINES

Name Null? Type

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

ENGINE_ID NUMBER

ENGINE_NAME VARCHAR2(30)

DISPLACEMENT NUMBER

Now consider the following SELECT statement:

SELECT ENGINE_NAME FROM ENGINES;

What will be the result of executing the SELECT statement?

It will display data from all of the rows in the ENGINES table, however many there may be

It will display only the rows in the ENGINES table that contain non-null values for the ENGINE_NAME column

It will display data from no more than one row of the ENGINES table

It will fail with a syntax error because it doesn't include the ENGINE_ID column

Question 7

Which component(s) of the following query is/are a literal (select all that apply):

SELECT order_id || '-' || line_item_id || ' ' || quantity "Purchase"

FROM line_item;

"Purchase"

' '

'-'

Question 8

Which of the following are NOT required to form a syntactically correct SELECT statement? Select all answers that apply.

SELECT

Valid name of a column

Valid name of a table

FROM

Question 9

Parenthesis can be used in an expression to do which one of the following?

Enclose keywords

Avoid the need for using column names in the select list

Evaluate the expression more quickly

Override the rules of operator precedence

Question 10

Given that the columns and table name are all correct, which of the following lines of the SELECT statement contain an error? (one answer)

SELECT name, contact,

"Person to call", phone

FROM publisher;

line 1

line 2

line 3

there are no errors

Chapter 3

Question 1

Which of the following will display only the books published by the publisher having publisher ID equal to 2 and having a retail price of at least $35.00?

(Assume table and column names are legitimate names in the database.)

Select all that apply.

SELECT * FROM book WHERE pubid IN (1, 2, 3) AND retail >= 35;

SELECT * FROM book WHERE pubid IN (1, 2, 3) AND retail NOT BETWEEN 1 and 29.99;

SELECT * FROM book WHERE pubid = 2, retail >= 35;

SELECT * FROM book WHERE pubid = 2 AND NOT retail < 35;

Question 2

Consider the following table named "ports":

PORT_ID PORT_NAME CAPACITY

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

1 Galveston 4

2 San Diego 4

3 San Francisco 3

4 Los Angeles 4

5 San Juan 3

6 Grand Cayman 3

Now consider the following SELECT statement:

SELECT *

FROM ports

WHERE port_name LIKE 'San%'

OR port_name LIKE 'Grand%'

OR capacity = 3;

How many rows from the data in the table will be returned?

None

Question 3

Examine the structure of the PRODUCT table:

PRODUCT_ID NUMBER

PRODUCT_NAME VARCHAR2(25)

SUPPLIER_ID NUMBER

QTY_PER_UNIT NUMBER

LIST_PRICE NUMBER(5,2)

COST NUMBER(5,2)

You want to display the product identification numbers of all products with 500 or more units available. You want the product numbers displayed numerically by supplier identification number, then by product identification number from lowest to highest. Which statement should you use to achieve the desired results?

SELECT product_id

FROM product

WHERE qty_per_unit >= 500

ORDER BY supplier_id, product_id;

SELECT product_id

FROM product

WHERE qty_per_unit > 500

ORDER BY supplier_id, product_id;

SELECT product_id

FROM product

WHERE qty_per_unit >= 500

SORT BY supplier_id, product_id;

SELECT product_id

FROM product

WHERE qty_per_unit >= 500

ORDERBY supplier_id, product_id;

Question 4

You want to query employee information and display the results sorted by the employee's department identification code, then by their salaries from highest to lowest. When multiple employees within the same department have the same salary, they should be listed by their last name in alphabetical order. When multiple employees share a last name, they must be displayed in alphabetical order by first name. Which ORDER BY clause should you use?

ORDER BY dept_id, salary, last_name, first_name

ORDER BY dept_id, salary ASC, last_name, first_name

ORDER BY dept_id, salary DESC , first_name, last_name

ORDER BY dept_id, salary DESC, last_name, first_name

Question 5

Review the following data listing for a table called SHIP_CABINS:

ROOM_NUMBER STYLE WINDOW

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

102 Suite Ocean

103 Ocean

104

The blank values are NULL. Now review the following SELECT statement:

SELECT room_number

FROM ship_cabins

WHERE (style = NULL) OR (window = NULL);

How many rows will this statement retrieve?

It will not execute due to a syntax error in the WHERE clause.

Question 6

Assume that the table and column names are correct for the SHIPS table. Consider the following SELECT statement. (Line numbers are added for readability.)

01 SELECT ship_id FROM ships

02 WHERE ship_name LIKE 'Codd %'

03 OR lifeboats >= 80

04 AND lifeboats <= 100;

Which of the following statements is true about this SELECT statement?

The syntax on lines 3 and 4 is incorrect.

Line 3 and 4 need parentheses

It will return all ships beginning with 'Codd' along with all ships that have between 80 and 100 lifeboats (inclusive)

It will return all ships beginning with 'Codd' that have between 80 and 100 lifeboats (inclusive)

Question 7

Consider the following SELECT statement:

SELECT ship_id

FROM ships

WHERE 10 = 5 + 5;

Which of the following is true of this statement?

It will execute and return no rows.

It will execute and return the ship_id for all of the rows in the table.

It will produce a syntax error because the WHERE statement does not refer to any columns in the table.

It will produce a syntax error because the WHERE condition returns a TRUE value.

Question 8

Which of the following statements is true of Boolean operators? For this quesion, ignore the role of parentheses. (Choose two.)

NOT is evaluated first

OR is evaluated before AND

OR is evaluated after NOT

AND is evaluated before NOT

Question 9

SELECT order_num, &order_date

FROM &&ordertbl

WHERE order_date = '&order_date';

Which statement regarding the execution of this statement is true?

The user will be prompted for the order_date twice, but only the first time that the statement is executed in a session.

The user will be prompted for the order_date twice, each time that the statement is executed in a session.

The user will be prompted for the order date only the first time the statement is executed in a session.

The user will be prompted for the order_date once each time the statement is executed in a session.

Question 10

ACCT_ID CRUISE_NAME START_DATE END_DATE

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

1 Hawaii 11-JUL-12 24-JUL-12

2 Hawaii 10-OCT-12 23-OCT-12

3 Mexico 04-OCT-12 17-OCT-12

4 Mexico 06-DEC-12 19-DEC-12

What will be the value of the ACCT_ID for the first row displayed, given the following ORDER BY clause:

ORDER BY cruise_name DESC, start_date

Chapter4

Question 1

Which of the following would be used to display how many months a book was available before the customer placed the order?

NOTE: (orderdate is the date that a book was ordered, and pubdate is the date that the book is available.)

NEXT_DAY(orderdate, pubdate)

MONTHS_BETWEEN(pubdate, orderdate)

MONTHS_BETWEEN(orderdate, pubdate)

NEXT_DAY(pubdate, orderdate)

Question 2

Which one of the following SELECT statements will return 30 as the result?

SELECT TRUNC(29.99, 0) FROM DUAL;

SELECT TRUNC(29.99, -1) FROM DUAL;

SELECT ROUND(24.37, 2) FROM DUAL;

SELECT ROUND(29.01, -1) FROM DUAL;

Question 3

Which statement(s) are true regarding single-row functions? (Choose all that are true.)

They may have zero or more input parameters.

They return one result per row in the selected dataset.

They must have at least one input parameter.

They may return more than one result.

Question 4

What is the result of:

SELECT TRUNC(ROUND(-15.749), 2) FROM dual;

-16

-15

Question 5

The value of the statement:

SELECT MOD(16, 3) FROM DUAL;

is 2.

True

False

Question 6

Which of the following will produce "Hello World" as the results (with the capitalization as shown.)

A) SELECT INITCASE('hello world') FROM DUAL;

B) SELECT "Hello World" FROM dual;

C) SELECT INITCAP('HELLO WORLD') FROM dual;

D) SELECT LOWER('HELLO WORLD') FROM DUAL;

Question 7

A bookstore has a policy that when a book is ordered, it is shipped on the first Monday after the date that the book was ordered. Which of the following will determine the correct ship date?

NEXT_DAY(orderdate, 'Monday')

LAST_DAY(orderdate, 'Monday')

NEXT_DAY(SYSDATE, 'MONDAY')

NEXT_DAY('Monday', SYSDATE)

Question 8

Which of the following is a valid SQL statement?

SELECT SUBSTR(ROUND(14.87, 2, 1), -4, 1) FROM DUAL;

SELECT TRUNC(ROUND(124.67, 1) , 0) FROM DUAL;

SELECT ROUND(TRUNC(125.38, 0) FROM DUAL;

SELECT LTRIM(RPAD(state, 5, '*'), 4, -3, "*") FROM DUAL;

Question 9

SYSDATE = 30-DEC-2012. What is the value returned by the following statement:

SELECT TRUNC(SYSDATE, 'YEAR') FROM dual;

01-DEC-2012

01-JAN-2013

30-DEC-2011

01-JAN-2012

Question 10

Review this SQL statement:

SELECT MONTHS_BETWEEN(LAST_DAY('15-JAN-12') + 1, '01-APR-12') FROM dual;

What will be the result from the query above?

-3

-2

College Brighten

Adv_Database Quizes

No comments:

Post a Comment

Popular Posts

College Brighten

Adv_Database Quizes

No comments:

Post a Comment

Social Profiles

Popular Posts