SQL yr 12

• Computing
• Further Education (Key Stage 5)

SQL

New language, new you!

SQL

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.

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.

SQL Select Exercises

Complete 1-3 here:

Extension: Try SQL WHERE Exercises 1 & 2

What is a wildcard?
* %

%* Wildcards

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

* = All from the result set.

What does this do?
SELECT * FROM asset

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

This is a good filter:
WHERE grade = *

1. True
2. False

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);

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

A GDPR request has come through. You've been asked to delete a customer and all related data related to, Sarah Mcdonalds, [e-mail] from your users table.

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;

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;

Need to knows

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