SQL Interview Q&A

SQL JOIN

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Tables in a database are often related to each other with keys. A primary key is a column (or a combination of columns) with a unique value for each row.
Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
Join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables.

Different SQL JOINs

• INNER JOIN: Return rows when there is at least one match in both tables.

SQL> SELECT table1.column1, table2.column2

           FROM table1 INNER JOIN table2

     ON table1.common_filed = table2.common_field;

• LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table.

SQL> SELECT table1.column1, table2.column2

     FROM table1 LEFT JOIN table2

           ON table1.common_filed = table2.common_field;

• RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table.

SQL> SELECT table1.column1, table2.column2

     FROM table1 RIGHT JOIN table2

           ON table1.common_filed = table2.common_field;

• FULL JOIN: Return rows when there is a match in one of the tables.

 SQL> SELECT table1.column1, table2.column2

      FROM table1 FULL JOIN table2

ON table1.common_filed = table2.common_field;

• CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.

SQL> SELECT Id, name, amount, date

    FROM CUSTOMERS, ORDERS;

• SELF JOIN: is used to join a table itself, as if the table were two tables, temporarily renaming at least one table in the SQL statement.

SQL> SELECT a.column_name, b.column_name

           FROM table1 a, table1 b

           WHERE a.common_filed = b.common_field;

-------------------------------------------------------------------------------------------------------

SQL VIEW

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column_name(s) FROM table_name WHERE condition

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s) FROM table_name WHERE condition

SQL DROP VIEW Syntax    - DROP VIEW view_name

WITH CHECK OPTION - The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERTs satisfy the condition(s) in the view definition. If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.

The following is an example of creating same view CUSTOMERS_VIEW with the WITH CHECK OPTION:

CREATE VIEW CUSTOMERS_VIEW AS

SELECT name, age FROM CUSTOMERS

WHERE age IS NOT NULL WITH CHECK OPTION;

The WITH CHECK OPTION in this case should deny the entry of any NULL values in the view's AGE column, because the view is defined by data that does not have a NULL value in the AGE column.

-------------------------------------------------------------------------------------------------------

SQL Wildcard Characters

          A wildcard character can be used to substitute for any other character(s) in a string. In SQL, wildcard characters are used with the SQL LIKE operator. SQL wildcards are used to search for data within a table. 

Wildcard	Description	Example
%	A substitute for zero or more characters	SELECT * FROM Customers WHERE City LIKE '%es%';
_	A substitute for a single character	SELECT * FROM Customers WHERE City LIKE '_erlin' OR LIKE 'L_n_on';
[charlist]	Sets and ranges of characters to match	SELECT * FROM Customers WHERE City LIKE '[a-c]%' OR LIKE '[bsp]%';
[^charlist] or [!charlist]	Matches only a character NOT specified within the brackets	SELECT * FROM Customers WHERE City LIKE '[!bsp]%';

-------------------------------------------------------------------------------------------------------

SQL Constraints

Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints could be column level or table level. Column level constraints are applied only to one column whereas table level constraints are applied to the whole table.

·         NOT NULL: Ensures that a column cannot have NULL value.

·         DEFAULT: Provides a default value for a column when none is specified.

·         UNIQUE: Ensures that all values in a column are different.

·         PRIMARY KEY: Uniquely identified each rows/records in a database table.

·         FOREIGN KEY: Uniquely identified a rows/records in any another database table.

·         CHECK: The CHECK constraint ensures that all values in a column satisfy certain conditions.

·         INDEX: Use to create and retrieve data from the database very quickly.

Constraints can be specified when a table is created with the CREATE TABLE statement or you can use ALTER TABLE statement to create constraints even after the table is created.

Dropping Constraints:

Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option.
For example, to drop the primary key constraint in the EMPLOYEES table, you can use the following command:

ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;

Some implementations allow you to disable constraints. Instead of permanently dropping a constraint from the database, you may want to temporarily disable the constraint, and then enable it later.

Integrity Constraints:

Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity.

