SQL

SQL

​

Introduction

Data, such as a list of names, can be stored in a straightforward text file. But it has some major limitations in terms of searching and sorting the data.​

An alternative to storing data directly in a file is to store it in a database.​

A database is designed specifically to handle, sort, search and manipulate organised data.​

A database is a collection of data or information which is held together in an organised or logical way.​

Because of the high organisation of a database, data can be retrieved, sorted and updated very efficiently.​

Multiple Choice

What is a database?

• something that holds data

• a program on a computer

• something that finds data

• something that sends data

SQL

Adding and retrieving information in a database is done using a special-purpose programming language called SQL. 

The basic actions carried out on a database are 

Insert  Update  Delete  Retreive  Sort 

Tables

A database is made up of one or more tables. 

Like this: 

Each table should be given a relevant name. The table above is named 'Characters' because it contains well known cartoon or comic characters. 

Fields

Each column in a table is called a field and contains information about a particular item of data. Each field should be given a relevant name, for instance the field called 'city' contains a city name. A field is always set to a certain data type such as 

numeric types: integer, float, real  boolean  character  string  Date and time  binary  Other e.g. blob 

Records

A record is made up of collection of fields related to a single item. 

In short: 

Each piece of information is entered into a 'field' 

Related fields are organised into records 

Related records are placed into tables 

Related tables are collected into a database 

Data types

• numeric types: integer (whole number), float and real (decimal number) 

• boolean  - TRUE, FALSE,

• character  - 1 single character

• string  - 2 or more characters

Multiple Choice

What data type would be the answer to the expression x < 5?

• Boolean
• Character
• Real
• String

Multiple Choice

What data type would be best used to record an email address?

• String
• Integer
• Real
• Character

Multiple Choice

Integers cannot store negative numbers.

• False
• True

Multiple Choice

What data type would be best to record the exact length of a piece of string in meters?

• Real
• Boolean
• Decimal
• Integer

Multiple Choice

What data type would be best used to record the number of people at a party?

• Integer
• Real
• String
• Boolean

Multiple Choice

In a database, all the information about one person or thing is called a

• Worksheet

• Presentation

• Record

• File

Multiple Choice

In a database, data is stored in

• Queries

• Forms

• Reports

• Tables

Multiple Choice

The best data type to choose for a memberID would be

• Number

• Date/Time

• String

• Currency

Multiple Choice

The best data type to choose for date of birth would be

• Number

• Date/Time

• Text

• Currency

Multiple Choice

In a TABLE, a row is known as a...

• Information

• Column

• Data

• Record

Multiple Choice

In a TABLE, a column is known as a...

• Record

• Field

• Row

• Data

Multiple Choice

Which of these holds lots of data?

• Record

• Field

• Table

• Drawing

SQL

_{Short for 'Structured Query Language'.} 

_{SQL was created for use with an SQL database. It consists of a set of commands that can carry out the following tasks} 

_{Search records, Update records,   Insert records , Delete records , Find related records}

_{SQL commands} 

_{SELECT This retrieves a record or a set of records. Other commands can then be carried out on that record set without affecting the whole database.} 

_{FROM The identifies the table where the data comes from} 

_{WHERE This sets out some condition with which to choose only certain records} 

Using SELECT

• _{This is a command to retrieve records from a database table. It doesn't do anything to the record itself. But it is very useful because by selecting a record, other commands can be carried out on just the selected records without affecting the rest of the database.} 

• If we wanted to retrieve every record from this table, the SELECT command is:- 
           SELECT * FROM Characters 
  The asterisk * is called a 'wildcard' and it means 'all records' or 'all fields'. The FROM is followed by the name of the table
  

SELECT

• _{If we wanted to fetch all fields from within a specific record then the wildcard is useful, e.g if we wanted all fields from the Donald Duck record the SELECT statement is} 

       _{SELECT * FROM Characters WHERE First Name = "Donald"}  

• _{The SELECT command is also used to fetch fields from within a record set. e.g}

   _{SELECT Address, City FROM Characters WHERE First Name = "Donald"}

Multiple Choice

How would you display Chairs in the Items table that have a Price greater than £50.

• SELECT * FROM Items WHERE Type = 'Chair' AND Price > 50

• SELECT * FROM Items WHERE Type = 'Chair' OR Price < 100

• SELECT * FROM Iterms WHERE Type = 'Chair' AND Price >= 50

• SELECT * FROM Items WHERE Price > 50

Multiple Choice

How would we script a SQL query to select "Description" from the Item table?

• SELECT Item.Description

• EXTRACT Description FROM Item

• SELECT Item FROM Description

• SELECT Description FROM Item

Multiple Choice

How do we select all rows for the "Designer" table?

• SELECT * FROM Designer

• SELECT [All] FROM Designer

• SELECT Designer.*

• SELECT FROM Designer

Multiple Choice

What is the correct order of clauses in a SQL statement?

• SELECT, FROM, ORDER BY, WHERE

• SELECT, FROM, WHERE, ORDER BY

• SELECT, WHERE, FROM, ORDER BY

• WHERE, FROM, SELECT, ORDER BY

Multiple Choice

Which character returns all the data found in a table?

• %

• #

• *

• /

Multiple Choice

Which word is missing from the following SQL statement? (Select, from, where)

Select * table_name

• With

• Where

• From

• And

Multiple Choice

Using which word allows you to specify that only one of a series of conditions needs to be met in a query filter?

• And

• Maybe

• If

• Or

Multiple Choice

Using which word allows you to specify that more than one condition must be met in a query?

• Also

• And

• Or

• Where

Multiple Choice

If you were wanting to filter data, which clause would you use?

• Where

• Order by

• From

• Select

Multiple Choice

Which statement is used to extract data from a database?

• Extract

• Get

• Open

• Select

Multiple Choice

What does "SQL" stand for?

• Structured Question Language

• Structured Query Language

• Simple Query Language

• Simple Question Language

Using SELECT .... WHERE

• This is a command to select only certain records from a database table. 

  Table name: Characters 

• The general format of the SELECT ... WHERE command is :- 

  SELECT fields FROM table WHERE Criteria 

SELECT, WHERE

_{Or, without the labels:} 

_{SELECT FirstName, LastName FROM Characters}

_{ WHERE City = 'Anaheim'} 

_{FirstName and LastName are fields (columns) within the table Characters.} 

_{This SQL command will return the first and last names of anyone in the table whose city is Anaheim. In this case, there are two matches:} 

_{Mickey Mouse  Arnold Mouse} 

Operators

• The standard set of arithmetic comparators can be used in the criteria (=, >, <., >=, <=, !=). For example Age < 60 would get all records with an age of less than 60. 

Open Ended

State the most appropriate data type used to store Age

Open Ended

State the most appropriate data type used to store City

Open Ended

Type an SQL statement to select the First Name field for all records that have an Age greater than 50

Open Ended

How many records would appear in the result of the SQL statement to select the First Name field for all records that have an Age greater than 50

Open Ended

What does the wildcard (*) do?

Home

Open in new tab

Copy link