INF-503 Advanced Database [Mar 25 Syllabus]

Lesson-1Quiz

The exam is timed.

True

What can DDL be used for? (Choose three.)

Add privileges for a user to a database table

Add columns to a database table

Add comments to a database table

Which of the following can be used to remove data from a table? (Choose two.)

DELETE

UPDATE

What is one of the purposes of DDL? (Choose the best answer.)

Issue privileges to users

When transforming an ERD into a relational database, you often use an entity to build a database's:

Table

Which of the following topics are not included in the SQL Fundamentals I exam but are addressed on the SQL Associate exam? (Choose all that apply.)

FLASHBACK

MERGE

External tables

The 1Z0-071 exam (which is the subject of this book) has been officially validated by Oracle Corporation against which of the following versions of the Oracle database? (Choose all that apply.)

12c

11g

If you focus on trying to achieve the minimum passing grade requirement for the exam, you can study more efficiently.

False

Which of the following is not a capability of the SELECT statement?

It can remove data from a table.

The unique identifier of a row in a database table is a(n):

Primary key

Which of the following is true of SQL?

It is the most commonly used language for interacting with a database.

What can a SELECT statement be used to query? (Choose the best answer.)

One or more tables

The best exam guide you could possibly get for preparing to take and pass the 1Z0-071 certification exam, SQL Associate, is which of the following? (Choose all that apply.)

(Choose all that apply.)

 

What can you use to submit SQL statements for execution? (Choose all that apply.)

 (Choose all that apply.)

Which one of the following is a DML statement?

 UPDATE

Lesson-2Quiz

A table is which of the following?

A schema object

Review the following SQL statement:

CREATE TABLE personnel
( personnel_ID    NUMBER(6),
   division_ID     NUMBER(6),
   CONSTRAINT personnel_ID_PK PRIMARY KEY (personnel_ID),
   CONSTRAINT division_ID_PK PRIMARY KEY (division_ID));

The statement will fail because you cannot create two primary key constraints on the table.

Review the following SQL statement:

CREATE TABLE shipping_Order
( order_ID    NUMBER,   
order_Year  CHAR(2),   
customer_ID NUMBER, 
CONSTRAINT shipping_Order_pk PRIMARY KEY (order_ID, order_Year));

Assume there is no table already called SHIPPING_ORDER in the database. What will be the result of an attempt to execute the preceding SQL statement?

The statement will succeed: the table will be created, and the primary key will also be created.

The following SQL statements create a table with a column named A, then add a row to that table, then query the table:

CREATE TABLE NUMBER_TEST (A NUMBER(5,3));
INSERT INTO NUMBER_TEST (A) VALUES (3.1415);
SELECT A FROM NUMBER_TEST;

What is the displayed output of the SELECT statement?

3.142

no bookmarked, confident, or note marked

Question 5 :The difference between dropping a column from a table with DROP and setting a column to be UNUSED is:

    The UNUSED column and its data are retained within the table's storage allocation and counts against the total limit on the number of columns the table is allowed to have.

no bookmarked, confident, or note marked

Question 6 :You are logged in to user FINANCE. It is currently the only schema in the entire database. The following exist in the database:

• A VIEW named VENDORS
• A CONSTRAINT named VENDORS
• An INDEX named CUSTOMER#ADDRESS

You attempt to execute the following SQL statement:

CREATE TABLE CUSTOMER#ADDRESS
(ID  NUMBER,
   NAME VARCHAR2(30));

Which one of the following is true?

The SQL statement will execute, and the TABLE will be created.

Which of the following is true about ROLES?

Roles are in the same namespace as USERS.

A CONSTRAINT is assigned to which of the following?

TABLE

The DESC command can be used to do which of the following?

Show a table's columns and the data types of those columns

Which of the following are schema objects? (Choose all that apply.)

SEQUENCE

INDEX

Which of the following options can be used with the reserved word CREATE to form the beginning of a complete SQL statement? (Choose three.)

SEQUENCE

TABLE

VIEW

no bookmarked, confident, or note marked

Question 12 :You have a single database, with only one schema. The following four objects exist in the database:

• A TABLE named PRODUCT_CATALOG
• A TABLE named ADS
• A USER named PRODUCT_CATALOG
• A VIEW named CONFERENCE_SCHEDULE

How many of the four objects are owned by the schema?

3

               

                      Which of the following are valid CREATE TABLE statements? (Choose three.)

                       

 CREATE TABLE workSchedule

(ID NUMBER,
NAME VARCHAR2(30));

CREATE TABLE CUSTOMER_HISTORY
(ID NUMBER,
NAME VARCHAR2(30));

CREATE TABLE "Boat Inventory"
(ID NUMBER,
NAME VARCHAR2(30));

The purpose of the CREATE DIRECTORY statement is to create a named object in the database:

That points to a directory you choose somewhere within the Oracle server's file system

Which of the following SQL statements creates a table that will reject attempts to INSERT a row with NULL values entered into the POSITION_ID column?

CREATE TABLE POSITIONS
(POSITION_ID NUMBER(3),
CONSTRAINT POSITION_CON PRIMARY KEY (POSITION_ID));

You attempt to execute the following SQL statement:

CREATE TABLE VENDORS 
(VENDOR_ID   NUMBER,
VENDOR_NAME VARCHAR2,
CATEGORY    CHAR);

Which one of the following is true?

The execution fails because there is no precision indicated for VARCHAR2.

Lesson-3 Quiz

no bookmarked, confident, or note marked

Question 1 :Review the SQL statements that follow, and assume that there is no table called ADDRESSES already present in the database:

Copy 

CREATE TABLE ADDRESSES (ID NUMBER, ZONE NUMBER, ZIP_CODE VARCHAR2(5)); 
INSERT INTO ADDRESSES (ID, ZONE, ZIP_CODE) VALUES (1, 1, '94065'); 
SAVEPOINT ZONE_CHANGE_01; 
UPDATE ADDRESSES SET ZONE = 2 WHERE ZIP_CODE = 94065; 
ROLLBACK;

The ADDRESSES table will have no rows.

Which of the following reserved words is/are optional in a complete DELETE statement? (Choose all that apply)

FROM

WHERE

Assume a table LAMPS that has no constraints. Which of the following is true about the UPDATE statement and the LAMPS table? (Choose all that apply.)

For existing rows in LAMPS, UPDATE can remove values from any column by changing its value to NULL.

For existing rows in LAMPS, UPDATE can add values to any column with a NULL value.

Review the following SQL statements:

CREATE TABLE BOUNCERS  
(NIGHTCLUB_CODE NUMBER,   
STRENGTH_INDEX NUMBER); 
INSERT INTO BOUNCERS VALUES (1, NULL); 
UPDATE BOUNCERS    
   SET STRENGTH_INDEX = 10;

What is the end result of the SQL statements listed here?

The BOUNCERS table will contain one row.

Assume a schema with only two tables: one named PRODUCTS and one named ENGINEERING. Review the following SQL statements:

SELECT PRODUCT_ID FROM PRODUCTS;
DROP TABLE SHIP_STAFF;
INSERT INTO ENGINEERING (PROJECT_ID, MGR) VALUES (27,21);
COMMIT;
INSERT INTO ENGINEERING (PROJECT_ID, MGR) VALUES (400,17);
ROLLBACK;

In this series of SQL statements, which line represents the first commit event?

Line 2

Review the following SQL statement:

TRUNCATE personnel;

Which of the following is true of the previous statement?

The statement will fail.

Review the following SQL statements:

CREATE TABLE INSTRUCTORS   
(INSTRUCTOR_ID NUMBER,    
   EXEMPT   VARCHAR2(5),    
   VACATION NUMBER,    
   PAY_RATE NUMBER); 
INSERT INTO INSTRUCTORS VALUES (1, 'YES', NULL, 25); 
INSERT INTO INSTRUCTORS VALUES (2, NULL,  NULL, NULL); 
UPDATE INSTRUCTORS    
   SET EXEMPT    = 'YES',    
   SET VACATION  = 15 
WHERE  PAY_RATE < 50;

What can be said of the statements listed here?

At least one of the statements will not execute.

Review the following SQL statements:

CREATE TABLE AB_INVOICES (INVOICE_ID NUMBER, VENDOR_ID NUMBER); 
ALTER TABLE AB_INVOICES ADD PRIMARY KEY (INVOICE_ID); 
INSERT INTO AB_INVOICES VALUES (1,1); 
DELETE AB_INVOICES WHERE INVOICE_ID = 2;

Which of the following best describes the results of attempting to execute the DELETE statement?

The DELETE statement will execute, but no rows in the table will be removed.

The CASCADE keyword, when used with TRUNCATE:

Is required if the table has any dependent child tables

Consider the following set of SQL statements:

CREATE TABLE MAILING_LIST(FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(30));
INSERT INTO MAILING_LIST VALUES('Smith', 'Mary');

What will be the result of the INSERT statement?

It will execute and create a new row in the table.

Review the following statement:

CREATE TABLE STUDENT_LIST  
(STUDENT_ID  NUMBER,  
NAME        VARCHAR2(30),   
PHONE       VARCHAR2(30)); 
INSERT INTO STUDENT_LIST    
VALUES (1, 'Joe Wookie', 5551212);

The table will create successfully. What will result from the INSERT statement?

The INSERT will execute—the table will contain one row of data.

Consider the following data in a table called PARTS:

