LAB1

DDL                                         DML                             CONSTRAINTS                             ADVANCED TOPICS

----CREATE TABLE               ----DELETE                 ----PRIMARY KEY, FOREIGN KEY,

----DROP TABLE                                                              UNIQUE, CHECK, NOT NULL

----ALTER TABLE      DESC                                                 

Welcome students, this is a tutorial which will help you to get started on the basics of SQL. You can use the tutorial in the following ways:

1. As a comprehensive guide to the SQL syntax.

2. As a reference tool.

3. As a guide to the language

4. And, most importantly as a guide to solve your lab manual questions :). No, I am not giving the solutions to the lab manual. Just some hints as to how you can solve the problems.

This guide will be updated on a week by week basis. So keep logging in.

To start with SQL, follow the path: START ----------> Programs -------> Oracle OraHome 90 --------> Application Development -----> SQL Plus .

Chapter 1

Table Creation Basics

A database as all of you know is just a collection of relations or tables and a table is a collection of records. Oracle is a RDBMS which will help you in maintaining the collection of tables. SQL is the language which will help as the interface between oracle and yourself. So if you have to use the features provided by oracle you have to learn SQL or some variants of it. A database when started will have no tables in your user space. If you were not given a user name, use "scott" as user name and "tiger" as password and "orcl" as the host string.

Example

Once you are provided with the sql prompt (>) you can view the tables already present by typing

> select * from tab;

The list of tables are the ones already created by people before you. Now you want to get your hands wet and create some tables on your own. Don't worry we will get there soon.

The first command you will learn:

In order to create tables you use the "create table" command. So easy to remember no?. Anyway, the syntax is given below.

Syntax:

CREATE TABLE tablename(

columnname datatype(size),

columnname datatype(size),

.........

........

);

Example

Explaination:

The semi colon at the end of the statement runs the command. SQL is not case sensitive. So you can use capitals or small letters. It doesn't matter. If you make a mistake while typing, you have two options.

1. Retype the whole thing

2. Type >ed at the prompt which will open the buffer and you can modify your command there. Then save the changes and close the buffer and type >/ at the prompt. "/" is the command which will run the command in the buffer. At any given time the last executed command will be there in the buffer. So you can modify only the last executed command.

Note: You can clear the screen by typing >cl scr.

Now as soon as you have seen the syntax, some questions will come in your mind. I will try to answer some of them for you.

Q1) Is this the complete create table syntax? It seems too easy to be true.

A) No, this is not the complete syntax. This is just the basic skeleton syntax. This syntax will in fact create a table, but this table will have no constraints attached to it. Constraints are the cornerstone using which you can restrict the data entered into your database. For a complete discussion of constraints and how to use them in SQL see the later sections.

Q2) What is the maximum number of columns a table can have?

A) 1000 according to the latest Oracle specification.

Q3) What are the different datatypes available in Oracle SQL?

A)

Data Type Remarks

CHAR(size) Store cahracter values. Size shows number of characters to hold. If inserted value shorter than size, then it is padded with spaces.

VARCHAR2(size) store variable length alphanumeric data. inserted values not padded.

NUMBER(p,s) stores numbers. P is the precision and s is the scale which specifies the number of places to the right of the decimal.

DATE standard format is dd-mon-yy

LONG stores variable length character string upto 2GB.

RAW/LONG RAW stores binary data like pictures, sound etc. RAW data type max length is and 255 LONG RAW is 2GB.

Q4) What is the maximum/minimum of

1)Table Name: 32 characters

2)CHAR size: 255

3)VARCHAR2 size: 2000

4)NUMBER precision: 38 digits

Q5) How can see if the my table is created with the correct structure?

A) Type >Desc tablename

The SQLPlus command describe returns the definitions of tables and views. For example, information about your tables are stored in the table TABS. However, since you do know the columns names of that table, you can not query that table. The command

describe tabs;

will produces that information

 Name				 Null?	  Type
 ------------------------------- -------- ----
 TABLE_NAME			 NOT NULL VARCHAR2(30)
 TABLESPACE_NAME			  VARCHAR2(30)
 CLUSTER_NAME				  VARCHAR2(30)
 IOT_NAME				  VARCHAR2(30)
 PCT_FREE				  NUMBER
 PCT_USED				  NUMBER
 INI_TRANS				  NUMBER
 MAX_TRANS				  NUMBER
 INITIAL_EXTENT 			  NUMBER
 NEXT_EXTENT				  NUMBER
 MIN_EXTENTS				  NUMBER
 MAX_EXTENTS				  NUMBER
 PCT_INCREASE				  NUMBER
 FREELISTS				  NUMBER
 FREELIST_GROUPS			  NUMBER
 LOGGING				  VARCHAR2(3)
 BACKED_UP				  VARCHAR2(1)
 NUM_ROWS				  NUMBER
 BLOCKS 				  NUMBER
 EMPTY_BLOCKS				  NUMBER
 AVG_SPACE				  NUMBER
 CHAIN_CNT				  NUMBER
 AVG_ROW_LEN				  NUMBER
 AVG_SPACE_FREELIST_BLOCKS		  NUMBER
 NUM_FREELIST_BLOCKS			  NUMBER
 DEGREE 				  VARCHAR2(10)
 INSTANCES				  VARCHAR2(10)
 CACHE					  VARCHAR2(5)
 TABLE_LOCK				  VARCHAR2(8)
 SAMPLE_SIZE				  NUMBER
 LAST_ANALYZED				  DATE
 PARTITIONED				  VARCHAR2(3)
 IOT_TYPE				  VARCHAR2(12)
 TEMPORARY				  VARCHAR2(1)
 NESTED 				  VARCHAR2(3)
 BUFFER_POOL				  VARCHAR2(7)

