Results 1 to 12 of 12

Thread: Guys i really need help University student doing SQL

  1. #1
    Join Date
    Nov 2002
    Posts
    19

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

  2. #2
    Join Date
    Nov 2002
    Posts
    19
    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
    Last edited by RichardK; 11-29-2002 at 05:17 AM.

  3. #3
    Join Date
    Nov 2002
    Posts
    6

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



    Last edited by pithhelmet; 11-30-2002 at 08:37 PM.

  4. #4
    Join Date
    Nov 2002
    Posts
    19
    Guys here is the Database-Task that i have to pass please can someone help me do this!!!!
    Attached Images Attached Images

  5. #5
    Join Date
    Nov 2002
    Posts
    6

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

  6. #6
    Join Date
    Nov 2002
    Posts
    19
    i have been given extra time to do it. So PLEASE HELP!!!

  7. #7
    Join Date
    Nov 2002
    Posts
    6

    hmmm

    So what have you completed on the project so far?

  8. #8
    Join Date
    Nov 2002
    Posts
    19
    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

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

  9. #9
    Join Date
    Nov 2002
    Posts
    6

    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?

  10. #10
    Join Date
    Nov 2002
    Posts
    19
    the code looks different to what i use and i use SQL Server 7 is that ok?

  11. #11
    Join Date
    Nov 2002
    Posts
    6

    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. #12
    Join Date
    Nov 2002
    Posts
    19
    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!!
    Attached Images Attached Images

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •