SQL Training Online

by Leave a Comment

How to use the SQL In Statement with Subquery

Video: How to use the SQL In Statement with Subquery – SQL Training Online
In this video, I show you how to use the SQL IN Statement with Subquery in SQL Server 2012 using SQL Server Management Studio.
[sharebox4 sharetext=”Share This Page”] [/sharebox4]

How do you use the SQL IN Statement with a Subquery?

I will show you in 3 steps that build on each other:

  1. Use the SQL OR operator.
  2. Use the SQL IN operator.
  3. Substitute a SQL Subquery into the IN Clause.

I will start with a simple query of the SQL Training Online Simple DB employee table where I want to retrieve 3 employee numbers: 7369,7499,7521

select * from employee
where employee_number=7369
or employee_number=7499
or employee_number=7521

 

Here, you can see that I used the SQL OR statement to get the 3 employees.

But, I can make this a little cleaner by using the SQL IN statement:

select * from employee
where employee_number in (7369,7499,7521)

 

This query returns the same data as before, but it has less typing.

SQL In Statement With Subquery

So, now I want to define a subquery. It is simply:

“A subquery is a query inside of another query.”

Let’s start the example. First, I will look at the customer table and specifically, the salesman employee number:

select saleman_employee_number from customer

 

This query returns a list of employee numbers attached to a customer. But, I want to filter out the nulls and get rid of the repeated employee numbers. So, here is the query for that.

select distinct saleman_employee_number from customer
where saleman_employee_number is not null

 

Now, this is where the magic of a subquery comes in. Since this query returns a list of values, you can simply use it in the original query as the SQL IN statement. It looks like this:

select * from employee
where employee_number in (select distinct saleman_employee_number from customer where saleman_employee_number is not null)

 

And, that’s it. You have now used the SQL IN statement with Subquery.

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

by Leave a Comment

SQL Variable Declaration – How to Declare a Variable in SQL Server

Video: SQL Variable Declaration – How to Declare a Variable in SQL Server – SQL Training Online
In this video, I show you how to Declare a Variable in SQL Server using SQL Server Management Studio.
[sharebox4 sharetext=”Share This Page”] [/sharebox4]

How do you declare a variable in SQL Server?

There are 3 things to know when declaring a SQL Server variable.

  1. SQL Server Variables start with ‘@’.
  2. You must use the DECLARE keyword.
  3. You can use SET or SELECT to put data into the variable.

So, know I want to show you an example.

SQL Variable Declaration

The first step is to create an empty variable. You do that with the DECLARE statement like this:

DECLARE @my_int int;

Here I have declared a variable that can store an integer.

The next step is to put something into the variable. You can do that by using the SET or the SELECT statements.

SET @my_int=3;

In that example, the variable will store the integer 3.

Then, the last step is to print out what is in the variable. You can do that with the SQL SELECT.

SELECT @my_int;

And that is it. But, you have to run the whole thing together for it to work.

DECLARE @my_int int;
SET @my_int=3;
SELECT @my_int;

Now, you can also change what is stored in the variable. In the following example, I change the value inside of the @my_int variable from 3 to 3+1 which equals 4.

DECLARE @my_int int;
SET @my_int=3;
SET @my_int=@my_int + 1;
SELECT @my_int;

And, the last thing I want to do is show you how to use the SELECT to populate the variable instead of the SET.

DECLARE @my_int int;
SELECT @my_int=3;
SET @my_int=@my_int + 1;
SELECT @my_int;

That’s it. That is how you do a SQL Variable Declaration.

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

by Leave a Comment

SQL Date Comparison – How to Filter Datetime in SQL Server

Video: SQL Date Comparison – How to filter Datetime in SQL Server – SQL Training Online
In this video, I show you how to do a SQL Date Comparison in SQL Server when your Datetime has an actual time in it. I also include the latest trick that will make this easier if you are using SQL Server 2008 or SQL Server 2012.

[sharebox5_no_text] [/sharebox5_no_text]

How do you compare a Datetime in SQL Server?

A SQL Datetime stores both Date and Time. So when you are filtering using the SQL Where clause, you have to know if you Datetime is storing the time also.

Let’s take a look at the employee table in SQL Training Online Simple DB

select *
from employee

The column we are looking at is the hire date. I want to go after the person who was hired on February, 22 2005.

So, I use the following SQL.

select *
from employee
where hire_date='02/22/2005'

Now, this works because we don’t have any times stored in this column. But, if I change the record from 2005-02-22 00:00:00.000 to 2005-02-22 08:22:25.000 and rerun the query, you see that I don’t get any results.

SQL Date Comparison

That is because the value of 02/22/2005 is really 2005-02-22 00:00:00.000 and I just changed the hire_date to include a time other than 00:00:00.000.

So, I have to tweak my query to use a greater than and less than sign.

select *
from employee
where hire_date >= '02/22/2005'
and hire_date < '02/23/2005'

