Sql Query which is need for Developer

Sql Query which is need for Developer

SQL Commands:

SQL commands are instructions used to communicate with the database to perform specific task that work with data. SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create tables, add data to tables, or modify data, drop the table, set permissions for users. SQL commands are grouped into four major categories depending on their functionality:

·         Data Definition Language (DDL) - These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.

·         Data Manipulation Language (DML) - These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE.

·         Transaction Control Language (TCL) - These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.

·         Data Control Language (DCL) - These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

--Use of COALESCE in Sql Server

DECLARE @listStr VARCHAR(MAX)

SELECT @listStr = COALESCE(@listStr+',' ,'') + cat_name

FROM dbo.tbl_catagory

SELECT @listStr

where Cat_name is the Collume Name and tbl_catagory is the Table Like

  id               Cat_name                modify_date

1001             Veg                        21 Aug 2013

1002             Non-Veg                 21 Aug 2013

Output is

Veg,Non-Veg

--How To Show all index which created in particular Database.

SELECT

            so.name AS TableName

             ,si.name AS IndexName

            , si.type_desc AS IndexType

FROM

            sys.indexes si

            JOIN sys.objects so ON si.[object_id] = so.[object_id]

WHERE

            so.type = 'U'    --Only get indexes for User Created Tables

            AND si.name IS NOT NULL

ORDER BY

            so.name, si.type

--Use of ROW_NUMBER function

SELECT us.row ,us.Id

FROM (SELECT ROW_NUMBER() OVER (ORDER BY id) AS Row, Id

     FROM Tbl_Log )  us

WHERE Row = 3

--select all database name which exists in Sql Server  (system database and user defind database)

select name as [Data Base name]  from sys.databases

--to show all table in particular database

SELECT distinct Table_Name FROM information_schema.columns

--How many table are created in particular database

SELECT count(distinct Table_Name) FROM information_schema.columns

--how many data types are used in particular database

select count(distinct data_type) from information_schema.columns

--to show all table name,all column name,all data type name from particular database

SELECT  Table_Name, Column_Name, Data_Type FROM information_schema.columns

--select data from existing table where field is null

select * #temp1 from emp where addr is null

-- to show all data type which are use in particular database

select distinct Data_Type from information_schema.columns

-- to show how many columns are created in particular table

 select count(column_name) from information_schema.columns where table_name='emp'

--create new table structure from existing table

select * into #temp1 from emp where 1=2

