Guys i really need help University student doing SQL

[RichardK]

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.

Thanks!!!

[RichardK]

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

TYPE BELOW TO CLEAR EXISTING TABLES

drop table CUSTOMER_0110020

[pithhelmet]

Here is the script i would use for the primary customer table -

CREATE TABLE [dbo].[CUSTOMER_0110020] (
[Customer_number] [int] IDENTITY (1, 1) NOT NULL ,
[Customer_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Customer_address_1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Customer_address_2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Customer_address_3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Customer_postcode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Customer_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


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 -

insert into customer_0110020
(customer_name, customer_address_1, customer_address_2, customer_address_3, customer_postcode, customer_phone) values
('tony', '123 west main', 'lake mary', 'florida', '32746', '123-123-1234'); select @@identity;

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!!

[RichardK]

Guys here is the Database-Task that i have to pass please can someone help me do this!!!!

[pithhelmet]

Dude -

your tanked -

that assignment was to be delivered today


good luck on this - from the read - it looks like a 5 day minimum to get it working - then the queries that have to be demonstrated as well -

good luck

[RichardK]

i have been given extra time to do it. So PLEASE HELP!!!

[pithhelmet]

So what have you completed on the project so far?

[RichardK]

umm this is what i have i basically need to start from scratch or fix what i have....


the essence of databases by f d rolland

-------------------------------------------------------

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 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','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

------------------------------------------------------

[pithhelmet]

Man - didn't you read the last few posts -

i gave you 90% of the system already

This is the same dribble that you posted before.

Did you even follow the code that i posted?

[RichardK]

the code looks different to what i use and i use SQL Server 7 is that ok?

[pithhelmet]

Yea dude -

that script that i posted was from my MS SQL 2k system -

Do you follow the releationships that are outlined -
although there aren't any foreing keys setup, i used the same naming for the matching fields....

[RichardK]

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!!