1Z0-051 - Oracle Database 11g: SQL Fundamentals I

Go back to Oracle

Exam FAQ

How many questions are there on the 1Z0-051 exam? How long is the 1Z0-051 exam? How much does the 1Z0-051 exam cost? What is the best way to study for the 1Z0-051 exam?

Example Questions

Which tasks can be performed using SQL functions that are built into Oracle database? (Choose three.) Which arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.) Which object privileges can be granted on a view? What is true about the WITH GRANT OPTION clause? You want to display the date for the first Mon day of the next month and issue the following command: SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'), 'dd "is the first Monday for" fmmonth rrrr') FROM DUAL; What is the outcome? Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME --------------------- Lex De Haan Renske Ladwig Jose Manuel Urman Jason Mallin You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows: CUST NAME --------------------- *** De Haan **** Manuel Urman Which two queries give the required output? (Choose two.) Which statement is true regarding the default behavior of the ORDER BY clause? Examine the data in the CUSTOMERS table: You want to list all cities that have more than one customer along with the customer details. Evaluate the following query: <br><br><b> SQL>SELECT c1.custname, c1.city FROM Customers c1 __________________ Customers c2 ON (c1.city=c2.city AND c1.custname<>c2.custname); <br><br></b> Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.) In the CUSTOMERS table, the CUST_CITY column contains the value ’Paris’ for the CUST_FIRST_NAME ’ABIGAIL’. Evaluate the following query: <br><br><b> SQL> SELECT INITCAP(cust_first_name || ’ ’ || UPPER(SUBSTR(cust_city,-LENGTH(cust_city),2))) FROM customers WHERE cust_first_name = ’ABIGAIL’; <br><br></b> What would be the outcome? Examine the structure proposed for the TRANSACTIONS table: <br>name Null Type <br>----- -------------- -------------- <br>TRANS_ID NOT NULL NUMBER(6) <br>CUST_NAME NOT NULL VARCHAR2(20) <br>CUST_STATUS NOT NULL VARCHAR2 <br>TRANS_DATE NOT NULL DATE <br>TRANS_VALIDITY INTERVAL DAY TO SECOND <br>CUST_CREDIT_VALUE NUMBER(10) <br><br>Which two statements are true regarding the storage of data in the above table structure? (Choose two.) Which two statements are true about sequences created in a single instance database? (Choose two.) Evaluate the following SQL statement: <br><b> SQL> SELECT cust_id, cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city =’Singapore’); <br><br></b>Which statement is true regarding the above query if one of the values generated by the subquery is NULL? Which CREATE TABLE statement is valid? Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: Which MERGE statement is valid? Examine the structure of the ORDERS table: <br><b> <br>Name Null Type <br>--------- ---------- -------------------- <br>ORDER_ID NOT NULL NUMBER(12) <br>ORDER_DATE NOT NULL TIMESTAMP(6) <br>CUSTOMER_ID NOT NULL NUMBER(6) <br>ORDER_STATUS NUMBER(2) <br>ORDER_TOTAL NUMBER(8,2) </b> <br><br>You want to find the total value of all the orders for each year and issue the following command: <br><br><b>SQL>SELECT TO_CHAR(order_date,’rr’), SUM(order_total) FROM orders GROUP BY TO_CHAR(order_date,’yyyy’); <br><br></b> Which statement is true regarding the outcome? Examine the description of the EMP_DETAILS table given below: <br><br><b>name NULL TYPE <br>EMP_ID NOT NULL NUMBER <br>EMP_NAME NOT NULL VARCHAR2 (40) <br>EMP_IMAGE LONG </b> <br><br>Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.) The following data exists in the PRODUCTS table: <br><br><b>PROD_ID PROD_LIST_PRICE <br>---------------------------------------------- <br>123456 152525.99 </b> <br><br>You issue the following query: <br><br><b>SQL> SELECT RPAD(( ROUND(prod_list_price)), 10,’*’) FROM products WHERE prod_id = 123456; </b> <br><br>What would be the outcome? Examine the structure of the PROMOTIONS table: <br><br><b>name Null Type <br>PROMO_ID NOT NULL NUMBER(6) <br>PROMO_NAME NOT NULL VARCHAR2(30) <br>PROMO_CATEGORY NOT NULL VARCHAR2(30) <br>PROMO_COST NOT NULL NUMBER(10,2) </b> <br><br>The management wants to see a report of unique promotion costs in each promotion category. Which query would achieve the required result? Examine the structure of the SHIPMENTS table: <br><br><b>name Null Type <br>PO_ID NOT NULL NUMBER(3) <br>PO_DATE NOT NULL DATE <br>SHIPMENT_DATE NOT NULL DATE <br>SHIPMENT_MODE VARCHAR2(30) <br>SHIPMENT_COST NUMBER(8,2) </b> <br><br>You want to generate a report that displays the PO_ID and the penalty amount to be paid if the SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day. Evaluate the following two queries: <br><br><b>SQL> SELECT po_id, CASE WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THEN TO_CHAR((shipment_date - po_date) * 20) ELSE ’No Penalty’ END PENALTY FROM shipments; <br>SQL>SELECT po_id, DECODE (MONTHS_BETWEEN (po_date,shipment_date)>1, TO_CHAR((shipment_date - po_date) * 20), ’No Penalty’) PENALTY FROM shipments; </b> <br><br>Which statement is true regarding the above commands? What does the FORCE option for creating a view do? The CUSTOMERS table has the following structure: <br><br><b>name Null Type <br>CUST_ID NOT NULL NUMBER <br>CUST_FIRST_NAME NOT NULL VARCHAR2(20) <br>CUST_LAST_NAME NOT NULL VARCHAR2(30) <br>CUST_INCOME_LEVEL VARCHAR2(30) <br>CUST_CREDIT_LIMIT NUMBER </b> <br><br>You need to write a query that does the following tasks: - <br>1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit. <br>2. Only those customers whose income level has a value should be considered. <br>3. Customers whose tax amount is null should not be considered. <br><br>Which statement accomplishes all the required tasks? What is true about sequences? Evaluate the following command: <br><br><b>CREATE TABLE employees (employee_id NUMBER(2) PRIMARY KEY, last_name VARCHAR2(25) NOT NULL, department_id NUMBER(2)NOT NULL, job_id VARCHAR2(8), salary NUMBER(10,2)); </b> <br><br>You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization: <br><br><b>CREATE OR REPLACE VIEW sales_staff_vu SELECT employee_id, last_name,job_id FROM employees WHERE job_id LIKE ’SA_%’ WITH CHECK OPTION; </b> <br><br>Which two statements are true regarding the above view? (Choose two.) Which best describes an inline view? Evaluate the following query: SQL> SELECT promo_name || q'{'s start date was }' || promo_begin_date AS "Promotion Launches" FROM promotions; What would be the outcome of the above query? The ORDERS table belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR. Which statement would create a synonym ORD so that HR can execute the following query successfully? <br><br><b>SELECT * FROM ord;</b> Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.) The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4) ENAME VARCHAR2 (25) JOB_ID VARCHAR2(10) Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column, for those employees whose ENAME ends with a the letter "n"? Which two statements are true regarding the COUNT function? (Choose two.) Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.) Which statement is true regarding the INTERSECT operator? 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 'ACTIVE' 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? You need to display the date 11-oct-2007 in words as 'Eleventh of October, Two Thousand Seven'. Which SQL statement would give the required result? Which statement is true regarding the UNION operator? Which constraint can be defined only at the column level? Which is the valid CREATE TABLE statement? The DBA issues this SQL command: CREATE USER scott IDENTIFIED by tiger; What privileges does the user Scott have at this point? Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table: <br><br><b>LIST_PRICE MIN_PRICE <br>10000 8000 <br>20000 30000 <br>30000 </b> <br><br>Which two expressions give the same output? (Choose two.) Examine the statement: <br><br><b>Create synonym emp for hr.employees; </b> <br><br>What happens when you issue the statement? You need to calculate the number of days from 1st Jan 2007 till date: Dates are stored in the default format of dd-mm-rr. Which two SQL statements would give the required output? (Choose two.) Which two statements are true regarding constraints? (Choose two.) Examine the description of the EMP_DETAILS table given below: <br><br><b>name NULL TYPE <br>----- -------------- -------------- <br>EMP_ID NOT NULL NUMBER <br>EMP_NAME NOT NULL VARCHAR2 (40) <br>EMP_IMAGE LONG </b> <br><br>Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.) Which two statements are true about constraints? (Choose two.) When does a transaction complete? (Choose all that apply.) Examine the structure proposed for the TRANSACTIONS table: <br><br><b>name Null Type <br>TRANS_ID NOT NULL NUMBER(6) <br>CUST_NAME NOT NULL VARCHAR2(20) <br>CUST_STATUS NOT NULL VARCHAR2 <br>TRANS_DATE NOT NULL DATE <br>TRANS_VALIDITY INTERVAL DAY TO SECOND <br>CUST_CREDIT_VALUE NUMBER(10) </b> <br><br>Which two statements are true regarding the storage of data in the above table structure? (Choose two.) 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? You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes this task? Which two statements are true regarding single row functions? (Choose two.) Evaluate the SQL statement: <br><br><b>SELECT LPAD (salary,10,’*’) FROM EMP WHERE EMP_ID = 1001; </b> <br><br>If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed? Which three statements/commands would cause a transaction to end? (Choose three.)

Study Guides