-------------------------------------------------------------------------------------------------------

Composite Primary Key

A table can have at most one primary key. A primary key consists of one or more columns (from that table). If a primary key consists of two or more columns it is called a composite primary key. The pair (QuestionID, MemberID) must then be unique for the table and neither value can be NULL. It is defined as follows:

CREATE TABLE voting (

  QuestionID NUMERIC,

  MemberID NUMERIC,

  PRIMARY KEY (QuestionID, MemberID)

);

-------------------------------------------------------------------------------------------------------

Indexes

Indexes allow the database application to find data fast; without reading the whole table.

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.
Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.

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)

-------------------------------------------------------------------------------------------------------

Triggers in SQL

A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers.

Basically, triggers are classified into two main types:-

(i) After Triggers (For Triggers)
(ii) Instead Of Triggers

After Triggers

These triggers run after an insert, update or delete on a table. They are not supported for views.
AFTER TRIGGERS can be classified further into three types as:
(a) AFTER INSERT Trigger.
(b) AFTER UPDATE Trigger.
(c) AFTER DELETE Trigger.

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]

FOR INSERT

AS

        declare @empid int;

        declare @status varchar(100);

       

        select @empid=i.Emp_ID from inserted i;      

       

        update Employee_Test_Audit set Status=@ status

               where Empid=@ empid;

        PRINT 'AFTER INSERT trigger fired.'

GO

Instead Of Triggers

These can be used as an interceptor for anything that anyonr tried to do on our table or view. If you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, and they will not actually get deleted (unless you issue another delete instruction from within the trigger)
INSTEAD OF TRIGGERS can be classified further into three types as:-

(a) INSTEAD OF INSERT Trigger.
(b) INSTEAD OF UPDATE Trigger.
(c) INSTEAD OF DELETE Trigger.

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test]

INSTEAD OF DELETE

AS

        declare @emp_id int;

        declare @emp_name varchar(100);

        declare @emp_sal int;

       

        select @emp_id=d.Emp_ID from deleted d;

        select @emp_name=d.Emp_Name from deleted d;

        select @emp_sal=d.Emp_Sal from deleted d;

        BEGIN

               if(@emp_sal>1200)

               begin

                       RAISERROR('Cannot delete where salary > 1200',16,1);

                       ROLLBACK;

               end

               else

               begin

                       delete from Employee_Test where Emp_ID=@emp_id;

                       COMMIT;

                       insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

                       values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());

                       PRINT 'Record Deleted -- Instead Of Delete Trigger.'

               end

        END

GO

-------------------------------------------------------------------------------------------------------

Normalization

Normalization is the process of efficiently organizing data in a database.

There are two goals of the normalization process:

1. Eliminating redundant data.
2. Ensuring data dependencies make sense

Benefits:

1. Eliminate data redundancy
2. Improve performance
3. Query optimization
4. Faster update due to less number of columns in one table
5. Index improvement

First Normal Form

• Eliminate repeating groups in individual tables.
• Create a separate table for each set of related data.
• Identify each set of related data with a primary key.

Second Normal Form

• Create separate tables for sets of values that apply to multiple records.
• Relate these tables with a foreign key.

Third Normal Form

• Eliminate fields that do not depend on the key.

-------------------------------------------------------------------------------------------------------

Normalization (In Detail)

Normalization is a method for organizing data elements in a database into tables.

Normalization Avoids

• Duplication of Data  – The same data is listed in multiple lines of the database
• Insert Anomaly  – A record about an entity cannot be inserted into the table without first inserting information about another entity – Cannot enter a customer without a sales order
• Delete Anomaly – A record cannot be deleted without deleting a record about a related entity.  Cannot delete a sales order without deleting all of the customer’s information.
• Update Anomaly – Cannot update information without changing information in many places.  To update customer information, it must be updated for each sales order the customer has placed

Normalization is a three stage process – After the first stage, the data is said to be in first normal form, after the second, it is in second normal form, after the third, it is in third normal form

Before Normalization

