SUBJ1 NUMBER(3) SUBJ2 NUMBER(3)
SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects. Examine this SELECT statement based on the MARKS table: SELECT subj1+subj2 total_marks, std_id FROM marks
WHERE subj1 > AVG(subj1) AND subj2 > AVG(subj2) ORDER BY total_marks;
What is the result of the SELECT statement?
A. The statement executes successfully and returns the student ID and sum of all marks for each student who
obtained more than the average mark in each subject. B. The statement returns an error at the SELECT clause. C. The statement returns an error at the WHERE clause. D. The statement returns an error at the ORDER BY clause. Q: 80 The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER(4) NOT NULL
CUSTOMER_NAME VARCHAR2(100) NOT NULL CUSTOMER_ADDRESS VARCHAR2(150) CUSTOMER_PHONE VARCHAR2(20)
You need to produce output that states \customer_name data
values come from the CUSTOMER_NAME column in the CUSTOMERS table. Which statement
produces this output?
A. SELECT dear customer, customer_name, FROM customers;
B. SELECT \FROM customers;
C. SELECT 'Dear Customer ' || customer_name ',' FROM customers;
D. SELECT 'Dear Customer ' || customer_name || ',' FROM customers;
E. SELECT \FROM customers;
F. SELECT 'Dear Customer ' || customer_name || ',' || FROM customers;
Q: 81 Examine the description of the STUDENTS table: STD_ID NUMBER(4)
COURSE_ID VARCHAR2(10) START_DATE DATE END_DATE DATE
Which two aggregate functions are valid on the START_DATE column? (Choose two.)
A. SUM(start_date) B. AVG(start_date) C. COUNT(start_date)
D. AVG(start_date, end_date) E. MIN(start_date)
F. MAXIMUM(start_date)
Q: 82 Click the Exhibit button and examine the data in the EMPLOYEES table. On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and
refers to the EMPLOYEE_ID. The JOB_ID column is a NOT NULL column. Evaluate this DELETE statement: DELETE employee_id, salary, job_id FROM employees
WHERE dept_id = 90;
Why does the DELETE statement fail when you execute it? A. There is no row with dept_id 90 in the EMPLOYEES table.
B. You cannot delete the JOB_ID column because it is a NOT NULL column. C. You cannot specify column names in the DELETE clause of the DELETE statement.
D. You cannot delete the EMPLOYEE_ID column because it is the primary key of the table.
Q: 83 Which three statements about subqueries are true? (Choose three.) A. A single row subquery can retrieve only one column and one row. B. A single row subquery can retrieve only one row but many columns.
C. A multiple row subquery can retrieve multiple rows and multiple columns. D. A multiple row subquery can be compared using the \E. A single row subquery can use the IN operator. F. A multiple row subquery can use the \
Q: 84 Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2)
Which statement produces the number of different departments that have employees with last name Smith?
A. SELECT COUNT(*) FROM employees
WHERE last_name='Smith'; B. SELECT COUNT(dept_id) FROM employees
WHERE last_name='Smith';
C. SELECT DISTINCT(COUNT(dept_id)) FROM employees
WHERE last_name='Smith';
D. SELECT COUNT(DISTINCT dept_id) FROM employees
WHERE last_name='Smith'; E. SELECT UNIQUE(dept_id) FROM employees
WHERE last_name='Smith';
Q: 85 Which view should a user query to display the columns associated with the constraints on a table owned by the user? A. USER_CONSTRAINTS B. USER_OBJECTS
C. ALL_CONSTRAINTS D. USER_CONS_COLUMNS E. USER_COLUMNS
Q: 86 You need to design a student registration database that contains several tables storing academic information.
The STUDENTS table stores information about a student. The STUDENT_GRADES table stores
information about the student's grades. Both of the tables have a column named STUDENT_ID. The
STUDENT_ID column in the STUDENTS table is a primary key. You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that
points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?
A. CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa NUMBER(4,3),
CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id)); B. CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa NUMBER(4,3),
student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id)); C. CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa NUMBER(4,3),
CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id)); D. CREATE TABLE student_grades
(student_id NUMBER(12), semester_end DATE, gpa NUMBER(4,3),
CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
Q: 87 Click the Exhibit button and examine the data in the EMPLOYEES and DEPARTMENTS tables.
You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use? A. SELECT last_name, department_name
FROM employees NATURAL JOIN departments; B. SELECT last_name, department_name FROM employees JOIN departments ; C. SELECT last_name, department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id); D. SELECT last_name, department_name FROM employees e
RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id); E. SELECT last_name, department_name FROM employees FULL JOIN departments ON (e.department_id = d.department_id); F. SELECT last_name, department_name FROM employees e LEFT OUTER
JOIN departments d ON (e.department_id = d.department_id); Q: 88 Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)); ROLLBACK; DESCRIBE DEPT
What is true about the set?
A. The DESCRIBE DEPT statement displays the structure of the DEPT table.
B. The ROLLBACK statement frees the storage space occupied by the DEPT table. C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.
D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT
statement introduced before the ROLLBACK statement. Q: 89 Evaluate the SQL statement: TRUNCATE TABLE DEPT;
Which three are true about the SQL statement? (Choose three.)
A. It releases the storage space used by the table.
B. It does not release the storage space used by the table.
C. You can roll back the deletion of rows after the statement executes.
D. You can NOT roll back the deletion of rows after the statement executes.
E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.
F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table.
Q: 90 For which two actions can you use the TO_DATE function? (Choose two.) A. convert any date literal to a date B. convert any numeric literal to a date C. convert any date to a character literal D. format 'January 10 1999' for input
E. format '10-JAN-99' to 'January 10 1999'
Q: 91 Click the Exhibit button to examine the data of the EMPLOYEES table. Evaluate this SQL statement:
SELECT e.employee_id \m.employee_id \FROM employees e JOIN employees m ON (e.mgr_id = m.employee_id) AND e.salary > 4000; What is its output?
Q: 92 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: 93 In which scenario would an index be most useful? A. The indexed column is declared as NOT NULL. B. The indexed columns are used in the FROM clause. C. The indexed columns are part of an expression.
D. The indexed column contains a wide range of values.
Q: 94 Which SQL statement displays the date March 19, 2001 in a format that appears as \A. SELECT
TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth \FROM dual; B. SELECT
TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspth \FROM dual;