Welcome to your 1z0-061 Oracle Database 12c SQL Fundamentals

1. Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?
2. View the Exhibit and examine the structure of the product, component, and PDT_COMP tables.
In product table, PDTNO is the primary key.
In component table, COMPNO is the primary key.
In PDT_COMP table, <PDTNO, COMPNO) is the primary key, PDTNO is the foreign key referencing PDTNO in product table and COMPNO is the foreign key referencing the COMPNO in component table.
You want to generate a report listing the product names and their corresponding
component names, if the component names and product names exist.
Evaluate the following query:

SQL>SELECT pdtno, pdtname, compno, compname
FROM product _____________ pdt_comp
USING (pdtno) ____________ component USING (compno)
WHERE compname IS NOT NULL;

Which combination of joins used in the blanks in the above query gives the correct output?
3. View the Exhibit for the structure of the student and faculty tables.

You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements:

Which statement is true regarding the outcome?
4. View the Exhibits and examine products and sales tables.


You issue the following query to display product name and the number of times the product has been sold:

What happens when the above statement is executed?
5. You want to create a table employees in which the values of columns EMPLOYEES_ID and LOGIN_ID must be unique and not null. Which two SQL statements would create the required table?
6. View the Exhibit and examine the structure of the products table.

Using the products table, you issue the following query to generate the names, current list price, and discounted list price for all those products whose list price falls below $10 after a discount of 25% is applied on it.

The query generates an error. What is the reason for the error?
7. Examine the structure proposed for the transactions table:

Which two statements are true regarding the creation and storage of data in the above table structure?
8. You need to create a table for a banking application. One of the columns in the table has the following requirements:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and subtracted with date data type without using conversion functions.
3) The maximum period of the credit provision in the application is 30 days.
4) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?
9. Which three tasks can be performed using SQL functions built into Oracle Database?
10. View the Exhibit and examine the description of SALES and PROMOTIONS tables.

You want to delete rows from the sales table, where the PROMO_NAME column in the promotions table has either blowout sale of everyday low prices as values.
Which three delete statements are valid?
11. Which three SQL statements would display the value 1890.55 as $1, 890.55?
12. You want to display 5 percent of the employees with the highest salaries in the
EMPLOYEES table. Which query will generate the required result?
13. In the customers table, the CUST_CITY column contains the value 'Paris' for the CUST_FIRST_NAME 'Abigail'.
Evaluate the following query:

What would be the outcome?
14. View the Exhibit and evaluate the structure and data in the CUST_STATUS table.

You issue the following SQL statement:

Which statement is true regarding the execution of the above query?
15. Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?
16. Examine the types and examples of relationships that follow:
1. One-to-one a) Teacher to students
2. One-to-many b) Employees to Manager
3. Many-to-one c) Person to SSN
4. Many-to-many d) Customers to products
Which option indicates the correctly matched relationships?
17. You execute the following commands:

For which substitution variables are you prompted for the input?
18. View the Exhibit and examine the structure of the customers table.

Using the customers table, you need to generate a report that shows the average credit limit for customers in Washington and NEW YORK. Which SQL statement would produce the required result?
19. View the Exhibit and examine the data in the employees table:

You want to display all the employee names and their corresponding manager names. Evaluate the following query:

Which join option can be used in the blank in the above query to get the required output?
20. Evaluate the following query:

What would be the outcome of the above query?
21. Examine the structure of the transactions table:

You want to display the date, time, and transaction amount of transactions that where done before 12 noon. The value zero should be displayed for transactions where the transaction amount has not been entered. Which query gives the required result?
22. Examine the data in the ename and hiredate columns of the employees table:


You want to generate a list of user IDs as follows:

You issue the following query:

What is the outcome?
23. Evaluate the following SQL commands:

The command to create a table fails. Identify the two reasons for the SQL  statement failure?
24. View the Exhibit and examine the structure of the SALES table.

The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times.

Which statement is true regarding this SQL statement?
25. View the Exhibit and examine the structure of the customers table.

Using the customers table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?
26. View the Exhibit and examine the structure of the promotions table.

Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?
27. Examine the structure and data of the CUST_TRANS table:

Dates are stored in the default date format dd-mon-rr in the CUST_TRANS table. Which three SQL statements would execute successfully?
28. View the Exhibit and examine the structure of the customers table.


NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and
CUST_CITY that have the same data types and size as the corresponding columns in the customers table. Evaluate the following insert statement:

The insert statement fails when executed.
What could be the reason?
29. YOU need to display the date ll-oct-2007 in words as ‘Eleventh of October, Two Thousand Seven'. Which SQL statement would give the required result?
30. Examine the data in the ORD_ITEMS table:

Evaluate the following query:

Which statement is true regarding the outcome of the above query?
31. View the Exhibit and examine the data in the promotions table.

PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the post category that were launched before January 1, 2000.

Which SQL statement would you use?
32. You need to create a table with the following column specifications:
1. Employee ID (numeric data type) for each employee
2. Employee Name (character data type) that stores the employee name
3. Hire date, which stores the date of joining the organization for each employee
4. Status (character data type), that contains the value 'active1 if no data is entered
5. Resume (character large object [CLOB] data type), which contains the resume submitted by the employee
Which is the correct syntax to create this table?
33. Examine the structure of the sales table:

Evaluate the following create table statement:

Which two statements are true about the creation of the SALES1 table?
34. Which two statements are true regarding subqueries?
35. Examine the structure of the products table:

You want to display the names of the products that have the highest total value for
UNIT_PRICE * QTY_IN_HAND.
Which SQL statement gives the required output?
36. Which statement is true regarding the UNION operator?
37. View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the promotions table, and the required output format.

Which two queries give the correct result?
38. View the Exhibit and examine the structures of the employees and departments tables.

You want to update the employees table as follows:
-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 iocation_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission in iocation_id 2100 to 1.5 times the average commission of their department
You issue the following command:

What is the outcome?
39. You need to produce a report where each customer's credit limit has been incremented by $1000. In the output, the customer's last name should have the heading Name and the incremented credit limit should be labeled New credit Limit. The column headings should have only the first letter of each word in uppercase.
Which statement would accomplish this requirement?
40. View the Exhibit and examine the data in the costs table.

You need to generate a report that displays the IDs of all products in the costs table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost.
You issue the following query:

Which statement is true regarding the above query?
41. View the Exhibits and examine the structures of the products, sales, and customers tables.



You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for a customers in 'Tokyo'.
Which two queries give the required result?
42. View the Exhibit and examine the structure of the products table.

Evaluate the following query:

What would be the outcome of executing the above SQL statement?
43. You issued the following command:

SQL> DROP TABLE employees;


Which three statements are true?
44. Examine the create table statements for the stores and sales tables.

SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);



SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id));


you executed the following statement


SQL> DELETE from stores WHERE store_id=900;


The statement fails due to the integrity constraint error:
ORA-02292: integrity constraint (HR.STORE_ID_FK) violated
Which three options ensure that the statement will execute successfully?
45. You want to create a sales table with the following column specifications and data types:
SALESID: Number
STOREID: Number
ITEMID: Number
QTY: Number, should be set to 1 when no value is specified
SLSDATE: Date, should be set to current date when no value is specified
PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified
Which statement would create the table?
46. Examine the data in the CUST_NAME column of the customers table.

You need to display customers' second names where the second name starts with "Mc" or "MC."
Which query gives the required output?
47. Evaluate the following query:
SQL> SELECT TRUNC(ROUND(156.00, -1), -1)FROM DUAL;

What would be the outcome?
48. You want to display 5 percent of the rows from the sales table for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLD even if this causes the output to exceed 5 percent of the rows.
Which query will provide the required result?
49. You need to list the employees in DEPARTMENT_ID 30 in a single row, ordered by HIRE_DATE.
Examine the sample output:

Which query will provide the required output?
50. Which create table statement is valid?
51. Which two statements are true regarding constraints?
52. In which three situations does a transaction complete?
53. Examine the structure of the orders table:

You want to find the total value of all the orders for each year and issue the following command:

Which statement is true regarding the outcome?
54. View the Exhibit and examine the structure of the products table.

All products have a list price.
You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on it. Freight charges of $100 have to be applied to all the products.

What would be the outcome if all the parentheses are removed from the above statement?
55. Examine the data in the PROMO_BEGIN_DATE column of the promotions table:

You want to display the number of promotions started in 1999 and 2000.
Which query gives the correct output?
56. You want to display the date for the first Monday of the next month and issue the following command:

What is the outcome?
57. View the Exhibit and examine the structure of the promotions table.
You need to generate a report of all promos from the promotions table based on the following conditions:
1. The promo name should not begin with 'T' or 'N'.
2. The promo should cost more than $20000.
3. The promo should have ended after 1st January 2001.
Which where clause would give the required result?
58. Examine the structure of the employees table.

You want to display the maximum and minimum salaries of employees hired 1 year ago. Which two statements would get the correct output?
59. Using the customers table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?
60. You issue the following command to drop the products table:

SQL> DROP TABLE products;


Which three statements are true about the implication of this command?
61. Evaluate the following SQL statement:

Which statement is true regarding the above query if one of the values generated by the subquery is null?
62. Examine the structure of the employees table:

There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the name, joining date, and manager for all the employees. Newly hired employees are yet to be assigned a department or a manager. For them, 'No Manager1 should be displayed in the manager column.
Which SQL query gets the required output?
63. Your new question!Which statement is true regarding the default behavior of the order by clause?
64. View the Exhibits and examine the structures of the products and sales tables.


Which two SQL statements would give the same output?
65. The customers table has the following structure:

You need to write a query that does the following tasks:
1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
2. Only those customers whose income level has a value should be considered.
3. Customers whose tax amount is null should not be considered.
Which statement accomplishes all the required tasks?
66. View the Exhibit and examine the structure of the CUSTOMERS table.

You have been asked to produce a report on the customers table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city.
Which query would accomplish this task?
67. View the Exhibit and examine the data in the products table.

You need to display product names from the products table that belong to the
'software/other' category with minimum prices as either S2000 or S4000 and no unit of measure.
You issue the following query:

Which statement is true regarding the above query?
68. You need to display the first names of all customers from the customers table that contain the character 'e' and have the character 'a' in the second last position.
Which query would give the required output?
69. Examine the structure of the customers table:

CUSTNO is the primary key in the table. You want to find out if any customers' details have been entered more than once using different CUSTNO, by listing all the duplicate names.
Which two methods can you use to get the required result?
70. You need to generate a list of all customer last names with their credit limits from the customers table.
Those customers who do not have a credit limit should appear last in the list.
Which two queries would achieve the required result?
71. You issue the following command to alter the country column in the departments table:

Which statement is true?
72. Which two statements are true regarding constraints?
73. Which statement adds a column called salary to the employees table having 100 rows, which cannot contain null?
74. Which two statements are true regarding single row functions?
75. Which two statements are true regarding the count function?