SQL Tutorials

The reason for having a database is to have vast amounts of data organized and readily available. SQL is the means by which we can communicate with the database and tell it to find exactly what we need. From thousands of records, we can filter it down to precisely what we are searching for, granted if the database schema was created efficiently.

Let’s look at an example, using the HAVING clause. Let’s say we wanted to search for all employees who have an average salary of $50,000.

*Note, when using HAVING you always have to incorporate GROUP BY filtering.

Here’s how we would do it.

SELECT employee, AVG(salary)
FROM employee_data
GROUP BY employee
HAVING AVG(salary) > 50000;

See how used the AVG aggragate function to get the average salaray?

You can also find this information using the WHERE clause.

We have previously found the minimum and maximum range for a price column in a table.

But what if we wanted to find the values in between? Say for example you wanted to view all the products you sell between $50 and $100 dollars?

SELECT * FROM products WHERE price BETWEEN 50 AND 100;

See how easy that is, it almost reads like a normal sentence, this is why SQL is considered to have natural language syntax.

Now, the between operator can also be used to find in a range of letters.

For example, if we have a table of employees and a column with employee last names, and we wanted to find all the employees whose last names range from a-m, we would do a query like this:

SELECT * FROM employees WHERE lastname BETWEEN a AND m;

Easy as pie.

When running more complex queries, such as concatenating strings, often times you may need to trim whitespace from the values.

Let’s look at an example of this. Remember the query we ran for concatenating:

SELECT CONCAT (productName, ” , ” , price) FROM products;

As it is the output would be XBOX , $399

Let’s say we wanted to trim the whitespace between XBOX and the comma.

Here’s the query:

SELECT CONCAT (RTRIM(productName) ” , ” , price) FROM products;

In this case we used rtrim because we wanted to trim to the right or immediately following the value. If we wanted to trim space before the value we would use the LTRIM, and if we wanted to trim from both sides, we would simply use trim.

Aggregate means a whole, the term is most used in economics (if you have ever taken an economics class, you probably remember how painful it was :)), it basically means ‘as a whole’. So, we’ll cover a few simple aggregate functions , avg,max, and min.

Remember the price we concatenated with a string? What if we wanted to see the average price of all our the products in our products table?

SELECT AVG(price) AS average_price FROM products;

the query would return something along the lines of 23.94443, obviously depending the prices in your table , if it were cars, it might be 23,232.2238576.

Notice we also used an alias in there.

Now , what if we wanted to find the minimum and maximum price in our products table?

SELECT MIN(price) AS mini_price, MAX(price) AS max_price FROM products;

The output should be two numbers let’s say : $3.99 $599.99

That’s it, aggregate functions are often overlooked as an easy way to get quick info.

A word of warning about concatenating with sql, if the data used is one that is created by a user where query strings are created dynamically, there could be a possibility of an sql injection. This method is best used for outputting information you have in a database. For example, let’s say you have a products table and you want to concatenate the product name and price.

SELECT CONCAT (productName, ” , ” , price) FROM products;

The output would be: XBOX, $399

You can see the power in this function. We could just as well add some text modifiers:

SELECT CONCAT (productName, ” , “”Buy it Now,” , price) FROM products;

The output would be: XBOX, Buy it Now, $399

The Concat operator is used mainly by mysql. Other database engines such as Oracle use the “||” operator.

SQL Joins, or joining of database tables is one the most difficult for noobs to understand, but this is one of the more powerful queries out there. Yeah, its nice and comfy when you have just one table and everything is right there. But there will be times, many actually, when your databases will hold various tables that you have to bring together in order to retrieve query results.

Let’s take an example of a two table database where one table is for colleges and the other is for states.

SELECT DISTINCT
c.id,c.name
FROM colleges as c
INNER JOIN state AS s
ON s.college_id = c.id
WHERE id = ” id ” ;

Okay, obviously I need to explain a few things first. For Example, what is c.name ? Up until now we had been calling columns by their name. But what happens when you get to Joins is that sometimes the table and column name start getting long and before you know it the query starts reading longer Tolstoy’s war and peace.

So, taking it peace by peace:

