Learning the SQL statements - select statement

Learning the SQL statements - select statement

Lesson Details:
June 29, 2020


I: Introduction

Sql is a database programming language, similar to programming languages like c++ or java. Although sql and other programming languages use different formats and syntaxes, they all use the same logic and flow of execution. This means that it is possible to learn one programming language and apply that knowledge to another. It also means that we can learn about the inner workings of one programming language by applying our knowledge to another.

I: Body

A: Learning the select statement

Sql statements are built around the concept of a query. A query is usually a question, such as "Which of my customers have spent more than $1000 in the last 3 months?" But not always. The basic form of a query is called a select statement. To do something useful, you have to write a select statement, so this will be our first topic.

The basic syntax for a select statement is:

select from where ;

is the name of the column that you want to extract information from. is the name of the table where you want to extract your data from. is an expression that results in either true or false, and determines whether the data should be selected or not.

So, for example, if you wanted to find out how many orders each customer has placed in the last 6 months, you could write the following query:

select count(*) from Orders where order_date > '2007-01-01';

What this query does is take every row in the orders table, then group all rows together that have an order_date value greater than or equal to Jan 1st 2007. From these rows, it counts how many there are, and puts the result in the variable count(*) . This counts all rows, even ones that wouldn't meet our condition, so we need to use count(*) to get around this. Our final query looks like this:

select count(*) from Orders where order_date > '2007-01-01';

This will select all results where the order date is greater than January 1st 2007. If you run this in mysql, you'll see something like this:

+----------+ | count(*) | +----------+ | 13459 | +----------+ 1 row in set (0.00 sec)

As you can see, there are 13459 rows in our orders table that meet this condition. So far this is pretty straight forward. But what if we wanted everything except the number of orders? Well, if we try to change our query slightly, we get an error:

select count(*) from Orders where order_date > '2007-01-01' -1; ERROR 1093 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'orders.order_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

This tells us that our query doesn't work because it's trying to add things up instead of just selecting them. In order to tell it that we don't care about sums or counts, we add a minus sign before the number:

select -count(*) from Orders where order_date > '2007-01-01'; +----------+ | count(*) | +----------+ | 11146 | +----------+ 1 row in set (0.00 sec)

This query will return all rows except for the count of orders. Try running the query yourself in mysql. Notice that it returned everything from our orders table except for the number of orders. So far we've seen how to select specific columns/data from a table. Now lets look at how to get specific kinds of data from a table. In our orders table, there is a field called total_price . We would like to know what percentage of orders cost more than $1000. In order to do this, we'll use a calculated column:

select sum(total_price) / count(*) as percentage from Orders where order_date > '2007-01-01'; +--------------+ | percentage | +--------------+ | 0.009958333333333 | +--------------+ 1 row in set (0.05 sec)

This queries adds up every single total price and divides it by the number of orders. The result is rounded down to two decimal places because that's what mysql uses when storing numbers in a database table. We then convert this into a percentage by adding an extra column to our result set called percentage . As you can see, the number we got back is 0.009958333... , which corresponds exactly with what we expect: 99.9% of orders cost more than $1000. Try running this query yourself in mysql and see if you get similar results. You can also experiment with adding multiple conditions using and/or clauses too:

loader
Course content