Difference between revisions of "Create DB & Table Queries"

From TRCCompSci - AQA Computer Science
Jump to: navigation, search
m (Changed lowered or titled sql commands into capital to avoid confusion between commands and variables names and text.)
Line 3: Line 3:
  
 
<syntaxhighlight lang=sql>
 
<syntaxhighlight lang=sql>
Create Database DBName ;
+
CREATE DATABASE DBName ;
 
</syntaxhighlight>
 
</syntaxhighlight>
  
Line 9: Line 9:
  
 
<syntaxhighlight lang=sql>
 
<syntaxhighlight lang=sql>
Create Database my_db ;
+
CREATE DATABASE my_db ;
 
</syntaxhighlight>
 
</syntaxhighlight>
  
Line 16: Line 16:
  
 
<syntaxhighlight lang=sql>
 
<syntaxhighlight lang=sql>
Create Table table_name
+
CREATE TABLE table_name
 
(
 
(
 
field_name1 data_type(size),
 
field_name1 data_type(size),
Line 40: Line 40:
  
 
<syntaxhighlight lang=sql>
 
<syntaxhighlight lang=sql>
Create Table Student
+
CREATE TABLE Student
 
(
 
(
 
ID Int(6),
 
ID Int(6),
Line 54: Line 54:
  
 
<syntaxhighlight lang=sql>
 
<syntaxhighlight lang=sql>
Create Table Student
+
CREATE TABLE Student
 
(
 
(
 
ID Int(6) Primary Key,
 
ID Int(6) Primary Key,
Line 67: Line 67:
  
 
<syntaxhighlight lang=sql>
 
<syntaxhighlight lang=sql>
Create Table Student
+
CREATE TABLE Student
 
(
 
(
 
ID Int(6),
 
ID Int(6),
Line 84: Line 84:
  
 
<syntaxhighlight lang=sql>
 
<syntaxhighlight lang=sql>
Create Table Student
+
CREATE TABLE Student
 
(
 
(
 
ID Int(6),
 
ID Int(6),

Revision as of 20:25, 19 March 2017

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.

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
);