ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Let's Learn Some SQL

Updated on May 17, 2014

The SELECT Statement

The very first command you will want to learn is the SELECT statement. This is used to query the database to be be able to see what data is inside a table.

SELECT * FROM <table_name>;

(where <table_name> is the name of the table you want to query.

e.g. SELECT * FROM EMPLOYEES;

This will SELECT all the fields from the table EMPLOYEES and display them on the screen. Using the asterisk (*) specifies that we want to return all the fields.

Below is an example of what this might look like:


As you can see, all the fields are displayed. If you only wanted to select specific fields, you could do this by specifying the fields instead of using the asterisk. You can specify any number of fields you like, separating them with a comma (,).

For example:

SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES;

This will select all the rows from the table EMPLOYEES, but will only return back the columns FIRST_NAME and LAST NAME.


The WHERE Clause

The next command you shall use will be the WHERE clause. The where clause is used to restrict the data you are wanting to return. The WHERE clause isn't only restricted to the SELECT statement; it can be used for the INSERT, UPDATE or DELETE statement as well, but more about those later. For now we will use the WHERE clause with our SELECT statement to only return data from the EMPLOYEES table where the employee's last name is 'Smith'.

SELECT * FROM EMPLOYEES

WHERE LAST_NAME = 'Smith';


As you can see, this has only returned the one row where the condition has been met.

A WHERE clause does not have to request a specific match like you have done above; it can also be used to limit a range of data. Let's say you wanted to return all the employees that had started after the 1st Jan 2014.

SELECT * from EMPLOYEES

WHERE START_DATE >= '1 Jan 2014';

You can restrict a range of data by using the greater than (>), less than (<), greater than or equal to (>=), and so on.

Using WHERE to Join tables

Apart from being able to restrict data returned, the WHERE clause can also be used to connect (join) multiple tables together to return data from both datasets.

Let's first start by querying the database on a different table 'JOBS' to see what sort of records may be of interest.

SELECT * from JOBS;


Here you can see a list of jobs which have been assigned to various employee numbers. In order to see the names of each employee, you would need to join the JOBS table to the EMPLOYEES table using a common element. In this example, the field which appears in both tables is the EMPLOYEE_ID so we will use this to link the two tables. Notice how rather than specifying one table, we are specifying two and we are separating these with a comma (,).

SELECT * FROM EMPLOYEES, JOBS

WHERE EMPLOYEES.EMPLOYEE_ID = JOBS.EMPLOYEE_ID;


Notice how for each job we now are displaying the employee details associated with that job. This is how the tables are joined. If you were to select from both tables without this join, you would return every employee detail for every job which is not usually recommended.

If we were to omit this join, and just select from both tables we would get a result which looks like this:

SELECT * FROM EMPLOYEES, JOBS;

Notice how this does not give us the data we are looking for.


Restricting with LIKE

LIKE can be used as another way of limiting the data returned. It is used for limiting a field where we know part of the data that we want to return. A percentage sign (%) is used to specify a wildcard/wildword. For example:

SELECT * FROM EMPLOYEES

WHERE FIRST_NAME LIKE 'JA%';

(Note, this is using a database that is not case sensitive)

The above SQL command will return all employee records which have a first name starting with 'JA'. The Percentage after the 'JA' indicates that the first name records in must begin with JA, but can have zero to many extra characters after. The percentage sign can be used many times, can can be used at different places. For example:

SELECT * FROM EMPLOYEES

WHERE FIRST_NAME LIKE '%JA%';


Here we see that we have specified a percentage sign at the beginning of 'JA' as well. This not only returns our two records from the previous statement, but also returns the additional row which has 'JA' at some point in the first name.


Aggregate Functions - SUM

Apart from just selecting data from a table, you can also perform calculations on that data.

You can use the ‘SUM’ function to total up a series of values. e.g.

SELECT SUM(JOB_EFFORT) FROM JOBS;


This will return a total of all the JOB_EFFORT together. This by itself may not be that useful until it is grouped by whatever fields you are trying to find the total for. E.g.

SELECT EMPLOYEE_ID, SUM(JOB_EFFORT)

FROM JOBS

GROUP BY EMPLOYEE_ID;

This will return the total job effort by employee ID. Whatever fields you wish to return will need to be added to the GROUP BY clause.

Aggregate Functions - AVG

This works very similar to SUM, and will return the average of whatever you have grouped by. E.g.

SELECT EMPLOYEE_ID, AVG(JOB_EFFORT)

FROM JOBS

GROUP BY EMPLOYEE_ID;

IN Condition

I use this one far too much. The IN condition can be used to restrict a query, similar to using a WHERE clause however instead of linking to another table 1 to 1, you can specify a range of values. These can be in the form of hardcoded values, e.g.

e.g.

SELECT * FROM EMPLOYEES

WHERE EMPLOYEE_ID IN(1001,1002);

Or they can even be a SELECT statement (providing you are only selecting a single field). E.g.

SELECT * FROM JOBS WHERE EMPLOYEE_ID IN

(SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE LAST_NAME LIKE’JA%’)

Here we can see that the query has returned all records from the JOBS table where the employee ID can be found in the employees table where the employee’s last name is starts with JA.

INSERT

The INSERT statement is used to add records to a table in the database. An insert statement can be executed by specifying hardcoded values, or by using another SQL select statement. A simple hardcoded select statement is written as follows:

INSERT INTO <table_name>

(

<field>,

<field>,

<field>

)

VALUES

(

<value>,

<value>,

<value>

);

Let’s start by adding a simple hardcoded set of values to our JOBS table. We will be adding an additional job for employee ID 1004.

INSERT INTO JOBS

(

JOB_ID,

EMPLOYEE_ID,

JOB_NAME

)

VALUES

(

2006,

1004,

‘Wax Floor’

);

If we run this command we should see the following: 1 ROW INSERTED.

Now if we query the JOBS table we should be able to see the newly added row:

SELECT * FROM JOBS;

Notice how we didn't need to specify all the columns? We purposefully left out inserting a value for the field JOB_EFFORT. Let’s now add this using an UPDATE statement.

UPDATE

An update statement is used to modify existing data in a table. We do so with the following format:

UPDATE <table_name>

SET <field> = <value>

WHERE <condition>;

Let’s update the row that we have inserted into the JOBS table with a JOB_EFFORT value of 45. We would do this with the following statement:

UPDATE JOBS

SET JOB_EFFORT = 45

WHERE EMPLOYEE_ID = 1004;

If we run this command we should see the following: 1 ROW UPDATED.

Now if we query the JOBS table we should be able to see the newly updated field:

SELECT * FROM JOBS;

But we have had enough of that row, so next we are going to delete it.

DELETE

The DELETE command is used to remove ROWS from a table in the database. Now let’s delete the job that we just created.

DELETE FROM JOBS

WHERE JOB_ID = 2006;

We will now confirm that our row is removed.

SELECT * FROM JOBS:

Congratulations!, you have successfully finished this quick guide in some of the simple commands used in SQL. Stay tuned for more tutorials.

Comments

    0 of 8192 characters used
    Post Comment

    • mts1098 profile image

      mts1098 3 years ago from InsideTheManCave

      very good intro - I work with Structured Query Language every day...cheers

    • kolastyles profile image
      Author

      Kane 3 years ago from Australia

      Thankyou, me too! One comment was all the motivation I needed to expand on this :)

    Click to Rate This Article