Twitter Facebook LinkedIn RSS Pinterest
  • You are here :
  • Home
  • SQL

SQL

Technical Article Section -

Session-1

View: is a virtual table or a subset of a table and serves as a security mechanism like-

  1. It provides only relevant data to the user.
  2. Hiding data complexity from the users.
  3. Organizing data from heterogeneous [elements those are not same kind of nature] source.
  4. Reducing object size.

Note: View does not store data in the table it only store the definition of a view.

Syntax:

Create view [view name]
As
Group of SQL statements.

Restriction of view:

  1. View can be created only in the current database.
  2. View can be created only if the select permission is granted on its base table.
  3. View cannot derive from temporary table.
  4. Order by clause cannot be used inside the view with its base table.

Temporary table: A table having hash (#) prefix with its name and accessible by the session is called temporary table.

  1.  Local temp table: a table having single hash (#) prefix and accessible only by the current session is called temp table.
  2. Global temp table: a table having double hash (##) prefix with its table name and accessible throughout the session is called global temp table.

If you want to modify view, jus pass the command like

Alter view [view name]
As
Group of modified SQL statement

If you want to see the status of view, jus pass the command like Sp_help [view name]

If you want to see the texture of view, just pass the command like Sp_helptext [view name]

If you want to see the texture of view, just pass the command like Sp_rename[ old view name], [new view name]

If you want to delete the view, just pass the command like Drop view [view name]

Session-2

Procedure: Is a group of SQL statements that has been previously created and stored in SQL server database.

  1. It reduces network traffic and improves the performance because of recompilation. so procedure does not need to compile again and again.
  2. Whenever any procedure gets modified all clients will get updated procedure automatically.

There are five types of Procedure:

  1. User defined
  2. System defined: Having prefix with sp_ with its name and stored in master and msdb database.
  3. Temporary: Having prefix # hash with its name and stored in tempdb database.
  4. Remote procedure: Created and stored on remote server.
  5. Extended: These are the dlls . Those are running outside the SQL environment.

Syntax:

Create procedure [view name]
Input and parameters
As Variable declaration
Begin
Group of SQL statements.
End

If you want to modify procedure, jus pass the command like- 

Alter Proc [name of proc]
Input and parameters
As
Variable declaration
Begin
Group of modified SQL statements.
End

If you want to see the status of proc, jus pass the command like Sp_help [procedure name]

If you want to see the texture of view, just pass the command like Sp_helptext [procedure name]

If you want to see the texture of view, just pass the command like Sp_rename[ old procedure name], [new procedure name]

If you want to delete the view, just pass the command like Drop procedure [procedure name]

Session-3

Trigger: Is a procedure that initiates an action whenever an event occurs. like –insert, update, delete.

Syntax:

Create trigger on [table name]
For insert/update/delete
As
 Group of SQL statements

We have two types of DML triggers.

  1. After Trigger (using FOR/AFTER CLAUSE) This trigger fires after SQL Server completes the execution of the action successfully that fired it. Example: If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.
  2. Instead of Trigger (using INSTEAD OF CLAUSE) This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.

Whenever a trigger fire there are two tables created like inserted, deleted collectively called magic table.
Inserted: Copies the triggered table data at time of record insert.
Deleted: Copies the triggered table data at time of record delete.

Note: In case of update both tables have the copy of a data.

Multiple triggers are allowed on the same table.

Session-4

Cursor: is a database object that helps in accessing and manipulating data row by row.

Syntax:

  1. Need to declare
  2. Need to open
  3. Fetched required row and perform manipulation
  4. Need to be closed
  5. Should be deallocated

Session-5

Index: is physical structure containing pointer to the database table’s records.

  1. Cluster index: It defines physical sorting of database table’s row. that’s why a table can have only one cluster index.
  2. Non Cluster index: It defines logical sorting of database table’s row. that’s why a table can have multiple non cluster indexes. like-

In 2005 it is up to 249 means total is 250.

In 2008 it is up to 999 means total is 1000.

Session-6

Joins: There six types of joins

  1. Inner join
  2. Outer join
    1. Left outer join: includes all records from 1st table and matching row from 2nd table those are not matched display null values.
    2. Right outer join: includes all records from 2nd table and matching record from 1st table those are not matched display null values.
    3. Full outer join: includes all records from 1st and 2nd table and display null values those are not matched.
  3. Cross join: a join that includes more than one table using cross key is called cross join. like-A=1, 2, 3 / A=4, 5, 6 R=1x4, 2x4, 3x4,5x1,5x2,5x3,6x1,6x2,6x3 No of Records= no of column of 1st table * no of column of 2nd table R=3*3=9
  4. Equi Join: a join that use an asterix [*] and display redundant [fields those are repeated in two or more tables is called redundancy] data is called Equi join.
  5. Natural Join: A join that restrict the redundant data is called natural joins.
  6. Self-join: A join is said to be self-join when one row co-relate with another row.

Session-7

Normalization: is a process of organizing data to minimize redundancy.

  1. 1-NF: we eliminate the repeating groups.
  2. 2-NF: eliminate the redundant data.
  3. 3-NF: eliminate the columns those are not dependent on the key.
  4. BCNF: if there are none tribal dependencies between candidate key and attribute, separate them out into distinct.
  5. 4-NF (Extension of the BCNF): isolate independent multiple relationship.
  6.  5-NF: isolate semantically relations
  7.  ONF: A model limited only simple facts.
  8.  DKNF: All models free from all modification.

Note: due to normalization database performance gets slow.

Denormalization: is a process of organizing database performance by adding redundant data.

Session-8

Database can be defined as a structural collection of records.

Master holds information of all databases those are allocation on server.

MSDB holds information of database jobs like backup, scheduling and replication.

Model is a template database that is used in creation of any new database.

Identity is a system generated unique value. Like- Emp_code int identity (100, 1)

Replication is process of distributing database across server for better availability.

SQL user 1433 port for TCP/IP

Transaction is sequence of operations performed together as single unit.

Deadlock is a situation where two users [transaction] have locks on separate object on each user waiting for lock on the other’s object. Locking if one transaction is going on, others have to wait for this.

ACID

  1. Atomicity: Either all operations are performed or none of them.
  2. Consistency: Grantees that transaction never leave your database in half-finished state
  3. Isolation: Transaction doesn’t interfere with one another.
  4. Durability: Once transaction is committed data is avail for ever.

Interview Questions and Answers +

CTE can be used to

  1. Create a recursive query.
  2. Substitute for a view when the general use of a view is not required; that is, you do not have t store the definition in meta data.

Procedure V/S function

  1. Procedure can use function whereas function can not use procedure.
  2. Procedure can use DML (like-insert,update and delete) statement whereas function can not use. It can only use select statement.
  3. Function must have return a value whereas procedure may or may not return a value.
  4. Function can only have input parameters whereas procedure can have both like input and output parameters.
  5. Function can not have try ..catch block whereas procedure can have.
  6. Function can not use transaction whereas procedure can use.
  7. Function can only use table variable but can not user temp table whereas procedure can use both.
  8. Function can be used in join clause as a result set whereas procedure can not.

Temp table V/S able variable

  1. Temp table can do all the DDL operations. Like- creating the indexes, dropping, altering, etc whereas table variable won't allow doing the DDL operations. But the table variable allows us to create the clustered index only.
  2. Temp table result can be used by multiple users whereas table variable can be used by the current user only.

Delete V/S truncate

  1. Delete does not reset the identity of the table whereas truncate resets the identity of the the table.
  2. Delete can be used with or without where clause whereas truncate can not use where clause.
  3. Delete can be roll-backed whereas truncate can not be roll-backed.

Primary key V/S unique key,foreign key

  1. Primary key(PK) can’t accept null values whereas unique key(UK) accept only one null value.
  2. PK creates clustered index by default whereas UK creates non-clustered index.
  3. You can’t change or delete PK value whereas UK value can be changed.
  4. A table can have only one PK whereas UK can be more than one.
  5. PK can be made FK into another table whereas UN can’t.
  6. PK supports auto increment whereas UK does not support.
  7. FK is the field int the table that is PK into another table.
  8. FK does not create any index by default you can manually create the same

Some of the Query

Selecting nth highest salary from employee table

1- SELECT MIN(salary) FROM Emply WHERE salary IN (SELECT top 2 salary FROM emply

ORDER BY salary DESC)

2- WITH CTEtble

AS(SELECT salary,Row_Number() OVER(ORDER BY salary DESC)NO FROM Emply)

SELECT salary FROM Emply WHERE NO=2

Deleting duplicate recoreds from table

1- SELECT DISTINCT * INTO temptable FROM tbl

TRUNCATE TABLE tbl

INSERT INTO tbl SELECT * FROM temptable

2-DELETE tblname WHERE ID NOT IN

(SELECT MAX(ID) FROM tblname GROUP BY cname)

Findign duplicate records from table

SELECT cname,COUNT('x')

FROM tblname

GROUP BY cname

HAVING cout('x')>1

updating column value

UPDATE tblname

SET a=b,

b=a

Updating single column values in its reverse order

UPDATE tblname

SET Cname=(CASE WHEN Cname=0 THEN 1 ELSE 0 END)

Self Join based query

CREATE TABLE ISR_TEST

(

Emp_Id NUMBER(12,2),

Emp_Name VARCHAR2(50),

Emp_Manger_Id NUMBER(12,2),

Emp_Salary NUMBER(12,2)

)

INSERT INTO ISR_TEST (EMP_ID, EMP_NAME, EMP_SALARY) VALUES (1, 'Test-1', 125000)

INSERT INTO ISR_TEST (EMP_ID, EMP_NAME, EMP_SALARY) VALUES (2, 'Test-2', 75000)

INSERT INTO ISR_TEST (EMP_ID, EMP_NAME, EMP_SALARY) VALUES (3, 'Test-3', 50000)

INSERT INTO ISR_TEST (EMP_ID, EMP_NAME, EMP_SALARY) VALUES (4, 'Test-4', 45000)

INSERT INTO ISR_TEST (EMP_ID, EMP_NAME, EMP_SALARY) VALUES (5, 'Test-5', 40000)

Selecting Employee and it's Manager

1- SELECT a.EMP_NAME AS Employee,b.EMP_NAME AS Manager FROM ISR_TEST a,ISR_TEST b

WHERE a.EMP_MANGER_ID=b.EMP_ID

AND b.EMP_MANGER_ID IS NOT NULL

2- Selecting Employee and it's Manager(if there is no manager displaying 'No Manager')

SELECT a.EMP_NAME AS Employee,NVL(b.EMP_NAME,'No Manager') AS Manager FROM ISR_TEST a,ISR_TEST b

WHERE a.EMP_MANGER_ID=b.EMP_ID

HAVING a.EMP_MANGER_ID IS NULL

How to insert into a table with just one IDENTITY column

CREATE TABLE [dbo].[tbl_test] ([ID] [INT] IDENTITY(101, 1) NOT NULL) ON [PRIMARY]

INSERT INTO [tbl_test] DEFAULT

VALUES

select * from [tbl_test]

DROP TABLE [tbl_test]

Identity Example

CREATE TABLE PARENT (ID INT IDENTITY);

CREATE TABLE CHILD (ID INT IDENTITY(100, 1));

CREATE TRIGGER Parentins ON PARENT

FOR INSERT

AS

BEGIN

INSERT CHILD DEFAULT

VALUES

END;

SELECT ID

FROM PARENT;

--id is empty.

SELECT ID

FROM CHILD;

--ID is empty.

Do the following in Session 1

INSERT PARENT DEFAULT

VALUES;

SELECT @@IDENTITY;

/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();

/* Returns the value 1. This was inserted by the

INSERT statement two statements before this query.*/

SELECT IDENT_CURRENT('Child');

/* Returns value inserted into Child, that is in the trigger.*/

SELECT IDENT_CURRENT('Parent');

/* Returns value inserted into Parent.

This was the INSERT statement four statements before this query.*/

Do the following in Session 2.

SELECT @@IDENTITY;

/* Returns NULL because there has been no INSERT action

up to this point in this session.*/

SELECT SCOPE_IDENTITY();

/* Returns NULL because there has been no INSERT action

up to this point in this scope in this session.*/

SELECT IDENT_CURRENT('Child')