Guys i really need help University student doing SQL

Printable View

11-29-2002, 05:13 AM
RichardK

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.

Thanks!!!
11-29-2002, 05:14 AM
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
11-30-2002, 08:16 PM
pithhelmet

Lots o problems here m8!!

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!!
12-13-2002, 11:35 AM
RichardK

1 Attachment(s)

Guys here is the Database-Task that i have to pass please can someone help me do this!!!!
12-13-2002, 11:50 AM
pithhelmet

dude

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
12-13-2002, 12:51 PM
RichardK

i have been given extra time to do it. So PLEASE HELP!!!
12-13-2002, 01:06 PM
pithhelmet

hmmm

So what have you completed on the project so far?
12-13-2002, 01:07 PM
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

------------------------------------------------------
12-13-2002, 01:10 PM
pithhelmet

dude

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?
12-13-2002, 01:22 PM
RichardK

the code looks different to what i use and i use SQL Server 7 is that ok?
12-13-2002, 03:13 PM
pithhelmet

it is MS SQL 2k

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....
12-16-2002, 06:20 AM
RichardK

1 Attachment(s)

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