SQL Lesson 2

Lesson 2:

SQL Architecture

 

In SQL TABLE has more importance. TABLE is a database object which is used to store the data in rows and columns wise. A table should have at least one column and maximum of 30 columns.

Table name consist names, numbers and some symbols also. The maximum length of table name is 30 characters.

We can classify SQL into 5 groups based on their nature. Those are called SQL Statements. They are:

DDL, DML, DQL/DRL, DCL and TCL. So total SQL is working based on these 5 statements.

I)  DDL

DDL means Data Definition Language. It is used to define the Data base Objects. There are 5 commands in DDL. These statements define the structure of database.

The DDL commands are 1) Create 2) Alter 3) Rename 4) Drop and 5) Truncate.

Let me explain these commands thoroughly.

1)   Create

This CREATE command is used to create a new Table or View or any other database object. We already discussed about Table. We will discuss about View later. So to create a new Table we will use CREATE command.

Syntax: CREATE TABLE <Table_Name> (<column1> <data type (size)>,

  <column2><data type (size)>);

Here we are creating table. <Table_Name> represents name of table. <column1> represents name of first column and <data type (size)> represents data type and its size.

Ex: CREATE TABLE EMP (EMPNO NUMBER (4, 0),

                  ENAME VARCHAR2 (10),

                  JOB VARCHAR2 (9),

                  MGR NUMBER (4, 0),

                  HIREDATE DATE,

                  SAL NUMBER (7, 2),

                  COMM NUMBER (7, 2),

                  DEPTNO NUMBER (2, 0)

                 );

We can run this query in Toad or SQL Developer or SQL Plus. Toad means Tool for Oracle Application Development. We do all SQL operations in any one of above. The Sql Developer and SQL Plus icons are shown below.

Double click on any icon and give username/password @data base name then it will connect to database. Then we can do SQL Operations in that. Write the above Ex: table script in SQL Developer and say control + enter then table will create in our database.

 

After creating table we can run SELECT * FROM EMP;

Here it shows only structure without data because there is no data in that table. We can also see the structure with below command.

DESC EMP;

In table Description it shows all column names and data types and their sizes.

Note: While creating table we should keep in mind the following points.

ü  The name should begin with a letter A-Z

ü  Don’t use same name of existing object

ü  It can’t be more than 30 characters

ü  It can contain numbers and some symbols also

ü  It is not case sensitive.

ü  Don’t use oracle keywords like update, delete, as, rowed, rownum etc

Case sensitive means “Text sometimes exhibits case sensitivity; that is, words can differ in meaning based on differing use of uppercase and lowercase letters. Words with capital letters do not always have the same meaning when written with lowercase letters”.

Now we have EMP table. If we try to create another table with same name the following error will occur.

ORA-00955: name is already used by an existing object

Create a table from another table: or Copy a table from another table:

By using below syntax we can create table based on existing table.

Create table emp1 as select * from EMP;

Here EMP is already existing table. Here ‘*’ represents all columns. If we want some columns only, then we use like below.

Create table emp2 as select Empno, ename, Sal from EMP;

In the above two cases if we data in EMP table those data also copied to new table. If we need to create table with out data based on existing table, then use below syntax.

Create table emp3 as select * from EMP where 1=3;

In above example, “where 1=3” is a false condition. So it will copy only Structure. We will discuss about WHERE condition in coming classes.

Create a table from another schema table: or Copy a table from another schema table:

By using below syntax we can create table based on existing table from another schema.

Create table emp1 as select * from scott.emp;

Here we are creating table in Apps schema. Scott is another schema.

Schema is a part of database.

Lesson 1 
Lesson 3
Lesson 4
Lesson 5
Lesson 6
Lesson 7