20 Questions

Oracle database sql Quiz

1 :-

Examine the description of the PROMOTIONS table:

name null type

promo_id not null number (6)

promo_name not null varchar2(30)

promo_category not null varchar2(30)

promo_cost not null number (10,2)

You want to display the unique promotion costs in each promotion category. Which two queries can be used? (Choose two.)

Select Distinct Promo_category || ' Has ' || Promo_cost As Costs From Promotions Order By 1; Select Distinct Promo_cost || ' In ' || Distinct Promo_category From Promotions Order By 1; Select Distinct Promo_category Promo_cost From Promotions Order By 1; Select Promo_category Distinct Promo_cost From Promotions Order By 2; Select Promo_cost Promo_category From Promotions Order By 1;

2 :-

Which three queries execute successfully? (Choose three.)

Select 1 - Sysdate - Date '2019-01-01' From Dual; Select Sysdate - Date '2019-01-01' - 1 From Dual; Select Sysdate / Date '2019-01-01' - 1 From Dual; Select Sysdate - 1 - Date '2019-01-01' From Dual; Select (sysdate - Date '2019-01-01') / 1 From Dual; Select 1 / Sysdate - Date '2019-01-01' From Dual;

3 :-

You create a table named 123. Which statement runs successfully?

Select * From Table(123); Select * From "123"; Select * From \'123\'; Select * From '123';

4 :-

Which three statements are true about single-row functions? (Choose three.)

They Return A Single Result Row Per Table. They Can Be Nested To Any Level. They Can Accept Only One Argument The Argument Can Be A Column Name Variable Literal Or An Expression. They Can Be Used Only In The Where Clause Of A Select Statement The Data Type Returned Can Be Different From The Data Type Of The Argument

5 :-

Examine the description of the PRODUCTS table:

name null type

product_id not null number(2)

product_name varchar2(10)

unit_price number(3)

surcharge varchar2(2)

expiry_date date

delivery_date date

Which three queries use valid expressions? (Choose three.)

6 :-

Which three statements are true about views in an Oracle Database? (Choose three.)

A Select Statement Cannot Contain A Where Clause When Querying A View Containing A Where Clause In Its Defining Query. B Views Have No Segment. Views Have No Object Number. Views Can Join Tables Only If They Belong To The Same Schema A View Can Be Created That Refers To A Non-existent Table In Its Defining Query. Rows Inserted Into A Table Using A View Are Retained In The Table If The View Is Dropped.

7 :-

Which two actions can you perform with object privileges? (Choose two.)

Create Roles. Create Foreign Key Constraints That Reference Tables In Other Schemas. Delete Rows From Tables In Any Schema Except Sys. Set Default And Temporary Tablespaces For A User. Execute A Procedure Or Function In Another Schema.

8 :-

Examine this SQL statement:

update orders 0

set customer _name=

(select cast _last _name

from customers

where customer_id = 0 customer_id);

Which two are true? (Choose two.)

All Existing Rows In The Orders Table Are Updated. The Subquery Is Executed Before The Update Statement Is Executed. The Subquery Is Not A Correlated Subquery. The Subquery Is Executed For Every Updated Row In The Orders Table. The Update Statement Executes Successfully Even If The Subquery Selects Multiple Rows.

9 :-

Examine this list of requirements for a sequence: 1. Name: EMP_SEQ 2. First value returned: 1 3. Duplicates are never permitted. 4. Provide values to be inserted into the EMPLOYEES.EMPLOYEE_ID column. 5. Reduce the chances of gaps in the values. Which two statements will satisfy these requirements? (Choose two.)

Create Sequence Emp_seq Start With 1 Increment By 1 Cycle; Create Sequence Emp_seq Start With 1 Increment By 1 Cache; Create Sequence Emp_seq; Create Sequence Emp_seq Start With 1 Increment By 1 Nocache; Create Sequence Emp_seq Nocache; Create Sequence Emp_seq Start With 1 Cache;

10 :-

Which two statements are true about the COUNT function? (Choose two.)

Count(*) Returns The Number Of Rows In A Table Including Duplicate Rows And Rows Containing Nulls In Any Column. It Can Only Be Used For Number Data Types. Count(distinct Inv_amt) Returns The Number Of Rows Excluding Rows Containing Duplicates And Nulls In The Inv_amt Column. Count(inv_amt) Returns The Number Of Rows In A Table Including Rows With Null In The Inv_amt Column A Select Statement Using The Count Function With A Distinct Keyword Cannot Have A Where Clause.

11 :-

Which three are true about dropping columns from a table? (Choose three.)

