Databases and SQL

Database

A computer-based, structured collection of data items which allows the user to store, retrieve, update, search and manipulate the data.

The basic structure for a database is one or more

tables

Reading across is a

row, we have the complete record

Each column

id one item of data from a record, and is known as a field

One advantage of a database

its ability to search through large volumes of data and quickly find the information we need.

Finding certain data in a database is usually called

‘querying’ the database or ‘searching’

A simple query

one where you are looking for all the records in the database that have one thing in common. E.g.: Pupils who are in UC4

A complex query is

where we are looking for records that have two (or more) things in common

e.g.: Pupils in UC4 who live in Manchester

We use these ‘operators’ when we compose a database query:

=, >, <, >=, <=

another advantage of a database

is its ability to sort large volumes of data quickly and put it in any order we wish

data is sorted in either…

ascending or descending order

SQL (structured query language) is a

computer language designed for creating database queries. Most database systems support SQL.

Casual users of a database

don’t need to use SQL. Usually they are provided with an easy-to-use interface (aka ‘front-end’) with text boxes, pull down menus, etc.

Many systems are internet based, we call this:

web-form

programmers building a database system will use

SQL to create the system used by ordinary users.

A line of SQL intended to perform a query on the database is called a

statement

An SQL statement refers to tables and columns (fields) in the database

e.g. SELECT title, author, price FROM books;

SELECT * FROM pupils WHERE house = “St. Austin’s”;

we usually write SQL keywords like SELECT and WHERE in

UPPERCASE. Table names and fields are written in lowercase.

SELECT forename, surname FROM pupils

SELECT - look in the database and find

forename, surname - the information from these fields

FROM pupils - using this table

SELECT * FROM pupils

* - the information from all tables

SELECT * FROM products WHERE price <= 10.0

WHERE - but only the ones where in the price field contains a value of 10.0 or less