PNO  PART_TITLE       STATUS 
---  ---------------- -------   
1  PROCESSOR V1.0   VALID   
2  ENCASEMENT X770  PENDING   
3  BOARD CPU XER A7 PENDING

Which of the following SQL statements will remove the word VALID from row 1, resulting in one row with a status of NULL and two rows with a status of PENDING?

None of these

Consider the following set of SQL statements:

CREATE TABLE INSTRUCTORS  
(INSTRUCTOR_ID  NUMBER,   
NAME           VARCHAR2(20),   
CONSTRAINT     ID_PK   PRIMARY KEY (INSTRUCTOR_ID),   
CONSTRAINT     NAME_UN UNIQUE (NAME));

INSERT INTO INSTRUCTORS (INSTRUCTOR_ID, NAME)     
   VALUES (1, 'Howard Jackson');
INSERT INTO INSTRUCTORS (INSTRUCTOR_ID, NAME)   
   VALUES (2, 'Trish Mars');

The table will create successfully. What will be the result of the two INSERT statements?

Both will execute successfully.

Which of the following reserved words is not required in order to form a syntactically correct UPDATE statement?

WHERE

RUNCATE TABLE:

Is a valid set of keywords to be used within a DDL statement

Lesson-4 Quiz

Review the following data listing for a table SHIPS:

Copy 

SHIP_ID  SHIP_NAME      CAPACITY  LENGTH  LIFEBOATS 
-------  -------------  --------  ------  ---------       
      1  Codd Crystal       2052     855         80       
      2  Codd Elegance      2974     952         95

In the SHIPS table, SHIP_NAME has a data type of VARCHAR2(20). All other columns are NUMBER. Now consider the following query (note that line numbers have been added for readability):

Copy 

SELECT SHIP_ID 
FROM   SHIPS 
WHERE  CAPACITY BETWEEN 2052 AND 3000 
AND  LENGTH IN ('100','855') 
AND  SHIP_NAME LIKE 'Codd_%';

How many rows will the SELECT statement return?

1

You can use a substitution variable to replace:

Both

To permanently delete a substitution variable named THE_NAME so that it can no longer be used, use:

UNDEFINE THE_NAME

Review this SELECT statement:

SELECT   SHIP_NAME 
FROM     SHIPS 
ORDER BY SHIP_ID, CAPACITY DESC;

Assume that all table and column references exist within the database. What can be said of this SELECT statement?

The rows will sort in order by SHIP_ID in ascending order and then by CAPACITY in descending order.

Consider the following text:

DEFINE vRoomNumber 
PROMPT "Enter a room number: " 
SELECT ROOM_NUMBER, STYLE, WINDOW 
FROM   SHIP_CABINS 
WHERE  ROOM_NUMBER = &RNBR;

What will happen when this script is executed?

The end user will be prompted to enter a number.

To list all the currently defined variables, use:

DEFINE

Which if the following is true of the ORDER BY clause? (Choose two.)

It can sort rows based on data that isn't displayed as part of the SELECT statement.

It is optional.

Assume you have a table ITEMS that includes a column STATUS. Which of the following statements is syntactically correct? (Choose all that apply.)

SELECT * FROM ITEMS FETCH NEXT 20 % ROWS ONLY;

Assume all table name and column name references in the SQL statement that follows are valid. That being said, what is wrong with the syntax of the following SQL statement?

SELECT SHIP_ID
FROM SHIPS
WHERE ((2*LIFEBOATS)+57) - CAPACITY IN (LIFEBOATS*20, LIFEBOATS+LENGTH);

There is nothing wrong with the syntax.

Review this SELECT statement:

SELECT   PRODUCT_ID, PRODUCT_NAME, UNIT_PRICE, SHIPPING 
FROM     PRODUCTS 
WHERE    (UNIT_PRICE + SHIPPING) * TAX_RATE > 5 
ORDER BY LIKE PRODUCT_NAME;

Assume all table and column references exist in the database. What can be said of this SELECT statement?

The statement will fail to execute because the ORDER BY clause includes the word LIKE.

Review the following data listing for the SHIPS table:

SHIP_ID  SHIP_NAME      CAPACITY  LENGTH  LIFEBOATS 
-------  -------------  --------  ------  ---------      
      1  Codd Crystal       2052     855         80       
      2  Codd Elegance      2974     952         95

Now review the following SQL statement (line numbers are added for readability):

SELECT SHIP_ID FROM   SHIPS
WHERE  SHIP_NAME IN ('Codd Elegance','Codd Victorious') 
   OR  (LIFEBOATS >= 80 
   OR   LIFEBOATS <= 100)
AND  CAPACITY / LIFEBOATS > 25;

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

The syntax is correct.

If you are using an ORDER BY to sort values in descending order, in which order will they appear?

f the data type is character, the value 'Michael' will appear first before the value 'Jackson'.

Consider the following statement:

SELECT * FROM ITEMS ORDER BY LIST_DATE 
OFFSET   -5 ROWS FETCH FIRST 4 ROWS ONLY;

Assume you have a table ITEMS with a column LIST_DATE. What is the result of an attempt to execute the statement?

It will sort the rows by LIST_DATE and return only the first four rows.

Review the following data listing for a table VENDORS:

VENDOR_ID  CATEGORY 
---------  ---------------         
        1  Supplier         
        2  Teaming Partner

Now review the following SQL statement:

SELECT VENDOR_ID 
FROM   VENDORS 
WHERE  CATEGORY IN ('Supplier','Subcontractor','%Partner');

How many rows will the SELECT statement return?

1

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 SQL statement (line numbers are added for readability):

SELECT ROOM_NUMBER
FROM   SHIP_CABINS 
WHERE  (STYLE = NULL) OR (WINDOW = NULL);

How many rows will the SQL statement retrieve?

0

Lesson-5 Quiz

The PERCENTILE_CONT function:

Can be used with PARTITION BY to specify groups of data

Consider the following:

SELECT MOD(5,3), REMAINDER(5,3) FROM DUAL;

Which of the following will be the result?

2, -1

Analytic functions are processed:

As the last set of operations before processing the ORDER BY clause

Built-in SQL functions: (Choose three.)

Are written by SQL developers and also known as "user-defined" functions.

The output of a function may be used: (Choose three.)

As an alternative to the keyword SET in an UPDATE statement.

Consider the following SQL statement:

SELECT SOUNDEX('Donald') FROM DUAL;

Which of the following is most likely to be the output of this SELECT statement? (Choose the best answer.)

D543

Which of the following is true of character functions?

They are generally used to process text data.

Which of the following is true of functions?

They always return a value.

The LEAD function returns data from:

The row specified by the LEAD function's offset

Review this SQL statement:

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

What will result from this query?

–2

Review this SQL statement:

SELECT LASTNAME FROM CUSTOMERS WHERE LASTNAME = SOUNDEX('Franklin');

What is a possible result for the query?

None of these

You are tasked to create a SELECT statement to subtract five months from the hired date of each employee in the EMPLOYEES table. Which function will you use?

None of these

Review this SQL statement:

SELECT TRUNC(ROUND(ABS(-1.7),2)) FROM DUAL;

What will be the result of the SQL statement?

1

Review this SQL statement:

SELECT SUBSTR('2009',1,2) || LTRIM('1124','1') FROM DUAL;

What will be the result of the SQL statement?

2024

The ORDER BY in an OVER clause:

Operates independently of the ORDER BY in the SELECT statement

Lesson-6 Quiz

You are tasked to create a report that displays the hours and minutes of the current date in a report. Which of the following will satisfy this requirement?

TO_CHAR(SYSDATE, 'HH:MI')

Which of the following SQL statements will display the current time, in hours, minutes, and seconds, as determined by the operating system on which the database server resides?

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;

Which format mask returns the local currency symbol?

L

The DECODE expression always ends with:

Neither of these

The purpose of NULLIF is to:

None of these

no bookmarked, confident, or note marked

Question 6 :Consider the following statement:

SELECT  NVL(SHIP_NAME,'None'), 
      CASE CAPACITY WHERE 234 THEN 'OK' 
                    WHERE 999 THEN 'OK' 
      END 
FROM  SHIPS;

Which of the following statements is true of the previous SELECT statement?

The statement will fail because of syntax errors on lines 2 and 3.

Conversion functions cannot be used to:

Convert columns to new data types

Create user-defined data types

Which of the following can be said of the CASE statement?

It uses the keyword THEN.

Consider the following query, its output, and a subsequent query:

SQL> SELECT * FROM LINE_ITEMS; 
LINE_ITEM  PRICE 
---------  -----       
      100   4.12       
      210          
      184   7.07 

SQL> SELECT NVL(PRICE,10) FROM LINE_ITEMS;

What is true of the final query shown previously?

It will return three rows, but it will not change the price for line items 100 and 184.

Which query returns an expression of the data type INTERVAL YEAR TO MONTHS representing an interval of 1 year and 3 months?

SELECT TO_YMINTERVAL('01-03') FROM DUAL;

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

You can use a data type conversion function to format numeric data to display with dollar signs and commas.

The presence of an explicit data type conversion documents your intent in the code.

Conversion functions:

Change a value’s data type in an equation to tell SQL to treat the value as that specified data type.

If you want to display a numeric value with dollar signs and commas, which of the following is the best approach to take?

The TO_CHAR function with a format model

Consider the following table listing from the table ALARM_HISTORY:

