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;
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.
To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.