SQL Training Online

  • Training Courses
  • Beginner Book
  • Blog

August 11, 2018 by Joey Blue Leave a Comment

SQL Database Normalization – 1NF, 2NF, 3NF, and 4NF

Learn how to normalize your relational tables.

  • 1st Normal Form and Atomic Values
  • 2nd Normal Form (2NF) and Partial Dependence
  • 3rd Normal Form (3NF) and Transitive Dependence
  • 4th Normal Form (4NF) and Multi-Valued Facts

The first half of the video discusses the normal forms with smaller examples.  The second half of the video goes into a little more complex example.

Video: Database Normalization Tutorial


1NF, 2NF, 3NF, 4NF

The following Microsoft Excel file is used throughout the training to demonstrate the normal forms:

Normalization Excel File

See the original Normalization in SQL. 1NF, 2NF, 3NF, and 4NF video.

Filed Under: SQL Tip, SQL Training Tagged With: 1NF, 2NF, 3NF, 4NF, Database Normalization

September 27, 2017 by Joey Blue Leave a Comment

SQL Joins Tutorial for Beginners – Inner Join, Left Join, Right Join, Full Outer Join – SQL Training Online

How to write SQL Joins in 10 minutes.  (*Plus another 8 minutes of bonus material.)

Learn how to create SQL Joins. The first 10 minutes teach you the basics. Inner Join, Left Outer Join, Right Outer Join, and Full Outer Join. The second 10 minutes show you are few techniques that will help you as you start building joins.

Video: SQL Join Tutorial


How to write SQL Joins in 10 minutes.

The following Scripts are used in the training:

