Skip to main content

SAQ 2.1

1

Practice

Create table

Create a table named 'Person' with five columns named 'National insurance number NI', 'LastName', 'FirstName', 'Address' and 'Age'.

Alter table

Add a column named 'City' to the person table.

Insert statement

Insert the following data in the person table:

Update statement

Update the person table by adding a first name 'Sammy' to the person table with the last name 'White'.

Delete a row

Delete 'Sammy White' from the person table.

Function

Part 1: Calculate the average age of the people in the person table.

Part 2: Calculate the average age for people who are older than 20 years.

Part 3: Calculate the maximum and minimum age in the person table.

Create Table

CREATE TABLE Person

(NI CHAR VARYING(25),

LastName CHAR VARYING(25),

FirstName CHAR VARYING(25),

Address CHAR VARYING(25),

Age INT)

Alter Table

ALTER TABLE Person ADD City varchar(30)

Insert Statement

INSERT INTO Person

VALUES (1, Billy, Jones, 23, '23 High Street ', 'East Kilbride')

INSERT INTO Persons

VALUES (2, Sam, White, 34, '129 Anderson ', 'Hamilton')

INSERT INTO Persons

VALUES (3, Abud, Al Zarrug, 17, '4 George street ', 'Glasgow')

Update Statement

UPDATE Person SET FirstName = 'Sammy'

WHERE LastName = 'White'

Delete a row

DELETE FROM Person WHERE LastName = 'White'

Function 1: SELECT AVG(Age) FROM Persons

Function 2: SELECT AVG(Age) FROM Persons WHERE Age>20

Function 3: SELECT MAX(Age) FROM Persons SELECT MIN(Age) FROM Persons

Check your answer

Next: Section 2 Crossword