Sql training online

--> Applying Unique Key constraint in SQL Table
Key is used for non-primary key data.
----------------------------------------------------

Create Table OrderDetails
(
ItemCode int NOT NULL constraint pkPrimaryKey Primary Key,
ItemName varchar(30) NOT NULL,
Qty int NOT NULL,
Price float NOT NULL,
Total float NOT NULL,
OrderDate datetime NOT NULL,
OrderID int NOT NULL constraint unqOrderId Unique,
Contact varchar(30)NOT NULL,
City varchar(30)NOT NULL,
Remarks varchar(100)NOT NULL
)

Select * from OrderDetails

insert into OrderDetails values('1','Laptop',1,100,1*100,GETDATE(),1,'2222222','NY','Order from ')

insert into OrderDetails values('2','Laptop2',2,200,2*200,GETDATE(),2,'5555554','SS','Order Success')

-> Primary Key after Table creation
-----------------------------------------
Create Table OrderDetails
(
ItemCode Int NOT NULL,
ItemName varchar(30)NOT NULL,
Qty int NOT NULL,
Price float NOT NULL,
TotalAmount float NOT NULL,
OrderDate datetime NOT NULL,
ContactNo varchar(30) NOT NULL,
City varchar(30)NOT NULL,
Stat varchar(30)NOT NULL,
Country varchar(30) NOT NULL,
eMail varchar(100) NOT NULL,
Remarks varchar(100) NULL,
)
Select * from OrderDetails
--Adding constraint
Alter table OrderDetails add constraint pkPrimaryKey Primary Key(ItemCode)

Insert into OrderDetails values(1,'Dell Leptop',1,200,1*200,GETDATE(),'123456789','NY','NY','USA','[email protected]',NULL)
Insert into OrderDetails values(2,'Dell Leptop',2,200,2*200,GETDATE(),'344483849','NY','NY','USA','[email protected]',NULL)
Select * from OrderDetails

--dropping primary key
Create Table OrderDetails
(
OrderId int not null constraint pkPrimaryKey1 primary key,
ItemName varchar(20)not null,
Qty int not null,
Price float not null,
Total float not null,
Orderdate datetime not null,
Contact varchar(20)not null,
Country varchar(30)not null,
City varchar(20)not null,
EMail varchar(50)not null,
Remarks varchar(20)NULL
)

Select * from OrderDetails

Insert into OrderDetails values ('1','iPhone 20',2,2000,2*2000,GETDATE(),'123456789','USA','NY',
'[email protected]',NULL)

Select * from OrderDetails
--Insert same orderid

Insert into OrderDetails values ('1','iPhone 20',1,2000,1*2000,GETDATE(),'123456789','USA','NY','[email protected]', NULL)

--Now Drop primary key constraint

Alter table OrderDetails drop pkPrimaryKey1

--again insert same orderid

Insert into OrderDetails values ('1','iPhone 20',1,2000,1*2000,GETDATE(),'123456789','USA','NY',
'[email protected]',NULL)

Select * from OrderDetails

--Primary key constraint deleted successfully..

Alter table OrderDetails drop pkPrimaryKey1

--Syntax to drop primary key
--Alter table TABLE_NAME Drop constraint_key_name

Primary Key SQL / Primary Key Constraint in SQL
**********************************************************
-- Primary Key Constraint: Primary Key constraint is used to create unique value in the field/column.
-- Primary Key constraint: It can not accept NULL Value.
--Example:

Create Table OrderDetails
(
OrderId int NOT NULL constraint pkPrimary Primary Key,--Applying Primary key 'pkPrimary' is name of Primary key
ProductId int NOT NULL,
ProductName varchar(30)NOT NULL,
Price float not null,
Quantity int NOT NULL,
SumAmount float NOT NULL,
Country varchar(30)NOT NULL,
ZipCode varchar(20)NOT NULL,
ContactNo Varchar(30)NOT NULL,
eMail varchar(50)NOT NULL,
OrderDate Datetime NOT NULL,
CustomerId int NOT NULL,
DeliveryDate Date not null,
TrackingID int not null,
Remarks varchar(300)
)

Select * from OrderDetails

--Now Inserting vlaues

Insert into OrderDetails values('1','1','DEL Laptop',200,50,200*50,'Korea',' # # ',' # # # # # # # # ','[email protected]',GETDATE(),2,GETDATE(),05,'Fast')