--Create Customer Table
CREATE TABLE [dbo].[Customer](
	[CustomerId] [int] NOT NULL,
	[CityId] [int] NULL,
	[CustomerName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
--Insert Customer Records
INSERT [dbo].[Customer] ([CustomerId], [CityId], [CustomerName]) VALUES (1, 1, N'Bob Smith')
GO
INSERT [dbo].[Customer] ([CustomerId], [CityId], [CustomerName]) VALUES (2, 1, N'Sally Smith')
GO
INSERT [dbo].[Customer] ([CustomerId], [CityId], [CustomerName]) VALUES (3, 2, N'Tom Smith')
GO
INSERT [dbo].[Customer] ([CustomerId], [CityId], [CustomerName]) VALUES (4, NULL, N'Mary Smith')
GO
--Create City Table
CREATE TABLE [dbo].[City](
	[CityId] [int] NOT NULL,
	[CityName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
--Insert City Records
INSERT [dbo].[City] ([CityId], [CityName]) VALUES (1, N'Kansas City')
GO
INSERT [dbo].[City] ([CityId], [CityName]) VALUES (2, N'New York')
GO
INSERT [dbo].[City] ([CityId], [CityName]) VALUES (3, N'Houston')
GO

See the original SQL Join Tutorial for Beginners video.

Filed Under: SQL Tip, SQL Training Tagged With: SQL Full Join, SQL Inner Join, SQL Join, SQL Left Join, SQL Outer Join, SQL Right Join, SQL Video, SQL Where

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

June 5, 2017 by Joey Blue Leave a Comment

Big Data Everywhere

Hey guys, I’m on ESPN.com today checking out Russell Westbrook’s 49 points in his return to the NBA after a freak accident.

Russell fell down and a teammate ran into him fracturing his jaw.But, despite surgery a few days ago, he came back with a mask and dropped 49 points.

Now, I’m not a huge Russell Westbrook fan, or a fan of the NBA in general, but that is a pretty cool story.

But, that isn’t what got my attention…

What really got my attention was the commercial that showed up on ESPN before the highlight.

It was about IBM analytics… Watson Analytics.

I thought, “Wow! Analytics and Big Data are main stream.”

It wasn’t normal consumer products like Insurance…

But, it was about data!

This is validation that the BI market is huge.

You don’t see commercials about .Net development, or Java development, but commercials about data and analytics.

You constantly see stuff in the news about Big Data and Analytics.

If the big companies are selling it, businesses need people like us to actually make them work.

This means jobs, higher demand, and ultimately money.

And, it doesn’t have to be crazy complex to get started.

Even the Big Data stuff ends up in relational databases.

If you haven’t checked out my 30 Free SQL videos yet, they have been moved to a free membership site.

You have to provide your email, but it is 100% free.

https://www.sqltrainingonline.com/30-free-videos/

Click it while it is still active.

In the member’s area, it is easier to find all of the videos and it also includes the practice database.

Sincerely,
Joey Blue

P.S.  Getting started with SQL is easiest by just starting with the SQL Query.  That is exactly what this training does.  Don’t wait any longer, start now.
https://www.sqltrainingonline.com/30-free-videos/

Filed Under: SQL Training

January 31, 2013 by Joey Blue Leave a Comment

How to Calculate a SQL Moving Average without a Cursor

Update:  If you are working with the newest versions of SQL Server, you can use the windowing functions to accomplish the same thing.  I posted the updated code at the end of the post.  For this video, I still like the thought process of anchoring to a date.

Video: 3 Day Moving Average in SQL


An efficient way to calculate a moving average in SQL using a few tricks to set date anchors.

There are debates on the best way to do a SQL Moving Average in SQL Server.

Some people think there are times when a cursor is most efficient.  Other’s think that you can do it all in a set-based way without the cursor.

The other day I was going to calculate a moving average and my first thought was to use a cursor.

I did some quick research and found this forum question: Moving Average in TSQL

There is a post that shows a subquery with an anchor date to help find the 1 and 2 day offset.

Here is the script you can use to test the 3 day SQL Moving Average final result.

CREATE TABLE [dbo].[daily_sales](
    [id] [int] NULL,
    [dt] [datetime] NULL,
    [revenue] [decimal](12, 2) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (1, CAST(0x0000A15700000000 AS DateTime), CAST(125.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (2, CAST(0x0000A15800000000 AS DateTime), CAST(114.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (3, CAST(0x0000A15900000000 AS DateTime), CAST(92.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (4, CAST(0x0000A15A00000000 AS DateTime), CAST(152.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (5, CAST(0x0000A15B00000000 AS DateTime), CAST(48.00 AS Decimal(12, 2)))
GO

Here is the final query.

SELECT        
            DATEADD(DAY, days_since_1900, '19000101') AS dt,
            AVG(revenue) AS Revenue_3_day_moving_average
FROM (
            select 
            DATEDIFF(DAY, '18991230', dt) AS days_since_1900
            ,revenue
            ,0 as Actualdate
            from daily_sales

            UNION ALL

            select 
            DATEDIFF(DAY, '18991231', dt) AS days_since_1900
            ,revenue
            ,0 as Actualdate
            from daily_sales

            UNION ALL
            select 
            DATEDIFF(DAY, '19000101', dt) AS days_since_1900
            ,revenue
            ,1 as Actualdate
            from daily_sales

) AS daily_sales_normalized_to_1900
GROUP BY    days_since_1900
HAVING        MAX(Actualdate) = 1
ORDER BY    dt

Here is the query you would use with SQL Server 2012.

select dt, 
    avg(revenue)
    OVER (
        ORDER BY dt
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )
FROM daily_sales

 

The original Youtube video: SQL Moving Average

Filed Under: SQL Training Tagged With: SQL Dateadd, SQL Datediff, SQL Datetime, SQL Group By, SQL Having, SQL Moving Average, SQL Server 2012, SQL Subquery

  • 1
  • 2
  • 3
  • …
  • 7
  • 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

Have Questions? Connect with me.

Link to my Facebook Page
Link to my Pinterest Page
Link to my Rss Page
Link to my Twitter Page
Link to my Youtube Page
Copyright © 2023 · SQLTrainingOnline.com · Consulting at EmbarkBlue.com