Structured Data -
DATABASES
Objectives
• Understand what structured data is
• Understand key features of DATABASES:
• Tables
• Records
• Fields
• Keys
• Relationships
• Queries
DATABASE
• An way of organising (STRUCTURING) data do that the data can easily
be accessed, managed and updated
Copy the Access database from this folder to your
own home drive
• StudentShare Drive
• COMPUTING
• 4. GCSE
• Year 10
• Databases
• Use CTRL + C and CTRL + V to copy and paste it to your home drive
Basic Search Query – SELECT
SELECT Subjects.SubjectName, Subjects.SubjectTeacher
FROM Subjects
WHERE Subjects.ExamBoard = 'Edexcel'
• Adapt this query to find:
• The names of all Teachers
• The names and subjects of all Teachers
• All subjects who have Orange as the Book Colour
• All students who have a surname starting with the letter ‘G’
• SELECT Classes.SubjectName, Classes.StudentName
• FROM Classes
• WHERE Classes.SubjectName = 'Business'
• SELECT *
• FROM Students
• WHERE Students.DateofBirth < #01/01/2000#
DELETE – removes records from database
DELETE FROM Students
WHERE ID=5;
• Adapt this query to:
• DELETE Student 31 from the Students table
• DELETE Business from the Subjects table
• DELETE class 9 from the Classes table
UPDATE – find and change values
UPDATE Students
SET Students.Firstname = 'Sarah'
WHERE Students.Firstname = 'Sara'
• Adapt this query to:
• UPDATE Student 33 – change their email address to [email protected]
• UPDATE Computer Science to be Computing as the SubjectName
• UPDATE Mathematics to be Maths and the Teacher to be Miss Green
Your Task
• Research the INSERT SQL query online
• Demonstrate in screenshots how this works – add a new
student to the Students table
• Screenshot the SQL code
• Screenshot the result from a table of a INSERT
• Explain what the INSERT SQL query does.
INSERT INTO
INSERT INTO Students (Firstname, Surname, DateOfBirth, Emailaddress)
VALUES ('Jasper', 'Freer', '01/01/2005', '
[email protected]')
https://www.w3schools.com/sql/default.asp
• Write a SELECT, UPDATE and DELETE query on the Demo Database
• Screenshot each example
• Adapt this query to find:
• All subjects who have NONE teach it
• All students who have a birthdate after 01/01/2000
• Try WHERE Students.DateofBirth>#31/12/1999#
• All students who have the first name “Matthew”
Database Queries – complex SELECT (find)
SELECT Students.ID, Students.Surname, Classes.ClassID, Subjects.SubjectName,
Classes.SubjectName
FROM ((Classes
INNER JOIN Students ON Classes.StudentName = Students.ID)
INNER JOIN Subjects ON Classes.SubjectName = Subjects.SubjectName);
Homework – database features
• Table
• A set of organised data (records) put into categories (fields)
• Field
• a single part of a data record.
• Record
• a set of data values in different fields about one ‘thing’ stored in a
table
• Primary Key
• a piece of data to uniquely identify a record
• Relationship
• links between records in different tables
