This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Subqueries”.
1. Select __________ from instructor where dept name= ’Comp. Sci.’;
Which of the following should be used to find the mean of the salary ?
Explanation:Avg() is used to find the mean of the values.
2. 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
Explanation:In checks if the query has the value but not in checks if it does not have the value.
3. Select ID, GPA from student grades order by GPA ____________ ;
Inorder to give only 10 rank on the whole we should use :
a) Limit 10
b) Upto 10
c) Only 10
d) Max 10
Explanation:Limit clause does not support partitioning, so we cannot get the top n within each partition without performing ranking; further, if more than one student gets the same GPA, it is possible that one is included in the top 10, while another is excluded.
4. Suppose we are given a view tot credits (year, num credits) giving the total number of credits taken by students in each year.The query that computes averages over the 3 preceding tuples in the specified sort order is :
Select year, avg(num credits) over (order by year rows 3 preceding) as avg total credits from tot credits;
Select year, avg(num credits) over (order by year rows 3 unbounded preceding) as avg total credits from tot credits;
Select year, min(num credits) over (order by year rows 3 unbounded preceding) as avg total credits from tot credits;
Select year, SUM(num credits) over (order by year rows 3 unbounded preceding) as avg total credits from tot credits;View Answer
Explanation:Suppose that instead of going back a fixed number of tuples, we want the window to consist of all prior years we use rows unbounded preceding.
5. Which of the following is not the function of client ?
a) Compile queries
b) Query optimization
c) Receive queries
d) Result formatting and presentation
Explanation: Query optimization is used to improve the quality.
6. Which server can join the indexes when only multiple indexes combined can cover the query ?
d) All of the mentioned
Explanation:Indexing reduces the difficulty in searching the data.
7. Select ________ dept_name from instructor;
Here which of the following displays the unique values of the column ?
Explanation:Distinct keyword selects only the entries that are unique.
8. Select ID, name, dept name, salary * 1.1 where instructor;
The query given below will not give an error. Which one of the following has to be replaced to get the desired output ?
Explanation:Where selects the rows on a particular condition.From gives the relation which involves the operation. Since Instructor is a relation it has to have from clause.
9. 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) All of the mentioned
Explanation:Join can be replaced by inner join.
10. The ______ clause allows us to select only those rows in the result relation of the ____ clause that satisfy a specified predicate.
a) Where, from
b) From, select
c) Select, from
d) From, where
Explanation:Where selects the rows on a particular condition.From gives the relation which involves the operation.
Sanfoundry Global Education & Learning Series – SQL Server.