Solved

Creating a table for a search form

Posted on 1998-07-20
2
231 Views
Last Modified: 2010-03-19
I am just starting with Oracle and SQL, so I was hoping to get some help on the best way to start making a simple table that will be queried by a VB search script. I have all the necessary software ie Oracle, ASP, but I really just want to know the best way to go about doing this - ie how to make the table. I hope this is not to ambiguous, but I would like some help.
0
Comment
Question by:mikebee
2 Comments
 
LVL 5

Accepted Solution

by:
mayhew earned 100 total points
ID: 1091793
I'm not sure which part you are specifically needing help with so I'll try to give an overview of the whole thing.  Forgive me if I say something that you already know.

The first step is to decide what data you are going to track in your table.  As an example, let's assume you will be keeping a mailing list with your table and you want to track the following information:
name varchar(25),
address varchar(25),
city varchar(15),
state char(2),
zip char(5)

A char field is a character field.  So "zip char(10)" just means a field name "zip" that will have 10 characters in it.

A varchar is like a char, but it helps to save memory.  With a char(10) the computer allocates 10 bytes of memory every time, even if your zip code is only 6 characters.  A varchar(10) means a field can hold up to 10 characters, but if your zip is only 6 characters long, that's all the space the computer will allocate.

Don't worry about this too much.  The general rule of thumb is to use char for fields that will usually be the same length and varchar if you expect a wide variance in length (as with peoples' names eg. "Ig Yu" versus "Jonathon Mendoza-Krantz").

The next thing is to actually create the table.  I expect the easiest way to do this will be from the interactive SQL program.  It gives you a line prompt where you can just enter your SQL commands.

So for the above example your SQL statement would be:

CREATE TABLE addresses(
name varchar(25),
address varchar(25),
city varchar(15),
state char(2),
zip char(5) );

"addresses" is the name of the table.  You can call it anything you want.  Execute that command and unless you have an error (check your typing) you will have a table.

To put information into the table you would use:
INSERT INTO addresses
VALUES ('John Doe','123 Maple','Anywhere','NY','12345');

BTW, don't worry about capitalization or line breaks.  The ; is what tells Oracle to run your statement.

To get info out of your table, use:
SELECT * FROM addresses;

The star * means to select all info from the table.  Or,
SELECT name, zip FROM addresses;

would give you just the names and zip codes of all the people in your table.  Or,
SELECT name FROM addresses
WHERE zip = '12345';

would give you all the names of people that live in the '12345' zip code.

You can also use ASP to execute your SQL statements.  So you could, for example, create your table through iSQL in Oracle as above, and you could populate the table with an ASP web page form that takes the information, and you could display the info on a different web page in response to a search.

I hope this is helpful.  Feel free to e-mail me for more SQL info.
don@mayhewnet.com
0
 

Author Comment

by:mikebee
ID: 1091794
Thanks!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question