36 + Interview Questions in Manipulating Data With Select in SQL Server Page 1 InterviewSolution

1.	Which of the constraint can be enforced one per table?(a) Primary key constraint(b) Not Null constraint(c) Foreign Key constraint(d) Check constraintI had been asked this question in class test.My enquiry is from Constraints topic in division Manipulating Data with Select of SQL Server
Answer» The CORRECT option is (a) Primary key constraint The BEST explanation: Each table is having only ONE primary key constraint and it CONTAINS only UNIQUE values.

Discussion

2.	Which of the following foreign key constraint specifies that the deletion fails with an error?(a) NO ACTION(b) CASCADE(c) SET NULL(d) All of the mentionedThis question was posed to me during an online exam.My question is taken from Constraints topic in portion Manipulating Data with Select of SQL Server
Answer» The correct answer is (a) NO ACTION The best EXPLANATION: The ON UPDATE CLAUSE defines the ACTIONS that are taken if you try to update a candidate key value to which existing foreign keys point.

Discussion

3.	How many types of constraints are present in SQL Server?(a) 4(b) 5(c) 6(d) 7This question was posed to me in quiz.My question is taken from Constraints in division Manipulating Data with Select of SQL Server
Answer» Right CHOICE is (c) 6 Best explanation: CONSTRAINTS are Primary KEY, Foreign Key, UNIQUE Key, Not Null, Check, Default.

Discussion

4.	Purpose of foreign key constraint in SQL Server is __________(a) FOREIGN KEY constraints identify and enforce the relationships between tables(b) A foreign key in one table points to a candidate key in another table(c) You cannot insert a row with a foreign key value, except NULL, if there is no candidate key with that value(d) None of the mentionedThis question was addressed to me by my school principal while I was bunking the class.I want to ask this question from Constraints in chapter Manipulating Data with Select of SQL Server
Answer» Right choice is (a) FOREIGN KEY CONSTRAINTS IDENTIFY and enforce the relationships between tables Best explanation: Foreign key is to enforce referential INTEGRITY.

Discussion

5.	Which of the following is not a foreign key constraint?(a) NO ACTION(b) CASCADE(c) SET NULL(d) All of the mentionedI have been asked this question in a national level competition.My doubt is from Constraints in chapter Manipulating Data with Select of SQL Server
Answer» CORRECT answer is (B) CASCADE Easy explanation: Foreign key CONSTRAINTS are the built-in mechanism for enforcing data INTEGRITY.

Discussion

6.	Point out the wrong statement.(a) Table constraints must be used when more than one column must be included in a constraint(b) A column constraint is specified as part of a column definition and applies only to that column(c) A table constraint is declared independently from a column definition and can apply to more than one column in a table(d) Primary keys allow for NULL as one of the unique valuesThe question was asked in examination.This is a very interesting question from Constraints topic in division Manipulating Data with Select of SQL Server
Answer» The correct OPTION is (d) PRIMARY keys allow for NULL as one of the unique values To ELABORATE: Primary keys also enforce UNIQUENESS, but primary keys do not allow for NULL as one of the unique values.

Discussion

7.	Constraints can be applied on ___________(a) Column(b) Table(c) Field(d) All of the mentionedI got this question during an interview for a job.I want to ask this question from Constraints in section Manipulating Data with Select of SQL Server
Answer» Right CHOICE is (d) All of the mentioned The BEST explanation: CONSTRAINTS can be COLUMN constraints or table constraints.

Discussion

8.	Which of the following constraint does not enforce uniqueness?(a) UNIQUE(b) Primary key(c) Foreign key(d) None of the mentionedThis question was addressed to me in a national level competition.My question comes from Constraints topic in section Manipulating Data with Select of SQL Server
Answer» Right answer is (c) Foreign key To EXPLAIN: FOREIGN KEY constraints IDENTIFY and ENFORCE the relationships between tables.

Discussion

9.	Which of the following is not a class of constraint in SQL Server?(a) NOT NULL(b) CHECK(c) NULL(d) UNIQUEThe question was asked in a job interview.My question is from Constraints topic in section Manipulating Data with Select of SQL Server
Answer» RIGHT choice is (C) NULL To elaborate: NOT NULL SPECIFIES that the column does not accept NULL values. For more information.

Discussion

