Guys i really need help University student doing SQL
Guys i really need help. I am a University student and doing a Multimedia course. For some reason i am doing a SQL course and the lecture is absoulutly CRAP! I need to know how to put data into SQL. Hers is the link http://www.lorraine.co.uk/record.html
I need to put the recorders into a table or in SQL table along with the address, what the customer or customers has orderd,phone number,stock available etc.
Here is i what i have.
----------------------------------------
CREATE TABLE CUSTOMER_0110020
(Customer_number INT PRIMARY KEY NOT NULL,
Customer_name CHAR (25) NOT NULL,
Customer_address_1 CHAR (25) NOT NULL,
Customer_address_2 CHAR (25),
Customer_address_3 CHAR (25),
Customer_postcode CHAR (7) NOT NULL,
Customer_phone CHAR (14))
---------------------------------------
CREATE TABLE CUSTOMER_0110020
(Stock_ID INT PRIMARY KEY NOT NULL,
Stock_name CHAR (25) NOT NULL,
Stock_price CHAR (25) NOT NULL,
Stock_description CHAR (25) NOT NULL,
Stock_dimension CHAR (25) NOT NULL,
Stock_quantity CHAR (25) NOT NULL,
Stock_manufacturer CHAR (25) NOT NULL,)
INSERT INTO CUSTOMER_0110020 VALUES (115469,'R.Kansley',
'£19.99','Romford','Essex','1','lorraine')
----------------------------------------
INSERT INTO CUSTOMER_0110020 VALUES (115469,'R.Kansley',
'£19.99','A high quality re-engineered instrument capable of recording for two hours on one cassette at normal speed','5cm X 10cm','1','lorraine UK')
----------------------------------------
INSERT INTO CUSTOMER_0110020 VALUES (115469,'R.Kansley',
'£19.99','Romford','Essex','1','lorraine')
----------------------------------------INSERT INTO CUSTOMER_0110020 VALUES (115469,'R.Kansley',
'11,Rise_Park_Boulevard','Romford','Essex','RM1_4P P',
'01708767754')
----------------------------------------
SELECT * FROM CUSTOMER_0110020
----------------------------------------
Notice the customer number is an autoincrement - set as the primary key
Now the purchase history table should be a detail table of the customer table -
Here is the script for this -
CREATE TABLE [dbo].[purchase_history] (
[purchase_id] [int] IDENTITY (1, 1) NOT NULL ,
[customer_number] [int] NOT NULL ,
[sale_date_time] [datetime] NOT NULL ,
[sale_amount] [money] NOT NULL ,
[cash_tendered] [money] NOT NULL
) ON [PRIMARY]
and for the final setup - the item detail -
CREATE TABLE [dbo].[purchase_items] (
[sale_id] [int] IDENTITY (1, 1) NOT NULL ,
[purchase_id] [int] NOT NULL ,
[item_upc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[price] [money] NULL
) ON [PRIMARY]
Now i didn't add in any foreign constraints and all that crap - because i feel the programmer should be able to handle that -
This is basically a master - detail - detail table setup -
The first thing you would do is to add in a customer -
NOTE - the @@identity at the end - that will return the customer number that is created - you will use this when you add in a sale.....
I'm sure there are folks out there that will say that you should be combine the purchase history and the items detail into the same database - but i like to bust them out myself -
its a start -
good luck!!
Last edited by pithhelmet; 11-30-2002 at 08:37 PM.
CREATE STOCK TABLE CUSTOMER_0110020
(Stock_ID INT PRIMARY KEY NOT NULL,
Stock_name CHAR (25) NOT NULL,
Stock_price CHAR (25) NOT NULL,
Stock_description CHAR (25) NOT NULL,
Stock_dimension CHAR (25) NOT NULL,
Stock_quantity CHAR (25) NOT NULL,
Stock_manufacturer CHAR (25) NOT NULL,)
INSERT INTO CUSTOMER_0110020 VALUES (115469,'R.Kansley',
'£19.99','A high quality re-engineered instrument capable of recording for two hours on one cassette at normal speed','5cm X 10cm','1','lorraine UK')
-------------------------------------------------------
INSERT INTO CUSTOMER_0110020 VALUES (115469,'R.Kansley',
'£19.99','Romford','Essex','1','lorraine')
Umm your code works but i cant get the customer's product info in there. You know what they ordered and how much it costs etc. I added in the 2nd and third line of code (purchase history and purchase items but it says it's already in the databases. Please can you help!!