TRACKING_DATE  INCIDENTS 
-------------  ---------   
17-OCT-2018         12   
18-OCT-2018          3   
19-OCT-2018              
20-OCT-2018             
21-OCT-2018          4

You are tasked to calculate the average number of alarm incidents per day in ALARM_HISTORY. You know the following query is syntactically correct:

SELECT AVG(INCIDENTS) FROM ALARM_HISTORY;

However, you are aware that the value for INCIDENTS might be NULL, and you want the AVG returned to be calculated across every day in ALARM_HISTORY, not just the non-NULL days. Which of the following queries will achieve this goal?

SELECT AVG(NVL(INCIDENTS,0)) FROM ALARM_HISTORY;

You need to determine the day of the week for a particular date in the future. Which function will reveal this information?

TO_CHAR

Lesson-7 Quiz

Review the following illustration:

Figure A

Now review this SQL statement:

Copy 

SELECT CRUISE_ORDER_ID, COUNT(ORDER_DATE) 
FROM   CRUISE_ORDERS;

What can be said of this statement?

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

Which of the following is true about aggregate functions? (Choose two.)

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

Are also called group functions.

Which of the following aggregate functions ignores NULL values in its calculations? (Choose all that apply.)

Choose all that apply

Review the following illustration:

Figure A

Your assignment: create a SELECT statement that queries the PROJECTS table to show the average project cost for each PURPOSE. You know there are only two values for PURPOSE in the table: 'Upgrade' and 'Maintenance'. You want to restrict rows where DAYS is greater than 3. Which of the following SELECT statements will perform this task?

SELECT PURPOSE, AVG(PROJECT_COST)
FROM PROJECTS
WHERE DAYS > 3
GROUP BY PURPOSE;

Which of the following aggregate functions can be used on character data? (Choose two.)

MIN

COUNT

Review the following illustration:

Figure A

and then look at the SQL code that follows:

SELECT   TO_CHAR(ORDER_DATE,'Q') "Quarter", COUNT(*) 
FROM     CRUISE_ORDERS 
WHERE    TO_CHAR(ORDER_DATE,'YYYY') = '2009'
GROUP BY TO_CHAR(ORDER_DATE,'Q');

Recall that the 'Q' format model is for quarter, so TO_CHAR using a DATE data type with the 'Q' format mask is translating the date into the quarter in which it falls—1, 2, 3, or 4. Given that, which of the following statements is true of the SQL statement?

It will execute and show the number of orders in the CRUISE_ORDERS table for each quarter in the year 2009.

Review the following illustration:

Figure A

and then review the following SQL statement:

SELECT AVG(CRUISE_ORDER_ID), MIN(ORDER_DATE) 
FROM   CRUISE_ORDERS;

What will result from an attempt to execute this SQL statement on the CRUISE_ORDERS table?

It will execute and perform as intended.

An aggregate function can be called from within: (Choose two.)

The ORDER BY clause of a SELECT statement

The select list of a SELECT statement

Review the following data listing from a table SCORES:

SCORE_ID  TEST_SCORE 
--------  ---------- 
1          95 
2 
3          85

Now consider the following query:

SELECT  TO_CHAR(AVG(TEST_SCORE),'999,999.99') FROM SCORES;

What will be the result of this query?

90.00.

Examine the following data listing of a table called PERMITS:

PERMIT_ID   FILED_DATE   VENDOR_ID 
---------   ----------   --------- 
1           05-DEC-09    101 
2           12-DEC-09    310903 
3           14-DEC-09    101

Which one of the following aggregate functions could be used to determine how many permits have been filed by VENDOR_ID 101?

COUNT

Review the following illustration:

Figure A

and then look at the SQL code that follows:

SELECT   COUNT(COUNT(PROJECT_COST)) 
FROM     PROJECTS 
GROUP BY PURPOSE;

What will happen if you try to execute this query on the PROJECTS table?

It will succeed and display one row.

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

It must occur after the WHERE clause.

It can be used only in the SELECT statement

Review the following illustration:

Figure A

Your task is to define a SELECT statement that groups rows according to their value for PURPOSE and, for each purpose, adds up the values stored in DAYS. Which one of the following queries will perform this task?

SELECT SUM(DAYS), PURPOSE
FROM PROJECTS
GROUP BY PURPOSE;

Review the following illustration:

Figure A

Which of the following SQL statements will execute correctly?

SELECT RANK(100000) WITHIN GROUP (ORDER BY PROJECT_COST) FROM PROJECTS;

Review the following illustration:

Figure A

and review the SQL statement that follows:

SELECT   SHIP_ID, MAX(DAYS)
FROM     PROJECTS 
GROUP BY SHIP_ID 
HAVING   AVG(PROJECT_COST) < 500000;

Which of the following statements is true for this SQL statement?

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

Lesson-8 Quiz

Review the following illustration:

Figure A

Which of the following is a syntactically correct outer join query? (Choose two.)

SELECT VENDOR_NAME, INVOICE_DATE
FROM VENDORS RIGHT OUTER JOIN INVOICES
ON VENDORS.VENDOR_ID = INVOICES.VENDOR_ID;

SELECT VENDOR_NAME, INVOICE_DATE
FROM VENDORS LEFT JOIN INVOICES
ON VENDORS.VENDOR_ID = INVOICES.VENDOR_ID;

You have two tables. One table is called CUSTOMERS. Another is called PURCHASES, and it records a list of 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 past two weeks, as recorded in the PURCHASES table. It’s possible that many customers have made no purchases in the past 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 outer join.

It will be an equijoin.

Review the following illustration:

Figure A

Which of the following is a valid self-join statement? (Choose all that apply.)

SELECT P1.POSITION_ID, P1.MIN_SALARY, P1.MAX_SALARY
FROM POSITIONS P1 SELF JOIN POSITIONS P2
ON P1.REPORTS_TO = P2.POSITION_ID;

Review the following illustration:

Figure A

and then review the following SQL statement:

SELECT VENDOR_ID, INVOICE_DATE, TOTAL_PRICE
FROM   VENDORS JOIN INVOICES
USING (VENDOR_ID);

What kind of join is this? (Choose two.)

INNER

Equijoin

Review the following illustration:

Figure A

and then review the following SQL statement:

SELECT A.EMPLOYEE_ID, B.POSITION
FROM   PAY_HISTORY A JOIN POSITIONS B 
ON   A.SALARY < B.MAX_SALARY AND A.SALARY > B.MIN_SALARY;

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

It is an inner join.

It is a non-equijoin.

Review the INVOICES and VENDORS tables.

Figure A

Next review the following SQL statement:

SELECT VENDOR_ID, INVOICE_DATE, TOTAL_PRICE
FROM   VENDORS JOIN INVOICES
USING (VENDOR_ID);

Which of the following statements is true for the SQL statement?

It will execute successfully.

How many tables can be joined in a query?

One, two, three, or more

Review this SQL statement:

SELECT   V.VENDOR_ID, INV.INVOICE_DATE 
FROM     VENDORS V INNER JOIN INVOICES INV 
ON       V.VENDOR_ID = INV.VENDOR_ID;

Which one of the following keywords in this statement is optional?

INNER

Equijoins look for:

Exact data matches

Which of the following symbols is most likely to be used in a SELECT statement using a non-equijoin?

<=

The difference between an INNER and an OUTER join is:

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

A self-join is: (Choose two.)

A SELECT statement that joins a table to itself by connecting a column in the table to a different column in the same table

A SELECT statement that specifies one table twice in the FROM clause

Review the POSITIONS, EMPLOYEES, and PAY_HISTORY tables.

Figure A

Review the following SQL statement:

SELECT LAST_NAME, POSITION, SALARY 
FROM   POSITIONS P JOIN EMPLOYEES   E  ON P.POSITION_ID = E.POSITION_ID                    
                   JOIN PAY_HISTORY PH ON E.EMPLOYEE_ID = PH.EMPLOYEE_ID;

Which of the following is true for the SQL statement? (Choose two.)

It will execute successfully

It connects three tables.

A table alias: (Choose two.)

Can be used to clear up ambiguity in the query.

Exists only for the SQL statement that declared it.

An inner join queries from two tables (looking at values in columns and optionally using expressions that reference columns) and compares the resulting values in one set of rows with the resulting values in another set of rows, looking for:

Values that match

Lesson-9 Quiz

Which of the following can a correlated subquery be used in? (Choose three.)

The FROM clause of a DELETE statement

Which of the following is a true statement?

A SELECT statement with a GROUP BY may use a subquery to return a value to the outermost WHERE clause.

Which of the following can a subquery be used in? (Choose all that apply.)

A GRANT statement

An inline view is a form of a subquery.

True

Which of the following forms of subquery never returns more than one row?

Scalar

Review the following illustration and the SQL code:

Figure A

DELETE FROM PORTS P 
WHERE  PORT_ID NOT EXISTS (SELECT HOME_PORT_ID 
                           FROM   SHIPS 
                           WHERE  HOME_PORT_ID = P.PORT_ID);

The code is attempting to delete any row in the PORTS table that is not a home port for any ship in the SHIPS table, as indicated by the HOME_PORT_ID column. In other words, only keep the PORTS rows that are currently the HOME_PORT_ID value for a ship in the SHIPS table; get rid of all other PORT rows. That’s the intent of the SQL statement. What will result from an attempt to execute the preceding SQL statement?

It will fail because of a syntax error on line 2.

Another name for an EXISTS query is:

Semijoin

Review the following illustration:

Figure A

