Anyone help me with basic sql script (create database with two tables?)

ironman

ironman

ironman

Soldato
Joined
18 Oct 2002
Posts
8,416
Location
South Central L.A.
Really simple request for you guys, basically I just need a basic script displaying how I would create a database using sql. Is the langauge type between sql and mysql any different?

Lets say

Database is called SalesLedger

Table 1 is called Sales
PK is SalesID
Date
SalesPerson
ClientID (FK)

Table 2 is called Client
PK is ClientID
Address
Town
PostCode

What would the script be, to create this database, implement the fields, define the primary keys and link the two tables together?

Just need a rough model answer to work from as a base!

Thanks
 
If you create it using phpMyAdmin then create it the way you want it using the GUI then just click create it shows you the query it executes, makes things a hell of a lot easier, especially when making installers etc.
 
Jaffa_Cake said:
If you create it using phpMyAdmin then create it the way you want it using the GUI then just click create it shows you the query it executes, makes things a hell of a lot easier, especially when making installers etc.
Click to expand...

Ah, I just need the code though, it's for revision purposes and I'm expected to write down the script to create a database.
 
gurdas said:
Ah, I just need the code though, it's for revision purposes and I'm expected to write down the script to create a database.
Click to expand...

Well, go do what i said and you will see the code, just experiment. SQL is nice and logical and easy to learn :)
 
ok I will have a bash at it,

I just quickly did this, am I far off the mark?

Code: 
create table TblSales
(
	SalesID int not null auto_increment,
	Date date,
	SalesPerson varchar(20),
	primary key (SalesID),
	foreign key (TblClient ClientID)
		references TblClient(ClientID)
)

create table TblClient
(
	ClientID int not null auto_increment,
	Address varchar(40),
	Town varchar(20),
	PostCode varchar(10),
	primary key (ClientID)
)
 
You need some error checking:

Code: 
DROP TABLE IF EXISTS 'tblSales';
CREATE TABLE 'tblSales' (
...
 
Cheers, will that code that I have used work then? Have I correctly identified the primary and foreign keys?
 
Heh, can't seem to get it running on my mac....

Can anyone that knows basic sql check if that script is valid? Will it produce the tables/fields I require with the relevant table links? All I need is a written valid script that I can base my answer off.
 
Jaffa_Cake said:
If its from phpMyAdmin its right :-)
Click to expand...

Do people actually use that?

It's got a really nasty GUI. It's much quicker to use the MySQL console,... well if you know SQL that is.
 
gurdas said:
Heh, can't seem to get it running on my mac....

Can anyone that knows basic sql check if that script is valid? Will it produce the tables/fields I require with the relevant table links? All I need is a written valid script that I can base my answer off.
Click to expand...

The foreign key looks a little weird. Try something more like:

Code: 
FOREIGN KEY (fk_ClientID) REFERENCES tblClient(ClientID)
 
Code: 
create table TblSales
(
	SalesID int not null auto_increment,
	Date date,
	SalesPerson varchar(20),
	primary key (SalesID),
	foreign key (fk_ClientID)
		references TblClient(ClientID)
)

create table TblClient
(
	ClientID int not null auto_increment,
	Address varchar(40),
	Town varchar(20),
	PostCode varchar(10),
	primary key (ClientID)
)

Better?

Can anyone with Sql setup quickly run that to see if it works?
 
Code: 
Error

SQL query:

CREATE TABLE TblSales(
SalesID int NOT NULL AUTO_INCREMENT ,
Date date,
SalesPerson varchar( 20 ) ,
PRIMARY KEY ( SalesID ) ,
FOREIGN KEY ( fk_ClientID ) REFERENCES TblClient( ClientID )
) CREATE TABLE TblClient(
ClientID int NOT NULL AUTO_INCREMENT ,
Address varchar( 40 ) ,
Town varchar( 20 ) ,
PostCode varchar( 10 ) ,
PRIMARY KEY ( ClientID )
)

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create table TblClient
(
    ClientID int not null auto_increment,
    Address varc' at line 11

You can fix it to help you learn, You are not using MYSQL type query here. Tell me if you get really stuck :P
 
Last edited:
gurdas said:
Code: 
create table TblSales
(
	SalesID int not null auto_increment,
	Date date,
	SalesPerson varchar(20),
	primary key (SalesID),
	foreign key (fk_ClientID)
		references TblClient(ClientID)
)

create table TblClient
(
	ClientID int not null auto_increment,
	Address varchar(40),
	Town varchar(20),
	PostCode varchar(10),
	primary key (ClientID)
)

Better?

Can anyone with Sql setup quickly run that to see if it works?
Click to expand...

I would run it on our test server, but my VPN tunnel to the office won't connect for some reason. If I get it working, then I'll test it.
 
Jaffa_Cake said:
You can fix it to help you learn, You are not using MYSQL type query here. Tell me if you get really stuck :P
Click to expand...

Do I have to run the code the other way around? So I create TblClient first, because when it tries to create a foreign key to TblClient, it can't because it hasn't been created yet?
 
gurdas said:
Do I have to run the code the other way around? So I create TblClient first, because when it tries to create a foreign key to TblClient, it can't because it hasn't been created yet?
Click to expand...

yes.
 
gurdas said:
Is that the only error?:D

If someone can give me a confirmation that it implements i'll be a happy monkey:D
Click to expand...

You need ; after each create table statement. ie.

Code: 
create table TblClient
(
	ClientID int not null auto_increment,
	Address varchar(40),
	Town varchar(20),
	PostCode varchar(10),
	primary key (ClientID)
);

create table TblSales
(
	SalesID int not null auto_increment,
	Date date,
	SalesPerson varchar(20),
	primary key (SalesID),
	foreign key (fk_ClientID)
		references TblClient(ClientID)
);

Then it works.
 
You must log in or register to reply here.
Back
Top Bottom