10.	Point out the correct statement.(a) CHECK constraints enforce domain integrity(b) UNIQUE constraints enforce the uniqueness of the values in a set of columns(c) In a UNIQUE constraint, no two rows in the table can have the same value for the columns(d) All of the mentionedThe question was posed to me in my homework.I'm obligated to ask this question of Constraints topic in portion Manipulating Data with Select of SQL Server
Answer» The CORRECT OPTION is (d) All of the mentioned To explain: Constraints LET you define the way the Database Engine automatically ENFORCES the integrity of a database.

Discussion

11.	To alter a database ___________ command is used.(a) ALTER database database_name(b) ALTER database_name(c) ALTER database database_name(d) ALTER database_nameThe question was posed to me in a national level competition.I would like to ask this question from Modifying Data topic in portion Manipulating Data with Select of SQL Server
Answer» Correct CHOICE is (c) ALTER DATABASE database_name The best EXPLANATION: ALTER STATEMENT will alter the database structure and its related functionalities.

Discussion

12.	_______________ joins are SQL server default.(a) Outer(b) Inner(c) Equi(d) None of the MentionedThis question was addressed to me during an interview.The doubt is from Modifying Data topic in section Manipulating Data with Select of SQL Server
Answer» The correct CHOICE is (B) INNER The best I can EXPLAIN: Inner query joins only the rows that are matching.

Discussion

13.	Which is a duplicate copy of a file program that is stored on a different storage media than the original location?(a) Concurrency(b) Deadlock(c) Backup(d) RecoveryThis question was addressed to me during an internship interview.This intriguing question originated from Modifying Data topic in section Manipulating Data with Select of SQL Server
Answer» Correct CHOICE is (C) BACKUP To EXPLAIN: Backup is required to protect the data.

Discussion

14.	The ________________ is essentially used to search for patterns in target string.(a) Like Predicate(b) Null Predicate(c) In Predicate(d) Out PredicateThe question was posed to me in an interview for internship.My query is from Modifying Data in section Manipulating Data with Select of SQL Server
Answer» The CORRECT OPTION is (a) Like Predicate To ELABORATE: Like matches the PATTERN with the query.

Discussion

15.	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, whereThe question was posed to me at a job interview.My enquiry is from Subqueries in chapter Manipulating Data with Select of SQL Server
Answer» Correct choice is (a) Where, from Best explanation: Where selects the ROWS on a PARTICULAR condition. From gives the RELATION which involves the OPERATION.

Discussion

16.	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 mentionedThe question was posed to me by my school teacher while I was bunking the class.The origin of the question is Subqueries in section Manipulating Data with Select of SQL Server
Answer» RIGHT choice is (a) SQL To ELABORATE: Indexing reduces the difficulty in searching the DATA.

Discussion

17.	Which of the following function is used when you want all tied rows to have the same ranking?(a) RANK(b) NTILE(c) ROW_NUMBER(d) None of the mentionedThis question was posed to me during an online exam.This key question is from Ranking in portion Manipulating Data with Select of SQL Server
Answer» CORRECT choice is (a) RANK To elaborate: The numbers ASSIGNED by RANK are not NECESSARILY consecutive.

Discussion

18.	Which of the following functions are similar?(a) RANK and NTILE(b) RANK and DENSE_RANK(c) DENSE_RANK and NTILE(d) None of the mentionedThe question was asked by my school principal while I was bunking the class.The above asked question is from Ranking in chapter Manipulating Data with Select of SQL Server
Answer» CORRECT answer is (B) RANK and DENSE_RANK To explain: DENSE_RANK() is same as RANK() function. Only difference is returns rank WITHOUT gaps.

Discussion

19.	Which of the following error message will be displayed if ORDER CLAUSE is not mentioned in ROW NUMBER function?(a) “The ranking function “ROW_NUMBER” must have an ORDER BY clause.”(b) “The ranking function “ROW_NUMBER” must have an OVER BY clause.”(c) “The ranking function “ROW_NUMBER” must have an PARTITION BY clause.”(d) All of the mentionedThis question was addressed to me during an interview.My question is from Ranking in chapter Manipulating Data with Select of SQL Server
Answer» Correct ANSWER is (a) “The ranking FUNCTION “ROW_NUMBER” must have an ORDER BY CLAUSE.” Explanation: ORDER BY CLAUSE is not OPTIONAL clause in ROW NUMBER function.

Discussion