Which of the following statements, when executed, will result in an error?

SELECT WITH SHIPPER_INFO AS
(SELECT SHIP_ID FROM SHIPS)
SELECT PORT_ID, SHIPPER_INFO.SHIP_ID
FROM PORTS, SHIPPER_INFO;

WITH (SELECT SHIP_ID FROM SHIPS)
SELECT PORT_ID
FROM PORTS;

When is a query considered a multirow subquery? (Choose the best answer.)

If it returns multiple rows at the time of execution

Which of the following comparison operators can be used with a multiple-row subquery? (Choose two.)

IN

>= ALL

Which subquery includes references to the parent query and thus cannot execute as a standalone query? (Choose the best answer.)

A correlated subquery

The WITH clause can be used to name a subquery. Which of the following is also true? (Choose two.)

The name of the subquery can be used in the SELECT statement following the WITH clause.

The name of the subquery can be joined to other tables in the SELECT statement following the WITH clause.

Review this WORK_HISTORY table:

Figure A

Your task is to create a query that will list—for each ship—all of the EMPLOYEE_ID values for all the employees who have the shortest work history for their ship. In other words, if there are two ships, you want to list all the employees assigned to the first ship who have the shortest work history, all the employees assigned to the second ship who have the shortest work history, and so on. Which of the following queries will accomplish this task? (Choose two.)

SELECT EMPLOYEE_ID FROM WORK_HISTORY W1
WHERE ABS(START_DATE - END_DATE) <= ALL
(SELECT ABS(START_DATE - END_DATE)
FROM WORK_HISTORY
WHERE SHIP_ID = W1.SHIP_ID);

SELECT EMPLOYEE_ID FROM WORK_HISTORY W1
WHERE ABS(START_DATE - END_DATE) =
(SELECT MIN(ABS(START_DATE - END_DATE))
FROM WORK_HISTORY
WHERE SHIP_ID = W1.SHIP_ID);

A correlated subquery:

Cannot be executed as a standalone query

Review the given PORTS and SHIPS tables:

Figure A

Your team is tasked with the job of creating a list of the ships with the least capacity in each port. In other words, each ship has a home port. For each port that is a home port to ships, which of each port’s ships has the least capacity? Your team produces the following query in answer to this task:

SELECT S1.SHIP_NAME, (SELECT PORT_NAME 
                      FROM   PORTS
                      WHERE  PORT_ID = S1.HOME_PORT_ID) HOME_PORT 
FROM   SHIPS S1 
WHERE  S1.CAPACITY = (SELECT MIN(CAPACITY) 
                      FROM   SHIPS S2 
                      WHERE  S2.HOME_PORT_ID = S1.HOME_PORT_ID);

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

The statement will execute successfully as intended.

Review the given PORTS and SHIPS tables and the SQL code:

Figure A

SELECT PORT_NAME 
FROM   PORTS P 
WHERE  PORT_ID IN (SELECT HOME_PORT_ID, SHIP_NAME 
                   FROM   SHIPS 
                   WHERE  SHIP_ID IN (1,2,3));

Which of the following is true of this statement?

The statement will fail with a syntax error because of line 3.

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

A single-row subquery can also be a multiple-column subquery.

A correlated subquery can also be a single-row subquery.

Review the PORTS and SHIPS tables:

Figure A

Next, review the following SQL code:

SELECT P.COUNTRY, P.CAPACITY 
FROM   PORTS P 
WHERE  P.PORT_ID > (SELECT S.HOME_PORT_ID 
                    FROM SHIPS S WHERE S.LENGTH > 900);

You know that there are five rows in the SHIPS table with a length greater than 900. What will result from an attempt to execute this SQL statement?

An execution error will result because the subquery will return more than one row and the parent query is expecting only one row from the subquery.

Which of the following problems can be solved with a subquery? (Choose the two best answers.)

You are tasked with determining which divisions in a corporation earned sales last year that were less than the average sales for all divisions in the prior year.

You are tasked with creating a view.

Review the following illustration and SQL code:

Figure A

UPDATE PORTS P 
SET    CAPACITY = CAPACITY + 1 
WHERE  EXISTS (SELECT * 
               FROM SHIPS 
               WHERE HOME_PORT_ID = P.PORT_ID);

The PORTS table has 15 rows. The SHIPS table has 20 rows. Each row in PORTS has a unique value for PORT_ID. Each PORT_ID value is represented in the HOME_PORT_ID column of at least one row of the SHIPS table. What can be said of this UPDATE statement?

The value for CAPACITY will increase once for each of the 15 rows in the PORTS table.

Lesson-10 Quiz

Review this code:

Copy 

DROP SEQUENCE PROJ_ID_SEQ#;
CREATE SEQUENCE PROJ_ID_SEQ# START WITH 1 INCREMENT BY 2;
SELECT PROJ_ID_SEQ#.CURRVAL FROM DUAL;

What will result from these SQL statements?

The SELECT statement will fail because you cannot reference the CURRVAL pseudocolumn of a sequence until after you have referenced NEXTVAL for the sequence in a session.

Review the following illustration:

Figure A

Now review the following SQL code:

CREATE OR REPLACE VIEW PROJECTS_ROLLUP AS
       SELECT SHIP_NAME, CAPACITY,
              COUNT(PROJECT_ID) NUM_PROJECTS, ROUND(SUM(DAYS)) TOTAL_DAYS
       FROM   SHIPS A JOIN PROJECTS B
       ON     A.SHIP_ID = B.SHIP_ID
       GROUP BY SHIP_NAME, CAPACITY;

What can be said of this code?

After the view is created, a valid SELECT statement will work on the PROJECTS_ROLLUP view, but an INSERT will not.

Review the following series of SQL statements:

CREATE TABLE SUPPLIES_01
(  SUPPLY_ID NUMBER(7),
   SUPPLIER  VARCHAR2(30),
   ACCT_NO   VARCHAR2(50));
CREATE INDEX IX_SU_01 ON SUPPLIES_01(ACCT_NO);
DROP TABLE SUPPLIES_01;
CREATE TABLE SUPPLIES_02
(  SUPPLY_ID NUMBER(7),
   SUPPLIER  VARCHAR2(30),
   ACCT_NO   VARCHAR2(50));
CREATE INDEX IX_SU_02 ON SUPPLIES_02(ACCT_NO,SUPPLIER);

Assuming there are no objects already in existence named SUPPLIES_01 or SUPPLIES_02 prior to the execution of the preceding statements, what database objects will result from these statements?

A table called SUPPLIES_02 and an index called IX_SU_02

The database object that stores lookup information to speed up querying in tables is:

INDEX

An invisible index is an index on one or more columns in a table:

And is updated for any DELETE statements performed on the table

Which of the following keywords cannot be used with the CREATE SEQUENCE statement?

JOIN

Choose the best answer from the choices below. An index:

May improve the performance of an UPDATE statement that uses a WHERE clause, if the WHERE clause performs an equality comparison on an indexed column in a table

A SEQUENCE is

None of these.

Review the following illustration:

Figure A

Now review the following SQL code:

CREATE OR REPLACE VIEW SHIP_CAP_PROJ AS
SELECT   SHIP_ID,
         TO_CHAR(CAPACITY,'999,999'),
         PROJECT_COST
FROM     SHIPS JOIN PROJECTS
USING    (SHIP_ID)
WHERE    (PROJECT_COST * 2) < 100000;

What will result from an attempt to execute this SQL code?

The statement will fail because of an error in line 3.

Review the following SQL code:

CREATE TABLE PO_BOXES (PO_BOX_ID NUMBER(3), PO_BOX_NUMBER VARCHAR2(10))
             ENABLE ROW MOVEMENT;
INSERT INTO PO_BOXES VALUES (1, 'A100');
INSERT INTO PO_BOXES VALUES (2, 'B100');
COMMIT;
DROP TABLE PO_BOXES;
COMMIT;
PURGE TABLE PO_BOXES;
COMMIT;

What statement will recover the PO_BOXES table after these statements are executed?

None of these—the table cannot be recovered.

Review this code:

DROP   TABLE SHIPS CASCADE CONSTRAINTS;
DROP   SEQUENCE PROJ_ID_SEQ#;
CREATE TABLE SHIPS (SHIP_ID NUMBER PRIMARY KEY,
                    LENGTH NUMBER);
