Searching and sorting a database

Display everyone’s strength, agility and intelligence and sort them by their name from A-Z

Fields
Table
Criteria
Sort Order

Searching and sorting a database

Display everyone’s strength, agility and intelligence and sort them by their name from A-Z

Fields CharacterName, Strength, Agility, Intelligence
Table BondCharacters
Criteria
Sort Order CharacterName ASCENDING

SQL

Learning Intentions

  • Understand what SQL is and why it is used

  • Understand how to write SQL SELECT queries

  • Understand how to produce conditions in SQL queries

Success Criteria

  • I am able to explain why we use SQL
  • I am able to write some SQL SELECT queries

What is SQL?

  • SQL or Structured Query Language is the most common database query language.

  • It is a way of 'querying' or asking the database to do something.

  • SQL is used by far too many companies to list but here are a couple:

     

SQL structure

  • SQL is written in statements that are designed to be read like English. SQL follows a standard structure and is, therefore, a structured language.

Keyword Input Description
SELECT <fields> List of fields that will be displayed
FROM
<tables> List of tables that the data will come from
WHERE <search criteria> The criteria of the query
ORDER BY <field> <sort order> How to sort the records when displayed

SQL structure

  • The way in which SQL commands are processed is different to the way it is written:

     

  1. FROM – the tables that are required

  2. WHERE – the criteria to be met

  3. SELECT – the fields that will be displayed

  4. ORDER BY – the order to return the records

Example 1

SELECT forename, surname, dob, regclass
FROM Pupil
WHERE forename = "John"
ORDER BY surname ASC;

Example 2

SELECT forename, surname, dob, regclass
FROM Pupil
WHERE regclass = "5B1" OR regclass = "5B2"
ORDER BY regclass ASC;

Example 3

SELECT forename, surname, dob, regclass
FROM Pupil
WHERE dob > #31-12-1999#
ORDER BY dob DESC;

Selecting from a database

  • We’re going to use the following database:

PupilID Forename Surname RegClass
501 Jack Madison 4B3
502 Margaret Addams 3S1
503 John Addams 1S1
504 Francis Peterson 2F2
505 Robert Sands 5W3
506 Angelica Bruce 1S3
507 Fiona French 3W2

Selecting specific fields

Forename Surname
Jack Madison
Margaret Addams
John Addams
Francis Peterson
Robert Sands
Angelica Bruce
Fiona French
SELECT forename, surname FROM Pupil;

Selecting every field

PupilID Forename Surname RegClass
501 Jack Madison 4B3
502 Margaret Addams 3S1
503 John Addams 1S1
504 Francis Peterson 2F2
505 Robert Sands 5W3
506 Angelica Bruce 1S3
507 Fiona French 3W2
SELECT * FROM Pupil;

Selecting every field

PupilID Forename Surname RegClass
502 Margaret Addams 3S1
503 John Addams 1S1
SELECT * FROM Pupil WHERE Surname = "Addams";

SQL conditions: using AND

PupilID Forename Surname RegClass
502 Margaret Addams 3S1
SELECT * FROM Pupil WHERE Surname = "Addams" AND RegClass = "3S1";

SQL conditions: using OR

PupilID Forename Surname RegClass
502 Margaret Addams 3S1
503 John Addams 1S1
507 Fiona French 3W2
SELECT * FROM Pupil WHERE Surname = "Addams" OR Surname="French";

SQL in the industry

Task

  1. Copy the Customer database from the server to your own server space from the SQL Tasks worksheet

  2. Work through Exercise 2 which you can find in the worksheet.

  3. Copy the Super Hero database from the server to your own server space

  4. Work through Exercise 4 which you can complete in the worksheet.

JB
Databases : 5.1 SQL
© J Balfour
Tools