SQL Training Online

  • Training Courses
  • Beginner Book
  • Blog

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

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

August 26, 2017 by Joey Blue Leave a Comment

Format Function in SQL Server

The Format Function can help you change how a date or decimal is displayed.

In this video I do a quick demonstration of the Format Function.

Video: SQL Server Format Function


How to use the SQL Format Function on a Date.

I use the Wide World Importers and the following scripts:

select OrderDate,Format(OrderDate,'MMM dd, yyyy') 
from [Sales].[Orders]
where year(OrderDate) = 2016

Link to the Microsoft SQL Server Custom Date Formatters.

 

See the original  SQL Format for Date video.

Filed Under: SQL Tip Tagged With: date, datetime, SQL Datetime, SQL Server, SQL Where

November 5, 2012 by Joey Blue Leave a Comment

How to Filter for SQL Null or Empty String

Video: How to Filter for SQL Null or Empty String – SQL Training Online
In this video, I show you how to filter a SQL table that has both Null values and an Empty string. I use SQL Server 2012 to demonstrate the technique.
[sharebox4 sharetext=”Share This Page”] [/sharebox4]

How do you filter a SQL Null or Empty String?

SQL Null or Empty String

A null value in a database really means the lack of a value. It is a special “value” that you can’t compare to using the normal operators. You have to use a clause in SQL IS Null.

On the other hand, an empty string is an actual value that can be compared to in a database. You simply use two ticks together.

''

 

Let’s take a look at the vendor table to demonstrate this.

select * from vendor
SQL Null or Empty String Results

 

In this table, I specifically put in some email addresses that are both null and empty strings.

Let’s pull out the NULL values using the IS NULL operator.

select * from vendor
where vendor_email is null

 

SQL Null or Empty String Results

Next, I want to pull out the empty string using the tick-tick, or empty string.

select * from vendor
where vendor_email = ''

 

SQL Null or Empty String Results

If you want to combine them to search for the SQL null or empty string together and retrieve all of the empty strings and nulls all at once, you could do something like this.

select * from vendor
where vendor_email = ''
or vendor_email is null

 

SQL Null or Empty String Results

Finally, I want to show you a little trick that I have used on occasion to change all of the null values to empty strings and then compare the result to an empty string.

select * from vendor
where isnull(vendor_email,'') = ''
SQL Isnull Results

 

And that is it.

Here is the script to create the vendor table I was using as an example.

create table dbo.vendor
(
vendor_id int identity(1,1) primary key,
vendor_name varchar(50),
vendor_email varchar(100)
)
insert into vendor (vendor_name,vendor_email) values ('Dell','[email protected]');
insert into vendor (vendor_name,vendor_email) values ('IBM','[email protected]');
insert into vendor (vendor_name,vendor_email) values ('Microsoft','[email protected]');
insert into vendor (vendor_name,vendor_email) values ('Oracle','');
insert into vendor (vendor_name,vendor_email) values ('Intel',NULL);
insert into vendor (vendor_name,vendor_email) values ('Apple','[email protected]');

 

There it is. That is how you filter for SQL Null or Empty String.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.

If you enjoy the video, please give it a like, comment, or subscribe to my channel.

You can visit me at any of the following:

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline

Filed Under: SQL Training Tagged With: SQL Course, SQL Empty String, SQL IS Null, SQL ISNULL Function, SQL Null, SQL Training Online, SQL Tutorial, SQL Where

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