CREATE SEQUENCE PROJ_ID_SEQ# START WITH 1 INCREMENT BY 4;
INSERT INTO SHIPS (SHIP_ID, LENGTH) VALUES (PROJ_ID_SEQ#.NEXTVAL, 'NOT A NUMBER');
INSERT INTO SHIPS (SHIP_ID, LENGTH) VALUES (PROJ_ID_SEQ#.NEXTVAL, 750);
COMMIT;

Note that the first INSERT statement is attempting to enter a string literal of 'NOT A NUMBER' into a column declared with a numeric data type. Given that, what will be the result of these SQL statements?

One row added to the SHIPS table, with a SHIP_ID value of 5.

Review the following illustration:

Figure A

Now review the following SQL code:

CREATE OR REPLACE VIEW MAJOR_PROJECTS AS
SELECT PROJECT_ID, SHIP_ID, PROJECT_NAME, PROJECT_COST
FROM   PROJECTS
WHERE  PROJECT_COST > 10000;

INSERT INTO MAJOR_PROJECTS
(PROJECT_ID, SHIP_ID, PROJECT_NAME, PROJECT_COST)
VALUES
((SELECT MAX(PROJECT_ID)+1 FROM PROJECTS),
(SELECT MAX(SHIP_ID) FROM SHIPS),
'Small Project',
500);

What will result from an attempt to execute these two SQL statements?

The CREATE and INSERT statements will successfully execute.

Which of the following SQL statements can always be executed on any VIEW object?

SELECT

All database data is stored in:

TABLES

Review the following SQL code:

DROP   TABLE PO_BOXES;
CREATE TABLE PO_BOXES (PO_BOX_ID NUMBER(3), PO_BOX_NUMBER VARCHAR2(10))
               ENABLE ROW MOVEMENT;
INSERT INTO PO_BOXES VALUES (1, 'A100');
INSERT INTO PO_BOXES VALUES (2, 'B100');
COMMIT;
EXECUTE DBMS_LOCK.SLEEP(30);
DELETE FROM PO_BOXES;
COMMIT;
EXECUTE DBMS_LOCK.SLEEP(30);

Which of the following statements could be added as line 11 and recover the deleted rows from the PO_BOXES table?

FLASHBACK TABLE PO_BOXES TO TIMESTAMP SYSTIMESTAMP—INTERVAL '0 00:00:45' DAY TO SECOND;

Lesson-11 Quiz

Which of the following statements about set operators is true? Choose the best answer.

You can connect two SELECT statements with one set operator.

You are tasked with cleaning up a database application. There are two tables in the database: ORDERS contains completed ORDERS, and ORDER_RETURNS contains duplicate information for all ORDERS that were later returned. Your goal is to find out whether any rows in ORDER_RETURNS exist that were never in the ORDERS table to begin with. Which of the following set operators should you use?

MINUS

Review the following illustrations, as well as the ONLINE_SUBSCRIBERS table in Figure 11-3.

Figure A

Figure B

Next, review the following SQL code:

SELECT   A.SUB_DATE, COUNT(*)
  FROM     ONLINE_SUBSCRIBERS A JOIN
                (SELECT LAST_ORDER, PRODUCT FROM STORE_INVENTORY
                 UNION
                 SELECT ADDED, ITEM_NAME FROM FURNISHINGS) B
  ON       A.SUB_DATE = B.LAST_ORDER
  GROUP BY A.SUB_DATE;

Which of the following are true about this SQL statement? (Choose two.)

The statement is syntactically correct and will execute successfully

The B.LAST_ORDER reference at the end of line 6 refers to data included in the ADDED column referred to in line 5.

When combining two SELECT statements, which of the following set operators will produce a different result, depending on which SELECT statement precedes or follows the operator?

MINUS

The ORDER BY clause can be included in a SELECT with set operators if:

It follows the final SELECT statement.

Review the following illustrations:

DescribeListen

Figure A

Figure B

Next, review the following SQL code:

(  SELECT PRODUCT FROM STORE_INVENTORY
     UNION ALL
     SELECT ITEM_NAME FROM FURNISHINGS
  )
  INTERSECT
  (  SELECT ITEM_NAME FROM FURNISHINGS WHERE ITEM_NAME = 'Towel'
     UNION ALL
     SELECT ITEM_NAME FROM FURNISHINGS WHERE ITEM_NAME = 'Towel'
  );

How many rows will result from this code?

1

Review the following illustrations, as well as the ONLINE_SUBSCRIBERS table in Figure 11-3.

Figure A

Figure B

Next, review the following SQL code:

SELECT  (SELECT LAST_ORDER FROM STORE_INVENTORY
  UNION
           SELECT ADDED "Date Added" FROM FURNISHINGS)
  FROM     ONLINE_SUBSCRIBERS
  ORDER BY 1;

What will happen when this SQL statement is executed?

It will fail with an execution error on line 1.

Review the following illustrations:

Figure A

Figure B

Next, review the following SQL code:

SELECT '--', SECTION
  FROM   FURNISHINGS
  WHERE  CAT# NOT IN (1,2)
  UNION ALL
  SELECT TO_CHAR(LAST_ORDER,'Month'), AISLE
  FROM   STORE_INVENTORY;

How many rows will result from this query?

4

Review the following illustrations:

DescribeListen

Figure A

Figure B

Next, review the following SQL code:

SELECT '--' "Order Date", SECTION
  FROM   FURNISHINGS
  WHERE  CAT# NOT IN (1,2)
  UNION ALL
  SELECT TO_CHAR(LAST_ORDER,'Month') "Last Order", AISLE
  FROM   STORE_INVENTORY;

Which of the following are valid ORDER BY clauses for this query? (Choose two.)

ORDER BY 1

ORDER BY SECTION

Review the following illustrations:

Figure A

Figure B

Next, review the following SQL code:

SELECT   TO_CHAR(A.LAST_ORDER,'RRRR-MM-DD')
  FROM     STORE_INVENTORY A
  ORDER BY 1
  UNION
  SELECT   ADDED
  FROM FURNISHINGS;

What will result from an attempt to execute this SQL statement?

It will fail with a syntax error on line 3 because you cannot use an ORDER BY in this context.

Review the following illustrations, as well as the ONLINE_SUBSCRIBERS table in Figure 11-3.

Figure A

Figure B

Next, review the following SQL code:

SELECT   A.SUB_DATE, COUNT(*)
FROM     ONLINE_SUBSCRIBERS A JOIN
             (SELECT LAST_ORDER, PRODUCT FROM STORE_INVENTORY
              UNION
              SELECT ADDED, ITEM_NAME FROM FURNISHINGS) B
ON       A.SUB_DATE = B.LAST_ORDER
GROUP BY A.SUB_DATE;

Where can you add an ORDER BY to this code? (Choose two.)

After line 7

At the end of line 5 before the parenthesis

Review the following illustrations:

DescribeListen

Figure A

Figure B

Next, review the following SQL code:

SELECT NUM, PRODUCT FROM STORE_INVENTORY
  INTERSECT
  SELECT CAT#, ITEM_NAME   FROM FURNISHINGS;

How many rows will result from this query?

0

Review the following illustrations, as well as the ONLINE_SUBSCRIBERS table in Figure 11-3.

Figure A

Figure B

Next, review the following SQL code:

SELECT  (SELECT PRODUCT FROM STORE_INVENTORY
           INTERSECT
           SELECT ITEM_NAME FROM FURNISHINGS)
  FROM     ONLINE_SUBSCRIBERS;

What will happen when this SQL statement is executed?

It will execute and repeat the value 'Towel' for each row of the ONLINE_SUBSCRIBERS table.

The set operators do not include which one of the following keywords?

SET

Review the following illustrations, as well as the ONLINE_SUBSCRIBERS table in Figure 11-3.

Figure A

Figure B

Next, review the following SQL code:

SELECT COUNT(*)
  FROM   ONLINE_SUBSCRIBERS
  WHERE  SUB_DATE IN
            (SELECT LAST_ORDER FROM STORE_INVENTORY
             UNION
             SELECT ADDED      FROM FURNISHINGS);

What will happen when this SQL statement is executed?

It will execute successfully.

Lesson-12Quiz

You are tasked to work with a view. The view's underlying table has been altered. What information can the data dictionary provide at this point? (Choose all correct answers.)

Choose all correct

Which of the following actions will not cause the contents of the data dictionary to be changed in some way?

None of these

The USER_CONSTRAINTS view in the data dictionary lists FOREIGN KEY constraints in the CONSTRAINT_TYPE column with which of the following single-letter abbreviations?

R

The data dictionary is owned by:

SYS

Now you have changed the purpose of the PIER column in the MARINA table and want to remove the comment you just created in the previous question. Which of the following statements will remove the comment?

COMMENT ON COLUMN MARINA.PIER IS '';

When you're looking for a particular bit of data and you're not sure where in the data dictionary it might be, a good starting point is: (Choose the best answer.)

SELECT * FROM DICTIONARY;

Which of the following data dictionary views does not have an OWNER column?

USER_TABLES

One place to get a master list of all the views that form the data dictionary is:

DICTIONARY

You need to get information about columns in a table you do not own, nor do you have privileges to it. Which view can you query to get this information?

DBA_TAB_COLUMNS

You are tasked with querying the data dictionary view that lists only those sequences to which you currently have privileges but don't necessarily own. To do this, you log in to your own user account and query the data dictionary view called:

ALL_SEQUENCES

If an ALTER TABLE ... DROP COLUMN statement is executed against an underlying table upon which a view is based, the status of that view in the data dictionary changes to:

INVALID

The term metadata means:

Data about data

You can add your own comments to the data dictionary with the COMMENT statement using which of the following? (Choose two.)

TABLE

COLUMN

You are tasked with the job of adding a comment to the data dictionary to accompany the column PIER in the table MARINA. Which of the following will execute successfully?

COMMENT ON COLUMN MARINA.PIER IS 'Number of piers';

Which among the following is considered an acceptable query with V$DATAFILE?

A query that displays rows from the table with no joins

Lesson-13Quiz

no bookmarked, confident, or note marked

Question 1 :Review the following diagrams:

Figure A

Figure B

Next, examine the following statement:

Copy 

INSERT
 WHEN (PART_NO < 500) THEN
   INTO STORE_INVENTORY (NUM, PRODUCT)
    VALUES (SPARE_ID, PART_NAME)
   INTO PORT_INVENTORY (NUM, PRODUCT)
    VALUES (SPARE_ID, PART_NAME)
 WHEN (PART_NO >= 500) THEN
    INTO SHIP_INVENTORY (NUM, PRODUCT)
    VALUES (SPARE_ID, PART_NAME)
 SELECT SPARE_ID, PART_NO, PART_NAME
 FROM   SPARES;

Which of the following statements is true for this SQL statement?

Regardless of whether the first WHEN condition is true, the second WHEN condition will be evaluated.

A multitable INSERT statement:

Can use conditional logic

Review the following SQL statement:

INSERT ALL
 WHEN (SUBSTR(PART_NAME,1,4) = 'MED-') THEN
    INTO STORE_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER)
    VALUES (SPARE_ID, 'Back', PART_NAME, SYSDATE)
    INTO SHIP_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER)
    VALUES (SPARE_ID, 'Back', PART_NAME, SYSDATE)
 WHEN (SUBSTR(PART_NAME,1,4) = 'ARR-') THEN
    INTO PORT_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER)
    VALUES (SPARE_ID, 'Back', PART_NAME, SYSDATE)
SELECT SPARE_ID, PART_NO, PART_NAME
FROM   SPARES;

If one of the INTO clauses executed on a table and resulted in a constraint violation on that table, what would result?

The row would not be inserted, the INSERT statement would stop, and all rows affected by the INSERT statement would be rolled back, as if the INSERT statement had never been executed.

What can an INSERT statement do? (Choose two.)

Add data into more than one column in a table

Add rows into more than one table

Review the following diagrams:

Figure A

Figure B

You want to merge rows from the PORT_INVENTORY table into the SHIP_INVENTORY table. You start with the following SQL statement:

MERGE INTO SHIP_INVENTORY A
USING PORT_INVENTORY B
ON (A.NUM = B.NUM)
WHEN NOT MATCHED THEN INSERT
   (A.NUM, A.AISLE, A.PRODUCT, A.LAST_ORDER)
   VALUES
   (B.NUM, B.AISLE, B.PRODUCT, B.LAST_ORDER)
WHERE TO_CHAR(A.LAST_ORDER,'RRRR') = '2019';

What will this SQL statement do?

It will fail with a syntax error because you cannot reference the target table (SHIP_INVENTORY) in the WHERE clause in line 8.

Review the following diagrams of the SPARES table:

Figure A

Also examine the diagrams of the tables PORT_INVENTORY, STORE_INVENTORY, and SHIP_INVENTORY, shown here.

DescribeListen

Figure B

Now consider the following SQL statement:

INSERT ALL
 WHEN (SUBSTR(PART_NAME,1,4) = 'MED-') THEN
    INTO STORE_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER)
    VALUES (SPARE_ID, 'Back', PART_NAME, SYSDATE)
    INTO SHIP_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER)
    VALUES (SPARE_ID, 'Back', PART_NAME, SYSDATE)
 WHEN (SUBSTR(PART_NAME,1,4) = 'ARR-') THEN
    INTO PORT_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER)
    VALUES (SPARE_ID, 'Back', PART_NAME, SYSDATE)
SELECT SPARE_ID, PART_NO, PART_NAME
FROM   SPARES;

Regarding this SQL statement, which of the following statements is true?

The statement will add a row returned from the SPARES table to the SHIP_INVENTORY table only if the WHEN condition on line 2 evaluates to true.

Review the following diagrams:

Figure A

Figure B

Next, consider the following SQL statement:

INSERT FIRST
 WHEN (SUBSTR(PART_NAME,5,3) = 'OPS') THEN
   INTO STORE_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER)
    VALUES (SEQ_NUM.NEXTVAL, 'Back', PART_NAME, SYSDATE)
 WHEN (SUBSTR(PART_NAME,1,4) = 'PAN-') THEN
    INTO SHIP_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER)
    VALUES (SEQ_SHIP_NUM.NEXTVAL, 'Back', PART_NAME, SYSDATE)
 ELSE
    INTO PORT_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER)
    VALUES (SEQ_PORT_NUM.NEXTVAL, 'Back', PART_NAME, SYSDATE)
 SELECT SPARE_ID, PART_NO, PART_NAME
 FROM   SPARES
 WHERE  LENGTH(PART_NO) > 2;

Which one of the following answers correctly identifies data that, if present in the SPARES table, will be inserted by this conditional INSERT statement into the table—or tables—identified by the answer?

PART_NO = 170; PART_NAME = 'TRA-OPS,' in STORE_INVENTORY

Review the following SQL syntax:

MERGE INTO SHIP_INVENTORY A
USING PORT_INVENTORY B
ON (A.NUM = B.NUM)
WHEN NOT MATCHED THEN INSERT
   (A.NUM, A.AISLE, A.PRODUCT, A.LAST_ORDER)
   VALUES
   (B.NUM, B.AISLE, B.PRODUCT, B.LAST_ORDER)
WHERE TO_CHAR(A.LAST_ORDER,'RRRR') = '2019';

Which of the following two alternatives for line 3 are syntactically correct?

OPTION 1:  ON (A.NUM = B.NUM AND A.AISLE = B.AISLE)
OPTION 2:  ON (A.LAST_ORDER < B.LAST_ORDER)

Both option 1 and option 2

The MERGE statement includes a USING clause. Which of the following statements is not true of the USING clause?

The USING clause is optional.

Which of the following statements is false?

It is possible to merge into two or more tables.

Lesson-14Quiz

Assume a database with three valid users: NEIL, BUZZ, and MICHAEL. Assume all users have the appropriate privileges they require to perform the tasks shown here. Assume NEIL owns a table called PROVISIONS. Examine the following code (assume all password references are valid):

Copy 

CONNECT NEIL/neilPassword
GRANT SELECT ON PROVISIONS TO BUZZ, MICHAEL;

CONNECT BUZZ/buzzPassword
CREATE VIEW PROVISIONS AS SELECT * FROM NEIL.PROVISIONS;
GRANT SELECT ON PROVISIONS TO MICHAEL;
CREATE PUBLIC SYNONYM PROVISIONS FOR BUZZ.PROVISIONS;

CONNECT MICHAEL/michaelPassword
CREATE SYNONYM PROVISIONS FOR NEIL.PROVISIONS;
SELECT * FROM PROVISIONS;

What object is identified in line 11 by the name PROVISIONS?

The synonym created in line 10

Your user account owns an updatable view, BACKLOG, which is based on the table PROJECTS. You are tasked to give SELECT and UPDATE capabilities to another user account named MARINO. Currently, MARINO has no privileges on either the table or the view. You want for MARINO to have the ability to grant SELECT on the view to other users as well. Examine the following SQL code:

GRANT SELECT ON BACKLOG TO MARINO WITH GRANT OPTION;
GRANT UPDATE ON BACKLOG TO MARINO;

Which of the following statements is true?

The statements will execute successfully and perform as intended.

You are logged in to user account FRED and have been tasked with granting privileges to the user account ETHEL. You execute the following SQL statements:

GRANT CREATE ANY TABLE TO ETHEL WITH ADMIN OPTION;
REVOKE CREATE ANY TABLE FROM ETHEL;

Assuming both statements execute successfully, what is the result?

ETHEL does not have the system privilege CREATE ANY TABLE or the right to grant the CREATE ANY TABLE system privilege to any other user.

Which of the following is the system privilege that empowers the grantee to create an index in his or her own user account but not in the accounts of others?

CREATE TABLE

Which of the following is the system privilege that is required as a minimum to allow a user account to log in to the database?

CREATE SESSION

Which of the following SQL statements will authorize the user account JESSE to create tables in each and every user account in the database?

GRANT CREATE ANY TABLE TO JESSE;

Which of the following statements will grant the role OMBUDSMAN to user JOSHUA in such a way that JOSHUA may grant the role to another user?

GRANT OMBUDSMAN TO JOSHUA WITH ADMIN OPTION;

User account MUSKIE owns a table called CBAY. Which of the following statements can be executed by MUSKIE and enable user ONEILL to execute UPDATE statements on the CBAY table? (Choose two.)

GRANT INSERT, UPDATE ON CBAY TO ONEILL;

GRANT ALL ON CBAY TO ONEILL;

User HARDING owns a table TEAPOT. User HARDING then executes the following SQL statements to give access to the table to user ALBERT:

CREATE PUBLIC SYNONYM TEAPOT FOR HARDING.TEAPOT;
CREATE ROLE DOME;
GRANT DOME TO ALBERT;
GRANT SELECT ON TEAPOT TO DOME;

Which of the following statements can user ALBERT now execute on the TEAPOT table?

SELECT * FROM HARDING.TEAPOT;

What can be granted to a role? (Choose all that apply.)

None of these

Examine the following two claims about the DBA_TAB_PRIVS data dictionary view:

1. Lists system privileges granted to a current user
2. Describes all object grants in the database

Which of these claims is true?

Only 2

Your user account owns a table BACK_ORDERS, and you want to grant privileges on the table to a user account named CARUSO, which already has the system privileges CREATE SESSION and UNLIMITED TABLESPACE. Examine the following SQL statement:

GRANT SELECT ON BACK_ORDERS TO CARUSO;

Once this statement has been executed, which of the following statements will be true for user CARUSO?

CARUSO will have SELECT privileges on BACK_ORDERS but not the ability to give other users SELECT privileges on BACK_ORDERS.

Which of the following data dictionary views contains information about grants on tables that have been made by other users to your user account, as well as grants on tables that have been made by your user account to other user accounts?

