Beginning SQL Archives - Page 3 of 3

June 9, 2012 by Joey Blue Leave a Comment

SQL AND OR

The SQL AND OR is used when filtering with the SQL Where clause. More specifically, we are talking about the “AND” and the “OR” clauses.

Let’s start by looking at a basic SQL statement:

select * from customer

The customer table has the following records:

Now if we were to filter to just the “Dell” records, we would use the following query:

select * 
from customer 
where customer_company = 'Dell';

But, I want to get the “Dell” records and the “HP” records. To do that we would use the following query:

select * 
from customer 
where customer_company = 'Dell' 
 or customer_company='HP';

And we get the following results:

Now, you are probably asking, “You said we want Dell AND HP records, why did we use the OR clause?”

It can be a little confusing, but here is how I think of it.

In the case where we want the company of Dell and HP, we are actually saying we want all of the records where the customer_company is Dell or the customer company is HP.

There is a never a case where the customer_company is both Dell and HP.

So let’s look at an example where we use the AND statement.

If, I wanted to get all of the Dell customers that have a name of Bill, I would use the following query:

select * 
from customer 
where customer_company = 'Dell' 
 and customer_name='Bill';

In this case, I am actually looking for records where both criteria is met. Here is the result:

Those are the basics of using the SQL AND and OR clauses.

Please leave and questions or comments below and let me know if you want me to talk about any other topics.

June 8, 2012 by Joey Blue Leave a Comment

SQL Where

The SQL Where clause in the SQL Language allows you to filter data when you query a database.

Let’s look at the following query that gets data from the customer table:

select * from customer;

Here are the results:

Now, I only need to get the “Dell” customers. In order to do that, I have to use the where clause.

Let’s add that to the query:

select * from customer where customer_company='Dell';

When I execute this query, I get the following results:

So I have filtered the records from 6 down to 2.

Notice the following part of the query:

customer_company='Dell';

The red part is how Microsoft SQL Server represents a string. It knows it is a string, because of the single tick (‘) marks.

All strings need to be enclosed in these.

Let’s show an example where we want everyone except for Dell customers:

select * from customer where customer_company != 'Dell';

Instead of using the “=” sign, we use the “!=” which says “Not Equal”.

Here are the results:

We have filtered out the 2 Dell records.

That is the basics of the where clause.

Please leave any comments or questions below.

June 7, 2012 by Joey Blue Leave a Comment

SQL Distinct

The SQL Distinct clause in the SQL Language allows you to remove any repeated data in a column.

Let’s take a look at the following table/query:

select * from customer;

Here are the results from that query:

We have 6 rows and 4 columns in this table, but I only want to look at the customer_company column.

Here is the query to bring back just that column:

select customer_company from customer;

With the following results:

Now, I want to remove the repeated companies. So if you look at “Dell” in the picture above, you will see that it is in there twice.

That is where the “Distinct” clause comes in. Take a look at what happens when I put distinct into the following query:

select distinct customer_company from customer;

I get the following results:

Notice how we only have 5 rows. That is because the extra/repeated Dell row has been removed.

So that is what the distinct clause does, it removes the repeated data elements in a column.

Please post any questions or comments below.

June 6, 2012 by Joey Blue Leave a Comment

SQL Select Statement

The basis for retrieving data from a relational database like Microsoft SQL Server, Oracle, and MySQL is the SQL Select Statement.

The minimal SQL Select Statement if you were trying to get data from a table named “Customer” would be:

select * from customer;

Here are the results from that query:

You can see that we have a table. You might say it looks like a Microsoft Excel spreadsheet.

It has columns: customer_number, customer_name, customer_company, salesman_employee_number.

And, it has 6 rows.

So there are two keys to the Select statement above.

First:

select *

The “Select” word is a keyword that you start your query with.

The “*” means we want all columns from the table.

Second:

from customer;

The “from” word is a keyword that says you are getting ready to tell the database what table you want.

The “customer” word means you want that table.

The final thing to notice is the “;”. This tells the database that you are done with your Select Statement.

It is optional in Microsoft SQL Server, but it is good to know about.

Now, if you only wanted to bring back the customer_name and the customer_company from the table,

the query (select statement) would be as follows:

select customer_name,customer_company

from customer;

Here are the results from that query:

And that is the basics of the SQL Select Statement.

Post any questions and comments below. I always read every comment.