20.	Which of the following will not raise error if not used?(a) OVER clause(b) ORDER BY clause(c) PARTITION BY clause(d) All of the mentionedThe question was asked during an online exam.Enquiry is from Ranking in section Manipulating Data with Select of SQL Server
Answer» RIGHT choice is (c) PARTITION BY clause Explanation: PARTITION BY clause is not mandatory and when you specify a column/set of columns with PARTITION BY clause then it will DIVIDE the result-set into RECORD groups/partitions.

Discussion

21.	Which of the function provides consecutive numbering except in the case of a tie?(a) RANK(b) NTILE(c) ROW_NUMBER(d) None of the mentionedThe question was asked in an online quiz.My question comes from Ranking topic in portion Manipulating Data with Select of SQL Server
Answer» Correct option is (a) RANK To EXPLAIN I would SAY: RANK FUNCTION is used when you WANT all tied rows to have the same RANKING.

Discussion

22.	Point out the wrong statement.(a) RANK() returns the rank of each row in the result set of partitioned column(b) DENSE_RANK() is same as RANK() function. Only difference is returns rank without gaps(c) NTILE() distributes the columns in an ordered partition into a specified number of groups(d) ROW_NUMBER() returns the serial number of the row order by specified columnI had been asked this question by my college director while I was bunking the class.My question is based upon Ranking in division Manipulating Data with Select of SQL Server
Answer» RIGHT OPTION is (c) NTILE() distributes the columns in an ordered partition into a specified number of GROUPS The best EXPLANATION: NTILE divides the partitioned result set into specified number of groups in an order.

Discussion

23.	Which of the clause is not mandatory?(a) OVER clause(b) ORDER BY clause(c) PARTITION BY clause(d) All of the mentionedThis question was addressed to me in a national level competition.The question is from Ranking topic in portion Manipulating Data with Select of SQL Server
Answer» CORRECT choice is (C) PARTITION BY clause The EXPLANATION: PARTITION BY clause is not mandatory and if you don’t specify it all the RECORDS of the result-set will be considered as a part of single record group.

Discussion

24.	Which of the following is the simplest ranking function?(a) RANK(b) NTILE(c) ROW_NUMBER(d) None of the mentionedI have been asked this question during an internship interview.I want to ask this question from Ranking topic in section Manipulating Data with Select of SQL Server
Answer» Right choice is (c) ROW_NUMBER Explanation: The ROW_NUMBER RANKING function is the SIMPLEST of the ranking functions.Its PURPOSE in life is to provide consecutive numbering of the rows in the result set by the order selected in the OVER clause for each PARTITION SPECIFIED in the OVER clause.

Discussion

25.	Which of the following is not a ranking function?(a) RANK(b) NTILE(c) ROW_NUMBER(d) All of the mentionedI got this question in an interview.Enquiry is from Ranking in chapter Manipulating Data with Select of SQL Server
Answer» The CORRECT option is (d) All of the mentioned The best explanation: Ranking FUNCTIONS are a SUBSET of the built in functions in SQL SERVER.

Discussion

26.	Point out the wrong statement.(a) The ROW_NUMBER function simply assigns sequential numbering to the records of a result-set or to the records within groups of a result-set(b) OVER clause is not required in all the ranking functions(c) SQL Server introduced four different ranking functions(d) All of the mentionedI have been asked this question during an interview.My query is from Ranking topic in portion Manipulating Data with Select of SQL Server
Answer» Right ANSWER is (B) OVER clause is not required in all the ranking functions To elaborate: OVER clause is required in all the ranking functions and with that you specify the partitioning and ORDERING of RECORDS before the ranking functions are EVALUATED.

Discussion

27.	LAST_VALUE comes in the category of _____________(a) Ranking window function(b) Associate window function(c) Analytic window functions(d) All of the mentionedThe question was posed to me in homework.The question is from Windowing topic in division Manipulating Data with Select of SQL Server
Answer» Correct choice is (c) Analytic window FUNCTIONS For EXPLANATION I would say: The analytic window functions are NEW for SQL SERVER 2012.

Discussion

28.	Which of the following function is an analytic window function?(a) LAG(b) LEAD(c) FIRST_VALUE(d) None of the mentionedThis question was addressed to me in an international level competition.This intriguing question originated from Windowing in division Manipulating Data with Select of SQL Server
Answer» Correct CHOICE is (b) LEAD Best explanation: There are 8 analytic WINDOW FUNCTIONS in SQL Server 2012.

