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
Explanation: None.
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
Explanation: Avg() is used to find the mean of the values.
advertisement
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
Explanation: Distinct keyword is used to select only unique items from the relation.
4. All aggregate functions except _____ ignore null values in their input collection.
a) Count(attribute)
b) Count(*)
c) Avg
d) Sum
Answer: b
Explanation: * is used to select all values including null.
5. A Boolean data type that can take values true, false, and________
a) 1
b) 0
c) Null
d) Unknown
Answer: d
Explanation: Unknown values do not take null value but it is not known.
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
Explanation: In checks, if the query has the value but not in checks if it does not have the value.
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 id
FROM SECTION
WHERE semester = ’Spring’ AND YEAR= 2010)
d)
SELECT COUNT (DISTINCT ID)
FROM takes
WHERE (course id, sec id, semester, YEAR) IN (SELECT course id, sec id, semester, YEAR
FROM teaches
WHERE teaches.ID= 10101);
Answer: a
Explanation: None.
8. The phrase “greater than at least one” is represented in SQL by _____
a) < all
b) < some
c) > all
d) > some
Answer: d
Explanation: >some takes atlest one value above it .
9. Which of the following is used to find all courses taught in both the Fall 2009 semester and in the Spring 2010 semester .
a)
SELECT course id
FROM SECTION AS S
WHERE semester = ’Fall’ AND YEAR= 2009 AND
EXISTS (SELECT *
FROM SECTION AS T
WHERE semester = ’Spring’ AND YEAR= 2010 AND
S.course id= T.course id);
b)
SELECT name
FROM instructor
WHERE salary > SOME (SELECT salary
FROM instructor
WHERE dept name = ’Biology’);
c)
SELECT COUNT (DISTINCT ID)
FROM takes
WHERE (course id, sec id, semester, YEAR) IN (SELECT course id, sec id, semester, YEAR
FROM teaches
WHERE teaches.ID= 10101);
d)
(SELECT course id
FROM SECTION
WHERE semester = ’Spring’ AND YEAR= 2010)
Answer: a
Explanation: None.
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
Explanation: Exists is used to check for the existence of tuples.
11.
SELECT dept_name, ID, avg (salary)
FROM instructor
GROUP BY dept_name;
This statement IS erroneous because
a) Avg(salary) should not be selected
b) Dept_id should not be used in group by clause
c) Misplaced group by clause
d) Group by clause is not valid in this query
Answer: b
Explanation: Any attribute that is not present in the group by clause must appear only inside an aggregate function if it appears in the select clause, otherwise the query is treated as erroneous.
advertisement
12. SQL applies predicates in the _______ clause after groups have been formed, so aggregate functions may be used.
a) Group by
b) With
c) Where
d) Having
Answer: b
Explanation: The with clause provides away of defining a temporary relation whose definition is available only to the query in which the with clause occurs.
13. Aggregate functions can be used in the select list or the_______clause of a select statement or subquery. They cannot be used in a ______ clause.
a) Where, having
b) Having, where
c) Group by, having
d) Group by, where
Answer: b
Explanation: To include aggregate functions having clause must be included after where.
14. The ________ keyword is used to access attributes of preceding tables or subqueries in the from clause.
a) In
b) Lateral
c) Having
d) With
Answer: b
Explanation:
Eg : SELECT name, salary, avg salary
FROM instructor I1, lateral (SELECT avg(salary) AS avg salary
FROM instructor I2
WHERE I2.dept name= I1.dept name);
Without the lateral clause, the subquery cannot access the correlation variable
I1 from the outer query.
15. Which of the following creates a temporary relation for the query on which it is defined?
a) With
b) From
c) Where
d) Select
Answer: a
Explanation: The with clause provides a way of defining a temporary relation whose definition is available only to the query in which the with clause occurs.
16.
WITH max_budget (VALUE) AS
(SELECT MAX(budget)
FROM department)
SELECT budget
FROM department, max_budget
WHERE department.budget = MAX budget.value;
In the query given above which one of the following is a temporary relation?
a) Budget
b) Department
c) Value
d) Max_budget
Answer: d
Explanation: With clause creates a temporary relation.
17. Subqueries cannot:
a) Use group by or group functions
b) Retrieve data from a table different from the one in the outer query
c) Join tables
d) Appear in select, update, delete, insert statements.
Answer: c
Explanation: None.
18. Which of the following is not an aggregate function?
a) Avg
b) Sum
c) With
d) Min
Answer: c
Explanation: With is used to create temporary relation and its not an aggregate function.
19. 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
Explanation: EXISTS keyword checks for existance of a condition.
20. How can you find rows that do not match some specified condition?
a) EXISTS
b) Double use of NOT EXISTS
c) NOT EXISTS
d) None of the mentioned
Answer: b
Explanation: None.
21. A Delete command operates on ______ relation.
a) One
b) Two
c) Several
d) Null
Answer: a
Explanation: Delete can delete from only one table at a time.
22.
Delete from r where P;
The above command
a) Deletes a particular tuple from the relation
b) Deletes the relation
c) Clears all entries from the relation
d) All of the mentioned
Answer: a
Explanation: Here P gives the condition for deleting specific rows.
advertisement
23. Which one of the following deletes all the entries but keeps the structure of the relation.
a) Delete from r where P;
b) Delete from instructor where dept name= ’Finance’;
c) Delete from instructor where salary between 13000 and 15000;
d) Delete from instructor;
Answer: d
Explanation: Absence of condition deletes all rows.
24. Which of the following is used to insert a tuple from another relation?
a)
INSERT INTO course (course id, title, dept name, credits)
VALUES (’CS-437’, ’DATABASE Systems’, ’Comp. Sci.’, 4);
b)
INSERT INTO instructor
SELECT ID, name, dept name, 18000
FROM student
WHERE dept name = ’Music’ AND tot cred > 144;
c)
INSERT INTO course VALUES (’CS-437’, ’DATABASE Systems’, ’Comp. Sci.’, 4);
d) Not possible
Answer: b
Explanation: Using select statement in insert will include rows which are the result of the selection.
25. Which of the following deletes all tuples in the instructor relation for those instructors associated with a department located in the Watson building which is in department relation.
a)
DELETE FROM instructor
WHERE dept_name IN ‘Watson’;
b)
DELETE FROM department
WHERE building=’Watson’;
c)
DELETE FROM instructor
WHERE dept_name IN (SELECT dept name
FROM department
WHERE building = ’Watson’);
d) None of the mentioned
Answer: c
Explanation: The query must include building=watson condition to filter the tuples.
26.
UPDATE instructor
_____ salary= salary * 1.05;
Fill in with correct keyword to update the instructor relation.
a) Where
b) Set
c) In
d) Select
Answer: b
Explanation: Set is used to update the particular value.
27. _________ are useful in SQL update statements, where they can be used in the set clause.
a) Multiple queries
b) Sub queries
c) Update
d) Scalar subqueries
Answer: d
Explanation: None.
28. The problem of ordering the update in multiple updates is avoided using
a) Set
b) Where
c) Case
d) When
Answer: c
Explanation: The case statements can add the order of updating tuples.
29. Which of the following is the correct format for case statements.
a)
CASE
WHEN pred1 … result1
WHEN pred2 … result2
. . .
WHEN predn … resultn
ELSE result0
END
b)
CASE
WHEN pred1 THEN result1
WHEN pred2 THEN result2
. . .
WHEN predn THEN resultn
ELSE result0
END
c)
CASE
WHEN pred1 THEN result1
WHEN pred2 THEN result2
. . .
WHEN predn THEN resultn
ELSE result0
d) All of the mentioned
Answer: b
Explanation: None.
30. Which of the following relation updates all instructors with salary over $100,000 receive a 3 percent raise, whereas all others receive a 5 percent raise.
a)
UPDATE instructor
SET salary = salary * 1.03
WHERE salary > 100000;
UPDATE instructor
SET salary = salary * 1.05
WHERE salary <= 100000;
b)
UPDATE instructor
SET salary = salary * 1.05
WHERE salary < (SELECT avg (salary)
FROM instructor);
c)
UPDATE instructor
SET salary = CASE
WHEN salary <= 100000 THEN salary * 1.03
ELSE salary * 1.05
END
d) None of the mentioned
Answer: a
Explanation: The order of the two update statements is important. If we changed the order of the two statements, an instructor with a salary just under $100,000 would receive an over 8 percent raise. SQL provides a case construct that we can use to perform both the updates with a single update statement, avoiding the problem with the order of updates.
31. The____condition allows a general predicate over the relations being joined.
a) On
b) Using
c) Set
d) Where
Answer: a
Explanation: On gives the condition for the join expression.
32. Which of the join operations do not preserve non matched tuples?
a) Left outer join
b) Right outer join
c) Inner join
d) Natural join
Answer: c
Explanation: INNER JOIN: Returns all rows when there is at least one match in BOTH tables.
33.
advertisement
SELECT *
FROM student JOIN takes USING (ID);
The above query is equivalent to
a)
SELECT *
FROM student INNER JOIN takes USING (ID);
b)
SELECT *
FROM student OUTER JOIN takes USING (ID);
c)
SELECT *
FROM student LEFT OUTER JOIN takes USING (ID);
d) None of the mentioned
Answer: a
Explanation: Join can be replaced by inner join.
34. What type of join is needed when you wish to include rows that do not have matching values?
a) Equi-join
b) Natural join
c) Outer join
d) All of the mentioned
Answer: c
Explanation: An outer join does not require each record in the two joined tables to have a matching record..
35. How many tables may be included with a join?
a) One
b) Two
c) Three
d) All of the mentioned
Answer: d
Explanation: Join can combine multiple tables.
36. Which are the join types in join condition:
a) Cross join
b) Natural join
c) Join with USING clause
d) All of the mentioned
Answer: d
Explanation: There are totally four join types in SQL.
37. How many join types in join condition:
a) 2
b) 3
c) 4
d) 5
Answer: d
Explanation: Types are inner join, left outer join, right outer join, full join, cross join.
38. Which join refers to join records from the right table that have no matching key in the left table are include in the result set:
a) Left outer join
b) Right outer join
c) Full outer join
d) Half outer join
Answer: b
Explanation: RIGHT OUTER JOIN: Return all rows from the right table and the matched rows from the left table.
39. The operation which is not considered a basic operation of relational algebra is
a) Join
b) Selection
c) Union
d) Cross product
Answer: a
Explanation: None.
40. In SQL the statement select * from R, S is equivalent to
a) Select * from R natural join S
b) Select * from R cross join S
c) Select * from R union join S
d) Select * from R inner join S
Answer: b
Explanation: None.