--We have used Primary Key Constraint on OrderId. we are going to check.
--Here is, the OrderId 1 is already inserted. Again we are trying to insert same order id

Insert into OrderDetails values('2','1','Dell Desktop',150,20,150*20,'USA',' # # ',' # # # # # # # # ','[email protected]',getdate(),2,getdate(),15,'Fast')

--Here is an error because we have inserted sam order id

Insert into OrderDetails values('3','2','Acer',150,50,150*50,'Canada',' # # ',' # # # # # # # # ','[email protected]',GETDATE(),4,GETDATE(),06,'Fast')

Insert into OrderDetails values('4','4','iPhone',100,300,100*50,'UK',' # # ',' # # # # # # # # ','[email protected]',GETDATE(),6,GETDATE(),07,'Fast')

Insert into OrderDetails values('5','1','iPhone',100,100,100*100,'Korea',' # # ',' # # # # # # # # ','[email protected]',GETDATE(),2,GETDATE(),09,'Fast')

Insert into OrderDetails values('6','1','Mouse',20,100,20*100,'Korea',' # # ',' # # # # # # # # ','[email protected]',GETDATE(),2,GETDATE(),12,'Fast')

Insert into OrderDetails values('7','5','DELL Server',500,50,500*50,'USA',' # # ',' # # # # # # # # ','[email protected]',GETDATE(),8,GETDATE(),11,'Fast')

Select * from OrderDetails
--Some more query
Select * from OrderDetails where OrderId='1'
Select * from OrderDetails where CustomerId='2'
Select SUM(SumAmount) from OrderDetails Where ProductId='1'
Select COUNT(CustomerId) from OrderDetails where CustomerId='2'

DENTITY in SQL / Auto Increment number in SQL
-------------------------------------------------------------------------
Create table NewProduct
(
NID int identity(1,1)not null,
Name char(20)not null,
Quantity int not null,
Price float not null,
Country char(20)not null,
Description varchar(200) not null
)

Select * from NewProduct

Insert into NewProduct values('iPhone Pro',10,300,'United Stat','iPhone Pro new item')

Insert into NewProduct values('iPhone Pls',5,250,'United Stat','iPhone+ New item')

Insert into NewProduct values('Dell Pro',2,350,'United Stat','DELL Pro Laptop')

Insert into NewProduct values('Dell Pro',1,400,'United Stat','DELL Pro Server Computer')

Select * from NewProduct

--NID is auto increased.
--One more example.
Insert into NewProduct values('Samsung Pro Laptop',3,200,'Korea','Samsung Pro Laptop new item')
Select * from NewProduct

SQL NULL and NOT NULL / SQL IS NULL and IS NOT NULL Operator
***************************************************
--NULL and NOT NULL
--NULL: No Value
--NOT NULL: With Value
--Creating Table with NULL and NOT NULL

create table ItemDetails
(
ItemCode int NOT NULL,
Name varchar(30)NOT NULL,
Quantity int NOT NULL,
Price float NULL,
Category varchar(30)NOT NULL,
Remarks varchar(200)NULL
)
Select * from ItemDetails

--Now inserting value(s) into table with NULL value
insert into ItemDetails values('1','Samsumg Laptop',10,200,'Computer','Samsumg Laptop')
Insert into ItemDetails values('2','Samsumg Computer',2,NULL,'Computer',NULL)
Insert into ItemDetails values('3','Dell Computer',1,300,'Computer',NULL)

Insert into ItemDetails Values('4','Dell Laptop',2,NULL,'Computer',NULL)
Insert into ItemDetails values('5','Sony Laptop',1,NULL,'Computer','Sony Laptop')

Select * from ItemDetails

--Displaying NULL and NOT NULL Value(s) from table.
Select * from ItemDetails where Price IS NULL
Select * from ItemDetails where Price IS NOT NULL

Select * from ItemDetails where Remarks IS NULL
Select * from ItemDetails where Remarks IS NOT NULL

How insert values into table in SQL?
--How to insert values into table?

Create table ItemDetails
(
ItemCode int,
Name varchar(30),
Quantity int,
Price float,
Country varchar(20),
Remarks varchar(300)
)
Select * from ItemDetails

--Inserting values into table
--Method 1
Insert into ItemDetails Values('1','Dell Computer',10,300,'USA','Made In USA')
Insert into ItemDetails Values('3','Dell Laptop',20,300,'USA','Made In USA')