Discussion

29.	How many types of window functions are present in SQL Server?(a) 3(b) 4(c) 5(d) 6The question was asked in an interview.This intriguing question originated from Windowing topic in section Manipulating Data with Select of SQL Server
Answer» Correct option is (a) 3 The best EXPLANATION: Window functions are DISTINGUISHED by the presence of an OVER() clause and COME in three VARIETIES.

Discussion

30.	Which of the function is not a ranking window function?(a) RANK(b) NTILE(c) ROW_NUMBER(d) None of the mentionedThis question was addressed to me in quiz.My question comes from Windowing topic in portion Manipulating Data with Select of SQL Server
Answer» RIGHT answer is (a) RANK To explain: SINCE SQL Server 2005 we have had support for some WINDOW functions, they are: ROW_NUMBER, RANK, DENSE_RANK and NTILE.

Discussion

31.	Which of the following is not a type of window function?(a) Ranking window function(b) Associate window function(c) Analytic window functions(d) All of the mentionedThe question was asked in quiz.My question is based upon Windowing in chapter Manipulating Data with Select of SQL Server
Answer» Right answer is (b) ASSOCIATE window function Easiest EXPLANATION: There is an AGGREGATE window function available in SQL SERVER.

Discussion

32.	Point out the wrong statement related to window function.(a) They are used to provide details of a backup plans(b) They are used to provide details of an aggregation(c) They are used to provide details of an aggregation(d) None of the mentionedI had been asked this question in an internship interview.This is a very interesting question from Windowing in division Manipulating Data with Select of SQL Server
Answer» Correct option is (B) They are USED to provide details of an aggregation The best EXPLANATION: WINDOW functions belong to a type of function known as a ‘SET function.

Discussion

33.	Which is the most important benefit of window function?(a) Aggregation(b) Association(c) Generalization(d) All of the mentionedI have been asked this question by my college director while I was bunking the class.I'd like to ask this question from Windowing in chapter Manipulating Data with Select of SQL Server
Answer» CORRECT choice is (a) Aggregation Best explanation: Most important benefit of WINDOW FUNCTIONS is that we can access the detail of the ROWS from an aggregation.

Discussion

34.	Windowing function was added in which of the following versions of SQL Server?(a) 2003(b) 2005(c) 2008(d) None of the mentionedI have been asked this question in a national level competition.My doubt is from Windowing topic in chapter Manipulating Data with Select of SQL Server
Answer» Right OPTION is (c) 2008 Explanation: SQL Server has had only a partial IMPLEMENTATION up to now, but it has COME up in SQL 2012.

Discussion

35.	Point out the correct statement.(a) Windowing functions were added to the standard SQL:2003(b) Windowing functions were added to the standard SQL:2008(c) Windowing functions were added to the standard SQL:2012(d) Windowing functions were added to the standard SQL:2014The question was posed to me in an online interview.Query is from Windowing topic in portion Manipulating Data with Select of SQL Server
Answer» RIGHT option is (a) Windowing functions were ADDED to the STANDARD SQL:2003 Best explanation: Windowing functions were added to the standard SQL:2003 that is managed by the ISO and it was SPECIFIED in more detail in SQL:2008.

Discussion

36.	Which of the following is a set function?(a) RANK(b) NTILE(c) Window(d) All of the mentionedThe question was posed to me during an interview.This intriguing question originated from Windowing topic in chapter Manipulating Data with Select of SQL Server
Answer» Right choice is (d) All of the mentioned To explain: The word ‘window’ is used to refer to the SET of ROWS that the FUNCTION works on.

Discussion

Explore topic-wise InterviewSolutions in .

Which of the constraint can be enforced one per table?(a) Primary key constraint(b) Not Null constraint(c) Foreign Key constraint(d) Check constraintI had been asked this question in class test.My enquiry is from Constraints topic in division Manipulating Data with Select of SQL Server

How many types of constraints are present in SQL Server?(a) 4(b) 5(c) 6(d) 7This question was posed to me in quiz.My question is taken from Constraints in division Manipulating Data with Select of SQL Server

Which of the following is not a foreign key constraint?(a) NO ACTION(b) CASCADE(c) SET NULL(d) All of the mentionedI have been asked this question in a national level competition.My doubt is from Constraints in chapter Manipulating Data with Select of SQL Server

