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 nth =n-1
2nd =2-1
4th =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);
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) 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.
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)
)
( 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)
);
( 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)
);
( 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)
);
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:
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)
);
( 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)
);
( 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)
);
( 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)
);
( 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)
);
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)
);
( id number(5),
name char(20) CONSTRAINT nm_nn NOT NULL,
dept char(10),
age number(2),
salary number(10),
location char(10)
);
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
);
( 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
);
( 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)
);
( 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)
);
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)
);
( 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'))
);
( 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)
0 comments:
Post a Comment