Week 2: Queries using SQL
SQL is a standard language for accessing and manipulating databases.
SQL stands for Structured Query Language.
SQL stands for Structured Query Language.
What are Queries???
After Tables, Queries are perhaps the most important component in a database.
Queries are used to retrieve information from a database.
A telephone directory can be used for an example of a query.
When you look up a phone number in a phone directory, you use query techniques.
You might begin by looking for names that begin with J. Then you might narrow it down to listings with the last name Johnson.
The criteria for your query is Last Name = Johnson. If there are still too many results, you may add additional criteria to narrow down the results.
E.g. Initials, Suburb, street address. Eventually, your criteria will narrow down the results until you have the result you are after.
When you query a database on a computer, you use the same techniques. A query is a set of rules for finding information in a database.
Queries in a database use the Structured Query Language (SQL – often pronounced “sequel”).
After Tables, Queries are perhaps the most important component in a database.
Queries are used to retrieve information from a database.
A telephone directory can be used for an example of a query.
When you look up a phone number in a phone directory, you use query techniques.
You might begin by looking for names that begin with J. Then you might narrow it down to listings with the last name Johnson.
The criteria for your query is Last Name = Johnson. If there are still too many results, you may add additional criteria to narrow down the results.
E.g. Initials, Suburb, street address. Eventually, your criteria will narrow down the results until you have the result you are after.
When you query a database on a computer, you use the same techniques. A query is a set of rules for finding information in a database.
Queries in a database use the Structured Query Language (SQL – often pronounced “sequel”).
Setting up a Query
Let's practice with the SQL file below.
schooldatabase.sql |
Select either your table or database that you would like to run queries on and click on the SQL tab.
You will get an area to start typing your Query.
Type: SELECT * FROM students;
Then hit go.
What happens?
Type: SELECT * FROM students;
Then hit go.
What happens?
To only show specific columns (fields) in your table, type the name of the field/s instead of *.
SELECT columnName1, columnName2 FROM tableName;
Now go back to the SQL editor and type:
SELECT studentID, firstName, surname FROM students;
SELECT columnName1, columnName2 FROM tableName;
Now go back to the SQL editor and type:
SELECT studentID, firstName, surname FROM students;
Tasks:
Try the following commands to see what they do:
SELECT DISTINCT suburb FROM students;
SELECT COUNT(DISTINCT suburbs) FROM students;
SELECT * FROM students WHERE suburb='Richmond';
SELECT * FROM students WHERE studentID=1;
SELECT * FROM students WHERE studentID<5;
SELECT * FROM students WHERE suburb='Richmond' AND Gender='M';
SELECT * FROM students WHERE suburb='St Kilda' OR Gender='M';
SELECT * FROM students ORDER BY suburb;
INSERT INTO students ( firstName, surname, houseNumber, street, suburb, city, state, postCode, phone, dateOfBirth, Gender, yearLevel, Mark, parentID)
VALUES ('Bob', 'Erichsen', '21', 'Stavanger Road', 'Altona', 'Melbourne', 'Vic', '3038', '0435675345','2000-09-03', 'M', '11', '80', '5');
UPDATE students SET phone = '0456765212' WHERE studentID = 1;
DELETE FROM students WHERE studentID=7;
SELECT MIN(Mark) AS lowestGrade FROM students;
SELECT MAX(Mark) AS highestGrade FROM students;
SELECT COUNT(studentID) FROM students;
SELECT AVG(Marks) FROM students;
SELECT SUM(Marks) FROM students;
Create 2 of your own to share with class.
SELECT DISTINCT suburb FROM students;
SELECT COUNT(DISTINCT suburbs) FROM students;
SELECT * FROM students WHERE suburb='Richmond';
SELECT * FROM students WHERE studentID=1;
SELECT * FROM students WHERE studentID<5;
SELECT * FROM students WHERE suburb='Richmond' AND Gender='M';
SELECT * FROM students WHERE suburb='St Kilda' OR Gender='M';
SELECT * FROM students ORDER BY suburb;
INSERT INTO students ( firstName, surname, houseNumber, street, suburb, city, state, postCode, phone, dateOfBirth, Gender, yearLevel, Mark, parentID)
VALUES ('Bob', 'Erichsen', '21', 'Stavanger Road', 'Altona', 'Melbourne', 'Vic', '3038', '0435675345','2000-09-03', 'M', '11', '80', '5');
UPDATE students SET phone = '0456765212' WHERE studentID = 1;
DELETE FROM students WHERE studentID=7;
SELECT MIN(Mark) AS lowestGrade FROM students;
SELECT MAX(Mark) AS highestGrade FROM students;
SELECT COUNT(studentID) FROM students;
SELECT AVG(Marks) FROM students;
SELECT SUM(Marks) FROM students;
Create 2 of your own to share with class.
LIKE OPERATOR
"The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards used in conjunction with the LIKE operator:
https://www.w3schools.com/sql/sql_like.asp
There are two wildcards used in conjunction with the LIKE operator:
- % - The percent sign represents zero, one, or multiple characters
- _ - The underscore represents a single character"
https://www.w3schools.com/sql/sql_like.asp
Task:
Try the following commands using the LIKE operator to see what happens:
SELECT * FROM students WHERE postcode LIKE '31%';
SELECT * FROM students WHERE postcode LIKE '%18';
SELECT * FROM students WHERE surname LIKE '%al%';
SELECT * FROM students WHERE firstName LIKE '_a%';
SELECT * FROM students WHERE firstName LIKE 'n%k';
SELECT * FROM students WHERE surname NOT LIKE 'c%';
SELECT * FROM students WHERE postcode LIKE '31%';
SELECT * FROM students WHERE postcode LIKE '%18';
SELECT * FROM students WHERE surname LIKE '%al%';
SELECT * FROM students WHERE firstName LIKE '_a%';
SELECT * FROM students WHERE firstName LIKE 'n%k';
SELECT * FROM students WHERE surname NOT LIKE 'c%';
More Queries:
ORDER BY
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
SELECT * FROM Customers
ORDER BY Country;
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
SELECT * FROM Customers
ORDER BY Country;
Query Designs:
Query Name _____SearchStudentID__
Field Name |
Calculation/Data Source |
Description |
studentID |
SELECT * FROM students WHERE postcode LIKE '1%'; |
Return StudentID starting with postcode starting with 1 |
Postcode |
Value |
Forms:
Although information in a database can be entered and edited directly in a table, most people find it simpler to use a form.
We use forms all the time in everyday life as a way of recording information so forms are familiar to us.
We use forms all the time in everyday life as a way of recording information so forms are familiar to us.
Reports
Reports are a way to display information.