The first column is the list of column names. The second column is a list of Not Null indicators. The third column is the list of data types. This output reveals that the column name "TABLE_NAME" is not allowed to be null. Now to get an alphabetical list of all the tables in your account, use the command

select table_name from tabs order by table_name;

You can use the describe command on the following views to obtain information on your account.

If you get any more questions, they will be answered and put on this page as soon as possible.

Now let us move forward from creation. With the knowledge that you have now you can create basic tables but without any constraints. That is now you know the job of Brahma(The creator). Let us now see shiva(the destroyer).

You have created some tables and now later on you find that you have no need any more for the table. Before going further let me remind you that there are two parts to any table, The structure of the table and the data in the table. Now if you want to completely get rid of the table, it's structure and data completely us the the following command:

>DROP TABLE tablename,where table name is the table you want to remove.

If you just want to remove all the data in the table but not the structure use

>DELETE FROM tablename

If you just want to remove some data in the table based on certain criteria use

>DELETE FROM tablename where search condition.

Now just as an exercise create a table that will just include two fields name and marks and then play with the commands you have just learnt. When you are confident of all the recently learnt commands move onto the next lesson.

Okay, so now have the powers of Brahma and Vishnu, now let us concentrate on arming you with the powers of Vishnu, that is to maintain table information and altering some information.

Use "Truncate" To Delete All Rows of a Table

The truncate command will permanently delete all rows from a table. You do not need to issue a "commit" command after the truncate command. This command is useful when you need a quick method of clearing out all the rows from a table without knowing the table definition. The following example deletes all rows from the table test_table

truncate table test_table;

Altering Table Structure:

The "Alter Table " command helps you in altering the structure of an exisiting table. You can add more columns, increase the size of columns, add and remove constraints and modify some contents.

Syntax for Adding new columns:

ALTER TABLE tablename ADD (newcolumnname datatype(size), newcolumnname datatype(size),......);

Syntax for Modifyung existing columns:

ALTER TABLE tablename MODIFY(columnname newdatatype(newsize));

Some Restrictions:

Cannot use alter table to: Change name of the table, change the name of a column, decrease size of a column if table data exists(except for varchar2, where you can reduce until the maximum existing data size), cannot change datatype if data exists.

QUESTIONS:

Q1) Okay, I cannot change a table name with alter table, so what other option do I have if I need to change table names?

A)use >RENAME oldtablename TO netablename;

Q2) Can I drop a column in a table?

A) Yes, you can. use >ALTER TABLE tablename DROP COLUMN columnname. or ALTER TABLE tablename DROP (columnname,columnname,...). But be very careful as a column dropped once cannot be recovered back and you will lose all the data.

Updating The Table Contents:

The Update command is used to update all the rows in the table or certain rows

Syntax for Updating all the rows:

UPDATE tablename SET columnname=expression, columnname=expression,...;

Syntax for Updating certain

UPDATE tablename SET columnname=expression, columnname=expression,...

WHERE columnname=expression;

Now you have all the power to create, alter and destroy as many tables as you want. So just as an excercise, try to use the commands you learnt by applying them on some test tables. Next we will move to the guide for your first excercise in the lab manual. But before that you need to know how to add constraints to the tables. Basically you will be working with two types of constraints, table constraints and column constraints. Table constraints apply to the whole table whereas column constraints are applicable only to that column. For an indepth study on constraints you can read some books like Korth, raghuramakrishnan etc. For our purposes we will be working with the following constraints:

PRIMARY KEY: Used to uniquely identify a row in the table

UNIQUE: Makes sure so that no two rows of the columns mentioned in UNIQUE have duplicate values

FOREIGN KEY: Provides a link between two tables and checks for referential integrity

NOT NULL: Makes sure values are entered in this field. This is only a column constraint

CHECK: General constraint checker.

Syntax:

Primary Key:

Table Constraint:

Create Table tablename(

colname datatype(size),

colname datatype(size),

.........

PRIMARY KEY(colname,colname,.....)

);

Column Constraint:

Create Table tablename(

colname datatype(size) PRIMARY KEY,

colname datatype(size),

.........

);

There can be only one primary key for any table. You can add a name to any constraint just add CONSTRAINT constraintname in front of any constraint

Foreign Key :

Table Constraint:

Create Table tablename(

colname datatype(size),

colname datatype(size),

.........

FOREIGN KEY(colname[,colname,.....]) REFERENCES tablename[(columnname,columnname,...)]

);

Column Constraint:

Create Table tablename(

colname datatype(size) REFERENCES tablename,

colname datatype(size),

.........

);

UNIQUE:

  alter table salary_table disable constraint chk_salary_salary;
  alter table salary_table enable constraint chk_salary_salary;