SQL INTERVIEW QUESTIONS

What is SQL?

SQL stands for Structured Query Language SQL lets you access and manipulate databases SQL is an ANSI (American National Standards Institute) standard

What Can SQL do?

SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views

SQL DML and DDL SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL). The query and update commands form the DML part of SQL:

SELECT - extracts data from a database UPDATE - updates data in a database DELETE - deletes data from a database INSERT INTO - inserts new data into a database

The DDL part of SQL permits database tables to be created or deleted. It also defines indexes (keys), specifies links between tables, and imposes constraints between tables. The most important DDL statements in SQL are: 1

CREATE DATABASE - creates a new database ALTER DATABASE - modifies a database CREATE TABLE - creates a new table ALTER TABLE - modifies a table DROP TABLE - deletes a table CREATE INDEX - creates an index (search key) DROP INDEX - deletes an index

SELECT : SELECT column_name(s) FROM table_name and SELECT * FROM table_name DISTINCT : The DISTINCT keyword can be used to return only distinct (different) values. SQL SELECT DISTINCT Syntax SELECT DISTINCT column_name(s) FROM table_name The WHERE Clause : The WHERE clause is used to extract only those records that fulfill a specified criterion. SQL WHERE Syntax SELECT column_name(s) FROM table_name WHERE column_name operator value The AND & OR Operators: The AND operator displays a record if both the first condition and the second condition is true. The OR operator displays a record if either the first condition or the second condition is true. SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson' SELECT * FROM Persons WHERE FirstName='Tove' OR FirstName='Ola' 2

SELECT * FROM Persons WHERE LastName='Svendson' AND (FirstName='Tove' OR FirstName='Ola') The ORDER BY Keyword: The ORDER BY keyword is used to sort the result-set by a specified column. The ORDER BY keyword sort the records in ascending order by default. If you want to sort the records in a descending order, you can use the DESC keyword. SQL ORDER BY Syntax SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC The INSERT INTO Statement: The INSERT INTO statement is used to insert a new row in a table. INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...) The UPDATE Statement: The UPDATE statement is used to update existing records in a table. UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value UPDATE Persons SET Address='Nissestien 67', City='Sandnes' WHERE LastName='Tjessem' AND FirstName='Jakob' UPDATE Persons SET Address='Nissestien 67', City='Sandnes' The DELETE Statement The DELETE statement is used to delete rows in a table. DELETE FROM table_name WHERE some_column=some_value DELETE FROM Persons WHERE LastName='Tjessem' AND FirstName='Jakob' 3

The TOP Clause The TOP clause is used to specify the number of records to return. SELECT TOP 2 * FROM Persons SELECT TOP number|percent column_name(s) FROM table_name The LIKE Operator The LIKE operator is used to search for a specified pattern in a column. SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern SELECT * FROM Persons WHERE City LIKE 's%' SQL Wildcards SQL wildcards can substitute for one or more characters when searching for data in a database. SQL wildcards must be used with the SQL LIKE operator. SELECT * FROM Persons WHERE City LIKE 'sa%' The IN Operator The IN operator allows you to specify multiple values in a WHERE clause. SELECT * FROM Persons WHERE LastName IN ('Hansen','Pettersen') The BETWEEN Operator The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates. SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 SQL Alias 4

You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long or complex table names or column names. An alias name could be anything, but usually it is short. SQL Alias Syntax for Tables SELECT column_name(s) FROM table_name AS alias_name SQL Alias Syntax for Columns SELECT column_name AS alias_name FROM table_name We use the following SELECT statement: SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po WHERE p.LastName='Hansen' AND p.FirstName='Ola' The same SELECT statement without aliases: SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName FROM Persons, Product_Orders WHERE Persons.LastName='Hansen' AND ersons.FirstName='Ola' SQL INNER JOIN Keyword The INNER JOIN keyword return rows when there is at least one match in both tables. SQL INNER JOIN Syntax SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name SQL LEFT JOIN Keyword The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

SQL LEFT JOIN Syntax SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name PS: In some databases LEFT JOIN is called LEFT OUTER JOIN. SQL RIGHT JOIN Keyword The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1). SQL RIGHT JOIN Syntax SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN. SQL FULL JOIN Keyword The FULL JOIN keyword return rows when there is a match in one of the tables. SQL FULL JOIN Syntax SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name The SQL UNION Operator The UNION operator is used to combine the result-set of two or more SELECT statements.

SQL UNION Syntax SELECT column_name(s) FROM table_name1 6