1.    Begin with a list of all of the fields that must appear in the database.  Think of this as one big table.

2.    Do not include computed fields

3.    One place to begin getting this information is from a printed document used by the system. 

4.    Additional attributes besides those for the entities described on the document can be added to the database.

Normalization:  First Normal Form

• Separate Repeating Groups into New Tables.
• Repeating Groups  Fields that may be repeated several times for one document/entity
• Create a new table containing the repeating data
• The primary key of the new table (repeating group) is always a composite key; Usually document number and a field uniquely describing the repeating line, like an item number.

First Normal Form Example

The new table is as follows:

SalesOrderNo, ItemNo, Description, Qty, UnitPrice

The repeating fields will be removed from the original data table, leaving the following.

SalesOrderNo, Date, CustomerNo, CustomerName, CustomerAdd, ClerkNo, ClerkName

These two tables are a database in first normal form

What if we did not Normalize the Database to First Normal Form?

Repetition of Data – SO Header data repeated for every line in sales order.

Normalization:  Second Normal Form

• Remove Partial Dependencies. 
• Functional Dependency  The value of one attribute in a table is determined entirely by the value of another.
• Partial Dependency A type of functional dependency where an attribute is functionally dependent on only part of the primary key (primary key must be a composite key).
• Create separate table with the functionally dependent data and the part of the key on which it depends.  Tables created at this step will usually contain descriptions of resources.

Second Normal Form Example

The new table will contain the following fields:

ItemNo, Description

All of these fields except the primary key will be removed from the original table.  The primary key will be left in the original table to allow linking of data:

SalesOrderNo, ItemNo, Qty, UnitPrice

Never treat price as dependent on item.  Price may be different for different sales orders (discounts, special customers, etc.)

Along with the unchanged table below, these tables make up a database in second normal form:

SalesOrderNo, Date, CustomerNo, CustomerName, CustomerAdd, ClerkNo, ClerkName

What if we did not Normalize the Database to Second Normal Form?

• Repetition of Data – Description would appear every time we had an order for the item
• Delete Anomalies – All information about inventory items is stored in the SalesOrderDetail table.  Delete a sales order, delete the item. 
• Insert Anomalies – To insert an inventory item, must insert sales order.
• Update Anomalies – To change the description, must change it on every SO.

Normalization:  Third Normal Form

• Remove transitive dependencies.
• Transitive Dependency  A type of functional dependency where an attribute is functionally dependent on an attribute other than the primary key.  Thus its value is only indirectly determined by the primary key.
• Create a separate table containing the attribute and the fields that are functionally dependent on it. Tables created at this step will usually contain descriptions of either resources or agents.  Keep a copy of the key attribute in the original file.

Third Normal Form Example

The new tables would be:

CustomerNo, CustomerName, CustomerAdd

ClerkNo, ClerkName

All of these fields except the primary key will be removed from the original table.  The primary key will be left in the original table to allow linking of data as follows:

SalesOrderNo, Date, CustomerNo, ClerkNo

Together with the unchanged tables below, these tables make up the database in third normal form.

ItemNo, Description

SalesOrderNo, ItemNo, Qty, UnitPrice

What if we did not Normalize the Database to Third Normal Form?

• Repetition of Data – Detail for Cust/Clerk would appear on every SO
• Delete Anomalies – Delete a sales order, delete the customer/clerk
• Insert Anomalies – To insert a customer/clerk, must insert sales order.
• Update Anomalies – To change the name/address, etc, must change it on every SO.

Completed Tables in Third Normal Form

Customers:  CustomerNo, CustomerName, CustomerAdd

Clerks:  ClerkNo, ClerkName

Inventory Items:  ItemNo, Description

Sales Orders:  SalesOrderNo, Date, CustomerNo, ClerkNo

SalesOrderDetail:  SalesOrderNo, ItemNo, Qty, UnitPrice

-------------------------------------------------------------------------------------------------------

Stored Procedures

1.    System Store Procedures

