Solved

Creating a table for a search form

Posted on 1998-07-20
2
192 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now