UNION SELECT column_name(s) FROM table_name2 Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL. SQL UNION ALL Syntax SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2 The SQL SELECT INTO Statement The SELECT INTO statement selects data from one table and inserts it into a different table. The SELECT INTO statement is most often used to create backup copies of tables. SQL SELECT INTO Syntax We can select all columns into the new table: SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablenameOr we can select only the columns we want into the new table: SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename The CREATE DATABASE Statement The CREATE DATABASE statement is used to create a database. SQL CREATE DATABASE Syntax 7

CREATE DATABASE database_name The CREATE TABLE Statement The CREATE TABLE statement is used to create a table in a database. SQL CREATE TABLE Syntax CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, .... ) CREATE TABLE Persons ( P_Id int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ) SQL Constraints 8

Constraints are used to limit the type of data that can go into a table. Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement). We will focus on the following constraints: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK DEFAULT

SQL NOT NULL Constraint The NOT NULL constraint enforces a column to NOT accept NULL values. The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field. The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, 9

FirstName varchar(255), Address varchar(255), City varchar(255) ) SQL UNIQUE Constraint The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. CREATE TABLE Persons ( P_Id int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) SQL PRIMARY KEY Constraint The PRIMARY KEY constraint uniquely identifies each record in a database table. 10

Primary keys must contain unique values. A primary key column cannot contain NULL values. Each table should have a primary key, and each table can have only ONE primary key. CREATE TABLE Persons ( P_Id int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) SQL FOREIGN KEY Constraint A FOREIGN KEY in one table points to a PRIMARY KEY in another table. SQL CHECK Constraint The CHECK constraint is used to limit the value range that can be placed in a column. CREATE TABLE Persons ( P_Id int NOT NULL CHECK (P_Id>0), LastName varchar(255) NOT NULL, FirstName varchar(255), 11

Address varchar(255), City varchar(255) ) Indexes An index can be created in a table to find data more quickly and efficiently. The users cannot see the indexes, they are just used to speed up searches/queries. SQL CREATE INDEX Syntax Creates an index on a table. Duplicate values are allowed: CREATE INDEX index_name ON table_name (column_name)SQL CREATE UNIQUE INDEX Syntax Creates a unique index on a table. Duplicate values are not allowed: CREATE UNIQUE INDEX index_name ON table_name (column_name) CREATE INDEX PIndex ON Persons (LastName) The DROP Statement DROP INDEX index_name DROP TABLE table_name DROP DATABASE database_name The ALTER TABLE Statement ALTER TABLE table_name ADD column_name datatype ALTER TABLE table_name DROP COLUMN column_name 12

ALTER TABLE table_name ALTER COLUMN column_name datatype SQL CREATE VIEW Statement A view is a virtual table. CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition SQL BUILTINS: GETDATE() DATEPART() DATEADD() DATEDIFF() CONVERT() Returns the current date and time Returns a single part of a date/time Adds or subtracts a specified time interval from a date Returns the time between two dates Displays date/time data in different formats

SQL Aggregate Functions SQL aggregate functions return a single value, calculated from values in a column. Useful aggregate functions: AVG() - Returns the average value COUNT() - Returns the number of rows FIRST() - Returns the first value LAST() - Returns the last value MAX() - Returns the largest value MIN() - Returns the smallest value SUM() - Returns the sum 13

SQL Scalar functions SQL scalar functions return a single value, based on the input value. Useful scalar functions: UCASE() - Converts a field to upper case LCASE() - Converts a field to lower case MID() - Extract characters from a text field LEN() - Returns the length of a text field ROUND() - Rounds a numeric field to the number of decimals specified NOW() - Returns the current system date and time FORMAT() - Formats how a field is to be displayed The AVG() Function The AVG() function returns the average value of a numeric column. SELECT AVG(column_name) FROM table_name SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders) SQL COUNT(column_name) Syntax The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column: SELECT COUNT(*) FROM table_name The FIRST() Function The FIRST() function returns the first value of the selected column. 14

SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders The LAST() Function The LAST() function returns the last value of the selected column. SELECT LAST(column_name) FROM table_name The MAX() Function The MAX() function returns the largest value of the selected column. SQL MAX() Syntax SELECT MAX(column_name) FROM table_name The MIN() Function The MIN() function returns the smallest value of the selected column. SQL MIN() Syntax SELECT MIN(column_name) FROM table_name The SUM() Function The SUM() function returns the total sum of a numeric column. SQL SUM() Syntax SELECT SUM(column_name) FROM table_name The GROUP BY Statement The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. SQL GROUP BY Syntax 15

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE Column_name operator value GROUP BY column_name SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer The HAVING Clause The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. SQL HAVING Syntax SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000

