SQL Examples

Nominations Database Data

These are the records in my database. This table is called nominations and records students and the activity they are involved in at school.

SQL Queries Template

This is the order that SQL statement must be written

SELECT

FROM

INNER JOIN

WHERE

GROUP BY

ORDER BY

SQL Statement Result

SELECT * FROM `nomination` WHERE Gender like "M" order by last

* means select all fields from the selected table

SELECT Year, First, Last FROM nomination WHERE Gender = "M" and Activity = "Soccer" order by last

When searching for fields that contain strings you will need to use quotations " "

SELECT Year, First, Last FROM nomination order by Year DESC

You should always order your query

ASC = ascending
DESC = descending
By default, records will be ordered based on when they were inserted

SELECT Activity,Count(Activity) FROM nomination group by Activity order by Activity

You can use aggregate functions to Count, Sum, average the data

Its good practice to assign these aggregates a different field name called an alias. We use as

SELECT Activity,Count(Activity) as count FROM nomination group by Activity order by Activity

Relational Database Queries - Inner Join

This is an example of a relational database. The table on the left contains the activities and the table on the right contains data about students and the activity they are completing.

Instead of the activity name being stored in the student_activities table we store a number which references the school activity id in the activities table. We store the activity id as foreign key in the student_activities table.

Count all activities and group by activity

SELECT activities.activity_name, Count(Activity) as count

from nominations

INNER JOIN activities

on nominations.Activity = activities.activity_id

group by Activity

order by activities.activity_name

Tips:

To join tables together we need to know their primary and foreign keys
We join the tables together through these keys as seen above.
The selected table should always be the transnational table i.e the one that holds the primary and foreign key value.

Report abuse