1. Aggregate functions are functions that take a ___________ as input and return a single value.
a) Collection of values
b) Single value
c) Aggregate value
d) Both Collection of values & Single value
Answer: a
2. SELECT __________FROM instructor WHERE dept name= ’Comp. Sci.’;
Which of the following should be used to find the mean of the salary?
a) Mean(salary)
b) Avg(salary)
c) Sum(salary)
d) Count(salary)
Answer: b
3. SELECT COUNT (____ ID) FROM teaches WHERE semester = ’Spring’ AND YEAR = 2010;
If we do want to eliminate duplicates, we use the keyword ______ in the aggregate expression.
a) Distinct
b) Count
c) Avg
d) Primary key
Answer: a
4. All aggregate functions except _____ ignore null values in their input collection.
a) Count (attribute)
b) Count(*)
c) Avg
d) Sum
Answer: b
5. The EXISTS keyword will be true if:
a) Any row in the subquery meets the condition only
b) All rows in the subquery fail the condition only
c) Both of these two conditions are met
d) Neither of these two conditions is met
Answer: a
6. The ____ connective tests for set membership, where the set is a collection of values produced by a select clause. The ____ connective tests for the absence of set membership.
a) Or, in
b) Not in, in
c) In, not in
d) In, or
Answer: c
7. Which of the following should be used to find all the courses taught in the Fall 2009 semester but not in the Spring 2010 semester.
a) SELECT DISTINCT course id FROM SECTION WHERE semester = ’Fall’ AND YEAR= 2009 AND course id NOT IN (SELECT course id FROM SECTION WHERE semester = ’Spring’ AND YEAR= 2010);
b) SELECT DISTINCT course_id FROM instructor WHERE name NOT IN (’Fall’, ’Spring’);
c) (SELECT course idFROM SECTIONWHERE semester = ’Spring’ AND YEAR= 2010)
d) SELECT COUNT (DISTINCT ID) FROM takesWHERE (course id, sec id, semester, YEAR) IN (SELECT course id, sec id, semester, YEARFROM teachesWHERE teaches.ID= 10101);
Answer: a
8. How many tables may be included with a join?
a) One
b) Two
c) Three
d) All the above
Answer: d
9. The SQL WHERE clause
a) Limits the column data that are returned
b) Limits the row data are returned
c) Both A and B are correct.
d) Neither a or b is correct
Answer: b
10. We can test for the nonexistence of tuples in a subquery by using the _____ construct.
a) Not exist
b) Not exists
c) Exists
d) Exist
Answer: b
11.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)
JOB_CAT VARCHAR (30)
SALARY NUMBER (8, 2)
Which statement shows the department ID, minimum salary, and maximum salary paid in that department, only if the minimum salary is less than 5000 and maximum salary is more than 15000?
a) SELECT dept_id, MIN (salary), MAX (salary) FROM employees WHERE MIN(salary) < 5000 AND MAX (salary) > 15000;
b) SELECT dept_id, MIN (salary), MAX (salary) FROM employees WHERE MIN (salary) < 5000 AND MAX (salary) 15000 GROUP BY dept_id;
c) SELECT dept_id, MIN(salary), MAX(salary) FROM employees HAVING MIN (salary) < 5000 AND MAX (salary)
d) SELECT dept_id, MIN (salary), MAX (salary) FROM employees GROUP BY dept_id HAVING MIN(salary) < 5000 AND MAX (salary) > 15000
E.SELECT dept_id, MIN (salary), MAX (salary) FROM employees GROUP BY dept_id, salary HAVING MIN (salary) < 5000 AND MAX (salary) > 15000;
ANS: D
12.The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4) ENAME VARCHAR2 (25) JOB_ID VARCHAR2(10) Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter “a” in the ENAME column, for those employees whose ENAME ends with a the letter “n”?
a) SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ‘a’) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = ‘n’;
b) SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = ‘n’;
c) SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, 1, 1) = ‘n’;
d) SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, -1, 1) = ‘n’;
ANS: A
13.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’) FROM dual;
b) SELECT TO_CHAR (SYSDATE, ‘FMDD, DY Month, YYYY’) FROM dual;
c) SELECT TO_CHAR (SYSDATE, ‘FMDay, DD Month, YYYY’) FROM dual;
d) SELECT TO_CHAR (SYSDATE, ‘FMDY, DDD Month, YYYY’) FROM dual;
e) SELECT TO_DATE (SYSDATE, ‘FMDY, DDD Month, YYYY’) FROM dual;
Ans: C
14. What is true about joining tables through an Equijoin?
a) You can join a maximum of two tables through an Equijoin.
b) You can join a maximum of two columns through an Equijoin.
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.
Ans. E
5. Which three is true regarding the use of outer joins?
A-You cannot use IN operator in a condition that involves an outer join.
B-You use (+) on both sides of the WHERE condition to perform an outer join.
C-You use (*) on both sides of the WHERE condition to perform an outer join.
D-You use an outer join to see only the rows that do not meet the join condition.
E-In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outer join.
F-You cannot link a condition that is involved in an outer join to another condition by using the OR operator.
a)A,B,C
b)B,C,D
c)D,E,F
d)A,E,F
Ans: d
16. Evaluate the SQL statement:
SELECT ROUND (TRUNC (MOD (1600, 10),-1), 2) FROM dual;
What will be displayed?
a) 0
b) 1
c) 0.00
d) an error statement
Ans A