Database objects: table, query, form and report

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which database object stores the records?

A Report

B Table

2. Which database object finds records that match a condition?

A Query

B Form

3. Which database object is used to enter new records?

A Form

B Query

4. Which database object presents data in a neat, printable layout?

A Table

B Report

5. A teacher wants a list of all Year 8 students in the Red house. Which object does she use?

A Query

B Form

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1SELECT firstName, house FROM students;		AThis describes a query. A table only stores records in rows and fields.
2CREATE TABLE products (productId, name, price);		BA table that stores records in fields, such as the products a shop sells
3A query finds only the records that match a condition		CSELECT * FROM students WHERE points > 100;
4A form enters a new record into a table, one field at a time		DINSERT INTO students VALUES (11, 'Sam', 'Ray', 7, 'Red', 0);
5A report is where you type in new records.		EThis describes a form. A report presents records, for example as a neat printed summary.
6A table finds the records that match a condition.		FA query that finds records and shows only the chosen fields from the students table

Definitions

table, query, form, report

Fill in the Blanks

Complete each line of code by filling in the missing parts.

CREATE _____ Students (name TEXT, year INT);

______ name FROM Students WHERE year = 7;

<____ action="/add"> <input name="name"> </form>

table: stores records query: finds records ______: presents records

table: stores records form: ______ records report: presents records

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A CREATE TABLE students (

B studentId INTEGER,

C firstName TEXT

D yearLevel INTEGER

E );

Question 2

A -- Query: find Year 8 students

B SELECT firstName FORM students

C WHERE yearLevel = 8;

Question 3

A -- Form: Add Student

B INSERT INTO students

C (firstName, yearLevel)

D VALUE ('Kai', 7);

Question 4

A -- Report: points leaderboard

B SELECT firstName, points

C FROM students

D ORDER points DESC;

Question 5

A SELECT name products

B WHERE stock = 0;

C ORDER BY name;

Answer Key

Database objects: table, query, form and report

Section A: Quick Questions

B — Table

A — Query

A — Form

B — Report

A — Query

Section B: Match the Pairs

SELECT firstName, house FROM students; → A query that finds records and shows only the chosen fields from the students table

CREATE TABLE products (productId, name, price); → A table that stores records in fields, such as the products a shop sells

A query finds only the records that match a condition → SELECT * FROM students WHERE points > 100;

A form enters a new record into a table, one field at a time → INSERT INTO students VALUES (11, 'Sam', 'Ray', 7, 'Red', 0);

A report is where you type in new records. → This describes a form. A report presents records, for example as a neat printed summary.

A table finds the records that match a condition. → This describes a query. A table only stores records in rows and fields.

Section C: Definitions

table, query, form, report — The four database objects: a table stores records, a query finds records, a form enters records and a report presents them.

Section D: Fill in the Blanks

CREATE TABLE Students (name TEXT, year INT);

SELECT name FROM Students WHERE year = 7;

<form action="/add">
  <input name="name">
</form>

table: stores records
query: finds records
report: presents records

table: stores records
form: enters records
report: presents records

Section E: Spot the Error

C — Each field definition in a table must be separated by a comma. Line 3 is missing the comma after TEXT, so it should be ' firstName TEXT,'.

B — A query names the table it reads with the keyword FROM. 'FORM' is a typo (and a form is a different database object). It should be 'SELECT firstName FROM students'.

D — When a form saves a new record, the SQL uses the keyword VALUES (with an S) to give the data for the new record. It should be "VALUES ('Kai', 7);".

D — The sorting clause that a report uses to present records in order is ORDER BY. The word BY is missing, so it should be 'ORDER BY points DESC;'.

A — A query must say which table the records come from using FROM. Line 1 is missing FROM, so it should be 'SELECT name FROM products'.