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

September 12, 2017 by Joey Blue Leave a Comment

Create Table Statement in SQL Server and Inserting Baseball Homerun Leader Dataset – SQL Training Online

How to use the Create Table Statement to store data in SQL Server

In this video I show you how to take some baseball data from a website, create a SQL Server table, and insert it into the table.

Video: Create Table Statement in SQL Server


How to use the Create Table Statement to store data in SQL Server..

Here is the Homerun Leaders Page that I use for the data.

The following Scripts are used in the video:

--Create Table
create table dbo.HomerunLeaders
(
 id int primary key identity(1,1),
 [Rank] int,
 Player varchar(50),
 Homeruns int,
 Bats varchar(1)
);

--Select from table
select * from dbo.HomerunLeaders
where Rank < 10;

See the original SQL Create Table video.

Filed Under: SQL Tip Tagged With: Create Table, SQL Create Table, SQL Delete, SQL Identity, SQL Server

August 30, 2017 by Joey Blue 1 Comment

Latitude and Longitude with the Geography Data Type – SQL Training Online

The Geography data type is used in many different SQL Server databases. It contains Latitude, Longitude, and many other properties and functions.

In this video I show you how to extract the latitude and longitude from the Geography data type.

Video: Latitude and Longitude with the Geography Data Type


How to extract the latitude and longitude from the Geography data type..

I use the Wide World Importers and the following scripts:

--SQL Geography Data Type
select CityName
,Location.Lat Latitude
,Location.Long Longitude
,cast(location as varchar(max))
from Application.Cities

Link to the a good tutorial on MSSQLTips.com.

 

See the original Latitude and Longitude in SQL Server video.

Filed Under: SQL Tip Tagged With: Latitude, Longitude, SQL Cast, SQL Geography, SQL Server

August 29, 2017 by Joey Blue 1 Comment

Year-to-Date using Partition By with Windowing – SQL Training Online

You can use the Partition By Function in SQL Server to get a Year-to-Date and Month-to_Date calculation.

In this video I show you how to create a Year-to-Date value using the Windowing Partition By Function in TSQL.

This video builds on the prior day’s video, Prior Day Profit using the Lag Function with Windowing .

Video: Month-to_Date using the Partition By Function in SQL Server


how to create a Year-to-Date value using the Windowing Partition By Function in TSQL.

I use the Wide World Importers and the following scripts:

--SQL Year-to-Date using Partition By
select 
year([Invoice Date Key]) as Year,
Month([Invoice Date Key]) as Month,
[Invoice Date Key],
Profit,
sum(Profit) OVER (PARTITION BY year([Invoice Date Key]), Month([Invoice Date Key]) ORDER BY [Invoice Date Key]) as MonthToDate,
sum(Profit) OVER (PARTITION BY year([Invoice Date Key]) ORDER BY [Invoice Date Key]) as YearToDate
from dbo.ProfitByDate
order by 1

Link to the Microsoft SQL Server Over Clause.

 

See the original Year-to-Date using the Partition Windowing Function video.

Filed Under: SQL Tip Tagged With: Over Clause, SQL Datetime, SQL Month Function, SQL Partition By, SQL Server, SQL Year Function, Windowing Functions

August 28, 2017 by Joey Blue 1 Comment

Prior Day Profit using the Lag Function with Windowing in SQL Server

You can get a prior day value using the Lag Function in SQL Server.

In this video I show you how to create a prior day squeeze of profit using the Windowing Lag function in TSQL.

This video builds on the prior day’s video, Moving Average with Windowing.

Video: Prior Day Profit using the Lag Function in SQL Server


How to use the SQL Lag Function for Prior Day Squeeze.

I use the Wide World Importers and the following scripts:

select 
[Invoice Date Key],
Profit,
Lag(Profit,1) OVER (ORDER BY [Invoice Date Key]) as PriorDayProfit,
Profit - isnull(Lag(Profit,1) OVER (ORDER BY [Invoice Date Key]),0) as DailySqueeze
from dbo.ProfitByDate
order by 1

Link to the Microsoft SQL Server Over Clause.

 

See the original SQL Prior Day Profit – Lag Windowing Function video.

Filed Under: SQL Tip Tagged With: Over Clause, SQL INTO, SQL ISNULL Function, SQL Lag, SQL Server, Subtraction, Windowing Functions

  • 1
  • 2
  • Next Page »

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