SQL SERVER - What is - DML, DDL, DCL and TCL - Introduction and Examples

==========================================================================
-----------------------Aggregate functions--------------------------------
==========================================================================

---aggregate functions-----
select t=substring('mani' ,1,3)

select substring(fldvchar_coun_name,1,1) from tblCountryMaster where fldvchar_coun_name like 'I%'

-- to create a new table and insert

SELECT * FROM New Table ( INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' ) ) AS t

---union---
select * from tblcustomermaster
union
select * from tblItemmaster

use dbsample

----To creating a stored procedures---------
create procedure samp_proc
(
@id int,
@name varchar(25)
)
as
insert into
 samp(id,name)
values
 (@id,@name)

samp_proc 1,'mani'
samp_proc 2,'Raj'
samp_proc 3,'Kamal'
samp_proc 4,'Ajay'
samp_proc 5,'Siva'
samp_proc 6,'Sam'

select * from samp

 ---aggregate functions
select t=substring('mani' ,1,1)

select substring(fldvchar_coun_name,1,1) from tblCountryMaster where fldvchar_coun_name like 'I%'

-- to create a new table and insert

SELECT * FROM NEW TABLE ( INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' ) ) AS t

---union---
select * from tblcustomermaster
union
select * from tblItemmaster
use dbsample

---To use Intersect----
SELECT *
FROM Order
WHERE Quantity BETWEEN 1 AND 100

INTERSECT

SELECT *
FROM Order
WHERE Quantity BETWEEN 50 AND 200;

----to use Except operator-------
SELECT *
FROM Order
WHERE Quantity BETWEEN 1 AND 100

EXCEPT

SELECT *
FROM Order
WHERE Quantity BETWEEN 50 AND 75;

-------avg function-----
select avg(cust_salary) as Average from tblemp
select sum(cust_salary) as Totalsum from tblemp
--COUNT always returns an int data type value.
select count(cust_salary) as TotalRows from tblemp
select count(*) as TotalRows from tblemp
--COUNT_BIG always returns a bigint data type value.
select count_big(cust_salary) as TotalRows from tblemp
select max(cust_salary) as TotalRows from tblemp
select min(cust_salary) as TotalRows from tblemp
select var(cust_salary) as Varience from tblemp
select varp(cust_salary) as Varienceforthepopulation from tblemp
select stdev(cust_salary) as StandardDeviation from tblemp
select stdevp(cust_salary) as StandardDeviationforthepopulation  from tblemp

-----DateTime functions-------
select GetDate() as CurrentDateTime
select current_timestamp as CurrentDateTime
select {fn now()} as CurrentDateTime
select Day('04/03/1964')
select Month('04/03/1964')
select year('04/03/1964')
select DateName(yy,'04/03/1964')
select DateName(mm,'04/03/1964')
select DateName(dd,'04/03/1964')
select DateName(wk,'04/03/1964')
select DateName(dy,'04/03/1964')
select DateName(dw,'04/03/1964')
select DateName(hh,'04/03/1964 12:00:22.000')
select DateName(mi,'04/03/1964 12:05:22.000')
select DateName(ss,'04/03/1964 12:00:22.000')
select DateName(ms,'04/03/1964 12:00:22.023')

---Add month/date/year
select Dateadd(yy,50,'04/03/1964')--DATEADD ( datepart , number, date )
SELECT DATEADD(yyyy,12,'12/09/1964')--year
SELECT DATEADD(mm,12,'12/09/1964')--month
SELECT DATEADD(dd,21,'12/09/1964')--date
SELECT DATEADD(ss,122,'12/09/1964')--seconds
SELECT DATEADD(mi,12,'12/09/1964')--minite
SELECT DATEADD(ms,12,'12/09/1964')--milli seconds
SELECT DATEADD(hh,12,'12/09/1964')--hour
SELECT DATEADD(wk,12,'12/09/1964')--week days
SELECT DATEADD(dy,12,'12/09/1964')--day of the year
SELECT DATEADD(dw,12,'12/09/1964')--Day of week

---date Differences---
SELECT DATEDIFF(dd,'12/10/1961','12/15/1964')--DATEDIFF ( datepart , startdate , enddate )
SELECT DATEDIFF(yyyy,'12/10/1961','12/09/1964')--year
SELECT DATEDIFF(mm,'12/10/1961','12/25/1964')--month
SELECT DATEDIFF(dd,'12/09/1964','12/20/1964')--date
SELECT DATEDIFF(ss,'12/10/1961','12/09/1964')--seconds
SELECT DATEDIFF(mi,'12/10/1961','12/09/1964')--minite
SELECT DATEDIFF(ms,'12/10/1964','12/11/1964')--milli seconds
SELECT DATEDIFF(hh,'12/10/1964','12/11/1964')--hour
SELECT DATEDIFF(wk,'12/10/1961','12/09/1964')--week days
SELECT DATEDIFF(dy,'12/10/1961','12/09/1964')--day of the year
SELECT DATEDIFF(dw,'12/10/1961','12/09/1964')--day of the week

-----Mathematical functions-------
SELECT ABS(-1.0), ABS(0.0), ABS(1.20)
SELECT sin(20),sin(-20)
SELECT cos(30),cos(30),cos(0)
SELECT tan(-1.0),tan(0.0),tan(1.0)
SELECT CONVERT(varchar,Asin(-1))
SELECT CONVERT(varchar, ACOS(-1))
SELECT ASCII('M')
SELECT ASCII('K')
SELECT ASCII(SUBSTRING('GOD',1,1))
SELECT CONVERT(varchar, ATAN(-45.01))
SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0)
SELECT CONVERT(varchar, DEGREES((PI()/2)))
SELECT CONVERT(varchar,EXP(252.164))
SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45)
SELECT CONVERT(varchar,LOG(215.12))
SELECT CONVERT(varchar,LOG10(25.23))
SELECT PI()
SELECT RAND(159784)
SELECT ROUND(123.9994, 3),ROUND(123.9995, 3)
SELECT 1000+10
SELECT 1000-10
SELECT 1000*10
SELECT 1000/10
SELECT 1000%11
SELECT 1000 & 10
SELECT CUST_NAME | CUST_SALARY FROM TBLEMP
SELECT ISNULL(NULL,'MANI')--MANI Replaces instead of null
SELECT ISNULL(NULL,10)--10 Replaces instead of null
SELECT ISNUMERIC(10) ---if true it returns 1
SELECT ISNUMERIC('mani')---if false it returns 0

-------use of Declare statement-------
--Method 1:
Declare @a as int ---@a ia a varible
set @a=10 ---Assign the value as corresponding Datatype
print @a  ---Display the result

--Method 2:
Declare @a as int,@b as int,@c as int,@d as varchar(20)
set @a=10
set @b=20
set @c=@a+@b
set @d='Hai'
print @c
print convert(varchar,@d)
PRINT 'Berkeley author'

--Method 3:
Declare @a as varchar(20),@b as float
set @a='ma%'
set @b=15.23
select cust_name,cust_salary * @b from tblemp where cust_name like @a

--Method 4:
Declare @a as varchar(20)
select @a=cust_name from tblemp
print @a

 Thanks and Regards,

Manivannan