Database objects are essentially the core of the SQL database, as you will see. They are used to store information in the database, so without objects, all you have is a fancy box with nothing to hold the information you want to put in it. This tutorial focuses mainly on the table, because it is the most frequently used. Other database objects include indexes, clusters, views, sequences, and synonyms. Besides being the most common, tables are the easiest to understand.
Schema
A schema is a collection of database objects (such as tables) that belong to a specific user on a database. While schema rules may vary slightly from one SQL server implementation to another, they are generally the same as presented below. The schema system allows a table (or other object, from here on I will just say table) to be attributed to a specific user and allows various users to have tables of the same name.
When you create a table, you create an object named filename_Tbl from your perspective, however, the name of the table is actually:
username.filename_Tbl (where username is the creator's username)
Since a file name really begins with the creator's name this places it in the user's schema. Tables may be accessed by the creator by either username.filename_Tbl of filename_Tbl. However, another user must specify the full name (username.filename_Tbl) in order to access the table. There are other methods of directing other users to your table, such as synonyms, which will be covered in another tutorial.
Below is an example of schema and database objects:
Database: Section_Eight_INC
User: bob (schema owners) tech
Tables: test_tbl test_tbl
employee_tbl inventory_tbl
tax_tbl atfinfo_tble
User can access the tables in their own schema by either using just the table name or by entering the whole name. However, if say, Bob wanted to access Tech's table that is called 'atfinfo' then, since he is not the schema owner of the table, he must refer to the table as tech.atfinfo. Both Bob and tech have a table called 'test',however both of these tables are unique since they belong to different schema. The 'test' table of tech is tech.test_tbl while Bob's 'test' table is really bob.test_tbl.
Object Management
The following section applies to tables, which are the most important of data objects. In general much of the following information is similar for other data object types. Tables in SQL as in other languages and in mathematics are made up of rows and columns. In SQL you can create both permanent and temporary tables.
Columns
The columns in an SQL table are more commonly known as fields. Each field is assigned a specific data type. Fields are given either NULL or NOT NULL values. If the value is NULL, then data does not have to be entered. NOT NULL means that data must be entered in this column. In order to have a table, you must have at least one column (this should be obvious). In a table, the columns are used to store data values. Examples of data stored in a column may be social security number, salary, hours worked, etc.
Rules for names in SQL can vary from flavor to flavor of SQL, so it is important to check before naming parts of your database or table. Generally, names of columns (and other parts of a database) must be a continuous string, i.e. no spaces. It is common as in other languages to use _ to represent a space.
It should be common sense, but it is usually a good idea to name tables and columns in some logical manner. Think of the poor guy who is going to replace you and work on your database when your're fired, he will appreciate your nice, easy to follow names.
Rows
Each table needs to have at least one row (again, this should be obvious) and rows are really just a series of columns containing specific information that is related. Examples of the use for rows include information on a certain product in inventory or an individual employee's personal information.
Table Creation
Table creation is deceptively simple. All you need to use is just the CREATE TABLE command. While seemingly simple on the surface, the act of table creation should come after much thought. You need to know what types of data it will hold, names for various columns, what will the max length be for each column, etc. The list of things that need to be considered are numerous and will vary with the purpose of the table you are creating. Tables can be modified later, it is just usually easier to get things well planned out and do the creation only once.
The syntax table creation is: (remember columns are called fields)
CREATE TABLE TABLE_NAME
( FIELD1 DATA TYPE [ NOT NULL ],
FIELD2 DATA TYPE [ NOT NULL ],
FIELD3 DATA TYPE [ NOT NULL ],
FIELD4 DATA TYPE [ NOT NULL ],
FIELD5 DATA TYPE [ NOT NULL ] );
The NOT NULL and NULL represent whether or not information must be entered in the column. (NULL is the default status of a field, so you don't really have to include NULL when creating a table) DATA TYPE refers to he data type that the column is meant to accept. Pleas see the tutorial on data types if you do not understand what this means. The semi-colon is the most common character that symbols the termination of a command. Various implementations may use other characters.
Below is an example for the creation of a table called INVENTORY_TBL:
CREATE TABLE INVENTORY_TBL
(INV_ID CHAR(6) NOT NULL,
NAME VARCHAR2(40) NOT NULL,
PRICE NUMBER(9) NOT NULL,
IN_STOCK NUMBER(5) NOT NULL,
MIN_AGE NUMBER(2) NULL,);
As you can see this table will have 5 columns and would look something like this if you drew it out:
INV_ID
NAME
PRICE
IN_STOCK
MIN_AGE
Most versions of SQL allow you to use something called the STORAGE clause. This optional specification can be added after the closing ')' after the last field and allows you to control the amount of space taken up by your table.
Here is an example using the above table:
IN_STOCK NUMBER(5) NOT NULL,
MIN_AGE NUMBER(2) NULL,)
STORAGE
(INITIAL 3K
NEXT 2K );
The INITIAL part of STORAGE sets the amount of space in bytes, kilobytes, etc for the initial allocation of storage for your table. NEXT specifies how much space to allocate on top of the current amount should the INITIAL space be filled up. Remember to check your version of SQL in order to make sure how STORAGE works because it tends to be different in various versions of SQL. If you do not specify STORAGE, the database will specify default values for you.
You can modify a table after it is created by using the ALTER TABLE command. Some of the things that can be done with this command include: adding and removing columns, redefining columns, adding or removing constraints on your table.
Below is the syntax for the ALTER TABLE command:
ALTER TABLE TABLE NAME [MODIFY] [COLUMN COLUMN_NAME][DATATYPE|NULL NOT NULL][RESTRICT|CASCADE]
[DROP] [CONSTRAINT CONSTRAINT_NAME]
[ADD] [COLUMN] COLUMN DEFINITION
An example of using this command is show below with explanation:
ALTER TABLE CATALOGUE_TBL MODIFY (PROD_ID NUMBER(8));
In this example, there was a column in a table called CATALOGUE_TBL known as PROD_ID. This column is of type NUMBER and when created had, say, 6 maximum characters. Now, because of catalogue expansion, you need to have product numbers of up to 8 digits.
Other modifications take the same form by simply substituting the appropriate information into the above syntax. At first, the alter table syntax may look like a beast, however when you realize that you may only need one of the optional parameters in brackets, it gets much simpler.
Addition of Columns with type NOT NULL is something you have to be careful with. If you have a table that contains data, then you cannot add a NOT NULL column by simply using the Alter Table command. (Because, obviously it would create a blank column defined as NOT NULL which is impossible) This can be worked around by first creating a column defined as NULL. Fill the column with data by inserting it into the table. Use the ALTER TABLE command to change the column specification from NULL to NOT NULL.
Another word of caution about column modification: when decreasing the length of a column, make sure that no data in the column is longer then the intended length. Otherwise your computer will probably gripe at you. Also, check and see what your implementation of SQL allows you to do with the ALTER TABLE command. Some implementations are more restrictive than others. For instance, your implementation may not allow you to drop a column. If you wanted to delete a column from your table you would have to delete the entire table and rebuild it from the ground up. So, be careful.
Copying Tables
By simply combining CREATE TABLE and the SELECT commands you can copy information from one table into a new one. This new table created from the old one will have the same column settings as the original. However, you may select the number of columns that you copy over to the new table. Below you will find the basic syntax:
CREATE TABLE NEW_TABLE_NAME AS
SELECT [ *|COLUMN1, COLUMN2 ] --> see note below
FROM TABLE OLD_TABLE_NAME
[ WHERE ] --> see note below
Notes:
SELECT [ *|COLUMN1, COLUMN2 ]: the * represents selecting the whole table, if you just want to select one or part of the table, you must name the columns by name (this is what is displayed on the right of the pipe symbol).
[WHERE] used if your table that you are copying from is not located locally to the new table (for instance if it is someone else's table)
Deleting aka Dropping Tables
Now that you have created and modified some tables, it’s time to get rid of them. In SQL, dropping a table (as deletion is known as) is rather easy to do. The general syntax of the Drop command is shown below:
DROP TABLE TABLE_NAME [ RESTRICT|CASCADE ]
The restrict option essentially ends the delete if the table is referenced by another table. This problem is bypassed (if you really want to delete it) by the CASCADE option. Be very careful to specify the correct schema name when calling the table by its name. Otherwise, you may delete the wrong table which could be very bad.
In order to select one or more elements of an SQL table, the SELECT statement is used. The output is stored in another table known as a result set. The basic syntax of the SELECT command is rather simple and is show below:
SELECT column_name FROM table_name;
You can select one or more columns from the table you are selecting from. In order to select all columns, you can substitute * for the column names, as the same thing will be accomplished.
Here is an example table named Catalogue:
Catalogue_tbl
Prod_id descrip price number
0001 1ozEagle 400.00 25
0002 HalfEagle 225.00 75
0003 SilverEagle 10.00 100
Now the command to select the Description and price columns.
SELECT descrip, price FROM Catalogue_tbl;
This stores the following in a result table:
descrip price
1ozEagle 400.00
HalfEagle 225.00
SilverEagle 10.00
The Result Set can then be manipulated via the SQL software and differs with each individual implementation. There are many programming commands that can be used with the result set, and it is beyond the scope of this tutorial to go into all of these. The most obvious use of a result set would be to store the data into another file. To do so you would simply use a command similar to the following (it may vary form implementation to implementation).
SELECT * FROM table_name INTO OUTFILE 'file_name';
Additional Useful options of the SELECT command
If you wish to select only different values in the table, SQL allows you to use the DISTICT attribute in the SELECT command. The syntax is the same as a regular SELECT statement, only that DISTICNT is added before the column names. This will allow you to not select any repeated data in the table.
SELECT DISTINCT column_name FROM table_name;
The number of rows returned can also be limited by adding the LIMIT operator to the SELECT statement.
SELECT * FROM table_name LIMIT 50;
The above code limits selection of columns to only the first 50 rows.
SELECT with conditional expressions
As seen above, you can select only the unique elements of a table with SQL. Well, what if you wanted to select only the items in your table that had a price of at least $50? Never fear, SQL has a nice solution for you.
By simply adding a WHERE statement, you can change the conditions that get data selected from the table.
SELECT column_name FROM table_name
WHERE column_name operator value;
The operators allowed by the SQL WHERE statement are the normal Boolean operators such as <,>,=, and <> or !=. While <> and != both represent the not equal to operator, different implementations will favor different operators.
You also have the BETWEEN operator, which is self-explanatory and allows you to specify a range.
The LIKE operator allows you to match data base on it being “like” the value you specify.
SELECT column_name FROM table_name WHERE column LIKE pattern;
Examples of patterns include ‘%auser', 15, or ‘%eu%'. The % is a wild card so will allow you to select anything that ends for example with –auser. Remember that in SQL character data is inclosed in quotes (usually single, although your implementation may allow double quotes) and numbers should be left alone, with no quotes.
An example of using WHERE with the select option using the above table:
SELECT * FROM Catalogue_tbl WHERE price > 100;
This would return:
Prod_id descrip price number
0001 1ozEagle 400.00 25
0002 HalfEagle 225.00 75
If you stated
SELECT * FROM Catalogue_tbl WHERE descrip LIKE ‘Ea%';
You would get:
Prod_id descrip price number
0001 1ozEagle 400.00 25
0002 HalfEagle 225.00 75
0003 SilverEagle 10.00 100
INSERT, UPDATE, and DELETE
INSERT INTO
In order to add new rows to a pre-existing table, you use the INSERT INTO command. The Syntax is as follows:
INSERT INTO table_name VALUES (data1, data2,...dataN);
If you want to specify specific columns into which data should be stored, the following code can be used:
INSERT INTO table_name (column1, column2,...) VALUES (data1, data2,...dataN);
Note: the columns listed and the data you want entered in that column should match up in the order they are listed.
Taking the following Original Table:
Catalogue_tbl
Prod_id descrip price number
0001 1ozEagle 400.00 25
0002 HalfEagle 225.00 75
0003 SilverEagle 10.00 100
You can add another row by doing the following:
INSERT INTO Catalogue_tble VALUES (0004, 1OzMaple, 415.00, 33);
Now the Table looks like this:
Prod_id descrip price number
0001 1ozEagle 400.00 25
0002 HalfEagle 225.00 75
0003 SilverEagle 10.00 100
0004 1OzMaple 415.00 33
The UPDATE command is used to update pre-existing data in a table
The UPDATE statement is used to modify the data in a table. The syntax is deceptively simple for this powerful command:
UPDATE table_name SET column_name = new_value WHERE column_name OPERATOR value;
This command will let you specify changing the data in an entire column (if you stop at WHERE) or you can change the value in a given column only on the same row “where” another column meets certain requirements as stated by the WHERE part of the expression. The WHERE part of UPDATE operates under the same principles as in the SELECT command.
Example:
UPDATE Catalogue_tbl SET price = 450 WHERE descrip = ‘1ozEagle';
This will change this line:
0001 1ozEagle 400.00 25
Into:
0001 1ozEagle 450.00 25
Deleting Rows:
DELETE FROM is used to delete specified rows from a table. The syntax is as follows:
DELETE FROM table_name WHERE column_name = value;
As in UPDATE, the Where part of the expression allows you to selectively delete only select rows in a given column, rather than an entire column. Be careful that you don't try to delete NOT NULL column data. If you would want to delete all the rows from a table but not delete the table itself, you can choose one of the following statements:
DELETE FROM table_name
DELETE * FROM table_name
Here is an example of DELETE in action:
DELETE FROM Catalogue_tbl WHERE descrip= SilverEagle;
This deletes the line containing data about the Silver eagles from the table used in the last example leaving:
Prod_id descrip price number
0001 1ozEagle 450.00 25
0002 HalfEagle 225.00 75
0004 1OzMaple 415.00 33
Written by Darkwater685 (16 March 2005)
This article was originally published by CyberArmy.net in the CyberArmy Library.
|