Q1. - (Topic 1)
For which action can you use the TO_DATE function?
A. Convert any date literal to a date
B. Convert any numeric literal to a date
C. Convert any character literal to a date
D. Convert any date to a character literal
E. Format ’10-JAN-99’ to ‘January 10 1999’
Answer: C
Q2. - (Topic 1)
The COMMISSION column shows the monthly commission earned by the employee. Exhibit
Which two tasks would require sub queries or joins in order to be performed in a single step? (Choose two.)
A. listing the employees who earn the same amount of commission as employee 3
B. finding the total commission earned by the employees in department 10
C. finding the number of employees who earn a commission that is higher than the average commission of the company
D. listing the departments whose average commission is more that 600
E. listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID
F. listing the employees whose annual commission is more than 6000
Answer: A,C
Q3. - (Topic 1)
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. Sub queries can contain GROUP BY and ORDER BY clauses
C. Only one column or expression can be compared between the main query and subqeury
D. Main query and sub query can get data from different tables
E. Main query and sub query must get data from the same tables
F. Sub queries can contain ORDER BY but not the GROUP BY clause
Answer: A,B,D
Q4. - (Topic 1)
Examine the structure of the EMPLOYEES table:
Which INSERT statement is valid?
A.
INSERT INTO employees (employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ‘01/01/01’);
B.
INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ’01 January 01’);
C.
INSERT INTO employees(employee_id, first_name, last_name, Hire_date) VALUES ( 1000, ‘John’, ‘Smith’, To_date(‘01/01/01’));
D.
INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, 01-Jan-01);
Answer: D
Explanation: It is the only statement that has a valid date; all other will result in an error. Answer A is incorrect, syntax error, invalid date format
Q5. - (Topic 2)
Which SQL statement accepts user input for the columns to be displayed, the table name, and WHERE condition?
A. SELECT &1, "&2"
FROM &3
WHERE last_name = '&4'
B. SELECT &1, '&2'
FROM &3
WHERE '&last_name = '&4' '
C. SELECT &1, &2
FROM &3
WHERE last_name = '&4'
D. SELECT &1, '&2'
FROM EMP
WHERE last_name = '&4'
Answer: C
Explanation:
In a WHERE clause, date and characters values must be enclosed within single quotation marks.
Sample of the correct syntax
SELECT EMPLOYEE_ID, &COLUMN_NAME FROM EMPLOYEES
Incorrect Answers :
A. Incorrect use of " symbol
B. Incorrect use of ' symbol
D. No input for table name as EMP has been use in the statement.
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Producing Readable Output with iSQL*PLUS, p. 7-8
Q6. - (Topic 1)
You want to create an ORD_DETAIL table to store details for an order placed having the following business requirement:
1) The order ID will be unique and cannot have null values.
2) The order date cannot have null values and the default should be the current date.
3) The order amount should not be less than 50.
4) The order status will have values either shipped or not shipped.
5) The order payment mode should be cheque, credit card, or cash on delivery (COD).
Which is the valid DDL statement for creating the ORD_DETAIL table?
A.
CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));
B.
CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));
C.
CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount >= 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));
D.
CREATE TABLE ord_details
(ord_id NUMBER(2),
ord_date DATE NOT NULL DEFAULT SYSDATE,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount >= 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));
Answer: C
Q7. - (Topic 2)
What is true about sequences?
A. Once created, a sequence belongs to a specific schema.
B. Once created, a sequence is linked to a specific table.
C. Once created, a sequence is automatically available to all users.
D. Only the DBA can control which sequence is used by a certain table.
E. Once created, a sequence is automatically used in all INSERT and UPDATE statements.
Answer: A
Q8. - (Topic 1)
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?
A. SELECT TO_CHAR('ll-oct-2007'. 'miDdspth "of Month. Year') FROM DUAL:
B. SELECT TO_CHAR(TO_DATE('ll-oct-2007'X 'miDdspth of month, year') FROM DUAL;
C. SELECT TO_CHAR(TO_DATE('ll-oct-2007'), 'miDdthsp "of* Month. Year') FROM DUAL;
D. SELECT TO_DATE(TO_CHAR('ll-oct-20077fiiiDdspth "of" Month. Year')) FROM DUAL:
Answer: A
Q9. - (Topic 1)
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit
Using the PROMOTIONS table, you need to display the names of all promos done after
January 1, 2001 starting with the latest promo.
Which query would give the required result? (Choose all that apply.)
A. SELECT promo_name,promo_begin_date
FROM promotions
WHERE promo_begin_date > '01-JAN-01'
ORDER BY 1 DESC;
B. SELECT promo_name,promo_begin_date "START DATE"
FROM promotions
WHERE promo_begin_date > '01-JAN-01'
ORDER BY "START DATE" DESC;
C. SELECT promo_name,promo_begin_date
FROM promotions
WHERE promo_begin_date > '01-JAN-01'
ORDER BY 2 DESC;
D. SELECT promo_name,promo_begin_date
FROM promotions
WHERE promo_begin_date > '01-JAN-01'
ORDER BY promo_name DESC;
Answer: B,C
Q10. - (Topic 1)
See the structure of the PROGRAMS table:
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM programs;
B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)) FROM programs;
C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing') FROM programs;
D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing') FROM programs;
Answer: A,D
Explanation:
NVL Function
Converts a null value to an actual value:
Data types that can be used are date, character, and number.
Data types must match:
–
NVL(commission_pct,0)
–
NVL(hire_date,'01-JAN-97')
–
NVL(job_id,'No Job Yet')
MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2 The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month. MONTHS_BETWEEN returns a numeric value. - answer C NVL has different datatypes -numeric and strings, which is not possible!
The data types of the original and if null parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert if null to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter.