Q. Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
A. SELECT ename, salary*12'Annual Salary' FROM employees;
B. SELECT ename, salary* 12 "Annual Salary" FROM employees
C. SELECT ename, salary* 12 AS Annual Salary FROM employees;
D. SELECT ename, salary* 12 AS INITCAP("ANNUAL SALARY") FROM employees
Q. 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 columns contains a wide range of values.
Q. Which two are attributes of iSQL* Plus? (Choose two).
A. iSQL * Plus commands cannot be abbreviated
B. iSQL* Plus commands are accessed from a browser.
C. iSQL*Plus commands are used to manipulate data in tables
D. iSQL* Plus command manipulate table definitions in the database
E. iSQL* Plus is the Oracle proprietary interface for executing SQL statements.
Q. 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 ">" operator
E. A single row subquery can use the IN operator
F. A multiple row subquery can use the "=" operator
Q. When should you create a role? (Choose two)
A. to simplify the process of creating new users using the CREATE USER xxx IDENTIFIED by yyy statement
B. to grant a group of related privileges to a user
C. When the number of people using the database is very high
D. to simplify the process of granting and revoking privileges
E. to simplify profile maintenance for a user who is constantly traveling.
Q. Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater than 5000?
A. ORDER BY SALARY > 5000
B. GROUP BY SALARY > 5000
C. HAVING SALARY > 5000
D. WHERE SALARY > 5000
Q. Which four are correct guidelines for naming database tables? (Choose four)
A. Must begin with either a number or a letter
B. must be 1-30 characters long
C. should not be an Oracle Server reserved word.
D. must contain only A-Z, a-z, 0-9, _,*, and #
E. must contain only A-Z, a-z, 0-9, _, $, and #
F. must begin with a letter
Q. Which two statements about sequences are true? (Choose two)
A. You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value.
B. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.
C. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value form the sequence
D. You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column.
E. If a sequence starting from a value 100 and incremented by 1 is used by more than one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.
F. You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.
Q. The EMP table contains these columns:
LAST NAME VARCHAR2(25)
SALARY NUMBER (6,2)
What is true about this SQL statement?
A. The SQL statement displays the desired results
B. The column in the WHERE clause should be changed to display the desired results.
C. The operator in the WHERE clause should be changed to display the desired results
D. The WHERE clause should be changed to use an outer join to display the desired results.
Q. Examine the description of the MARKS table:
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 us 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. You want to display the titles of books that meet these criteria:
1. Purchased before January 21, 2001
2. Price is less than $ 500 or greater than $ 900
You want to sort the result by their date of purchase, starting with the most recently bought book.
Which statement should you use?
A. SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < '21 - Jan-2001' ORDER BY purchase_date;
B. SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_dae< '21-jan-2001'
ORDER BY purchase date ASC;
C. SELECT book_title FROM books WHERE price < 500 OR>900 AND purchase_date DESC;
D. SELECT BOOK_title FROM books WHERE price BETWEEN 500 AND 900 AND purchase_date<'21-JAN-2001' ORDER BY purchase date DESC;
E. SELECT book_title FROM books WHERE (price< 500 OR price> 900 AND purchase date> '21 - JAN-2001') ORDER BY purchase date ASC;
Q. Click the Exhibit button to examine the structure of the EMPOLOYEES, DEPARTMENTS and TAX tables.
EMPLOYEE_ID NUMBER NOT NULL primary key
MGR_ID NUMBER Reference EMPLOYEE_ID Column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID TO column of the DEPARTMENT table
DEPARTMENT_ID NUMBER NOT NULL primary key
MGR_ID NUMBER Reference MGR_ID column of the EMPLOYEES table
For which situation would you use a nonequijoin query?
A. to find the tax percentage for each of the employees
B. to list the name, job id, and manager name for all the employees
C. to find the name, salary and the department name of employees who are not working with Smith
D. to find the number of employees working for the Administrative department and earning less than 4000
E. to display name, salary, manager ID, and department name of all the employees, even if the employees do not have a department ID assigned
Q. Which operator can be used with a multiple row subquery?
D. NOT IN
Q. You need to perform certain data manipulation operations through a view called EMP_DEPT_VU, which you previously created. You want to look at the definition of the view (the SELECT statement on which the view was created) How do you obtain the definition of the view?
A. Use the DESCRIBE command on the EMP_DEPT VU view
B. Use the DEFINE VIEW command on the EMP_DEPT VU view
C. Use the DESCRIBE VIEW command on the EMP_DEPT VU view
D. Query the USER_VIEWS data dictionary view to search for the EMP_DEPT_VU view
E. Query the USER_SOURCE data dictionary view to search for the EMP_DEPT_VU view
F. Query the USER_OBJECTS data dictionary view to search for the EMP_DEPT_VU view
Q. Which statement explicitly names a constraint?
A. ALTER TABLE student_grades ADD FOREIGN KEY (student_id) REFERENCES students (student_id);
B. ALTER TABLE student_grades ADD CONSTRAINT NAME=student_id_fk
FOREIGN KEY (student_id) REFERENCES student(student_id);
C. ALTER TABLE student_grades ADD CONSTRAINT student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id);
D. ALTER TABLE student grades ADD NAMED CONSTRAINT student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id)
F. ALTER TABLE student grades ADD NAME student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id)
Q. You need to display the last names of those employees who have the letter “A” as the second character in their names. Which SQL statement displays the required results?
A. SELECT last_name FROM EMP WHERE last_name LIKE’_A%;
B. SELECT last_name FROM EMP WHERE last name=’*A%
C. SELECT last_name FROM EMP WHERE last name =’* _A%;
D. SELECT last_name FROM EMP WHERE last name LIKE ‘* a%
Q. In which case would you use a FULL OUTER JOIN?
A. Both tables have NULL values
B. You want all unmatched data from one table
C. You want all matched data from both tables
D. You want all unmatched data from both tables
E. One of the tables has more data than the other.
F. You want all matched and unmatched data from only one table.
Q. Which two statements about creating constraints are true? (Choose two)
A. Constraint names must start with SYS_C.
B. All constraints must be defined at the column level
C. Constraints can be created after the table is created
D. Constraints can be created at the same time the table is created
E. Information about constraints is found in the VIEW_CONSTRAINTS dictionary view
Q. Examine the SQL statements that creates ORDERS table:
CREATE TABLE orders
(SER_NO NUMBER UNIQUE,
ORDER_DATE DATE NOT NULL,
STATUS VARCHAR2(10) CHECK (status IN (‘CREDIT’, ‘CASH’)),
PROD_ID NUMBER REFERENCES PRODUCTS (PRODUCT_ID),
PRIMARY KEY (order id, order date));
For which columns would an index be automatically created when you execute the above SQL statement? (Choose two.)
F. Composite index on ORDER_ID and ORDER_DATE
Q. You are granted the CREATE VIEW privilege. What does this allow you to do?
A. create a table view
B. create a view in any scheme
C. create a view in your schema
D. create a sequence view in any schema
E. create a view that is accessible by everyone
F. create a view only if it is based on tables that you created