Oracle SQL 9i, Databases

Question: Which of the following statements are correct with regard to WHERE and HAVING clauses?
A
A WHERE clause can be used to restrict both rows and groups

B
A WHERE clause can be used to restrict rows only

C
A HAVING clause can be used to restrict both rows and groups

D
A HAVING clause can be used to restrict groups only

E
A HAVING clause CANNOT be used in Subqueries

Note: Not available
1. Report
Question: Examine the description of the STUDENTS table: STD_ID NUMBER (4) COURSE_ID VARCHAR2 (10) START_DATE DATE END_DATE DATE The aggregate functions valid on the START_DATE columns are:
A
SUM(start_date)

B
AVG(start_date)

C
COUNT(start_date)

D
AVG(start_date, end_date)

E
MIN(start_date)

Note: Not available
1. Report
Question: Examine the data in the EMPLOYEES table given below: LAST_NAME �DEPARTMENT_ID SALARY ALLEN 10 3000 MILLER 20 1500 King 20 2200 Davis 30 5000 Which of the following Subqueries work?
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 department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);

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

Note: Not available
1. Report
Question: You have to calculate the value 12* salary* commission_pct for all the employees in the EMP table. Which of the following statements ensures that a value is displayed in the calculated column for all the employees?
A
SELECT last_name, 12 * salary* commission_pct FROM emp;

B
SELECT last_name, 12 * salary* (commission_pct,0) FROM emp;

C
SELECT last_name, 12 * salary* (nvl(commission_pct,0) )FROM emp;

D
SELECT last_name, 12 * salary* (decode(commission_pct,0)) FROM emp;

Note: Not available
1. Report
Question: Evaluate the following 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 all the parentheses are removed 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

Note: Not available
1. Report
Question: Which of the following is an iSQL*Plus command?
A
INSERT

B
UPDATE

C
SELECT

D
DESCRIBE

E
DELETE

Note: Not available
1. Report
Question: Examine the code given below: SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000 Which of the following statement is correct with regard to this code?
A
It returns employees who have 50% of the salary greater than $23,000:

B
It returns employees who have 50% commission rate or salary greater than $23,000:

C
It returns employees who have 50% of salary less than $23,000:

D
None of these above

Note: Not available
1. Report
Question: Examine the two SQL statements given below: 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 while first will be executed successfully

C
The second statement will return top 2 records having maximum salary, while first statement will return all records

D
There is no need to specify DESC because the results are sorted in descending order by default

Note: Not available
1. Report
Question: What will happen if you query the emp table shown below: select empno,DISTINCT ename,Salary from emp;
A
EMPNO ,unique value of ENAME and then SALARY are Displayed.

B
EMPNO ,unique value of the two columns, ENAME and salary are displayed.

C
DISTINCT is not a valid keyword in SQL.

D
No values will be displayed because the statement will return an error

Note: Not available
1. Report
Question: How many join conditions should be there to avoid a Cartesion Join for joining three tables?
A
1

B
2

C
3

D
None of these

Note: Not available
1. Report

First Prev 1 2 3 4 Next Last

Question: Which of the following statements are correct with regard to WHERE and HAVING clauses?

Question: Examine the description of the STUDENTS table: STD_ID NUMBER (4) COURSE_ID VARCHAR2 (10) START_DATE DATE END_DATE DATE The aggregate functions valid on the START_DATE columns are:

Question: Examine the data in the EMPLOYEES table given below: LAST_NAME �DEPARTMENT_ID SALARY ALLEN 10 3000 MILLER 20 1500 King 20 2200 Davis 30 5000 Which of the following Subqueries work?

Question: You have to calculate the value 12* salary* commission_pct for all the employees in the EMP table. Which of the following statements ensures that a value is displayed in the calculated column for all the employees?

Question: Evaluate the following 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 all the parentheses are removed from the calculation?

Question: Which of the following is an iSQL*Plus command?

Question: Examine the code given below: SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000 Which of the following statement is correct with regard to this code?

Question: Examine the two SQL statements given below: 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?

Question: What will happen if you query the emp table shown below: select empno,DISTINCT ename,Salary from emp;

Question: How many join conditions should be there to avoid a Cartesion Join for joining three tables?