SQL - Structured Query Language
|
Up until now, when we have made queries we have used the query builder's graphical design view in order to do so. In this unit we will learn the basics of SQL and how to write SQL queries on our own. |
|
Getting Started
|
First things first, download the Access file on the right. This contains a simple shop database with three tables and some dummy data, we will be using this database for the majority of our work.
|
|
1) To make a query, first go to the Create tab, then Query Design
|
|
2) Save the query as "qry1", then on the "Query Design" tab make sure that you are on the "SQL View"
|
|
3) Finally, copy into the blank area what you see in the green box in the image below, then hit "Run" in the top left.
Congratulations, you have just written your first SQL query!
Select, Insert, Update, Delete
The four main queries that we use in databases are:
First of all let's focus on doing some select queries. To help us distinguish the SQL commands from the table names/columns, try to use capital letters (like in the examples).
- Select - used to obtain data for viewing
- Insert - used to add new rows into our database
- Update - used to update the information in rows of our database
- Delete - surprise! Used to delete rows.
First of all let's focus on doing some select queries. To help us distinguish the SQL commands from the table names/columns, try to use capital letters (like in the examples).
SELECT
|
Example One: Let's say we wanted to just show the first name, last name, and date of birth of the people in our Customers table. After the SELECT I can remove the "*" and write the column names that I'm interested in data from.
NOTE: You need to write the name of the table that the data is coming from, followed by the column name (kind of like we did with objects in JS). Task: Change your qry1 to match this, run it and see what happens. |
|
|
Example Two: Let's say we only wanted to get the information for people whose name starts with an "S". We can add on a WHERE and specify that we want any names that are LIKE "S*". Here "*" will match any other characters after.
Task: Make a qry2 like the one to the right, run it and see what happens. |
|
SELECT Challenges
Below are a series of challenges for you to complete using the Shop database. Use the examples at W3Schools - https://www.w3schools.com/
When you complete a challenge make sure to put a screenshot of the SQL and the output into the page for these in OneNote.
When you complete a challenge make sure to put a screenshot of the SQL and the output into the page for these in OneNote.
- Retrieve all customers born after January 1, 2000.
- Show all orders that not been sent.
- Find all orders placed before 20/11/2024
- Display the first and last name of customers whose preferred name is "BigJeff"
- List all products that cost more than $50
- Show all orders that have been sent but not delivered
- Display all customers whose last name starts with "S"
- Retrieve all orders placed by the customer with CustomerID = 8
- Show all products sorted by price
- List all products where the price is between $5 and $30
- Show all customers sorted by their last name alphabetically
- Display all orders that have been sent but are not marked as delivered