SELECT Customer,SUM(OrderPrice) FROM Orders WHERE Customer='Hansen' OR Customer='Jensen' GROUP BY Customer HAVING SUM(OrderPrice)>1500 The UCASE() Function The UCASE() function converts the value of a field to uppercase. SQL UCASE() Syntax SELECT UCASE(column_name) FROM table_nameSyntax for SQL Server SELECT UPPER(column_name) FROM table_name The LCASE() Function The LCASE() function converts the value of a field to lowercase. SQL LCASE() Syntax 16

SELECT LCASE(column_name) FROM table_nameSyntax for SQL Server SELECT LOWER(column_name) FROM table_name The MID() Function The MID() function is used to extract characters from a text field. SQL MID() Syntax SELECT MID(column_name,start[,length]) FROM table_name SELECT MID(City,1,4) as SmallCity FROM Persons The LEN() Function The LEN() function returns the length of the value in a text field. SQL LEN() Syntax SELECT LEN(column_name) FROM table_name SELECT LEN(Address) as LengthOfAddress FROM Persons The ROUND() Function The ROUND() function is used to round a numeric field to the number of decimals specified. SQL ROUND() Syntax SELECT ROUND(column_name,decimals) FROM table_name SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products The NOW() Function The NOW() function returns the current system date and time. SQL NOW() Syntax 17

SELECT NOW() FROM table_name SELECT ProductName, UnitPrice, Now() as PerDate FROM Products

ProductName Jarlsberg Mascarpone

UnitPrice 10.45 32.56

PerDate 10/7/2008 11:25:02 AM 10/7/2008 11:25:02 AM

DELETE The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire. TRUNCATE TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE. DROP The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back. DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back. Difference between TRUNCATE and DELETE commands 1>TRUNCATE is a DDL command whereas DELETE is a DML command. 2>TRUNCATE is much faster than DELETE. 18

Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data. 3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently. 4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired. 5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause What is Normalization? Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored. The Normal Forms The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you'll often see 1NF, 2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely seen and won't be discussed in this article.

3 TIRE ARCHITECTURE LAYERS OF AN APPLICATION:

Presentation layer contains pages like .aspx or windows form where data is presented to the user or input is taken from the user. Business Access Layer (BAL) or Business Logic Layer BAL contains business logic, validations or calculations related with the data, if needed. I will call it Business Access Layer in my demo. Data Access Layer (DAL) DAL contains methods that helps business layer to connect the data and perform required action, might be returning data or manipulating data (insert, update, delete etc). For this demo application, I have taken a very simple example. I am assuming that I have to play with record of persons (FirstName, LastName, Age) and I will refer only these data through out this article. Stored Procedure: Stored procedures may return result sets, i.e. the results of a SELECT statement. Such result sets can be processed using cursors, by other stored procedures, by associating a result set locator, or by applications. Stored procedures may also contain declared variables for processing data and cursors that allow it to loop through multiple rows in a table. Stored procedure languages typically include IF, WHILE, LOOP, REPEAT, and CASE statements, and more. Stored procedures can receive variables, return results or modify variables and return them, depending on how and where the variable is declared. Cursors in SQL procedures In SQL procedures, a cursor make it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application. A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed. To use cursors in SQL procedures, you need to do the following: 1. Declare a cursor that defines a result set. 2. Open the cursor to establish the result set. 3. Fetch the data into local variables as needed from the cursor, one row at a time. 4. Close the cursor when done To work with cursors you must use the following SQL statements:

DECLARE CURSOR 20

OPEN FETCH CLOSE

SQL triggers The SQL CREATE TRIGGER statement provides a way for the database management system to actively control, monitor, and manage a group of tables whenever an insert, update, or delete operation is performed. The statements specified in the SQL trigger are executed each time an SQL insert, update, or delete operation is performed. An SQL trigger may call stored procedures or user-defined functions to perform additional processing when the trigger is executed. Unlike stored procedures, an SQL trigger cannot be directly called from an application. Instead, an SQL trigger is invoked by the database management system on the execution of a triggering insert, update, or delete operation. The definition of the SQL trigger is stored in the database management system and is invoked by the database management system, when the SQL table, that the trigger is defined on, is modified.

21