SQL Select Query Template:
SELECT table_column1, table_column2, table_column3
FROM my_table;
Select queries require two essential parts. The first part is the "WHAT", which determines what we want SQL to go and fetch. The second part of any SELECTcommand is the "FROM WHERE". It identifies where to fetch the data from, which may be from a SQL table, a SQL view, or some other SQL data object.
Now we would like SQL to go and fetch some data for us from the orders table that was created in the previous lesson. How do we translate this request into SQL code so that the database application does all the work for us? Simple! We just need to tell SQL what we want to select and from where to select the data, by following the schema outlined below.
SQL Select Query Code:
USE mydatabase;
SELECT id, customer, day_of_order, product, quantity
FROM orders;
SQL Orders Table Results:
| id | customer | day_of_order | product | quantity |
| 1 | Tizag | 2008-08-01 00:00:00.000 | Pen | 4 |
Below, we will manipulate the result output by rearranging the list of table column names inside of the SELECT statement.
SQL Select Query: Rearranged:
USE mydatabase;
SELECT day_of_order, customer, product, quantity
FROM orders;
SQL Orders Table Results:
| day_of_order | customer | product | quantity |
| 2008-08-01 00:00:00.000 | Tizag | Pen | 4 |
By rearranging the table column list inside the SELECT statement, we altered the appearance of the result set. Also, by not including the id column in the list of table columns, SQL did not fetch any column data for this column because we didn't ask SQL to do so.
sql - select all (*)
"SELECT (*)" is a shortcut that can be used to select all table columns rather than listing each of them by name. Unfortunately, going this route doesn't allow for you to alter the presentation of the results.
SQL Select All Query:
USE mydatabase;
SELECT *
FROM orders;
SQL Orders Table Results:
| id | customer | day_of_order | product | quantity |
| 1 | Tizag | 2008-08-01 00:00:00.000 | Pen | 4 |
ql - where
The WHERE clause sets a conditional statement, and it can be used with any type of SQL query. As the select query executes, SQL processes one row at a time. Each time the conditional statement is met (returns true), a row is returned as a result. SQL WHERE is essentially, a filtering mechanism for SQL queries and is a tremendous asset to any aspiring SQL developer.
SQL Where Query:
USE mydatabase;
SELECT *
FROM orders
WHERE customer = 'Tizag'
As we take a look at the results, notice how only the rows that meet the criteria (where the customer column value is Tizag) are returned. In this example, we are using the WHERE clause to filter out rows and only selecting data that meets the conditional statement.
SQL Results:
| id | customer | day_of_order | product | quantity |
| 1 | Tizag | 2008-08-01 00:00:00.000 | Pen | 4 |
| 2 | Tizag | 2008-08-01 00:00:00.000 | Stapler | 1 |
| 5 | Tizag | 2008-07-25 00:00:00.000 | 19" LCD Screen | 3 |
| 6 | Tizag | 2008-07-25 00:00:00.000 | HP Printer | 2 |
Conditional statements are not unique to SQL, and neither are operators. Operators are symbols such as (=) or (<), and they are seen inside of conditional statements and expressions in SQL and other programming languages. While we're not going to dive into much detail about the different kinds of operators yet, it is a good idea to be familiar with them and be able to recognize them inside of conditional statements as we look over the next few examples.
sql - where queries
With the WHERE clause on our tool belts, we can be more creative when querying for table rows. For instance, there may come a time where we would like to take a look at all the orders placed after a certain date.
SQL Where Date Query:
USE mydatabase;
SELECT *
FROM orders
WHERE day_of_order > '7/31/08'
This conditional statement will return only the orders that have made it into the table since the end of July, filtering out any orders in the table made prior to July 31st.
SQL Results:
| id | customer | day_of_order | product | quantity |
| 1 | Tizag | 2008-08-01 00:00:00.000 | Pen | 4 |
| 2 | Tizag | 2008-08-01 00:00:00.000 | Stapler | 1 |
| 3 | A+Maintenance | 2008-08-16 00:00:00.000 | Hanging Files | 12 |
| 4 | Gerald Garner | 2008-08-15 00:00:00.000 | 19" LCD Screen | 3 |
Notice how the date value is formatted inside the conditional statement. We passed a value formatted MM/DD/YY, and we've completely neglected the hours, minutes, and seconds values, yet SQL is intelligent enough to understand this. Therefore, our query is successfully executed.
sql - where with multiple conditionals
A WHERE statement can accept multiple conditional statements. What this means is that we are able to select rows meeting two different conditions at the same time.
Perhaps the easiest way to go about this is to add another condition to the previous example, where we retrieved only the orders placed after July 31st. We can take this example one step further and link two conditional statements together with "AND".
SQL Where And:
USE mydatabase;
SELECT *
FROM orders
WHERE day_of_order > '7/31/08'
AND customer = 'Tizag'
At this point, we have sent SQL two conditional statements with a singleWHERE clause, essentially applying two filters to the expected result set.
SQL Results:
| id | customer | day_of_order | product | quantity |
| 1 | Tizag | 2008-08-01 00:00:00.000 | Pen | 4 |
| 2 | Tizag | 2008-08-01 00:00:00.000 | Stapler | 1 |
By applying the
AND clause, SQL has now been asked to return only rows that meet both conditional statements. In this case, we would like to return all orders that were made before July 31
st and made by a specific company - which is, in this case,
Tizag. We have more examples of
SQL AND/OR. Just follow the link.
sql - insert
To use the INSERT command, we must first have an understanding of where we would like to insert data and what types of data we want to insert. Do we plan on inserting numbers? Strings? Files? Let's return to the orders table we created in an earlier lesson.
SQL tables store data in rows, one row after another. The INSERT command is the command used to insert new data (a new row) into a table by specifying a list of values to be inserted into each table column. The arrangement of values is important, and how they are arranged in the code corresponds to how the data values will be arranged in the the SQL table.
- id - (identity, integer)
- customer - (customer name, character string)
- day_of_order - (date value)
- product - (name of product, character string)
- quantity - (quantity, integer)
Looking at the column names alone will give you an idea of what type of data each column is expected to hold. The quantity column, for example, is expecting a number or integer of some sort and the day_of_order column is expecting a date value to be inserted.
SQL Insert Query:
USE mydatabase;
INSERT INTO orders (customer,day_of_order,product, quantity)
VALUES('Tizag','8/1/08','Stapler',1);
SQL Insert Results:
You may notice that the id column has been left out of the query statement. The reason behind this is that when we created the orders table, we gave the idcolumn a unique attribute called identity. SQL handles identity columns automatically for us and therefore, we do not need to manually insert data into this column.
The first value Tizag corresponds with the customer table column. This ensures SQL will insert this value into the corresponding table column.
Now when we run the SELECT (*) query, SQL should return two rows with our statement instead of only a single row.
Verification Query:
USE mydatabase;
SELECT *
FROM orders;
SQL Results:
| id | customer | day_of_order | product | quantity |
| 1 | Tizag | 2008-08-01 00:00:00.000 | Pen | 4 |
| 2 | Tizag | 2008-08-01 00:00:00.000 | Stapler | 1 |
sql - inserting values
As a shortcut, you may omit the table columns entirely and only supply thevalues in the INSERT statement:
SQL Insert Shortcut:
USE mydatabase;
INSERT INTO orders
VALUES('A+Maintenance','8/16/08','Hanging Files',12);
Again, we can skip the id column because SQL is able to identify that this column is an identity column and handle it accordingly.
SQL Results:
| id | customer | day_of_order | product | quantity |
| 1 | Tizag | 2008-08-01 00:00:00.000 | Pen | 4 |
| 2 | Tizag | 2008-08-01 00:00:00.000 | Stapler | 1 |
| 3 | A+Maintenance | 2008-08-16 00:00:00.000 | Hanging Files | 12 |
Before moving on, let's add some more rows and execute some more INSERTqueries. If you are using SQL Express, you should be able to copy the entire code section below and execute all the queries at once and then track the results with the verification query (SELECT * FROM orders).
SQL Inserts:
USE myDatabase;
INSERT INTO orders
VALUES('Gerald Garner','8/15/08','19" LCD Screen',3)
INSERT INTO orders
VALUES('Tizag','7/25/08','19" LCD Screen',3);
INSERT INTO orders
VALUES('Tizag','7/25/08','HP Printer',2);
Final Results:
| id | customer | day_of_order | product | quantity |
| 1 | Tizag | 2008-08-01 00:00:00.000 | Pen | 4 |
| 2 | Tizag | 2008-08-01 00:00:00.000 | Stapler | 1 |
| 3 | A+Maintenance | 2008-08-16 00:00:00.000 | Hanging Files | 12 |
| 4 | Gerald Garner | 2008-08-15 00:00:00.000 | 19" LCD Screen | 3 |
| 5 | Tizag | 2008-07-25 00:00:00.000 | 19" LCD Screen | 3 |
| 6 | Tizag | 2008-07-25 00:00:00.000 | HP Printer | 2 |