--create new table structure and all data from existing table(structure and data where #temp1 is new temporary table and emp is existing table)

select * into #temp1 from emp

--Inserting  all data from existing table(structure and data ) to existing table (structure and data)

INSERT INTO #temp1 SELECT * FROM dbo.emp

--to show all column name in particular table and  particular database

select Column_Name from information_schema.columns where Table_Name='emp'

to show all Table name in particular database or selected database

select * from sys.tables

to show all Database name in Sql Server SELECT name FROM sys.databases

to show all Database name in Sql Server 

SELECT name FROM sys.sysdatabases

to show all Table name & created Date  in particular database or selected database in Sql Server 

SELECT [name] AS [TableName], [create_date] AS [CreatedDate] FROM sys.tables

to count all Table name in particular database or selected database

select count(name) as [Total_Number_Of_Table] from sys.tables

to count all Database name in SQL Server

select count(name) as [Total_Number_Of_Data_base] from sys.sysdatabases

How to check a table created or not in selected database

How to check a table created or not in selected database

if  exists (select name from syscolumns    where id=object_id('treetable') )

begin

  print'table Exist''

end

else

begin

print 'table not Exist'

                 end

How to check a database created or not

if exists (select * from sys.sysdatabases   where name='emp')

         

begin

         

          print'Database exist'

         

          end

else

          begin

          print'Database exist'

          end

note:- where emp is database name

How to check a trigger created or not in current database

if exists (select * from sysobjects where name='Trigger_name' and xtype='TR')

    print 'Trigger exists'

else

    print 'Trigger does not exist'

How to check a Stored Procedure created or not in current database

if exists (select * from sysobjects where name='stored_procedure name' and xtype='P')

    print 'stored_procedure exists'

else

    print 'stored_procedure  not exist'

Write a sql query for nth maximum Salary from Salary Table

For n^th  =n-1

2^nd =2-1

4^th =4-1

we have salary table

create table tbl_salary(id int not null primary key identity(1,1),name varchar(50),salary int)

     

     

      insert into tbl_salary(name,salary) values('Rahul',30000)

      insert into tbl_salary(name,salary) values('Mukta',31000)

      insert into tbl_salary(name,salary) values('Arnita',32000)

      insert into tbl_salary(name,salary) values('Sachi',33000)

      insert into tbl_salary(name,salary) values('Surbhi',34000)

      insert into tbl_salary(name,salary) values('Soni',35000)

      insert into tbl_salary(name,salary) values('Suman',36000)

     

      select * from tbl_salary

     

Now write the query to  show 3rd maximum salary

select name,salary from tbl_salary sal1 where (3-1) =(select Count(Distinct (sal2.salary)) from tbl_salary sal2 where sal2.salary > sal1.salary)

    We have two Table:-

One Member and another Table  is product.

create table member (memberid int primary key,member_name varchar(50),productid int)

insert into member values(1,'a1',1)

insert into member values(2,'a2',1)

insert into member values(3,'a3',1)

insert into member values(4,'b1',2)

insert into member values(5,'b2',2)

insert into member values(6,'b3',2)

insert into member values(7,'c1',3)

insert into member values(8,'c2',3)

insert into member values(9,'c3',3)

select * from member

another table :-

create table product(productid int primary key,product_name varchar(50))

insert into product values(1,'a')

insert into product values(2,'b')

insert into product values(3,'c')

select * from product

Write a Sql Query to show the desired output is as bellow

The Desired Sql Query is :-

select product.*,[member name]=(select top 1 member.member_name from member where

member.productid=product.productid) from product

Write a Sql Query to show the how many member used each product

The Desired Sql Query is :-

select product_table.*,[Total Member number]=(select count( member.member_name) from member where member.productid=product_table.productid) from product_table

The SQL ALTER TABLE command is used to modify the definition (I mean structure) of a table by modifying the definition of its columns. The ALTER command is used to perform the following functions.

1)   Add, drop, modify table columns

2)   Add and drop constraints

3)   Enable and Disable constraints

 Syntax to add a column

ALTER TABLE table_name ADD column_name datatype;

For Example: To add a column "experience" to the emp table, the query would be like

ALTER TABLE emp ADD experience number(3);

Syntax to drop a column

ALTER TABLE table_name DROP column_name;

For Example: To drop the column "location" from the emp table, the query would be like

ALTER TABLE emp DROP location;

Syntax to modify a column

ALTER TABLE table_name MODIFY column_name datatype;

For Example:

To modify the column salary in the emp table, the query would be like

ALTER TABLE emp MODIFY salary number(15,2);

 SQL Command to rename table name

The SQL RENAME command is used to change the name of the table or a database object.

Syntax to rename a table

RENAME old_table_name To new_table_name

For Example: To change the name of the table employee to emp

RENAME employee TO emp

Syntax to create Index:

CREATE INDEX index_name ON table_name (column_name1,column_name2...)

Syntax to create SQL unique Index:

CREATE UNIQUE INDEX index_name ON table_name (column_name1, column_name2…);

SQL HAVING Clause

Having clause is used to filter data based on the group functions.

This is similar to WHERE condition but is used with group functions.

But WHERE Clause cannot be used with Group functions.

HAVING clause can be used with Group functions.

SELECT dept, SUM (salary) FROM employee GROUP BY dept HAVING SUM (salary)> 25000

SQL Integrity Constraints

Integrity Constraints are used to apply business rules for the database tables.

The constraints available in SQL are Foreign Key, Not Null, Unique, Check.

Constraints can be defined in two ways 
1) The constraints can be specified immediately after the column definition. This is called column-level definition. 
2) The constraints can be specified after all the columns are defined. This is called table-level definition.

1) SQL Primary key:

This constraint defines a column or combination of columns which uniquely identifies each row in the table.

Syntax to define a Primary key at column level:

column name datatype [CONSTRAINT constraint_name] PRIMARY KEY

Syntax to define a Primary key at table level:

[CONSTRAINT constraint_name] PRIMARY KEY (column_name1,column_name2,..)