SELECT DISTINCT (this helps to reduce any duplicates that could be in your DB)
c.id,c.name
FROM colleges as c (here we’re choosing the column’s id and name from the colleges table and giving them the alias of c (for colleges)
INNER JOIN (this is what is going to join the two tables)
state AS s (assigning the table to s)
ON (here’s the comparison, or with)
s.college_id = c.id (this says where the table holding ’state’ and college_id should equal the table ‘college’ id
WHERE id=’id’ (this is the final refinement that gets all matches of id of this state has id of these colleges)

We discussed the SQL query for inserting data into a database, now let’s look at all the options we have of changing, deleting, and updating that data.

Okay, so let’s say we have a field where we would like to change or Update the contents of the entire column in every row. We would need to run a query like this: (once again we’ll be using the names table from our actors database)

UPDATE names SET name= “Jodie Foster”;

Now, that would essentially change all the fields in the ‘name’ column to Jodie Foster. It may make sense to change all in a lot of situations, but not in a names table, so what if we just wanted to change one name in the table. Say for example that an employee gets married and all instances of her last name need to change , we would need to use the conditional WHERE.

So let’s say Jodie Foster gets married (yeah right , wink wink):) and changes here last name, how would we change just her last name and leave everyone else’s alone. Here’s an example of the WHERE clause which is what we’ll use.

UPDATE names SET name =”Jodie Smith”
WHERE id =”1″;

See what we did there? We told the database, where id is =1 change the value of the ‘name’ column to ‘Jodie Smith’
This would be ideal in a work situation where more than likely an employee id is the primary key used.

Okay, now let’s see an example of DELETE

Rarely will you want to delete everything in a database (unless your a malicious hacker), usually you’ll want the more targeted form of ’sql delete from database or table” so let’s focus on more useful commands involving the DELETE query.

Once again we can rely on the WHERE conditional to help us find exactly what we want to delete.

Let’s continue with the Jodie Smith example, say we wanted to delete just her. We would run a query like this:

DELETE FROM names WHERE name =”Jodie Smith”;

This would work in this instance, but how about in a giant corp where there are thousands of employees and very many people with the same name? It’s always best to use a unique identifier such as a Primary key:

DELETE FROM names WHERE id =”1″;

Remember before deleting anything, to have a backup.

Inserting data into a database is essential especially if we have a dynamic site. Data can be inserted into an existing table using INSERT INTO query.

INSERT INTO tableName VALUES (value,value);

Let’s see an example of INSERT INTO in action, remember the table we created, let’s continue where we left off.

In our SQL form, we are going to type in:

INSERT INTO names VALUES
(
‘NULL’,'Tom Cruise’
)

now remember we had two fields, id and name. What this query does is insert “Tom Cruise” into the name field , but notice the NULL, that is so it ignores the first field which is our primary key and is on auto_incremement. To get a glimpse of what our database is starting to look like, let’s see its layout.

In your SQL interface, let’s type in:

SELECT * FROM names;

You should see a 1 under id and Tom Cruise under the name column

Now obviously for any database of substantial size, we are going to need some quicker way of inserting information, or how about automated? Like when someone enters information on a form, all that goes into a database.

Now that you know what a SQL table looks like, and you have the tools to start running SQL commands, let’s go ahead and create one. If you have installed Xampp, go ahead and go to http://localhost in your browser, click on phpmyadmin.

Next need to create a database

In the text box under the create new database, enter actors as the name of the database and click ‘create’

Now go to the tab on top where it says SQL and click that. From now on we will always go to this tab to run our commands. phpmyadmin does have a gui interface to create, edit, and delete databases, but our goal is to learn SQL so we will go the way of the code.

Okay, so with the SQL tab open we are going to run our first query:

Type in:

CREATE TABLE names
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
name VARCHAR( 100 )
);

(notice the commands are in all capitals, it is not required but it makes a clear distinction between SQL syntax and column names)

Let’s look at that for a moment,

• id is going to be the unique identifier for each value
• NOT NULL means it won’t show a null value
• AUTO_INCREMENT means the id number will automatically increase by each row added
• PRIMARY KEY is the unique identifier for each value- no two rows can have the same primary key

For the name column we used VARCHAR or variable character field meaning we could basically put anything in there and is the most widely used field. the 100 is the maximum number of characters allowed so if someone’s name is longer than that you would probably get an error…but I don’t know anyone who’s name is longer than 100 characters, do you?

Databases are similar to storage containers in that they hold information in a logical and convenient format. Your job as a database guru is to create and manage databases.

The Inner Workings of a Database

A database can have one table or many tables. Imagine if you are looking at an excel sheet, there are columns and rows in every book. The same principle applies to a database table. Just like you can have many books in an excel sheet, you can have many tables in a database.

Here’s an example table with 3 columns and 1 row, the top row are the names of the column: