Queries For reference

Queries For Reference

Creating Table Employee


create table Employe
(Empid int primary key identity,
Empname varchar(20),
Dob datetime,
Doj datetime,
sex varchar(10))

Creating table Depart

create table Depart(
Depid int not null,
Empid int not null,
Dept varchar(15),
Salary int)

Alter Primary Key

alter table Departnew add primary key(depid)


select * from Employe
select * from Employe where Empid=2
select * from Employe where Empid!=2
select Empid,Empname from Employ

Top Queries

Select Top 1 * from Employe
select Top 25 percent * from Employe
select top 25 percent Empid,Empname from Employe
select top 25 percent * from Employe where Empid=1

Using Top query in Descending Order
select top 25 percent * from Employe order by Empid Desc

Distinct Queries

select distinct Empname from Employe
select COUNT(distinct Empname)from Employe

Query Multiple Tables

select Employe.Empname,Employe.Dob,Depart.Depid,Depart.Salary from Employe,Depart where Employe.Empid=Depart.Depid

Sub Queries

select Empid,Empname from Employe where exists(Select Empid from Depart where Employe.Empid=Depart.Empid)

select Depid,Empid,Dept,Salary from Depart where Salary in(Select MAX(Salary)from Depart)


Orderby

select * from Trainee.dbo.Depart order by Empid desc

Aggregate Function

select AVG(Salary)"Average salary"from Trainee.dbo.Depart

select * from Trainee.dbo.Depart where Salary=(select MIN(Salary)from Trainee.Dbo.Depart)

select * from Trainee.dbo.Depart where Salary=(select MAX(Salary)from Trainee.Dbo.Depart)



To create databackup in table without making schema

select * into Worker1 from Employe

Union
select * from Worker1 union Select * from Employe

Union all
select * from Worker1 unionall Select * from Employe

Inner Join
select * from Employe join Depart on Employe.Empid=Depart.Empid

Left Join

LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
select * from Employe left join Depart on Employe.Empid=Depart.Empid

Right Join

RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
select * from Employe Right join Depart on Employe.Empid=Depart.Empid


FULL JOIN

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

select * from Employe FULL JOIN Depart on Employe.Empid=Depart.Empid

SQL Constraints

Constraints are used to limit the type of data that can go into a table.
NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK,DEFAULT
SQL DEFAULT Constraint
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

Group By function

select Depid,sum(Salary) from Depart group by Depid


Count Function

select COUNT (*) from Employe

Count Disinct Column

SELECT COUNT(DISTINCT Dept) FROM Depart

To find Length

SELECT len(Empname) from Employe

EXCEPT operator
SELECT * from Employe where Empid between 1 AND 8
EXCEPT
SELECT * from Employe where Empid between 5 and 8

INTERSECT operator

The SQL INTERSECT operator takes the results of two queries and returns only rows that appear in both result sets
SELECT * from Employe where Empid between 1 AND 8
intersect
SELECT * from Employe where Empid between 5 and 8
Using the % Wildcard and like operator
select * from Employe where Empname like 'a%'

Dateadd

SELECT Empid,DATEADD(day,45,Doj) AS Doj
FROM Employe
Datediff
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='1984-02-29'
SET @EndDate ='2008-02-29'
SELECT DATEDIFF(Year, @StartDate, @EndDate) AS NewDate
Quarter Difference

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-05'
SELECT DATEDIFF(quarter, @StartDate, @EndDate) AS NewDate

Month Difference
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(Month, @StartDate, @EndDate) AS NewDate


DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(dayofyear,@StartDate, @EndDate) AS NewDate
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(Day, @StartDate, @EndDate) AS NewDate
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(Week, @StartDate, @EndDate) AS NewDate

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(Hour, @StartDate, @EndDate) AS NewDate
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(minute, @StartDate, @EndDate) AS NewDate
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(second, @StartDate, @EndDate) AS NewDate
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-06-06'
SELECT DATEDIFF(millisecond, @StartDate, @EndDate) AS NewDate
Datetime format
Value of current Date Time GETDATE()
SELECT (GETDATE()) = 2007-06-06 23:41:10.153


SELECT CONVERT(varchar,GETDATE(),0)
Return Value = Jun 6 2007 11:07PM
SELECT CONVERT(varchar,GETDATE(),100)
Return Value = Jun 6 2007 11:07PM


SELECT CONVERT(varchar,GETDATE(),1)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),101)
Return Value = 06/06/2007


SELECT CONVERT(varchar,GETDATE(),2)
Return Value = 07.06.06
SELECT CONVERT(varchar,GETDATE(),102)
Return Value = 2007.06.06


SELECT CONVERT(varchar,GETDATE(),3)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),103)
Return Value = 06/06/2007


SELECT CONVERT(varchar,GETDATE(),4)
Return Value = 06.06.07
SELECT CONVERT(varchar,GETDATE(),104)
Return Value = 06.06.2007


SELECT CONVERT(varchar,GETDATE(),5)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),105)
Return Value = 06-06-2007


SELECT CONVERT(varchar,GETDATE(),6)
Return Value = 06 Jun 07
SELECT CONVERT(varchar,GETDATE(),106)
Return Value = 06 Jun 2007


SELECT CONVERT(varchar,GETDATE(),7)
Return Value = Jun 06, 07
SELECT CONVERT(varchar,GETDATE(),107)
Return Value = Jun 06, 2007


SELECT CONVERT(varchar,GETDATE(),8)
Return Value = 23:38:49
SELECT CONVERT(varchar,GETDATE(),108)
Return Value = 23:38:49


SELECT CONVERT(varchar,GETDATE(),9)
Return Value = Jun 6 2007 11:39:17:060PM
SELECT CONVERT(varchar,GETDATE(),109)
Return Value = Jun 6 2007 11:39:17:060PM


SELECT CONVERT(varchar,GETDATE(),10)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),110)
Return Value = 06-06-2007


SELECT CONVERT(varchar,GETDATE(),11)
Return Value = 07/06/06
SELECT CONVERT(varchar,GETDATE(),111)
Return Value = 2007/06/06


SELECT CONVERT(varchar,GETDATE(),12)
Return Value = 070606
SELECT CONVERT(varchar,GETDATE(),112)
Return Value = 20070606


SELECT CONVERT(varchar,GETDATE(),13)
Return Value = 06 Jun 2007 23:40:14:577
SELECT CONVERT(varchar,GETDATE(),113)
Return Value = 06 Jun 2007 23:40:14:577


SELECT CONVERT(varchar,GETDATE(),14)
Return Value = 23:40:29:717
SELECT CONVERT(varchar,GETDATE(),114)
Return Value = 23:40:29:717


SELECT CONVERT(varchar,GETDATE(),20)
Return Value = 2007-06-06 23:40:51
SELECT CONVERT(varchar,GETDATE(),120)
Return Value = 2007-06-06 23:40:51


SELECT CONVERT(varchar,GETDATE(),21)
Return Value = 2007-06-06 23:41:10.153
SELECT CONVERT(varchar,GETDATE(),121)
Return Value = 2007-06-06 23:41:10.153


SELECT CONVERT(varchar,GETDATE(),126)
Return Value = 2007-06-06T23:41:10.153


SELECT CONVERT(varchar,GETDATE(),131)
Return Value = 21/05/1428 11:41:10:153PM