Q. What does the TRUNCATE statement do?
A. removes the table
B. removes all rows from a table
C. shortens the tale to 10 rows
D. removes all columns from a table
E. removes foreign keys from a table
Q. the ORDERS table has these columns
ORDER_ID NUMBER(4) NOT NULL
CUSTOMER_ID NUMBER(12) NOT NULL
The ORDERS table tracks the Order number, the order total and the customer to whom the Order belongs. Which two statements retrieve orders with an inclusive total that ranges between 100.00 and 200.00 dollars? (Choose Two).
A. SELECT customer_id, order_id, order_total
RANGE ON order_total (100 AND 2000) INCLUSIVE
B. SELECT customer_id, order_id, order_total
HAVING order total BETWEEN 100 and 2000
C. SELECT customer_id, order_id, order_total
WHERE order_total BETWEEN 100 and 2000
D. SELECT customer_id, orde_id, order_total
WHERE order_total >= 100 and <=2000
E. SELECT customer_id, order_id, order _total
WHERE order_total>= 100 and order_total <=2000.
Q. Which is an /SQL * Plus command?
Q. Which SELECT statement should you use to extract the year form the system date and display it in the format “1998”?
A. SELECT TO_CHAR(SYSDATE, ‘yyyy’)
B. SELECT TO_DATE(SYSDATE,’yyyy’)
C. SELECT DECODE (SUBSTR (SYSDATE, 8), ‘YYYY’)
D. SELECT DECODE (SUBSTR (SYSATE, 8),’year’)
E. SELECT TO_CHAR (SUBSTR(SYSDATE, 8,2),’yyyy’)
Q. The EMPLOYEES table contains these columns:
You need to write a query that will produce these results:
1. Display the salary multiplied by the commission_pct
2. Exclude employees with a zero commission_pct
3. Display a zero for employees with a null commission value
Evaluate the SQL statement:
SELECT LAST_NAME, SALARY * COMMISSION_PCT FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL; What does the statement provide?
A. all of the desired results
B. two of the desired results
C. one of the desired results
D. an error statement
Q. A subquery can be used to _________.
A. create groups of data
B. sort data in a specific order
C. convert data to a different format
D. retrieve data based on an unknown condition
Q. Which clause should you use to exclude group results?
D. GROUP BY
E. ORDER BY
Q. Scott issues the SQL statements:
CREATE TABLE dept
If Sue needs to select from Scott’s DEPT table, which command should she use?
A. SELECT *
B. SELECT *
FROM SCOTT. DEPT.
C. SELECT *
D. SELECT *
WHERE USER_NAME = ‘SCOTT’
AND TABLE NAME= ‘DEPT’;
Q. What is true about joining tables through an equation?
A. you can join a maximum of two tables through an equation
B. you can join a maximum, of two columns through an equation
C. you specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
D. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.
E. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.
Q. You need to calculate the total of all salaries in the accounting department. Which group function should you use?
Q. The EMP table has these columns:
SALARY NUMBER (8,2)
Management wants a list of names of employees who have been with the company for more than five yeas. Which SQL statement displays the required results?
A. SELECT ENAME
B. SELECT ENAME
WHERE HIRE_DATE-SYSDATE > 5
C. SELECT ENAME
WHERE (SYSDATE-_DATE)/365 > 5
D. SELECT ENAME
WHERE (SYSDATE-HIRE_DATE)* 365 > 5
Q. You would like to display the system date in the format *Monday, 01 June, 2001*
Which SELECT statement should you use?
A. SELECT TO_DATE (SYSDATE, ‘FMDAY, DD Month, YYYY’)
B. SELECT TO_CHAR(SYSDATE, ‘FMDD, DY Month ‘YYY’)
C. SELECT TO_CHAR(SYSDATE, ‘FMDay, DD Month YYYY’)
D. SELECT TO_CHAR(SYSDATE, ‘FMDAY, DDD Month, YYYY’)
E. SELECT TO_DATES(SYSDATE,’FMDY, DDD Month, YYYY’)
Q. The CUSTOMERS table has these columns:
CUSTOMER_ID NUMBER (4) NOT NULL
Which statement finds the rows in the CUSTOMERS table that do not have a postal code
A. SELECT customer_id, customer_name
WHERE postal_code CONTAINS NULL
B. SELECT customer_id, customer name
C. SELECT customer_id, customer_name
WHERE postal_code IS NULL
D. SELECT customer_id, customer_name
WHERE postal code IS NVL
E. SELECT customer_id, customer_name
Q. Evaluate this SQL statement
SELECT e.employee_id, (15*e.salary) + .(5* e.commission_pct) + (s.sales amount* (.35* e.bonus)) AS CALC_VALUE FROM employees e,sales s WHERE e.employee_id = s.emp_id What will happen if you remove al the parentheses from the calculation?
A. The value displayed in the CALC_VALUE column will be lower
B. The value displayed in the CALC_VALUE column will be higher
C. There will be no difference in the value displayed in the CALC_VALUE column
D. An error will be reported.
Q. You define a multiple-row subquery in the WHERE clause of an SQL query with a comparison operator”=” What happens when the main query is executed?
A. the main query executes with the first value returned by the subquery
B. the main query executes with the last value returned by the subquery
C. the main query executes with all the values returned by the subquery
D. the main query fails because the multiple-row subquery cannot be used with the comparison operator.
E. You cannot define multiple-row subquery in the WHERE clause of a SQL query
Q. which three statements correctly describe the functions and use of constraints? (Choose three)
A. constraints provide data independence
B. constraint make complex queries easy
C. constraints enforce rules at the view level
D. constraints enforce rules at the table level
E. constraints prevent the deletion of a table if there are dependencies
F. constraints prevent the deletion of an index if there are dependencies
Q. Which two are character manipulation functions? (Choose two)
Q. You need to create a view EMP_VU. The view should allow the users to manipulate the records of only the employees that are working for departments 10 or 20. Which SQL statement would you use tocrete the view EMP_VU?
A. CREATE VIEW emp_vu AS SELECT employees WHERE department_id 1N (10,20)
B. CREATE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20) WITH READ ONLY
C. CREATE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20) WITH CHECK OPTION
D. CREATE FORCE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10, 20) NO UPDATE
Q. Evaluate these two SQL statemens
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 statements
Q. Which two statements accurately describe a role? (Choose two)
A. a role can be given to a maximum of 1000 users
B. a user can have access to a maximum of 10 roles
C. a role can have a maximum of 100 privileges contained in it.
D. Privileges are given to a role by using the CREATE ROLE statement.
E. A role is a named group of related privileges that can be granted to the user
F. A user can have access to several roles, and several users can be assigned the same role.
Q. You added a PHONE-NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table Some of the employees may not have a phone number available. Which data manipulation operation do you perform?
F. You cannot enter the phone number for the existing employee records