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

    • kolastyles profile imageAUTHOR

      Kane 

      4 years ago from Australia

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

    • profile image

      mts1098 

      4 years ago

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

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)