USER_TAB_PRIVS

A role:

Can be created by a user only if that user has the CREATE ROLE system privilege

You have a table FURNISHINGS and are told to grant DELETE privileges on the table to user HEARST. Examine the following SQL statements:

GRANT DELETE ON FURNISHINGS TO HEARST;
CREATE ROLE MGR;
GRANT DELETE ON FURNISHINGS TO MGR;
GRANT MGR TO HEARST;

Now you are told to change the privileges given to HEARST so that HEARST can no longer execute DELETE statements on the FURNISHINGS table. Which of the following will accomplish the goal? (Choose the best answer.)

REVOKE DELETE ON FURNISHINGS FROM HEARST, MGR;

    Final Test

Review the following exhibit:

Table: teachers

Class	teacher_name	student_count
1	Franks	20
2	SimMs	20
3	AddAms	30
4	frEEmaN	15
5	YouNG	30

Which of the following queries will correctly return the row for all teachers who's name starts with an f, regardless of case?

Select teacher_name from teachers where upper(teacher_name) like 'F%';

Review the structure/data contained in two tables. The first table is called people and the second table is called people_history. The structure of both tables is the same as seen here:

people_num	number	primary_key
Record_add_date	Date	Not null
People_history_date	Date

Which of the following queries will return all rows from both tables where the people_history_date is in the year 2017?

select * from people where to_char(people_history_date, 'yyyy')='2017'
union
select * from people_history where to_char(people_history_date, 'yyyy')='2017';

Which of the following statements are true of a correlated subquery?

It performs column-by-column analysis in cooperation with the parent query.

Which data dictionary view would you query if you wanted to find the column that are contained in a database view?

DBA_TAB_COLS

no bookmarked, confident, or note marked

Question 5 :Review the following exhibit:

Table Name: USER_PAGE_COUNTS

user_id	Action_Date	page_count
1	12/01/2016	5
2	12/01/2016	50
3	12/01/2016	100
4	12/01/2016	70
1	12/02/2016	12
2	12/02/2016	34
3	12/02/2016	65
2	12/03/2016	26
4	12/03/2016	43
3	12/04/2016	65
2	12/04/2016	26
4	12/04/2016	43
1	12/05/2016	175

Which SQL statement will provide the maximum page count for each user in the user_page_counts table?

select user_id, max(page_count) max_page_count
from user_page_counts
group by user_id;

When writing a SQL statement, which clause can be used to create a temporary result set?

with

Which of the following functions returns a number value?

INSTR

You run the following query from the SQL prompt:

SQL> select sysdate from dual;

and get the following results:

SYSDATE
---------
13-NOV-17 

You want the output to look like the following:

SYSDATE
-------------------
11/13/2017 13:09:10 

Which of the following SQL statements will create the output you desire?

select to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') "SYSDATE" from dual;

Which of the following SQL statements will successfully create a table?

Create table employees

( emp_num         number primary key,

 Emp_last_name    varchar2(30),

 
 Emp_first_name   varchar2(30),

 
 Stores_store_num number,

 Dept_dept_num    number,

 Emp_supervisor   number)

Tablespace user_data;

Which of the following statements are true with regards to a multitable insert? (Choose two.)

A multitable insert statement can conditionally insert data into multiple tables.

A multitable insert statement can replace and perform better than PL/SQL routines

no bookmarked, confident, or note marked

Question 11 :Review the following exhibit:
create index ix_emp on scott.emp(job) invisible tablespace index;
Which of the following statements is true regarding the create index statement above?

The DDL will successfully create an invisible index, which will be ignored by the optimizer by default.

When issuing a SQL statement, if you wish to sort the rows in a specific order which of the following clauses would you use?

Order by

Which of the following statements is true about DDL statements in Oracle (choose all that apply)

DDL statements can not be rolled back.

The create table statement is one type of DDL statement.

Which of the following are valid types of constraints that can be defined when creating a table? (select all that apply)

Unique

Primary key

Which of the following are types of subqueries? (select all that apply)

Single row

Multiple row

Correlated

no bookmarked, confident, or note marked

Question 16 :

Which of the following options does not describe the purpose of a set operation?

Each correct answer represents a complete solution. Choose all that apply.

Set operators can only include result sets with number and varchar2 datatypes.

Long data types are supported by all set operations

Which of the following is used to include or exclude rows from a query that involves a grouping function?

having

Which of the following SQL statements will display all records in the EMP table as they looked at noon on 11/3/2017?

select * from scott.emp as of timestamp to_timestamp('11/03/2017 12:00:00','mm/dd/yyyy hh24:mi:ss');

Which of the following statements will create an index that the Oracle optimizer will ignore, by default, when parsing a SQL statement?

create index ix_emp on employee(hiredate) tablespace users invisible;

Review the following exhibit:

SQL> desc scott.emp
Name                                      Null?    Type
----------------------------------------- -------- -------------
EMPLOYEE_ID                                        NUMBER
EMPLOYEE_NAME                                      VARCHAR2(61)
DEPARTMENT_NAME                                    VARCHAR2(30)
HIRE_DATE                                          VARCHAR2(10)
COMMISSION                                         NUMBER
HIREDATE                                  NOT NULL DATE 

Which of the following SQL statements will be successful?

insert into emp (employee_id, employee_name, hiredate) values (1,'Freeman', sysdate);

Which of the following statements is a DML statement? (select all that apply)

delete from emp where em_id=5;

insert into emp values (1,'Freeman');

Which of the following is not a type of function available in Oracle Database?

Regression functions

Which of the following update statements will result in an error?

Update employees
Set date=01/01/2017;

Which of the following is a valid create table command?

Create table my_table(table_number number);

no bookmarked, confident, or note marked

Question 25 :Which of the following SQL statements will request input from the user?

select *from emp where empno=&1;

Examine the data in the SALES table below:

Sale_number	store_number	sales_date	employee_number	item_number	quantity_sold
1	1	01/02/2017	1234	102	1
1	1	01/02/2017	1234	115	2
2	1	01/02/2017	1500	122	1
2	1	01/02/2017	1500	101	3
3	2	01/02/2017	176	22	2
4	2	01/03/2017	199	43	4
5	1	01/03/2017	1234	108	6
5	1	01/03/2017	1234	102	2
6	4	01/03/2017	1500	300	1
1	1	01/02/2017	1235	114	1
1	1	01/02/2017	1235	118	1

Which query will sort the results first by store_number, sale_number, and sales_date, and then return the first six sorted rows?

select *
from
(select *
from sales
order by store_number, sale_number, sales_date
)
Where rownum < 7;

Which of the following will insert rows in the employee table into the employee_history table with a term_date less than or equal to today's date minus 365 days?

Insert into employee_history select * from employee where term_date <=sysdate-365;

You have a new set of data to load from a staging table into a master table. Some of the new data contains new records to be added to the master table, some of the data contains records to be updated in the master table and there are also records to be removed from the master table.

Which of the following ways of is the most efficient way of updating the master table from the staging table?

You should load the new data into a staging table and then use the merge SQL command to load it into the master table.

Review the following exhibit:

SQL> desc scott.emp
Name Null? Type
----------------------------------------- -------- -----------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> desc scott.dept
Name Null? Type
----------------------------------------- -------- --------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

Assume that there is a row in DEPT that does not have an associated record in EMP. Which of the following it true related to such a query?

You will be able to see the department rows that don't have matching emp rows through the use of an outer join.

no bookmarked, confident, or note marked

Question 30 :Review the following figure:

DescribeListen

Figure A

Data in Employees table
Emp_num	emp_last_name	emp_first_name	stores_store_num	dept_dept_num	emp_supervisor
1	Freeman	Robert	1	1	NULL
2	Freeman	Carrie	1	2	NULL
3	Freeman	Amy	2	3	NULL
4	Freeman	Abbie	2	4	NULL

Data in Stores
Store_num	Store_description
1	Oklahoma City
2	Tulsa
3	Dallas

Which of the following SQL statements will fail?

delete from stores where store_num=2;

Which of the following is not an Oracle schema object type?

Transactions

Examine the data in the CLASS table below:

Class	teacher_number	student_count
1	1	20
2	2	20
3	3	30
4	4	15
5	1	30
6	2	35
7	3	22
8	4	14
9	1	30
10	1	22
11	2	30

Which of the following delete statements will remove all of the classes with teachers less than teacher number 4?

delete from class where teacher_number < 4;

Which of the following functions might be used along with a group by clause? (Select all that apply)

distinct

Review the following exhibit:

SQL> select sysdate from dual;

SYSDATE
---------
13-NOV-17


SQL> desc scott.emp
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

and review the following SQL:

SQL> insert into scott.emp (empno, hiredate) 
values (100,'20-JAN-2017');

Which of the following statements is true about this SQL?

It might be successful if the database date format mask matched that of the date being inserted because of an implicit conversion.

no bookmarked, confident, or note marked

Question 35 :Assume you issue the following command:

delete from payroll where payroll_date>sysdate-1;

What command would reverse the results of this delete?

rollback;

Which of the following functions will take a character string as an argument and then return that character string in a modified form?

All of these

Which of the following statements is true about a self join? (choose all that apply)

option C

A self join will not return duplicate rows.

no bookmarked, confident, or note marked

Question 38 :Examine the data in the CLASS table below:

Class	teacher_number	student_count
1	1	20
2	2	20
3	3	30
4	4	15
5	1	30
6	2	35
7	3	22
8	4	14
9	1	30
10	1	22
11	2	30

