SQL

Question 1

SQL

Answer 1

Structured Query Language

Question 2

What can you use SQL for?

Answer 2

Create, read/retrieve, update and delete any data in a database. The data can be a single value, a row, a column, a table or even an entire database.

Question 3

How do you define text or character based value in SQL

Answer 3

You can define a text- or character-based value in SQL by placing the value inside single quotes (‘ ‘)

Question 4

How do you punctuate a SQL statement?

Answer 4

with a semi colon (;)

Question 5

What is a database?

Answer 5

Databases are collections of data that are organized for easy management, storage, update, search and retrieval. Databases can consist of items in any format such as audio, video, microfiche, print and electronic files.

Question 6

What is DBMS?

Answer 6

Database management systems are computer software that enables a user to create and manage a database using a certain platform.

Question 7

What is RDBMS?

Answer 7

Think of relational databases as systems of ordinary row-and-column tables that are interrelated.

Question 8

How to populate a table?

Answer 8

use INSERT INTO

Question 9

What is normalization (in the case of databases)?

Answer 9

The term “normalization” refers to the process of analyzing the data that your system is required to store, and organizing it so that every fact is stored in exactly one place. Understanding how to normalize data is an absolute requirement for the database programmer who wants to design databases.

Question 10

Why would you denormalize?

Answer 10

denormalization, the deliberate re-intoduction of redundant values to improve the usability of the database.

typically done for performance reasons, to reduce the number of table joins. This is not a good idea in a transactional environment as there are inherent data integrity risks or performance risks due to excessive locking to maintain data integrity.

Question 11

What are DML triggers and what types of triggers can you have on a SQL Server table?

Answer 11

A trigger, as the name implies, is a collection of SQL statements triggered to execute by an insert, update, or delete action on a table that affects its contents. INSTEAD OF and AFTER can be specified for each trigger. When INSTEAD OF is specified, the trigger takes the place of the triggering action. So if you had an INSTEAD OF UPDATE trigger, the code inside the trigger would be executed instead of the original update. AFTER triggers are the same as the FOR used in earlier versions of SQL Server. This just means the trigger will execute following the original action. An AFTER UPDATE trigger will allow the update to complete and then the update trigger will execute. For more information, see DML Trigger Planning Guidelines.

Question 12

What is the difference between a clustered and a non-clustered index?

Answer 12

his is one of my favorite questions for sorting out the developers that have an understanding beyond your basic T-SQL to more of an understanding of performance. I’m looking for them to tell me that a clustered index represents the physical order of the rows as they are stored in the database. A non-clustered index is a logical ordering of the rows.

Both types of indexes facilitate faster lookups and joins.

As a follow-up question, should they get this right, ask them to tell you how many clustered indexes you can have on a table. The answer should be one. Rows can only be physically stored with one ordering.

Question 13

What is a Linked Server?

Answer 13

Another question that can tell you if the candidate has experience with distributed databases is “What is a Linked Server?” A linked server is a reference from one SQL Server server to another. If you have databases on another SQL Server server that contains data you need for your system, you could create a link server on your server to the other SQL Server server. Then, you can use the four-part name of the remote table to use it within your local queries.

Question 14

How do you auto number rows in SQL Server?

Answer 14

ometimes I have to rephrase this one to get the answer. What I’m looking for here is the candidate’s knowledge of identity columns. Identity columns are used to automatically increment a column in a table from a seed (or starting) value and by a defined increment. If no seed is specified and no increment is specified, the first entry is 1 and the subsequent entries are incremented by 1. If either the seed or the increment is specified, other must be specified.

Question 15

What is a left outer join? Give an example.

Answer 15

The answers I get on this one blow me away. I can’t believe how many candidates I’ve interviewed that do not grasp this concept. Assume you have two tables, TableA and TableB. You need all the rows from TableA and all matching rows from TableB. You would use a left outer join to accomplish this with TableA being the left table as in the following.

SELECT *
FROM TableA
LEFT OUTER JOIN TableB
ON TableA.Col1 = TableB.Col1

Question 16

What is a cursor and why would you use it?

Answer 16

The candidate will probably be able to tell you cursors are used to perform row-by-row processing on a result set. The candidate might tell you they use them for batch type processing. In their answer though I am secretly hoping the candidate will elaborate on performance issues with cursors and how it is preferred to use loops or set-based processing instead of cursors. If not, the follow up question here is “Is there another way to process the result set row-by-row without using a cursor?” To see more of my thoughts on cursors, see What Every DBA Ought to Know About SQL Server Cursors (and Their Alternatives).

Question 17

What are the different index configurations a table can have?

Answer 17

A table can have one of the following index configurations:

No indexes
A clustered index
A clustered index and many nonclustered indexes
A nonclustered index
Many nonclustered indexes

Question 18

What’s the difference between a primary key and a unique key?

Answer 18

Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

Question 19

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Answer 19

They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

Question 20

What are the properties and different Types of Sub-Queries?

Answer 20

Properties of Sub-Query
A sub-query must be enclosed in the parenthesis.
A sub-query must be put in the right hand of the comparison operator, and
A sub-query cannot contain an ORDER-BY clause.
A query can contain more than one sub-query.
Types of Sub-Query
Single-row sub-query, where the sub-query returns only one row.
Multiple-row sub-query, where the sub-query returns multiple rows,. and
Multiple column sub-query, where the sub-query returns multiple columns

Question 21

What is PRIMARY KEY?

Answer 21

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

Question 22

What is UNIQUE KEY constraint?

Answer 22

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

Question 23

What is FOREIGN KEY?

Answer 23

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

Question 24

What is CHECK Constraint?

Answer 24

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

Question 25

What is NOT NULL Constraint?

Answer 25

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

Question 26

Write the command to remove all employees named John from the EMPLOYEE table.

Answer 26

DELETE from EMPLOYEE WHERE firstName = ‘John’

Question 27

Which SQL command is used to add a row?

Answer 27

INSERT