Adding a table
The database doesn’t currently hold any information about employees.
employee table would allow you to store information about who they are, along with their contact details and contract information.
When adding a new table, you should first consider what information needs to be stored.
The basic information about an employee might be:
- Phone number
- Email address
- How many days a week they work
You can then break this down into a set of fields and associated data types:
You will notice that I have used multiple fields to store name and address by splitting them into their component parts. For example, name is split into
surname, while address is broken down into:
address_number(the number of the property)
address_1(the first line of the address, probably the street)
address_2(an optional second line of the address, maybe for a village or town)
locality(probably the city)
region(the county or state)
postal_code(the postcode of the address)
Breaking the information down into multiple fields means that it can be searched and analysed easily. For example, you can filter employees by city. Having a single field for
address would make this more difficult and less reliable.
In addition to the data, an
employee_id primary key should also be included that meets the following criteria:
- It’s unique: no two records can have the same primary key data
- The primary key value should never be reused; if a record is deleted from the table, the primary key value should not be allocated to a new record
- It must not be modified
- There must be no NULL values in the primary key
Creating the table
Execute the following SQL statement to create the
employeetable with these fields:
CREATE TABLE employee ( employee_id text PRIMARY KEY, first_name text, surname text, address_number integer, address_1 text, address_2 text, locality text, region text, postal_code text, phone_number text, days_per_week real );
CREATE TABLEstatement is used to define the table name followed by the field name and the data type.
PRIMARY KEYparameter is added to the
employee_idfield to denote that this is the primary key for the table.
INSERTSQL statement to create an employee in the table, and then execute it.
The syntax for an INSERT statement is:
INSERT INTO table_name (field1_name, field2_name, field3_name, ...) VALUES (field1_value, field2_value, field3_value, ...);
You can download an example database that includes the changes to the
sales table and the new
What other information do you think you would need to store about your employees? What fields would you add to the
employee table? What data types would you use? Share your thoughts in the comments section.