SQL yr 12

SQL
New language, new you!
1 / 16
next
Slide 1: Slide
ComputingFurther Education (Key Stage 5)

This lesson contains 16 slides, with interactive quizzes and text slides.

time-iconLesson duration is: 50 min

Items in this lesson

SQL
New language, new you!

Slide 1 - Slide

SQL

Slide 2 - Mind map

Key Objectives: Structured Query Language

All: Know the fundamental commands of SQL.
Most: Be able to write and understand simple queries.
Some:  Be able to write and understand complex queries.

Slide 3 - Slide

Basic query syntax
SELECT Specifies the column(s) to retrieve:
SELECT name, age, email

FROM Specifies the table:
FROM user

WHERE optionally filters the results:
WHERE student = 1

End with a semicolon ;

SELECT first_name, age, email
FROM user
WHERE student = 1;

This will retrieve from the school database; first name, age and email, for all students but not staff.

Slide 4 - Slide

SQL Select Exercises
Complete 1-3 here:
Extension: Try SQL WHERE Exercises 1 & 2

Slide 5 - Slide

What is a wildcard?
* %

Slide 6 - Open question

%* Wildcards
% = zero, one or multiple characters in a string.

* = All from the result set.

Slide 7 - Slide

What does this do?
SELECT * FROM asset

Slide 8 - Open question

What does this do?
WHERE NOT last_name LIKE '%s'

Slide 9 - Open question

This is a good filter:
WHERE grade = *
A
True
B
False

Slide 10 - Quiz

DELETE

The command to delete results:
DELETE FROM users
WHERE email = '@';
INSERT

The command to add one or more new records:
INSERT INTO users (first_name, second_name)
VALUES (Victor, Frankenstein);

Slide 11 - Slide

Write a query to insert a new employee into the "employees" table with employee_id = 103, employee_name = "John Smith", and department_id = 2.

Slide 12 - Open question

A GDPR request has come through. You've been asked to delete a customer and all related data related to, Sarah Mcdonalds, smaccies@beeffarmer.com from your users table.

Slide 13 - Open question

SQL DROP
DROP is DELETE for database objects instead of records. You can remove; tables, views, indexes or even the whole database:
Keywords; DROP, TABLE, VIEW, INDEX, DATABASE.
Syntax:
DROP TABLE users;

DROP DATABASE school_database;

Slide 14 - Slide

JOIN (INNER JOIN)
JOIN combines rows from two or more tables with a related column. 
Syntax:
SELECT columns
FROM table1
INNER JOIN table2 on table1.column = table2.column;

Slide 15 - Slide

Need to knows
SELECT (including nested SELECTs), FROM, WHERE, LIKE, AND, OR, DELETE, INSERT, DROP, JOIN,  WILDCARDS (*, %)

Slide 16 - Slide