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?
a) Mean(salary)
b) Avg(salary)
c) Sum(salary)
d) Count(salary)
View Answer
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
View Answer
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
View Answer
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 _________
a)
SELECT YEAR, avg(num credits) OVER (ORDER BY YEAR ROWS 3 preceding) AS avg total credits FROM tot credits;
b)
SELECT YEAR, avg(num credits) OVER (ORDER BY YEAR ROWS 3 unbounded preceding) AS avg total credits FROM tot credits;
c)
SELECT YEAR, MIN(num credits) OVER (ORDER BY YEAR ROWS 3 unbounded preceding) AS avg total credits FROM tot credits;
d)
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
View Answer
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?
a) SQL
b) DBMS
c) RDBMS
d) All of the mentioned
View Answer
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?
a) All
b) From
c) Distinct
d) Name
View Answer
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?
a) Salary*1.1
b) ID
c) Where
d) Instructor
View Answer
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
View Answer
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
View Answer
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.
- Apply for Programming Internship
- Practice Programming MCQs
- Check Information Technology Books
- Check SQL Server Books