Slides badge

SQL

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

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

SQL structure

  • We could rewrite the starter task into an SQL query using the table format:

SELECT CharacterName, Strength, Agility, Intelligence
FROM BondCharacters
WHERE
ORDER BY CharacterName ASCENDING

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

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

Running sql

Task

  1. Go to national5computing.co.uk/sql/ and complete the tasks there.

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

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

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

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

Presentation Overview
Close
JB
SQL
© 2020 - 2024 J Balfour
17:40 | 23-05-2024
Join Live Session
Start Remote
Save Progress
Slideshow Outline
Presenter Mode
Widget Screen
Canvas Controls
Random Selector
Timer
Volume Meter
Binary Converter
Python Editor
Show Knox 90
Provide Feedback
Help
!
Keywords
    DragonDocs Management
    Random selector
    Sections
      Binary conversion
      Denary to binary conversion
      Binary to denary conversion
      Feedback 👍
      Accessibility

      Apply a filter:

      ×
      All slideshow files