Constraints can be applied on ___________(a) Column(b) Table(c) Field(d) All of the mentionedI got this question during an interview for a job.I want to ask this question from Constraints in section Manipulating Data with Select of SQL Server

Which of the following constraint does not enforce uniqueness?(a) UNIQUE(b) Primary key(c) Foreign key(d) None of the mentionedThis question was addressed to me in a national level competition.My question comes from Constraints topic in section Manipulating Data with Select of SQL Server

Which of the following is not a class of constraint in SQL Server?(a) NOT NULL(b) CHECK(c) NULL(d) UNIQUEThe question was asked in a job interview.My question is from Constraints topic in section Manipulating Data with Select of SQL Server

_______________ joins are SQL server default.(a) Outer(b) Inner(c) Equi(d) None of the MentionedThis question was addressed to me during an interview.The doubt is from Modifying Data topic in section Manipulating Data with Select of SQL Server

The ________________ is essentially used to search for patterns in target string.(a) Like Predicate(b) Null Predicate(c) In Predicate(d) Out PredicateThe question was posed to me in an interview for internship.My query is from Modifying Data in section Manipulating Data with Select of SQL Server

Which of the following function is used when you want all tied rows to have the same ranking?(a) RANK(b) NTILE(c) ROW_NUMBER(d) None of the mentionedThis question was posed to me during an online exam.This key question is from Ranking in portion Manipulating Data with Select of SQL Server

Which of the following will not raise error if not used?(a) OVER clause(b) ORDER BY clause(c) PARTITION BY clause(d) All of the mentionedThe question was asked during an online exam.Enquiry is from Ranking in section Manipulating Data with Select of SQL Server

Which of the function provides consecutive numbering except in the case of a tie?(a) RANK(b) NTILE(c) ROW_NUMBER(d) None of the mentionedThe question was asked in an online quiz.My question comes from Ranking topic in portion Manipulating Data with Select of SQL Server

Which of the clause is not mandatory?(a) OVER clause(b) ORDER BY clause(c) PARTITION BY clause(d) All of the mentionedThis question was addressed to me in a national level competition.The question is from Ranking topic in portion Manipulating Data with Select of SQL Server

Which of the following is the simplest ranking function?(a) RANK(b) NTILE(c) ROW_NUMBER(d) None of the mentionedI have been asked this question during an internship interview.I want to ask this question from Ranking topic in section Manipulating Data with Select of SQL Server

Which of the following is not a ranking function?(a) RANK(b) NTILE(c) ROW_NUMBER(d) All of the mentionedI got this question in an interview.Enquiry is from Ranking in chapter Manipulating Data with Select of SQL Server

LAST_VALUE comes in the category of _____________(a) Ranking window function(b) Associate window function(c) Analytic window functions(d) All of the mentionedThe question was posed to me in homework.The question is from Windowing topic in division Manipulating Data with Select of SQL Server

Which of the following function is an analytic window function?(a) LAG(b) LEAD(c) FIRST_VALUE(d) None of the mentionedThis question was addressed to me in an international level competition.This intriguing question originated from Windowing in division Manipulating Data with Select of SQL Server

How many types of window functions are present in SQL Server?(a) 3(b) 4(c) 5(d) 6The question was asked in an interview.This intriguing question originated from Windowing topic in section Manipulating Data with Select of SQL Server

Which of the function is not a ranking window function?(a) RANK(b) NTILE(c) ROW_NUMBER(d) None of the mentionedThis question was addressed to me in quiz.My question comes from Windowing topic in portion Manipulating Data with Select of SQL Server

Which of the following is not a type of window function?(a) Ranking window function(b) Associate window function(c) Analytic window functions(d) All of the mentionedThe question was asked in quiz.My question is based upon Windowing in chapter Manipulating Data with Select of SQL Server

Windowing function was added in which of the following versions of SQL Server?(a) 2003(b) 2005(c) 2008(d) None of the mentionedI have been asked this question in a national level competition.My doubt is from Windowing topic in chapter Manipulating Data with Select of SQL Server

Which of the following is a set function?(a) RANK(b) NTILE(c) Window(d) All of the mentionedThe question was posed to me during an interview.This intriguing question originated from Windowing topic in chapter Manipulating Data with Select of SQL Server