Queries

Finally, we have data to play with. Unless someone is learning SQL to be a database administrator or computer programmer they will be pulling data most of the time. Creating tables and inserting data is not what most end users do. So the emphasis in these tutorials is about pulling data.

You pull data with a select statement. Let's look at an example.

SELECT * FROM person;

That's it. That is all the code. It starts with the SELECT keyword. After that we need to list which columns we need. Since we want all of them we can just use an asterisk (or star, *). The FROM keyword indicates the end of the column listing and the name of the table. When we run this code we get the following output.

person_id	person_first	person_last
X00001	Alex	Alexander
X00002	Bob	Baker
X00003	Charlene	Crystal
X00004	Dolly	Donaldson
X00005	Edgar	East
X00006	Freddy	Freaky
X00007	Grant	Gradenow
X00008	Helen	Helene
X00009	Isabella	Inanova
X00010	John	Jacobson

And there is all of the person data. We can easily do the same for the awards data.

SELECT
	award_id,
	award_fund,
	award_offered,
	award_accepted,
	award_disbursed
FROM award;

award_id	award_fund	award_offered	award_accepted	award_disbursed
X00001	PELL	100.0	100.0	100.0
X00001	SEOG	101.0	101.0	101.0
X00001	SUB	102.0	202.0	0.0
X00001	UNSUB	103.0	2002.0	0.0
X00002	PELL	200.0	0.0	0.0
X00002	SEOG	21.0	20.0
X00002	SUB	202.0	2202.0	0.0
X00002	UNSUB	203.0	2333.0	0.0
X00003	PELL	3000.0	300.0	300.0
X00004	SUB	402.0	4202.0	0.0
X00004	UNSUB	400.0	4.0	0.0
X00005	PELL	555.0	500.0	500.0
X00008	PELL	88.0	0.0	0.0
X00008	SEOG	80.0	80.0	80.0
X00009	SEOG	90.0
X00010	PELL	100.0
X00010	SEOG	110.0

In this code we didn't use an asterisk. Instead we enumerated the columns that we wanted in the output. If we didn't want one of them we just wouldn't list it.

Aliases

As supergeniuses, we understand that it is important to keep in mind which tables contain which columns. So we wisely put a prefix on each column name indicating the table. As our queries get more complex this will be very important if we don't want our hair to fall out.

But SQL has a feature that also help organize our code. It is called an alias. When you name a table after the FROM keyword you can add an AS keyword followed by an arbitrary name. In the example below we rename "person" as "p".

SELECT * FROM person AS p;

You can use the alias to indicate the table the column came from by prefixing it with the alias and a dot (or period for old people like me).

SELECT p.* FROM person AS p;

Here we rename the "award" table as "a" and prefix each enumerated column.

SELECT
	a.award_id,
	a.award_fund,
	a.award_offered,
	a.award_accepted,
	a.award_disbursed
FROM award AS a;

You can drop the AS keyword and just add the alias separated from the table name with a space. I am not a big fan of doing this. It is easy misread with old code or someone else's code. But it is valid, not a big problem, and you probably will see it often.

SELECT p.* FROM person p;

SELECT
	a.award_id,
	a.award_fund,
	a.award_offered,
	a.award_accepted,
	a.award_disbursed
FROM award a;

You probably are not that impressed with aliases. I get it. I haven't yet given you much to be impressed by. But in the (probably) near future your queries are going to get much more complicated. Aliases will make things much organized and easy to trace.

I strongly recommend you get into the habit of using aliases.

And Now...

And now we need to limit our data with where statements.