set 3

 

 

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.

Leave a Comment