Difference between revisions of "Create DB & Table Queries"
| (6 intermediate revisions by 2 users not shown) | |||
| Line 3: | Line 3: | ||
<syntaxhighlight lang=sql> | <syntaxhighlight lang=sql> | ||
| − | + | CREATE DATABASE DBName ; | |
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 9: | Line 9: | ||
<syntaxhighlight lang=sql> | <syntaxhighlight lang=sql> | ||
| − | + | CREATE DATABASE my_db ; | |
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 16: | Line 16: | ||
<syntaxhighlight lang=sql> | <syntaxhighlight lang=sql> | ||
| − | + | CREATE TABLE table_name | |
( | ( | ||
field_name1 data_type(size), | field_name1 data_type(size), | ||
| Line 37: | Line 37: | ||
Date, Time, DateTime, TimeStamp | Date, Time, DateTime, TimeStamp | ||
| − | ==Example== | + | ===Example=== |
<syntaxhighlight lang=sql> | <syntaxhighlight lang=sql> | ||
| − | + | CREATE TABLE Student | |
( | ( | ||
ID Int(6), | ID Int(6), | ||
| Line 47: | Line 47: | ||
Initial VARCHAR(1), | Initial VARCHAR(1), | ||
DoB Date() | DoB Date() | ||
| + | ); | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | ==Primary Key== | ||
| + | The option 'Primary Key' can be added to your definition to identify which field is the key. | ||
| + | |||
| + | <syntaxhighlight lang=sql> | ||
| + | CREATE TABLE Student | ||
| + | ( | ||
| + | ID Int(6) Primary Key, | ||
| + | FirstName VARCHAR(16), | ||
| + | LastName VARCHAR(16), | ||
| + | Initial VARCHAR(1), | ||
| + | DoB Date() | ||
| + | ); | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | The above example is for SQL and would be considered ideal. Others such as MySQL uses Primary Key differently: | ||
| + | |||
| + | <syntaxhighlight lang=sql> | ||
| + | CREATE TABLE Student | ||
| + | ( | ||
| + | ID Int(6), | ||
| + | FirstName VARCHAR(16), | ||
| + | LastName VARCHAR(16), | ||
| + | Initial VARCHAR(1), | ||
| + | DoB Date(), | ||
| + | Primary Key(ID) | ||
| + | ); | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | 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: | ||
| + | |||
| + | <syntaxhighlight lang=sql> | ||
| + | CREATE TABLE Student | ||
| + | ( | ||
| + | FirstName VARCHAR(16), | ||
| + | LastName VARCHAR(16), | ||
| + | Initial VARCHAR(1), | ||
| + | DoB Date(), | ||
| + | Primary Key(FirstName, LastName, DoB) | ||
| + | ); | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | ==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: | ||
| + | |||
| + | <syntaxhighlight lang=sql> | ||
| + | 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 | ||
); | ); | ||
</syntaxhighlight> | </syntaxhighlight> | ||
Latest revision as of 08:07, 8 October 2020
Contents
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
);
