SQL Basic for Beginers

Quick Overview....

- Quick Introduction of SQL
- Create a Database & Tables
- Insert Data
- Select Data
- Where Clauses
- Update Data
- Delete Data
- Change Table (new column, Update column)
- Create a Primary Kay
- Create an Index
- Create More Tables
- Create Foreign Key
- Join and Relationship
- Functions
- Group By

---------------------------------------------------------------------------------------------------------------
What is SQL? ( Stands for Structured Query Language) 
Used for access and manipulating the database.

What is SQL Instance?
Is collection of SQL server databases run in a one single SQL server services.


--Create Database--
create database Customer

use customer

-- Create tables--
create table Customer
(
FirstName varchar(50),
LastName varchar(50),
Age int
)


-- Insert Data into the Table --
insert into dbo.Customer (FirstName, LastName, Age)
Values ('Mahen','Peiris',35),
('Gayan','Perera',40);

insert into Customer (FirstName,LastName,Age)
values ('Saman','Peiris',20);

--Select All Data in the Table--
Select *
From Customer

Use Customer

-- How to find the Firstname & Age who name is Mahen--
Select Firstname,Age
From Customer
Where FirstName = 'Mahen'

-- Show all the details relating to the FirstName = Mahen & Age = 35 --
Select *
From Customer
Where FirstName = 'Mahen'
and Age = 35

-- Insert the Data to the Database--
Insert into Customer (FirstName,LastName,Age,City)
values
('Mahen','Peiris1',37,'NY'),
('Mahen','Peiris2',31,'SF'),
('Mahen','Peiris3',30,'Dallas'),
('Mahen','Peiris4',36,'NJ');

--Using Wildcards, _ & % -- 
Select *
From Customer
where FirstName = 'Mahen'
and LastName like 'Peiris_'


Select *
From Customer
where FirstName = 'Mahen'
and LastName like 'Peiris%'

--Update Details in the database --

update Customer
set Age = 4
Where FirstName = 'Mahen'
and LastName = 'Peiris'

-- To Delete the whole Data in a Table --
--(It's Not get delete the columns but all the data inside the Table get deleted --
Delete Customer

--Delete a specific customer--

Delete Customer
Where FirstName = 'Mahen'
and LastName like 'Peiris_'

/*
Select *
from customer
*/

alter table Customer
add City varchar(50);

update Customer
set city = 'Panadura'
where FirstName = 'Gayan'

Delete Customer
where FirstName = 'Mahen'
and Age = 'Null'


Update Customer
set FirstName = 'Mahen'
where City = 'Moratuwa'

-- Delete the Customer table

Drop table Customer

-- Creating the Primary Key

create table Customer
(
ID int Primary Key identity(1,1),
FirstName varchar(50),
LastName varchar(50),
Age int,
City varchar(50)
)

-- Create a Product Table
create table Products
(
ID int Primary Key Identity (1,1),
ProductName varchar(50),

)


Select *
From Products

insert into Products (ProductName)
values
('Handel'),
('Peddle');

-- Alter the Table Add another column of Price

alter table Products
add Price float;

Update Products
Set Price = 15.25
where ID = 2


-- Creating a Order table
Create Table Orders
(
OrderID int Primary key Identity(1,1),
OrderDate Datetime,
CustomerID int,
ProductID int
)

insert into Orders (OrderDate, CustomerID, ProductID)
Values
(Getdate(), 4,1)



Select * from Orders
Select * from Customer
Select * from Products

-- Asign the Foreign Key

alter table Orders
add foreign key (CustomerID) references customer(ID)

alter table Orders
add foreign key (ProductID) references Products(ID)

Insert into Orders (OrderDate,CustomerID,ProductID)
values
(GETDATE(),7,1)


-- Join tables

 Use Customer

Select * from Orders
Select * from Customer
Select * from Products

Select *
from Orders as o inner join Products as p on O.ProductID = P.ID

-- Same time with out as command u can use the fuction

select *
from Orders o inner join Products p on O.ProductID = p.ID

select o.*, p.*
from Orders o inner join Products p on O.ProductID = p.ID

-- Join the cutomer table
select o.*,P.*,c.*
from Orders o inner join Products p on o.ProductID = p.ID
inner join Customer c on o.CustomerID = c.ID

select o.OrderDate,p.Price,p.ProductName,c.FirstName,c.City
from Orders o inner join Products p on o.ProductID = p.ID
inner join Customer c on o.CustomerID = c.ID

-- calculate the total amout of sales
select c.LastName,p.ProductName,sum(p.Price) Total
from Orders o inner join Products p on o.ProductID = p.ID
inner join Customer c on o.CustomerID = c.ID
group by c.LastName,p.ProductName

-- calcuate the average
select avg(p.Price) Total
from Orders o inner join Products p on o.ProductID = p.ID
inner join Customer c on o.CustomerID = c.ID