SQL Training Online

  • Training Courses
  • Beginner Book
  • Blog

September 13, 2017 by Joey Blue 2 Comments

Learn Basic SQL – 1 Hour Training Course – SQL Training Online

How to write SQL from scratch in 1 hour.

**Full Beginner’s Course**

In this video I show you how to write SQL using SQL Server and SQL Server Management Studio. We go through Creating a Database, Creating Tables, Inserting, Updating, Deleting, Selecting, Grouping, Summing, Indexing, Joining, and other basics you need to get starting writing SQL.

Video: Learn Basic SQL


How to write SQL from scratch in 1 hour.

The following Scripts are used in the training:

--Create Database
Create Database Customer;

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

--Insert Initial Data
insert into Customer (FirstName,LastName,[Age]) values ('Joey','Blue',40);
insert into Customer (FirstName,LastName,[Age]) values ('Barry','Bonds',50);
insert into Customer (FirstName,LastName,[Age]) values ('Mike','Schmidt',60);
insert into Customer (FirstName,LastName,[Age]) values ('Mike','Schmidt1',61);
insert into Customer (FirstName,LastName,[Age]) values ('Mike','Schmidt2',62);
insert into Customer (FirstName,LastName,[Age]) values ('Mike','Schmidt3',63);
insert into Customer (FirstName,LastName,[Age]) values ('Mike','Schmidt4',64);
insert into Customer (FirstName,LastName,[Age]) values ('Mike','Schmidt5',65);

--This deletes mike schmidt
/*
DELETE Customer
where FirstName='Mike'
and LastName Like 'Schmidt_';
*/

--Select Mike Schmidt
select *
from Customer
where FirstName='Mike'
and LastName Like 'Schmidt_';

--Update City on Barry Bonds
update Customer
Set City='Goddard'
where FirstName='Barry'
and LastName = 'Bonds' ;

--Add City to the Customer Table
alter table customer
add City varchar(50);

--Drop the Customer Table
drop table customer;

--Create Customer Table with Primary Key
create table Customer
(
	Id int Primary Key identity(1,1),
	FirstName varchar(50),
	LastName varchar(50),
	Age int,
	City varchar(50)
)

--Insert Customer Data
insert into Customer 
(FirstName,LastName,[Age],City) values ('Joey','Blue',40, 'Goddard');
insert into Customer 
(FirstName,LastName,[Age],City) values ('Barry','Bonds',50, 'San Francisco');
insert into Customer 
(FirstName,LastName,[Age],City) values ('Mike','Schmidt',60, 'KC');

--Create Products Table
create table Products
(
	id int primary key identity(1,1),
	ProductName varchar(50)
)

--Add Price to Products Table
alter table Products
add Price float;

--Select everything from Products table
select * from Products

--Insert Products
insert into Products (ProductName, Price) values ('Baseball', 5.95);
insert into Products (ProductName, Price) values ('Bat', 195.99);

--Create Orders Table
Create table Orders
(
	OrderId int primary key identity(1,1),
	OrderDate Datetime,
	CustomerID int,
	ProductID int
)

--Insert Orders
insert into Orders (OrderDate,CustomerID,ProductID) values (GetDate(),1,1);
insert into Orders (OrderDate,CustomerID,ProductID) values (GetDate(),1,1);
insert into Orders (OrderDate,CustomerID,ProductID) values (GetDate(),2,1);
insert into Orders (OrderDate,CustomerID,ProductID) values (GetDate(),2,1);
insert into Orders (OrderDate,CustomerID,ProductID) values (GetDate(),2,2);
insert into Orders (OrderDate,CustomerID,ProductID) values (GetDate(),2,2);
insert into Orders (OrderDate,CustomerID,ProductID) values (GetDate(),2,2);
insert into Orders (OrderDate,CustomerID,ProductID) values (GetDate(),2,2);
insert into Orders (OrderDate,CustomerID,ProductID) values (GetDate(),3,2);
insert into Orders (OrderDate,CustomerID,ProductID) values (GetDate(),3,2);
insert into Orders (OrderDate,CustomerID,ProductID) values (GetDate(),4,2); --Bad Record

--Select from Tables
select * from Orders;
select * from Products;
select * from Customer;

--Delete Bad Order  **Your OrderId may be different
delete Orders
where OrderId=21;

--Add Foreign Keys to the Orders table
alter table orders
add foreign key (CustomerId) references Customer(Id);

alter table orders
add foreign key (ProductId) references Products(Id);

--Find the Total and Average Price per City
select c.City,sum(p.Price),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
group by c.City

See the original SQL Training Course for Beginners video.

Filed Under: SQL Tip, SQL Training Tagged With: Beginning SQL, Create Table, SQL AND OR, SQL Average, SQL Create Database, SQL Create Table, SQL Delete, SQL Foreign Keys, SQL Function, SQL Identity, SQL Insert, SQL Join, SQL Like, SQL Primary Key, SQL Server, SQL Update, SQL Video, SQL Where

August 27, 2017 by Joey Blue 1 Comment

Moving Average with Windowing in SQL Server

The Moving Average used to be complex, but with the Windowing Functions, the Moving Average is easier and performs much better.

In this video I show you how to create a moving average in SQL using the OVER Clause and the ROWS Clause.

Video: SQL Moving Average with Windowing


How to use the SQL Over Clause for a Moving Average.

I use the Wide World Importers and the following scripts:

select [Invoice Date Key], sum(Profit) Profit
into dbo.ProfitByDate
from [Fact].[Sale]
group by [Invoice Date Key];

select 
[Invoice Date Key],
avg(Profit) OVER (ORDER BY [Invoice Date Key] ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) as AverageProfit
from dbo.ProfitByDate
order by 1

Link to the Microsoft SQL Server Over Clause.

 

See the original SQL Rolling Average with Windowing video.

Filed Under: SQL Tip Tagged With: datetime, Over Clause, SQL Average, SQL Datetime, SQL INTO, SQL Moving Average, SQL Server, SQL Where

June 18, 2012 by Joey Blue Leave a Comment

SQL Average

The SQL Average function is an easy function to use.

Let’s take a look at the Employee table:

select * from employee; 

Results:

SQL Average Results

If I want to find the average of the salary column, I would write the following query:

select avg(salary) as average_salary from employee; 

With the following results:

SQL Avg Results

Notice how we only get one column returned because that is all we asked for.

You can use the SQL Average on any numeric column using this technique.

When we get to the Group By clause, you will see how we can average across different groups of columns.

Filed Under: SQL Training Tagged With: Beginning SQL, Function, SQL Average, SQL Select

Recent Posts

  • SQL Database Normalization – 1NF, 2NF, 3NF, and 4NF
  • SQL Joins Tutorial for Beginners – Inner Join, Left Join, Right Join, Full Outer Join – SQL Training Online
  • Zillow House Price Analysis from CSV Archive – #PowerBI 002
  • Learn Basic SQL – 1 Hour Training Course – SQL Training Online
  • Create Table Statement in SQL Server and Inserting Baseball Homerun Leader Dataset – SQL Training Online

Popular Posts

  • SQL Functions
  • SQL Jobs in Oracle and Microsoft SQL Server
  • Troubleshooting a SQL Join
Copyright © 2023 · SQLTrainingOnline.com · Consulting at EmbarkBlue.com