Which SQL statement would increase the number of students for class number 3?

update class 
set student_count=student_count+1
where class=3;

no bookmarked, confident, or note marked

Question 39 :You have just created a user called bsmith. Which command would you execute to grant a privilege that will allow the bsmith account to connect to the database?

Grant create session to bsmith;

Which of the following will correctly format the output of the data column HIRE_DATE column to look like the following in the output: 01/01/2017 14:00:00?

select to_char(hire_date, 'mm/dd/yyyy hh24:mi:ss') from emp;

no bookmarked, confident, or note marked

Question 41 :In which of the following SQL statements might you find a subquery?

All of these

Which of the following DML operations are available when using a merge command (choose all that apply)?

Delete

Insert

option D

Update

Consider this exhibit:

select b.ename, b.sal, a.dname
from scott.dept a, scott.emp b
where a.deptno=b.deptno (+)
order by 1,2;

Which of the following statements is true concerning the SQL statement in the exhibit? (Choose two.)

If an employee is not assigned to a department, they will not appear in the output.

If a department is not assigned to an employee, it will appear in the output.

Which of the following is representative of an analytic function?

avg(sales_amount)
over (partition by sales_date order by sales_date) average_sale_by_date

Review the following tables:

SQL> desc scott.emp
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> desc scott.dept
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

Which of the following SQL statements will return the department name (contained in the DEPT.DNAME column) and the average salary (contained in the EMP.SAL column) for that department?

select a.dname, avg(b.sal) from scott.dept a, scott.emp b where a.deptno=b.deptno group by a.dname;

Which of the following would not be a case for the use of a group (or aggregate) function?

Finding all the first names of persons who's last name ended in SMITH and who lived on the west coast.

Examine the data in the SALES table below:

Sale_number	store_number	sales_date	employee_number	item_number	quantity_sold
1	1	01/02/2017	1234	102	1
1	2	01/02/2017	1234	115	2
2	3	01/02/2017	1500	122	1
2	4	01/02/2017	1500	115	3
3	5	01/02/2017	176	22	2
4	1	01/03/2017	199	43	4
5	2	01/03/2017	1234	43	6
5	3	01/03/2017	1234	102	2
6	4	01/03/2017	1500	300	1

Which of the following SQL statements will return the store number and sales date for records in the sales table with a sales_date of 1/2/2017 and for store 1 and store 2? The sales_date should be returned in the form of date and time in the 24 hour format.

Select store_number, to_char(sales_date, 'mm/dd/yyyy hh24:mi:ss') sales_date from sales where sales_date=to_date('01/02/2017','mm/dd/yyyy') and store_number in (1,2);

eview the following exhibit:

DescribeListen

Figure A

Which of the following SQL statements will create the following output:

STORE_DESCRIPTION EMP_LAST_NAME  EMP_FIRST_NAME DEPT_DESC
  ------------------ --------------- --------------- -----------------
  Oklahoma City   Freeman     Robert     MGT
  Oklahoma City   Freeman     Carrie     Asst. Mgr
  Oklahoma City   Freeman     Amy       Floor Supervisor
  Oklahoma City   Freeman     Abbie      Sales Assoc 

select c.store_description, a.emp_last_name, a.emp_first_name, b.dept_desc
from employees a, stores c, dept b
where a.stores_store_num=c.store_num
and a.dept_dept_num=b.dept_num;

no bookmarked, confident, or note marked

Question 49 :

Review the following exhibit:

DescribeListen

Figure A

What kind of join would be required if you want a list of all employees and the department they worked in, including employees that were not assigned to a department?

outer join

Which of the following SQL statements will correctly drop a column called id_2 in a table called id_table?

Alter table id_table drop column id_2;

Which if the following is a type of subquery (choose all that apply):

Scalar subquery

Nested subquery

Which of the following clauses or functions can limit the rows returned by a query? (Choose two.)

rownum

Fetch next rows

no bookmarked, confident, or note marked

Question 53 :Review the following exhibit:

SQL> select * from test;

        ID
----------
         1
         2
         3
         4
         5
         6

6 rows selected. 

Which of the following SQL queries will successfully return 3 records from the test table, starting at the second record and return the result set in the correct order?

Select * from test order by id offset 1 rows fetch next 3 rows only;

Which statement would limit the rowset of a sql statement to no more than 5 rows?

where rownum < 6

Which of the following SQL statements will correctly create a sequence?

Create sequence my_sequence start with 1 increment by -1;

Which of the following statements completes a transaction?

commit

Which of the following is not an analytic function? (choose all that apply)

All of these are analytic functions.

Which of the following is true with respect to using the flashback database command?

The database must be in ARCHIVELOG mode to use the flashback database command.

Use the alter database flashback on command to enable flashback database mode.

Which of the following operators is indicitive of an equijoin?

=

no bookmarked, confident, or note marked

Question 60 :Examine the data in the USER_BIRTHDATE table below:

User_last_name	user_first_name	user_birthdate
Smith	John	09/30/1960
Masters	Elane	07/15/1970
Fredricks	George	08/16/1977
Houston	Sam	01/30/1992
Flur	Candance	02/28/1989

Which option will return all the rows and columns of the table, renaming the user_birthdate column to birthdate?

select user_last_name, user_first_name, user_birthdate birthdate from user_birthdate;

Which of the following is true about a truncate statement (choose all that are correct):

A truncate statement includes an implicit commit.

A truncate statement is considered a DDL statement.

Which of the following is not a valid column datatypes that can be used when creating a table?

LOB

Which of the following statements is true?

scalar subquery is used to return one value, which can be used in lieu of a literal value.

Which of the following statements defines the possible use cases of a subquery? Select all that apply.

A subquery is used to define a set of rows that should be inserted into a table.

A subquery is used to define conditions in a where clause to be applied to the results returned by a select statement.

Which of the following functions can take a character data type as an argument (choose all that apply)

None of these

Which of the following functions are not conversion functions?

Substr

Review the following query:

select a.lname, a.dept_id 
from scott.emp a 
where a.salary > (select avg(z.sal) 
                  from employees z 
                  where z.dept_id=a.dept_id);

What kind of query is represented in the SQL statement above?

Correlated

Which of the following function will return a date when passed a character argument?

to_date

Review the following exhibit:

select 'true' from dual 
where exists (select null from dual);

Which of the following statements is true?

The query will return the value true.

no bookmarked, confident, or note marked

Question 70 :Review the exhibit for a description of the sales table.

Create table sales (sales_id number, sales_clerk number, sales_amount number);

Which if the following is a valid insert statement into the sales table?

insert into sales values (6,8,10);

Which statement is used to sort the rows returned by a SQL statement?

order by

Review the following output:

SQL> select * from scott.emp

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
       100                                 01/20/2017
      7369 SMITH      CLERK           7902 12/17/1980        800                    20
      7499 ALLEN      SALESMAN        7698 02/20/1981       1600        300         30
      7521 WARD       SALESMAN        7698 02/22/1981       1250        500         30
      7566 JONES      MANAGER         7839 04/02/1981       2975                    20
      7654 MARTIN     SALESMAN        7698 09/28/1981       1250       1400         30
      7698 BLAKE      MANAGER         7839 05/01/1981       2850                    30
      7782 CLARK      MANAGER         7839 06/09/1981       2450                    10
      7788 SCOTT      ANALYST         7566 04/19/1987       3000                    20
      7839 KING       PRESIDENT            11/17/1981       5000                    10
      7844 TURNER     SALESMAN        7698 09/08/1981       1500          0         30
      7876 ADAMS      CLERK           7788 05/23/1987       1100                    20
      7900 JAMES      CLERK           7698 12/03/1981        950                    30
      7902 FORD       ANALYST         7566 12/03/1981       3000                    20
      7934 MILLER     CLERK           7782 01/23/1982       1300                    10 

Which SQL statement below will return the average salary for each job in the job column, and order it with the highest average salary at the top of the ouptut?

select job, avg(sal) salary from scott.emp group by job order by 2 desc;

Which of the following represent DDL commands?

Create table

What SQL statement will both remove all data from a table as well as reset the high water mark of that table?

truncate

Which clause is used at the end of a SQL statement to aggregate data across multiple records and then group the results?

group by clause

You have a need to insert data from one table, into two different tables. Which is the most efficient way to perform this operation?

Use the Oracle insert all SQL command to insert the data into both tables as the same time.

Which of the following SQL statements will create a view on a table called employee, where the hire_date column is invisible?

create view vw_employee (empid, hd invisible) 
as select employee_id, hire_date from scott.employee;

no bookmarked, confident, or note marked

Question 78 :

Review the following exhibit:

Table Name: USER_PAGE_COUNTS

user_id	Action_Date	page_count
1	12/01/2016	5
2	12/01/2016	50
3	12/01/2016	100
4	12/01/2016	70
1	12/02/2016	12
2	12/02/2016	34
3	12/02/2016	65
2	12/03/2016	26
4	12/03/2016	43
3	12/04/2016	65
2	12/04/2016	26
4	12/04/2016	43
1	12/05/2016	175

Which of the following SQL statements will produce a report for all users and page counts who had over 100 page_counts on 12/05/2016?

select user_id, sum(page_count) page_counts
from user_page_counts
where action_date=to_date('12/05/2016','mm/dd/yyyy')

group by user_id 

having sum(page_count) > 100;