Total Pageviews

November 16, 2015

11/16/2015 07:54:00 PM
1)

Examine the data in the EMPLOYEES table:
LAST_NAME    DEPARTMENT_ID     SALARY  
Getz                       10                              3000
Davis                     20                               1500
King                      20                                2200
Davis                     30                               5000 ...
 Which three sub queries work? (Choose three)
A)SELECT   *
  FROM   employees
 WHERE   salary > (  SELECT   MIN (salary)
                       FROM   employees
                   GROUP BY   department.id);
B. SELECT   *
  FROM   employees
 WHERE   salary = (  SELECT   AVG (salary)
                       FROM   employees
                   GROUP BY   department_id);

C. SELECT   DISTINCT department_id
  FROM   employees
 WHERE   salary > ANY (  SELECT   AVG (salary)
                           FROM   employees
                       GROUP BY   department_id);
D. SELECT   DISTINCT department_id
  FROM   employees
 WHERE   salary > ANY (  SELECT   AVG (salary)
                           FROM   employees
                       GROUP BY   department_id);
E) SELECT   last_name
  FROM   employees
 WHERE   salary > ANY (  SELECT   MAX (salary)
                           FROM   employees
                       GROUP BY   department_id);

f) SELECT   department_id
  FROM   employees
 WHERE   salary > ALL (  SELECT   AVG (salary)
                           FROM   employees
                       GROUP BY   AVG (SALARY));

2)
The database administrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table. As a user of the database, you created a table called HR in your schema. What happens when you execute this query?

SELECT * FROM HR;

A. You obtain the results retrieved from the public synonym HR created by the database administrator.
B. You obtain the results retrieved from the HR table that belongs to your schema.
C. You get an error message because you cannot retrieve from a table that has the same name as a public synonym.
D. You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a Cartesian product.
E. You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a FULL JOIN.



3)
Which two statements about views are true? (Choose two.)
A. A view can be created as read only.
 B. A view can be created as a join on two or more tables.
C. A view cannot have an ORDER BY clause in the SELECT statement.
D. A view cannot be created with a GROUP BY clause in the SELECT statement.
E. A view must have aliases defined for the column names in the SELECT statement.

4)
Which three are DATETIME data types that can be used when specifying column definitions? (Choose three.)
A. TIMESTAMP
B. INTERVAL MONTH TO DAY
C. INTERVAL DAY TO SECOND
D. INTERVAL YEAR TO MONTH
E. TIMESTAMP WITH DATABASE TIMEZONE

5)Which SQL statement defines the FOREIGN KEY constraint on the DEPTNO column of the EMP table

A. CREATE TABLE EMP (empno NUMBER(4), ename VARCNAR2(35), deptno NUMBER(7,2) NOT NULL CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno);
B. CREATE TABLE EMP (empno NUMBER(4), ename VARCNAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
C. CREATE TABLE EMP (empno NUMBER(4) ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));
D. CREATE TABLE EMP (empno NUMBER(4), ename VARCNAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp deptno fk REFERENCES dept (deptno));


6)
Which data dictionary table should you query to view the object privileges granted to the user on specific columns?
A. USER_TAB_PRIVS_MADE
B. USER_TAB_PRIVS
C. USER_COL_PRIVS_MADE
D. USER_COL_PRIVS


7)
The EMP table contains these columns:
LAST NAME   VARCHAR2(25)
SALARY    NUMBER(6,2)
DEPARTMENT_ID  NUMBER(6)
You need to display the employees who have not been assigned to any department. You write the SELECT statement: SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP WHERE DEPARTMENT_ID = NULL; 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.

8)
For which two constraints does the Oracle Server implicitly create a unique index? (Choose two.) A. NOT NULL
B. PRIMARY KEY
C. FOREIGN KEY
D. CHECK E. UNIQUE

9)
Which two are true about aggregate functions? (Choose two.)
A. You can use aggregate functions in any clause of a SELECT statement.
B. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.
C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
D. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.
E. You can use aggregate functions on a table, only by grouping the whole table as one single group. F. You cannot group the rows of a table by more than one column while using aggregate functions.

10)In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed in the SELECT statement?
A. Immediately after the SELECT clause
B. Before the WHERE clause
C. Before the FROM clause
D. After the ORDER BY clause
E. After the WHERE clause
 
Related Posts Plugin for WordPress, Blogger...