Beginning SQL Archives - SQL Training Online

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.

January 18, 2013 by Joey Blue 1 Comment

SQL Not Like with Multiple Values

I came across a forum post where someone wanted to use SQL NOT LIKE with multiple values.

They were trying to exclude multiple values from the SQL query, but they were needing to use wildcards.

If you wanted to just filter values without wildcards, you would use the following query.

select *
from table1
where column1 not in ('value1','value2','value3');

The only problem was that they needed to compare using the LIKE operator.

It would be nice if you could just stick some wildcards in the in clause like this:

where column1 not in ('%value1%','%value2%','%value3%')

But, you can’t stick a wildcard inside of the IN clause. So, here is the easiest solution.

select *
from table1
where column1 not like '%value1%'
and column1 not like '%value2%'
and column1 not like'%value3%';

If you want to play around with the Boolean logic, you rearrange the query like this.

select *
from table1
where not (column1 like '%value1%'
or column1 like '%value2%'
or column1 like'%value3%');

SQL Not Like with Multiple Values

What happens if you don’t put those parenthesis in?

Here are a few other posts you might enjoy:

SQL Wildcards | SQL Multiple Filter | How to use the SQL In Statement with Subquery

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

June 19, 2012 by Joey Blue Leave a Comment

SQL Count

The SQL Count is an important function that I use when I am exploring a new table.

I often use it to see how big a table is, but I also use it to determine counts within a group.

Let’s take a look at it’s uses.

Here is the employee table:

select * 
from employee;

Results:

You can tell this has 14 records, but imagine that it had 1,000,000 records.

You wouldn’t be able to just select * without it taking a while to bring all of the records back.

That is where the SQL Count comes in:

select count(*) 
from employee;

Here you can see it gave us one column with the count of the whole table.

Pretty simple.

Now, what if we wanted to find out how many people had a commission?

If we look above, we can simply count those people. But, we have this new SQL Count function.

So, let’s use it:

select count(commision) 
from employee;

Will we get 14 records, or 4 records?

Well, here are the results:

We only got 4 records. Why is that?…

That is because the count function doesn’t count NULL records. Again, if you look above, you will see a bunch of commissions that have the value of NULL.

This NULL value means we don’t have a value. So, when the SQL Count function looks at those rows, it ignores them.

But, look at the 10th row above. It has a “0.00” value. Why does it count that one?

Because, even though the value is 0, it is still a value (it isn’t null).

An that’s it. That’s how you use the SQL Count function.

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:

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:

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.

June 15, 2012 by Joey Blue Leave a Comment

SQL Functions

Today I want to talk about SQL Functions and how they are used in the SQL language.

Let’s start by looking at a simple employee table.

select * from employee;

This employee table has 14 rows.

Now, there are some built-in SQL Functions that allow you to average, sum, get the max and minimum and count. (There are others too, but I am only mentioning a few.)

These functions will look at a group of rows and perform the required action.

So, what if I want to get the average salary of all 14 of my employees?

Then I would do the following:

select AVG(salary) from employee;

The average function is actually just AVG and you put the column in between the parenthesis.

Here are the results:

So the average salary is 2073.21…

That was pretty easy. But, what if you want the column to have a name?

select AVG(salary) as average_salary from employee;

You do what is called a column alias. After you put the column in (in this case AVG(salary) ) you put in the “as” keyword, then you put whatever you want to name the column. (In this case I called the column average_salary)

And here is the result:

And now your column has a name.

So now let me go ahead and use the rest of the functions I mentioned.

select 
AVG(salary) as average_salary 
,sum(salary) as sum_salary 
,min(salary) as min_salary 
,max(salary) as max_salary 
,count(salary) as count_salary 
from employee;

And here are the results:

Not too difficult? But, I want to try one more thing.

I want to use the SUM function and the COUNT function to create my own average function.

If you remember from Algebra, the average is calculated by summing all of the values and dividing by the number of values.

So let’s give it a try:

select sum(salary)/count(salary) as my_new_average 
from employee;

Notice how I took the Sum/Count.

Here is the result:

Notice that the value is the same as when we used the built in average function. That is what we would expect.

So that is an introduction into SQL Functions.

Leave any question, comments, or topic suggestions bellow.