A Column Must Be Set As Unused Before It Is Dropped From A Table A Primary Key Column Cannot Be Dropped. Multiple Columns Can Be Dropped Simultaneously Using The Alter Table Command. A Column Can Be Removed Only If It Contains No Data. A Column That Is Referenced By Another Column In Any Other Table Cannot Be Dropped. A Column Drop Is Implicitly Committed.

12 :-

Which two statements are true about conditional INSERT ALL? (Choose two.)

Each Row Returned By The Subquery Can Be Inserted Into Only A Single Target Table A Single When Condition Can Be Used For Multiple Into Clauses. Each When Condition Is Tested For Each Row Returned By The Subquery It Cannot Have An Else Clause. The Total Number Of Rows Inserted Is Always Equal To The Number Of Rows Returned By The Subquery.

13 :-

The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which two queries execute successfully? (Choose two.)

Select Prod_id From Sales Where Quantity_sold > 55000 And Count(*) > 10 Group By Count(*) > 10; Select Prod_id From Sales Where Quantity_sold > 55000 Group By Prod_id Having Count(*) > 10; Oracle 1z0-071 Exam " Select Count(prod_id) From Sales Group By Prod_id Where Quantity_sold > 55000; Select Prod_id From Sales Where Quantity_sold > 55000 And Count(*) > 10 Group By Prod_id Having Count(*) > 10; Select Count(prod_id) From Sales Where Quantity_sold > 55000 Group By Prod_id;

14 :-

Examine this partial query:

Which GROUP BY clause must be added so the query returns the results shown?

15 :-

You own table DEPARTMENTS, referenced by views, indexes, and synonyms. Examine this command which executes successfully: DROP TABLE departments PURGE; Which three statements are true? (Choose three.)

It Will Remove The Departments Table From The Database. It Will Drop All Indexes On The Departments Table It Will Remove All Views That Are Based On The Departments Table. It Will Remove All Synonyms For The Departments Table Neither Can It Be Rolled Back Nor Can The Departments Table Be Recovered. It Will Delete All Rows From The Departments Table But Retain The Empty Table

16 :-

Examine the data in the INVOICES table:

INVOICE ID CURRENCY CODE RAISED _DATE

1 EUR 01-JAN-2019

2 USD 01- FEB -2019

3 JPY 31-MAR-2019

CURRENCY _ CODE

JPY

GPB

CAD

EUR

USD

Examine the data in the CURRENCIES table:

A) SELECT * FROM CURRENCIES

WHERE NOT EXISTS (

SELECT NULL FROM INVOICES WHERE CURRENCY-CODE =CURRENCY_CODE);

B) SELECT * FROM CURRENCIES

MINUS

SELECT * FROM INVOICES ;

C) SELECT CURRENCY -CODE FROM CURRENCIES

MINUS

SELECT CURRENCY _CODE FROM INVOICES

D) SELECT CURRENCY _CODE FROM CURRENCIES

INTERSECT

SELECT CURRENCY _ CODE FROM INVOICES ;

Which query returns the currencies in CURRENCIES that are not present in INVOICES?

A B C D

17 :-

The STORES table has a column START_DATE of data type DATE, containing the date the row was inserted. You only want to display details of rows where START_DATE is within the last 25 months. Which WHERE clause can be used?

Where To_number(start_date - Sysdate) <= 25 Where Months_between(start_date Sysdate) <= 25 Where Months_between(sysdate Start_date) <= 25 Where Add_months(start_date 25) <= Sysdate

18 :-

Examine this statement:

SELECT 1 AS ID, ‘JOHN’ AS FIRST _NAME

FROM DUAL

UNION

SELECT 1, ‘JOHN ’ AS NAME

FROM DUAL

ORDER BY 1;

What is returned upon execution?

An Error 2 Rows 0 Rows 1 Row

19 :-

Which three actions can you perform on an existing table containing data? (Choose three.)

Increase The Width Of A Numeric Column. Add A New Column As The Table's First Column. Define A Default Value That Is Automatically Inserted Into A Column Containing Nulls. Change A Date Column Containing Data To A Number Data Type. Change The Default Value Of A Column. Add A New Not Null Column With A Default Value

20 :-

The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE. You want to display the date of the first Monday after the completion of six months since hiring. The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week.Which query can be used?

Back

Mock Test Completion Certificate

You scored 0 out of 20 questions.

🏅

For your extraordinary service and contributions to your profession.

We are delighted in providing this certificate to you.

Tips for improving your score:

Make sure to read the questions and answer choices carefully.
Don't try to answer any choices that you don't know. It's better to skip a question.

Back Load More