In SQL Server, many administrative and informational activities can be performed by using system stored procedures. Every time we add or modify a table, make a backup plan, or perform any other administrative function from within Enterprise Manager, we actually call a stored procedure specifically written to complete the desired action.

2.    User Defined Stored Procedures

• User stored procedures 
• Triggers, and
• User defined functions.

User Stored Procedure

Stored procedures are special objects available in SQL server. Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. Stored procedure will reduce network traffic and increase the performance. Maximum size of stored procedure is 128 MB. Maximum number of parameter passes to stored procedure is 2100.

Advantages: 

• Security due to encryption.
• Performance gains due to compilation.
• Reduction in the amount of data passed over a network by keeping the code on the server.
• Hiding the raw data by allowing only stored procedures to gain access to the data.

-------------------------------------------------------------------------------------------------------

Difference between Stored Procedure and Functions

• Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
• Functions can have only input parameters for it whereas Procedures can have input/output parameters.
• Function takes one input parameter it is mandatory but Stored Procedure may take 0 to n input parameters.
• Functions can be called from Procedure whereas Procedures cannot be called from Function.
• Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
• Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
• Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
• User Defined Function can have upto 1023 input parameters while a Stored Procedure can have upto 2100 input parameters.
• Like Stored Procedure, Function can be nested up to 32 levels.

-------------------------------------------------------------------------------------------------------

Function in SQL

Function is a database object in SQL Server. Basically it is a set of SQL statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update and Delete records in the database table.

1.    System Defined Function

These functions are defined by SQL Server for different purpose. We have two types of system defined function in SQL Server

1.    Scalar Function

Scalar functions operate on a single value and returns a single value. Below is the list of some useful SQL Server Scalar functions.

abs(-10.67) - This returns absolute number of the given number means 10.67.

rand(10) - This will generate random number of 10 characters.

round(17.56719,3) - This will round off the given number to 3 places of decimal means 17.567

upper('dotnet') - This will returns upper case of given string means 'DOTNET'

lower('DOTNET')- This will returns lower case of given string means 'dotnet'

ltrim(' dotnet') -This will remove the spaces from left hand side of 'dotnet' string.

convert(int, 15.56) -This will convert the given float value to integer means 15.

2.    Aggregate Function

An aggregate function operates on a collection of values and returns a single value. Below is the list of some useful SQL Server Aggregate functions.

max() - This returns maximum value from a collection of values.

min() - This returns minimum value from a collection of values.

avg() - This returns average of all values in a collection.

count() - This returns no of counts from a collection of values.

2.    User Defined Function

These functions are created by user in system database or in user defined database. We three types of user defined functions.

   1.    Scalar Function

User defined scalar function also returns single value as a result of actions perform by function. We return any data type value from function.

Create function fnGetEmpFullName

(@FirstName varchar(50), @LastName varchar(50))

returns varchar(101)

As

Begin

   return (Select @FirstName + ' '+ @LastName);

End


2. Inline Table-Valued Function

User defined inline table-valued function returns a table variable as a result of actions performs by function. The value of table variable should be derived from a single SELECT statement.

Create function fnGetEmployee()

returns Table

As

return (Select * from Employee)

3.    Multi-Statement Table-Valued Function

User defined multi-statement table-valued function returns a table variable as a result of actions performs by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple SQL statements.

Create function fnGetMulEmployee()

returns @Emp Table (EmpID int, FirstName varchar(50),Salary int)

As begin

          Insert @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;

          update @Emp set Salary=25000 where EmpID=1;

return

end

-------------------------------------------------------------------------------------------------------

SQL Profiler

SQL Profiler is a tool that captures SQL Server events from the server and saves those events in what's known as a trace file. You can then analyze or use the trace file to troubleshoot logic or performance problems. You can use this utility to monitor several areas of server activity as:

• Analyzing and debugging SQL statements and stored procedures.
• Monitoring slow performance.
• Stress analysis.
• General debugging and troubleshooting.
• Fine-tuning indexes &
• Auditing and reviewing security activity.

--------------------------------------------------------------------------------------------------------