--Method 2

Insert into ItemDetails(ItemCode, Name,Quantity,Price,Country,Remarks)values('2','Samsung Laptop',5,200,'Korea','Made In Korea')

Insert into ItemDetails(ItemCode,Name, Quantity,Price,Country,Remarks)Values('4','Samsung Computer',1,200,'Korea','Made In Korea')

Insert into ItemDetails(ItemCode,Name,Quantity,Price,Country,Remarks)values('5','Sony Laptop',2,250,'Japan','Made In Japan')

Select * from ItemDetails

Creating Table in Sql Server
=====================
--Creating Table(s) in Sql Server

Create Table Customer
(
Id int,
FirstName varchar(30),
LastName Varchar(30),
Gender char,
Age int,
DateOfBirth date,
Phone varchar(15),
Email varchar(50),
City varchar(30),
State varchar(30),
PostCode varchar(10),
Country varchar(30),
Remarks varchar(200)
)

--Checking Table
Select * from Customer

Date Different Example in SQL Server

reate table OrderDet
(
OrderId int not null,
ProductId int not null,
OrderDate Date not null,
OrderQuantity int not null
)
insert into OrderDet Values('1','01','2020-03-01','5')
insert into OrderDet Values('2','02','2020-04-02','5')
insert into OrderDet Values('3','03','2020-05-01','5')
insert into OrderDet Values('4','04','2020-05-02','5')
insert into OrderDet Values('5','05','2020-06-01','5')
insert into OrderDet Values('6','06','2020-08-02','5')

Select * from OrderDet

Select DATEDIFF(YY,OrderDate, '2021-06-12') as 'Year' From OrderDet
Select DATEDIFF(qq,OrderDate, '2020-06-12') as 'Quarter' from OrderDet
Select DATEDIFF(mm,OrderDate, '2020-08-06') as 'Month' from OrderDet
Select DATEDIFF(dayofyear,OrderDate,'2020-08-06') as 'Day Of Year' from OrderDet
Select DATEDIFF(dy,OrderDate, '2020-08-06') as 'Day' from OrderDet
Select DATEDIFF(ww,OrderDate, '2020-08-06') as 'Week' from OrderDet
Select DATEDIFF(w,OrderDate, '2020-08-06') as 'weekday' from OrderDet
Select DATEDIFF(hh,OrderDate, '2020-08-06') as 'Hour' from OrderDet
Select DATEDIFF(mi,OrderDate, '2020-08-06') as 'Minute' from OrderDet
Select DATEDIFF(ss,OrderDate, '2020-08-06') as 'Second' from OrderDet

Select * from OrderDet

SL Date Different

/*
year, yyyy, yy = Year
quarter, qq, q = Quarter
month, mm, m = month
dayofyear = Day of the year
day, dy, y = Day
week, ww, wk = Week
weekday, dw, w = Weekday
hour, hh = hour
minute, mi, n = Minute
second, ss, s = Second
millisecond, ms = Millisecond
*/
Select DATEDIFF(YYYY,'2000-01-01', GETDATE()) as Year
Select DATEDIFF(qq,'2000-01-01', GETDATE()) as Quarter
Select DATEDIFF(mm,'2000-01-01', GETDATE()) as Month
Select DATEDIFF(dayofyear,'2000-01-01',Getdate()) as 'Day Of Year'
Select DATEDIFF(dy,'2000-01-01', GETDATE()) as Day
Select DATEDIFF(ww,'2000-01-01', GETDATE()) as Week
Select DATEDIFF(w,'2000-01-01', GETDATE()) as weekday
Select DATEDIFF(hh,'2000-01-01', GETDATE()) as Hour
Select DATEDIFF(mi,'2000-01-01', GETDATE()) as Minute
Select DATEDIFF(ss,'2000-01-01', GETDATE()) as Second

Date Add in SQL Server. (Year Add)

/*
Date add format in SQL Server

Add Year
year, yyyy, yy=Year

Example:
Select DATEADD(yy,3,getdate())
Select DATEADD(yyyy,3,getdate())
Select DATEADD(year,3,getdate())
*/
Select GETDATE()--current date
Select DATEADD(yy,3,getdate())
Select GETDATE(),DATEADD(yyyy,3,getdate())
Select GETDATE()as CurrentDate, Dateadd(year,3,getdate()) as New_Date

[For more tutorials: https://sqlquery123.blogspot. com/]