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:

FROM – the tables that are required
WHERE – the criteria to be met
SELECT – the fields that will be displayed
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

Go to national5computing.co.uk/sql/ and complete the tasks there.
Copy the Customer database from the server to your own server space from the SQL Tasks worksheet
Work through Exercise 2 which you can find in the worksheet.
Copy the Super Hero database from the server to your own server space
Work through Exercise 4 which you can complete in the worksheet.

Presentation Overview

SQL

16:22 | 29-04-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

Remove after pick

Sections

Binary conversion

Denary to binary conversion

Binary to denary conversion

Use two's complement

Slideshow Outline

Help

The following are key shortcuts for using DragonSlides and reveal.js.

Key combination	Action
→, PAGE DOWN, SPACE	Next slide
←, PAGE UP	Previous slide
SHIFT + →	Last Slide
SHIFT + ←	First slide
B	Whiteboard
W	Widget Board/White Screen
C	Toggle Notes Canvas
DEL	Clears Whiteboard/Notes Canvas
S	Show Presenter Mode
SHIFT + D	Toggle Dark Mode
H	Goes to the Home Slide
META + P	Show Print options window

In order to use the 'Start Remote' feature, you'll need to download JB Slide Controls from the Apple App Store. Once you have this you'll be able to control the slides using the app from you Apple Watch.

Feedback 👍

Accessibility

Apply a filter:

Dark Mode

Bold Text

All slideshow files