This works. But, to really understand what is going on, let me show you what SQL Server really sees.

select *
from employee
where hire_date >= '2005-02-22 00:00:00.000'
and hire_date < '2005-02-23 00:00:00.000'

There are other ways, and in this StackOverflow article you can read more about these other ways. But, I want to point out a few.

If you are working in SQL Server 2005 or earlier, the following is supposed to be the fastest performing way to do this.

select datediff(dd,0, hire_date),*
from employee
where dateadd(dd,0, datediff(dd,0, hire_date)) = '2005-02-22'

But, if you are working in SQL Server 2008 or SQL Server 2012, they have introduced an actual Date datatype that doesn’t include the Time portion. Since this is the case, you can use the CAST function to remove the time from the Datetime.

Here is the SQL to do that.

select *
from employee
where CAST(hire_date AS Date) = '2005-02-22'

And that’s it.  That is the SQL Date Comparison in Microsoft SQL Server.

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

by Leave a Comment

SQL Into – How to Copy Table Data with Select Into Statement – SQL Training Online

Video: SQL Into – How to Copy Table Data with Select Into Statement – SQL Training Online
In this video, I show you how to copy data using the SQL INTO statement. Including a trick to copy only a table schema in SQL Server.

[sharebox5_no_text] [/sharebox5_no_text]

How do you copy a whole table in SQL Server? You use the SQL INTO statement.

You often see the SQL INTO statement when you are using INSERT, but there is a special INTO statement in SQL Server that is used with the SELECT statement that allows you to make a copy of a table for development, testing, or whatever purpose you can think of.

So, to start, let’s take a look at the SQL Training Online Simple DB employee table.

You can see that we have 14 records

Now, all we have to do to make a copy of the table is to simply add the SQL INTO clause:

SELECT *
INTO EMPLOYEE_TEST
FROM EMPLOYEE

If we run that, you see that we made a copy of the table with all of the data.  You can get the complete syntax at MSDN.

SQL INTO - SELECT INTO FROM

Now, I want to go ahead and empty the employee_test table and show you how to use the INSERT INTO.

If you already have a copy of the table, (I will show you a shortcut in a minute on how to create a copy without data.) then you can use the following insert into statement:

INSERT INTO EMPLOYEE_TEST
SELECT *
FROM EMPLOYEE

After running that statement, you see that I have inserted data from the Employee table into the employee_test table. This is sometimes referred to as INSERT INTO SELECT.

But, there is one trick that I have used throughout the years that is pretty cool.

Sometimes, you want the table schema, but you don’t want the data. Now, I know that you can use Management Studio to script out the table for you, but there is an easier way that is more flexible as far as I am concerned.

That is to use the SELECT INTO FROM, but put the following in the WHERE clause:

SELECT *
INTO EMPLOYEE_TEST
FROM EMPLOYEE
WHERE 1=2

The 1=2 makes it so the data will not come over, but you get the new table with the right column types.

That is how you use the SQL INTO in Microsoft SQL Server

Is that pretty cool? I have found many uses for this over the years. Let me know what you think bellow.

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

by Leave a Comment

Jet Driver – How to find the Microsoft Access Driver (ODBC) in Windows 7 64 bit – SQL Training Online

Video: Jet Driver – How to find the Microsoft Access Driver (ODBC) in Windows 7 64 bit – SQL Training Online
In this video, I show you how to find the Jet Driver (ODBC) that is missing in Windows 7.

[sharebox5_no_text] [/sharebox5_no_text]

Where are the Windows 7 Jet Drivers?

The Jet Driver is used for ODBC and allows you to interact with Microsoft Access, Microsoft Excel, and other ODBC sources that don’t have OLEDB drivers.

I went and looked for them today, and they were gone!

You find the ODBC connections in Administrative Tools.

When I opened them up, and clicked ADD, I only saw 4 drivers.

The problem is because, by default, when you are using the 64 bit Windows 7, the ODBC Administrator that opens up is the 64 bit version.

Well, the Jet Driver doesn’t support 64 bit. This means you have to find the 32 bit ODBC Administrator.

I found this article, which showed me the location of my 32 bit ODBC Administrator here:

C:\Windows\SysWOW64\odbcad32.exe

If you open that one up, click on the User DSN tab, and click Add, you will find all of the missing drivers.

ODBC Jet Drivers for Windows 7

So, that’s where they went. Now I see the Jet Driver.

But, you don’t want to go find them every time, do you?

So, I will show you how to create a shortcut in your Administrative Tools.

Open your windows explorer, and navigate to the following location:

C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Administrative Tools

Make a copy of the Data Sources (ODBC)

Rename the copy to Data Sources (ODBC) – 32 Bit

Now go to the properties and change the Target and Start In and replace “system32” with “SysWOW64”. And click OK.

That’s it.

Now you have a shortcut in your Administrative Tools that will pull up the 32 bit version of the ODBC Administrator and you will see the Jet Driver.

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