1Z0-051 Exam - Oracle Database: SQL Fundamentals I

Q1. - (Topic 1) 

Evaluate the following SQL statements: 

DELETE FROM sales; 

There are no other uncommitted transactions on the SALES table. 

Which statement is true about the DELETE statement? 

A. It removes all the rows as well as the structure of the table 

B. It removes all the rows in the table and deleted rows cannot be rolled back 

C. It removes all the rows in the table and deleted rows can be rolled back 

D. It would not remove the rows if the table has a primary key 

View Answer

Q2. - (Topic 1) 

Which two statements are true about constraints? (Choose two.) 

A. The UNIQUE constraint does not permit a null value for the column. 

B. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints. 

C. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index. 

D. The NOT NULL constraint ensures that null values are not permitted for the column. 

View Answer

Q3. - (Topic 2) 

Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME 

Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikkilineni Julia Nayer 

You need to display customers' second names where the second name starts with "Mc" or "MC." 

Which query gives the required output? 

A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) 

FROM customers 

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc' 

B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) 

FROM customers 

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%' 

C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) 

FROM customers 

WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%'); 

D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) 

FROM customers 

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%'); 

View Answer

Q4. - (Topic 1) 

Evaluate the following SQL statements: Exhibit: 

You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization. 

Exhibit: 

Which two statements are true regarding the above view? (Choose two.) 

A. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table 

B. It allows you to delete details of the existing sales staff from the EMPLOYEES table 

C. It allows you to insert rows into the EMPLOYEES table 

D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements 

View Answer

Q5. - (Topic 2) 

The CUSTOMERS table has these columns: 

A promotional sale is being advertised to the customers in France. Which WHERE clause identifies customers that are located in France? 

A. WHERE lower(country_address) = "france" 

B. WHERE lower(country_address) = 'france' 

C. WHERE lower(country_address) IS 'france' 

D. WHERE lower(country_address) = '%france%' 

E. WHERE lower(country_address) LIKE %france% 

View Answer

Q6. - (Topic 1) 

The SQL statements executed in a user session as follows: Exhibit: 

Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.) 

A. Both the DELETE statements and the UPDATE statement are rolled back 

B. The rollback generates an error 

C. Only the DELETE statements are rolled back 

D. Only the seconds DELETE statement is rolled back 

E. No SQL statements are rolled back 

View Answer

Q7. - (Topic 1) 

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; 

View Answer

Q8. - (Topic 1) 

The following data exists in the PRODUCTS table: PROD_ID PROD_LIST_PRICE 

123456 152525.99 

You issue the following query: 

SQL> SELECT RPAD(( ROUND(prod_list_price)), 10,'*') 

FROM products 

WHERE prod_id = 123456; 

What would be the outcome? 

A. 152526**** 

B. **152525.99 

C. 152525** 

D. an error message 

View Answer

Q9. - (Topic 2) 

Which best describes an inline view? 

A. a schema object 

B. a sub query that can contain an ORDER BY clause 

C. another name for a view that contains group functions 

D. a sub query that is part of the FROM clause of another query 

View Answer

Q10. - (Topic 2) 

View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables. 

You want to update the EMPLOYEES table as follows:4 ? 4; 

-Update only those employees who work in Boston or Seattle (locations 2900 and 2700). 

-Set department_id for these employees to the department_id corresponding to London 

 (location_id 2100). 

-Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department. 

-Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department. 

You issue the following command: SQL>UPDATE employees SET department_id = (SELECT department_id FROM departments WHERE location_id = 2100), (salary, commission) = (SELECT 1.1*AVG(salary), 1.5*AVG(commission) FROM employees, departments WHERE departments.location_id IN(2900,2700,2100)) WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700) 

What is the outcome? 

A. It executes successfully and gives the correct result. 

B. It executes successfully but does not give the correct result. 

C. It generates an error because a subquery cannot have a join condition in an UPDATE statement. 

D. It generates an error because multiple columns (SALARY, COMMISION) cannot be specified together in an UPDATE statement. 

View Answer