·         column_name1, column_name2 are the names of the columns which define the primary Key.

The syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional.

For Example: To create an employee table with Primary Key constraint, the query would be like.

Primary Key at table level:

CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
salary number(10), 
location char(10) 
)

or

CREATE TABLE employee
( id number(5) CONSTRAINT emp_id_pk PRIMARY KEY, 
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10)
);

Primary Key at table level:

CREATE TABLE employee 
( id number(5), 
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
CONSTRAINT emp_id_pk PRIMARY KEY (id)
);

2) SQL Foreign key or Referential Integrity :

This constraint identifies any column referencing the PRIMARY KEY in another table. It establishes a relationship between two columns in the same table or between different tables. For a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table which it is referring. One or more columns can be defined as Foreign key.

Syntax to define a Foreign key at column level:

[CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(column_name)

Syntax to define a Foreign key at table level:

[CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name);

For Example:

1) Lets use the "product" table and "order_items". 
Foreign Key at column level:

CREATE TABLE product 
( product_id number(5) CONSTRAINT pd_id_pk PRIMARY KEY, 
product_name char(20),
supplier_name char(20),
unit_price number(10)
);

CREATE TABLE order_items
( order_id number(5) CONSTRAINT od_id_pk PRIMARY KEY,
product_id number(5) CONSTRAINT pd_id_fk REFERENCES, product(product_id),
product_name char(20),
supplier_name char(20),
unit_price number(10)
);

Foreign Key at table level:

CREATE TABLE order_items
( order_id number(5) ,
product_id number(5),
product_name char(20),
supplier_name char(20),
unit_price number(10)
CONSTRAINT od_id_pk PRIMARY KEY(order_id),
CONSTRAINT pd_id_fk FOREIGN KEY(product_id) REFERENCES product(product_id)
);

2) If the employee table has a 'mgr_id' i.e, manager id as a foreign key which references primary key 'id' within the same table, the query would be like,

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
mgr_id number(5) REFERENCES employee(id),
salary number(10),
location char(10) 
);

3) SQL Not Null Constraint :

This constraint ensures all rows in the table contain a definite value for the column which is specified as not null. Which means a null value is not allowed.

Syntax to define a Not Null constraint:

[CONSTRAINT constraint name] NOT NULL

For Example: To create a employee table with Null value, the query would be like

CREATE TABLE employee
( id number(5),
name char(20) CONSTRAINT nm_nn NOT NULL,
dept char(10),
age number(2),
salary number(10),
location char(10) 
);

4) SQL Unique Key:

This constraint ensures that a column or a group of columns in each row have a distinct value. A column(s) can have a null value but the values cannot be duplicated.

Syntax to define a Unique key at column level:

[CONSTRAINT constraint_name] UNIQUE

Syntax to define a Unique key at table level:

[CONSTRAINT constraint_name] UNIQUE(column_name)

For Example: To create an employee table with Unique key, the query would be like,

Unique Key at column level:

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10) UNIQUE 
);

or

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10) CONSTRAINT loc_un UNIQUE 
);

Unique Key at table level:

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
CONSTRAINT loc_un UNIQUE(location) 
);

5) SQL Check Constraint :

This constraint defines a business rule on a column. All the rows must satisfy this rule. The constraint can be applied for a single column or a group of columns.

Syntax to define a Check constraint:

[CONSTRAINT constraint_name] CHECK (condition)

For Example: In the employee table to select the gender of a person, the querywould be like

Check Constraint at column level:

CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
gender char(1) CHECK (gender in ('M','F')), 
salary number(10), 
location char(10) 
);

Check Constraint at table level:

CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
gender char(1), 
salary number(10), 
location char(10), 
CONSTRAINT gender_ck CHECK (gender in ('M','F')) 
);

TO show all row value of one column with comma saperate  Like

Ramu

Rahul

Output is Ramu,Rahul Like that…………………….

Varchar Data…………………….

SELECT SUBSTRING((SELECT ',' + s.Name FROM t33 s ORDER BY s.Name FOR XML PATH('') ),2,200000)

Int Data Like

1,2,3,4,5

SELECT SUBSTRING((SELECT ',' +cast(s.roll as varchar(40)) FROM t33 s ORDER BY s.roll FOR XML PATH('')) ,2,200000)