Structured Query Language

Structured Query Language (SQL)

Mr Mordey

Big Picture

Described how sub routines are used in programming.

Past

Describe how SQL is used throughout programming and accessing databases.

Develop code that makes use of SQL procedures

Present

Future

Careers: HTML Developer, Database Manager.

Multiple Choice

What is a sub routine?

• A subroutine is a method for storing data.
• A subroutine is a graphical user interface element.
• A subroutine is a type of variable.
• A subroutine is a small chunk of contained within a larger program.

Multiple Choice

What is the difference between a function and procedure?

• A function is always faster than a procedure.
• Procedures can return multiple values, while functions cannot.
• Functions are used only for mathematical calculations, whereas procedures are not.
• The main difference is that a function returns a value, whereas a procedure does not.

SQL stands for structured query language. This is a specific programming language to search for data in a database.

Databases can have thousands of records so SQL allows us to search for data in a more efficient manner so it can be returned in a program.

​

SQL

A database is made up of fields and records.

A record contains different data about a single product/person and goes across in the table. Each record is uniquely identified by a primary key. A record is unique.

A field is a piece of information that is stored for each record, this goes down in the table. A field is not unique.

​

Database Structure

​Records

Multiple Choice

What is the purpose of SQL?

• To write and compile programming languages.
• The purpose of SQL is to manage search, and manipulate databases.

• To create and design websites.
• To manage and manipulate spreadsheets.

Labelling

Label the database features

Drag labels to their correct position on the image

Data

Primary Key

Container

Secondary Key

Field

Record

Searching For Data

Databases store huge amounts of information. SQL allows us to easily search for data from each table using the SELECT command, lets use this table as an example:

• SELECT MovieName FROM movies would return all movies from the table

• SELECT MovieName, Rating FROM movies would return all movies and their rating from the table

• SELCECT * FROM movies would return all records from the table

Filtering Data

We can filter the data down using the WHERE command to only show certain information using conditions, we will still use this table:

• SELECT * FROM movies WHERE rating >= 7 would return the following data:

Multiple Choice

What is the purpose of the * command when searching for records in SQL?

• The * command is used to delete records in SQL.
• The * command limits the number of records returned in a SQL query.
• The * command updates specific columns in a SQL query.
• The * command returns all records in a SQL query.

Drag and Drop

Complete the code below so that only Star Wars and the Director are returned.



​

Placeholder

MovieName, ​

Placeholder

FROM ​

Placeholder

​

Placeholder

rating ​

Placeholder

Drag these tiles and drop them in the correct blank above

SELECT

Director

WHERE

<6

movies

>6

HIGHLIGHT

GET

MovieID

WHEN

Filtering Data with operators

We can also use Boolean operators to select multiple fields to filter data from.

• SELECT Director FROM movies WHERE rating >=7 AND age = 12 would return the following data:

• SELECT * FROM movies WHERE rating >= 5 AND age = "PG" would return the following data:

WildCards

We can use Wildcards so search for patterns in data using the WHERE and LIKE command:

• SELECT director FROM movies WHERE MovieName LIKE "%World" would return the following data:

• SELECT * FROM movies WHERE MovieName LIKE "%End" would return the following data:

Multiple Choice

How are Boolean operators used in SQL?

• Boolean operators in SQL are used to filter multiple fields.

• Boolean operators are used to sort results in SQL.
• Boolean operators are only applicable in programming languages, not SQL.
• Boolean operators are used to create tables in SQL.

Multiple Choice

What is the purpose of the Like "% " wildcard in SQL

• To only return records that include symbols.

• To remove data from a record.

• To convert all records into a percentage.

• To return data with the pattern outlined in the condition.

Open Ended

Write SQL code to return all records contain the letters "end" in the movie name

Main Task

Computer Task:

Add to your exam reference language programming guide in Publisher.

You should include information about SQL including an example.

You should include the definition of SQL, why we use SQL and examples of code to create a database and search for data.

Complete all the questions on the work sheet.