SQL Homework help

Discussion in 'Mac Programming' started by jskuh, Apr 29, 2014.

  1. jskuh macrumors newbie

    Joined:
    Apr 29, 2014
    #1
    :confused: I've been staring at this forever and I can't even find a starting point. I don't know if anyone on here could help with this, but I would greatly appreciate it...

    I guess I don't know SQL as well as I thought I did. Everything I've tried has failed. I typed in some of my code already but I left it empty because what I'm doing isn't working. If you could help me or offer me a section of code I would appreciate it. I think I'm over thinking the project... I heard it's not too hard but I'm struggling a lot.

    This project will involve the use of “cust” table. You are to create a package that has a procedure to add a new customer, a procedure to update the customer information (city, state), a procedure to remove a customer and a procedure to report customer information by state.


    http://i57.tinypic.com/3304upt.jpg

    ----------

    I'm not asking for solutions, although if you do want to do them I will not defer you from that. I need help, suggestions, a starting point. Please and thank you !!!!!!
     
  2. wannabepcuser macrumors member

    Joined:
    May 25, 2013
    #2
    When I create SQL for work I always do one at a time. Google is your friend to figure out the syntax. A good SQL book is also mandatory.
     
  3. ArtOfWarfare macrumors G3

    ArtOfWarfare

    Joined:
    Nov 26, 2007
    #3
    First off, don't use screenshots when what you're sharing is text. That's annoying.

    Second, what have you tried? What is causing you to get stuck? I know almost no SQL whereas your post implies you know a good deal (or at least that you thought you knew more than me), but I feel like I could write something for this assignment before looking for help on the internet.
     
  4. jskuh thread starter macrumors newbie

    Joined:
    Apr 29, 2014
    #4
    I've probably been trying to tackle too much at once. Thank you!
     
  5. Nishi100 macrumors 6502a

    Joined:
    Mar 27, 2010
    #5
    Have you created the table in EasyPHP (or alternative)?

    Which language do you have to create this in?
     
  6. jskuh thread starter macrumors newbie

    Joined:
    Apr 29, 2014
    #6
    Sorry if I have offended you. The screenshot was because I no longer have the document and didn't have the time to retype it up at that moment. I am a student, and I'm still learning SQL. We are at the end of our course and I was mostly hoping for some helpful tips to start. I was asking for help because when I failed, instead of doing the logical thing I deleted my work. My mistake.

    ----------

    In class we use sqldeveloper. We have to use PL/SQL format
     
  7. D.T. macrumors 604

    D.T.

    Joined:
    Sep 15, 2011
    Location:
    Vilano Beach, FL
    #7
    OK, let’s get a few things sorted out.

    You’re using Oracle (assuming something recent like 11gR2), but nothing you’re doing is very “version specific”.

    First, you’re gen’ing up a table. That code is directly supplied. Same for the population statements for that table - that’s a direct copy and paste.

    Are you getting your table and your data populated? I’m assuming the instructor has walked you through setting up SQLDeveloper? (That’s an Oracle freeware GUI for Oracle management and development, actually Java based).

    The task is to create a package, which is a bundle of Stored Procs with a header section that defines the interfaces for the prods. You’re going to be using PL/SQL which is Oracles DB programming language (Procedural Language SQL).

    I’d take it a step at a time.

    Create the table.

    Populate the table.

    Run some basic queries against the table like select <columns) from schema.tablename;

    Then develop your stored procs before bundling them into a package.

    For example (note this isn’t complete):

    Code:
    CREATE OR REPLACE PROCEDURE <your_schema>.ADD_CUSTOMER
    
    /*
    	Name:	AddCustomer
    	Function:	Adds customer record
    	Version:	1.0
    
    */
    (
        p_id NUMBER,
        p_firstname VARCHAR2
    
    )
    
    AS
    
    BEGIN
    
    
       INSERT INTO <your_schema>.cust
         ( cust_id,
           cust_firstname
         )
         VALUES
         ( p_id,
           p_firstname
        );
    
    END;

    That’s a super basic stored proc that you can use to test your simple SQL logic, parameters, etc.

    Then you’ll eventually migrate this into a package like so:


    Code:
    CREATE PACKAGE <your_schema>.customer
    
     IS
    
    PROCEDURE ADD_CUSTOMER
    (
        p_id NUMBER,
        p_firstname VARCHAR2
    
    );
    
    
    END;
    /

    And you’ll migrate your proc code to a package body:

    Code:
    CREATE  PACKAGE BODY  <your_schema>.ADD_CUSTOMER
    
    IS
      /*
    	Name:	AddCustomer
    	Function:	Adds customer record
    	Version:	1.0
    
    */
    (
        p_id NUMBER,
        p_firstname VARCHAR2
    
    )
    
    
      BEGIN
    
       INSERT INTO <your_schema>.cust
         ( cust_id,
           cust_firstname
         )
         VALUES
         ( p_id,
           p_firstname
        );
    
         END;
    Again, this is off the top of my head, so this may not work if just C&P’ed into SQLDeveloper.

    Just trying to give you a starting point. :)
     
  8. Senor Cuete macrumors regular

    Joined:
    Nov 9, 2011
    #8
    What is "gen'ing up"?
     
  9. D.T. macrumors 604

    D.T.

    Joined:
    Sep 15, 2011
    Location:
    Vilano Beach, FL
    #9
    Gen’ing, Generating ... Creating ... whatever :D Day to day I deal with a number of different languages, frameworks, DBs, infrastructures, so sometimes I use some generic language (gen up a table, a model, a package, etc.)

    :)

    I’m actually ramped up on a project using Oracle, .NET at the moment, but I’ve also been deep in Obj-C/Rails/Postgres this week too :D
     
  10. JustMartin macrumors 6502a

    Joined:
    Feb 28, 2012
    Location:
    UK
    #10
    D.T. pretty much has it nailed. The only thing that worries me slightly is the requirement to report customers by state. I'm guessing you're going to have use a DBMS_OUTPUT.put command (and remember to enable it before you run).
     

Share This Page