Explore topic-wise InterviewSolutions in .

The CORRECT answer is (d) Get-ChildItem

To explain I WOULD say: Get-ChildItem is USED to get the contents of a folder or REGISTRY KEY.

Correct option is (B) Not EXISTS

The EXPLANATION: Exists is used to check for the EXISTENCE of tuples.

Right option is (c) With

For explanation: With is USED to CREATE temporary relation and its not an AGGREGATE FUNCTION.

The correct option is (b) Lateral

The explanation: 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.

Correct answer is (b) With

To EXPLAIN I would say: 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.

Correct CHOICE is (a) Collection of values

For explanation: Aggregate FUNCTIONS PERFORM a calculation on a set of values.

Correct ANSWER is (d) None of the mentioned

Explanation: Server-scoped DYNAMIC MANAGEMENT VIEW is stored only in the Master DATABASE.

Right option is (d) sys.dm_exec_connections

Explanation: The INFORMATION_SCHEMA views allow you to retrieve METADATA about the objects WITHIN a database.These views can be found in the master database under Views / System Views and be called from any database in your SQL SERVER instance.

Right answer is (b) These are stored only in the Master database

The explanation is: SCHEMABINDING binds the view to the schema of the underlying table or TABLES. When SCHEMABINDING is SPECIFIED, the base table or tables cannot be modified in a WAY that would affect the view definition.

Right option is (a) DROP VIEW

To ELABORATE: DROP VIEW REMOVES one or more views from the CURRENT database.

The correct ANSWER is (a) System DEFINED VIEWS

To explain: Dynamic Management Views were introduced in SQL Server 2005. These Views give the administrator information of the DATABASE about the current STATE of the SQL Server machine.

The correct OPTION is (a) CREATE VIEW AS SELECT

Explanation: SQL CREATE VIEW Syntax:CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition.

Correct ANSWER is (d) All of the Mentioned

The explanation is: Views are a valuable tool for the SQL Server DEVELOPER because they hide COMPLEXITY and allow for a readable style of SQL EXPRESSION.

Correct answer is (B) A view is a virtual table which results of executing a pre-compiled query

The best explanation: VIEW is a virtual table, through which a selective PORTION of the DATA from one or more tables can be SEEN. A view do not contain data of their own.

Right answer is (b) INDEX Created on View Used OFTEN

Best explanation: VIEWS created on indexed Columns are often used because of performance optimization PROBLEMS.

Right ANSWER is (d) CARTESIAN join

For explanation I would SAY: Cartesian Join is simply the JOINING of one or more table which returns the product of all the ROWS in these tables.

The correct option is (d) All of the Mentioned

The explanation is: JOIN can be used for more than ONE table. For ‘N’ tables the no of join conditions REQUIRED are ‘n-1’.

The correct choice is (a) Uses the result of an inner QUERY to determine the processing of an outer query

For explanation: A ‘CORRELATED subquery’ is a term USED for SPECIFIC types of queries in SQL in computer databases. It is a subquery (a query nested inside another query) that uses values from the outer query in its WHERE clause.

Right option is (a) Involves the USE of an inner and outer query

To explain I would say: Subquery—also referred to as an inner query or inner select—is a SELECT STATEMENT embedded within a data MANIPULATION LANGUAGE (DML) statement or nested within another subquery.

The correct OPTION is (a) Equi-join

The explanation: Equi-join JOINS only same data entry field. For example, one table contains department ID and another table should contain department id.

The CORRECT answer is (d) All of the Mentioned

To explain I would say: The SQL SUBQUERY is a SELECT query that is embedded in the main SELECT statement. In many CASES, a subquery can be used instead of a JOIN.

The correct ANSWER is (d) All of the Mentioned

To EXPLAIN I would say: Outer JOIN RETURNS the row having matching as well as NON matching values.

Correct choice is (C) SELECT * FROM weather WHERE humidity BETWEEN 63 AND 79

The explanation: The BETWEEN OPERATOR is used to select values WITHIN a RANGE.

The correct answer is (a) SELECT * FROM WEATHER WHERE temperature > (SELECT temperature FROM weather WHERE city = ‘Paris’

To elaborate: Subquery—also REFERRED to as an inner query or inner select—is a SELECT STATEMENT embedded within a DATA manipulation LANGUAGE (DML) statement or nested within another subquery.

The CORRECT ANSWER is (a) SELECT CITY, TEMPERATURE, condition FROM weather WHERE condition NOT IN (‘sunny’, ‘cloudy’)

The best explanation: The IN operator allows you to specify multiple VALUES in a WHERE clause.

The correct OPTION is (d) Feature has two 0’s in it, at any position

Easiest EXPLANATION: The LIKE operator is used in a WHERE CLAUSE to search for a specified PATTERN in a column.

Right option is (d) SELECT CITY, temperature FROM WEATHER ORDER BY city

Explanation: The ORDER BY keyword SORTS the RECORDS in ascending order by default.

The correct CHOICE is (b) SELECT city FROM WEATHER WHERE humidity = 95

For EXPLANATION: The SQL WHERE clause is USED to filter the results and apply conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Right ANSWER is (b) COMMIT

The explanation is: Rollback is USED for undoing the work DONE in the current transaction. This command also releases the locks if any hold by the current transaction.

Correct answer is (b) COMMIT

To explain: Commit command is used to permanently SAVE any TRANSACTION into the DATABASE.

Correct OPTION is (d) UPDATE Persons SET LASTNAME=’Nilsen’ WHERE LastName=’Hansen’

To elaborate: In its simplest form, the syntax for the UPDATE statement when UPDATING one table is:UPDATE table SET column1 = expression1,column2 = EXPRESSION2,… WHERE conditions.

The correct OPTION is (a) ORDER BY

Easy EXPLANATION: The ORDER BY keyword SORTS the records in ascending order by default.

Correct answer is (C) SELECT DISTINCT

The explanation is: The SELECT DISTINCT statement is USED to return only distinct (DIFFERENT) VALUES.

The correct option is (a) SELECT CLAUSE only

The explanation is: The SQL UNION operator is used to combine the RESULT sets of 2 or more SELECT STATEMENTS. It removes duplicate ROWS between the various SELECT statements.

Correct choice is (b) update more than one row at a time

Best EXPLANATION: The SQL UPDATE Query is used to modify the existing records in a table. You can use WHERE CLAUSE with UPDATE query to update SELECTED rows otherwise all the rows WOULD be affected.

Right choice is (a) The SQL ALTER TABLE CLAUSE modifies a table definition by altering, adding, or deleting table COLUMNS and/or constraints

Easiest EXPLANATION: The ALTER TABLE statement is used to add, DELETE, or modify columns.

The CORRECT choice is (a) SELECT COUNT() FROM Persons

For explanation: COUNT(column_name) is used to count the NUMBER of rows of a table where column name is a column that does not allow NULL values.

Correct choice is (d) SELECT * FROM Persons ORDER BY FirstName DESC

Explanation: The SQL SELECT statement queries DATA from TABLES in the database.

Right answer is (c) SELECT * FROM PERSONS WHERE FirstName LIKE ‘%a’

Explanation: The SQL LIKE clause is used to compare a VALUE to SIMILAR values using wildcard OPERATORS.

The CORRECT OPTION is (d) Data Manipulation language

For explanation I WOULD say: Data Manipulation Language is used to MODIFY the records in the database.

Correct ANSWER is (a) The AS SQL clause is used to change the name of a COLUMN in the RESULT SET or to ASSIGN a name to a derived column

Easiest explanation: SQL Aliases are defined for columns and tables. Basically aliases are created to make the column selected more readable.

Correct CHOICE is (a) DELETE does not free the SPACE CONTAINING the table and TRUNCATE free the space containing the table

The explanation: The SQL TRUNCATE COMMAND is used to delete all the ROWS from the table and free the space containing the table.