Exit demo 1Z0-051 Oracle Database 11g : SQL Fundamentals I PDF format · free preview

Oracle 1Z0-051 - Questions & Answers

Free preview · every answer includes a full explanation

Product page: https://prepkeys.com/1z0-051.html

Question 1
Multiple choice

Evaluate the SQL statement: TRUNCATE TABLE DEPT;

Which three are true about the SQL statement? (Choose three.)

A.

It releases the storage space used by the table.

B.

It does not release the storage space used by the table.

C.

You can roll back the deletion of rows after the statement executes.

D.

You can NOT roll back the deletion of rows after the statement executes.

E.

An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.

F.

You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table

Question 2
Single choice

You need to design a student registration database that contains several tables storing academic information.
The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key.

You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table.

Which statement creates the foreign key?

A.

CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3),
CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id));

B.

CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3),
student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

C.

CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3),
CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));

D.

CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3),
CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

Question 3
Multiple choice

Here is the structure and data of the CUST_TRANS table: Exhibit:

Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table.

Which three SQL statements would execute successfully? (Choose three.)

A.

SELECT transdate + '10' FROM cust_trans;

B.

SELECT * FROM cust_trans WHERE transdate = '01-01-07';

C.

SELECT transamt FROM cust_trans WHERE custno > '11';

D.

SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';

E.

SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;

Question 4
Multiple choice

See the Exhibit and examine the structure and data in the INVOICE table:

Exhibit:

Which two SQL statements would executes successfully? (Choose two.)

A.

SELECT MAX(inv_date),MIN(cust_id) FROM invoice;

B.

SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;

C.

SELECT (AVG(inv_date) FROM invoice;

D.

SELECT AVG(inv_date - SYSDATE),AVG(inv_amt) FROM invoice;

Question 5
Multiple choice

Which three statements are true regarding sub queries? (Choose three.)

A.

Multiple columns or expressions can be compared between the main query and sub query

B.

Main query and sub query can get data from different tables

C.

Sub queries can contain GROUP BY and ORDER BY clauses

D.

Main query and sub query must get data from the same tables

E.

Sub queries can contain ORDER BY but not the GROUP BY clause

F.

Only one column or expression can be compared between the main query and subqeury

Question 6
Single choice

See the Exhibit and examine the structure of the CUSTOMERS table:

Using the CUSTOMERS table, you need to generate a report that shown the average credit limit for
customers in WASHINGTON and NEW YORK.

Which SQL statement would produce the required result?

A.

SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_credit_limit, cust_city;

B.

SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city,cust_credit_limit;

C.

SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city;

D.

SELECT cust_city, AVG(NVL(cust_credit_limit,0))
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK');

Question 7
Single choice

Evaluate these two SQL statements:

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT
last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;

What is true about them?

A.

The two statements produce identical results.

B.

The second statement returns a syntax error.

C.

There is no need to specify DESC because the results are sorted in descending order by default.

D.

The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.

Question 8
Multiple choice

Where can sub queries be used? (Choose all that apply)

A.

field names in the SELECT statement

B.

the FROM clause in the SELECT statement

C.

the HAVING clause in the SELECT statement

D.

the GROUP BY clause in the SELECT statement

E.

the WHERE clause in only the SELECT statement

F.

the WHERE clause in SELECT as well as all DML statements

Question 9
Multiple choice

Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

A.

SELECT TO_CHAR(1890.55,'$99G999D00')
FROM DUAL;

B.

SELECT TO_CHAR(1890.55,'$9,999V99')
FROM DUAL;

C.

SELECT TO_CHAR(1890.55,'$0G000D00')
FROM DUAL;

D.

SELECT TO_CHAR(1890.55,'$99G999D99')
FROM DUAL;

E.

SELECT TO_CHAR(1890.55,'$9,999D99')
FROM DUAL;

Question 10
Single choice

Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?

A.

It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement

B.

It executes successfully and displays rows in the descending order of PROMO_CATEGORY

C.

It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement

D.

It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators

Showing 10 of 292 questions · Unlock the full set