Create DB & Table Queries

Creating A Database

Once you have an sql server / phpmyadmin running, you will need to create a database. This is very simple:

CREATE DATABASE DBName ;

so to create a database called my_db:

CREATE DATABASE my_db ;

Creating A Table

Now you have a database, you will need to create a table within your database. The create table sql should give a name for the table and then the name of each field and its data type & size.

CREATE TABLE table_name
(
field_name1 data_type(size),
field_name2 data_type(size),
filed_name3 data_type(size),
....
);

The data types available within SQL can vary and within your exam any suitable data type will be allowed:

Text data types

CHAR(x), VARCHAR(x), TEXT, STRING, Long Text, Memo

Number data types

INT(x), Tiny INT(x), Long, double, decimal, float

Date data types

Date, Time, DateTime, TimeStamp

Example

CREATE TABLE Student
(
ID Int(6),
FirstName VARCHAR(16),
LastName VARCHAR(16),
Initial VARCHAR(1),
DoB Date()
);

Primary Key

The option 'Primary Key' can be added to your definition to identify which field is the key.

CREATE TABLE Student
(
ID Int(6) Primary Key,
FirstName VARCHAR(16),
LastName VARCHAR(16),
Initial VARCHAR(1),
DoB Date()
);

The above example is for SQL and would be considered ideal. Others such as MySQL uses Primary Key differently:

CREATE TABLE Student
(
ID Int(6),
FirstName VARCHAR(16),
LastName VARCHAR(16),
Initial VARCHAR(1),
DoB Date(),
Primary Key(ID)
);

In your exam either will be accepted.

Composite Key

Remember a composite key is when several fields are used to make the primary key. This is easily created in this example:

CREATE TABLE Student
(
FirstName VARCHAR(16),
LastName VARCHAR(16),
Initial VARCHAR(1),
DoB Date(),
Primary Key(FirstName, LastName, DoB)
);

Foreign Key

Remember a relationship is essentially the use of a primary key from one table, as a normal field in another table. So to make your database relational you will need to define the foreign keys as well:

CREATE TABLE Student
(
ID Int(6),
FirstName VARCHAR(16),
LastName VARCHAR(16),
Initial VARCHAR(1),
DoB Date(),
GenderID Int(1)
Primary Key(ID)
Foreign Key (GenderID) References GenderTypes.ID
);