WHAT IS A REALATIONAL DATABASE MANAGEMENT SYSTEM …



WHAT IS A REALATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS)

A Relational Database Management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by Dr. Edgar F. Codd. Strictly speaking it should also satisfy codd’s 12 rules, but in practice there is no DBMS that satisfies all these rules. In fact, most successful DBMS that are considered to be relational violate the relational model in several important ways, including the structured Query language (SQL). However, most database practitioners and researchers use the term in a loose way such that most databases that support SQL are included.

Relational Database Management system (RDBMS) stores data in the form of related tabled. RDBMS are powerful because they require few assumptions about how data is related or how it will be extracted from the database. As a result, the same database can be viewed in many different ways.

An important feature of relational systems is that a single database can be spread across several tables. This differs from flat file database, in which each database is self- contained in a single table.

The first released RDBMS that was a relatively faithful implementation of the relational model was the Multics Relational Data store first sold in 1978. Others have been Berkeley Ingres, QUEL and IBM BS12.

Today, popular commercial RDBMS for large databases include Oracle Microsoft SQL server, sybase SQL server, and IBM’s DB2. The most commonly used free RDBMS are MySQL, postgreSQL.

Dr. E. F. Codd’s Rules for RDBMS

Dr. E. F. Codd is an IBM researcher who first developed the relational data model in 1970. In 1985, Dr. Codd published a list of 12 rules that define ideal relational database and has provided a guideline for the design of all relational database systems.

Dr. Codd has used the term guideline because till date no commercial relational database system fully conforms to all 12 rules. For a few years, scorecards were kept that rated each commercial product’s conformity to Codd’s rules. Today, the rules are not talked about as much but remain a goal for relational database design.

• Rule 1: The Information Rule:

• All data should be presented in table form.

• Means CLIENT_MASTER table represent following:

TABLE: CLIENT_MASTER

COMMAND:

CREATE TABLE CLIENT_MASTER

(

CLIENTNO VARCHAR2(6) PRIMARY KEY,

NAME VARCHAR2(20) NOT NULL,

ADDRESS1 VARCHAR2(30),

ADDRESS2 VARCHAR2(30),

CITY VARCHAR2(15),

PINCODE NUMBER(8),

STATE VARCHAR2(15),

BALDUE NUMBER(10,2)

);

STRUCTURE:

Name Null? Type

---------------------------- -------- ----

CLIENTNO NOT NULL VARCHAR2(6)

NAME NOT NULL VARCHAR2(20)

ADDRESS1 VARCHAR2(30)

ADDRESS2 VARCHAR2(30)

CITY VARCHAR2(15)

PINCODE NUMBER(8)

STATE VARCHAR2(15)

BALDUE NUMBER(10,2)

• Rule 2: Guaranteed Access Rules:

• All data should be accessible without ambiguity.

• This can be accomplished through a combination of the table name, Primary Key, and column name.

• EXAMPLE:

|Emp_info | |Emp_Salary_info |

|EmpId |Name | |EmpId |Salary |

|91 |John | |91 |20000 |

|92 |Thomas | |92 |40000 |

OR

• Each and every data is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.

• Rule 3: Systematic Treatment of Null Values:

• A field should be allowed to remain empty.

• This involves the support of a null value, which is distinct from an empty string or number with a value of zero.

• Of course, this can’t apply to primary key. In addition, most database implementations support the concept of a not- null field constraint that prevents null values in a specific table column.

• EXAMPLE:

|Emp Id |Name |Phone No |Mobile No |

|09BCA01 |John |2326864 |9725142752 |

|09BCA02 |Thomas |2555631 | - |

• NULL Values are supported in the fully relational DBMS for representing missing information in a systematic way independent of data type.

• NULL values are distinct from empty character string or a string of blank character and distinct from 0 or any other number.

• Rule 4: Dynamic On-Line Catalog based on the Relational Model:

• A relational database must provide access to its structure through the same tools that are used to access the data.

• EXAMPLE:

This is usually accomplished by storing the structure definition within special system tables.

• Rule 5: Comprehensive Data Sublanguage Rule:

• The Relational database must support at least one clearly defined language that includes functionality for data definition, data manipulation, data integrity and database transaction control.

1. Data Definition

2. View Definition

3. Data Manipulation

4. Integrate Constraints

5. Authorization

6. Transaction Control

• All commercial relational database use forms of standard SQL (i.e. Structure Query Language) as their supported comprehensive language.

• Rule 6: View Updating Rule:

• Data can be presented in different logical combination called views.

• Each view should support the same full range of data manipulation that has direct access to a table available.

• In practice, providing update and delete access to logical view is difficult and not fully supported by any current database.

• EXAMPLE:

All views those are theoretically updatable by the system.

This rule is not really implemented yet any available.

• Rule 7: High –Level Insert, Update, and Delete:

• Data can be retrieved from a relation database in sets constructed of data from multiple rows and/or multiple tables.

• This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.

• EXAMPLE:

Suppose if we need to change ID then it will reflect everywhere automatic.

• Rule 8: Physical Data independence:

• The user is isolated from the physical method of storing and retrieving information from the database.

• Changes can be made to the underlying architecture (hardware, disk storage methods) without affecting how the user accesses it.

• EXAMPLE:

The user is isolated (Separated) from the physical method of storing and retrieving information from the database in which affecting directly in database.

• Rule 9: Logical Data Independence:

• How data is viewed should not be changed when the logical structure (table’s structure) of the database changes.

• This rule is particularly difficult to satisfy.

• Most databases rely on strong ties between the data viewed and the actual structure of the underlying tables.

• EXAMPLE:

In this rule we want to retrieve any ID, when we retrieve Data without ID that time we can not satisfy this rule.

• Rule 10: Integrity Independence:

• The database language (like SQL) should support constraints on user input that maintain database integrity.

• This rule is not fully implemented by most major vendors.

• At a minimum, all databases do preserve two constraints through SQL.

• No component of a primary key can have a null value.

• If a foreign key is defined in one table, any value in it must exist as a primary key in another table.

• All databases do preserved to constrain through SQL.

1. Primary Key can not have NULL.

2. Foreign Key is define in one table any value in it must exists as a primary key in another table.

3. Integrity constraints specific to a particular relation database must be definable in the relational data sub-language & storable in a catalog not in the application program.

• Rule 11: Distribution independence:

• A user should be totally unaware of whether or not the database is distributed (whether parts of database exist in multiple locations) make this rule difficult to implement.

• A variety of reasons make this rule difficult to implement.

• EXAMPLE:

This rule difficult to implement due to variety of reasons.

• Rule 12: Non subversion Rule:

• There should be no way to modify the database structure other than through the multiple row database language (like SQL).

• Most databases today support administrative tools that allow some direct manipulation of the data structure.

• EXAMPLE:

If a relational system supports a low level (Single record at a time) language that low level language can not be used to sub word or by pass the integrity rules or constraints expressed in the higher level (multiple record at a time) relational language.

What is table ?

( The data in RDBMS is stored in database objects called tables. The table is a collection

of related data entries and it consists of columns and rows.

( Remember, a table is the most common and simplest form of data storage in a relational

database. Following is the example of a CUSTOMERS table:

|+----+----------+-----+-----------+----------+ |

|| ID | NAME | AGE | ADDRESS | SALARY | |

|+----+----------+-----+-----------+----------+ |

|| 1 | Ramesh | 32 | Ahmedabad | 2000.00 | |

|| 2 | Khilan | 25 | Delhi | 1500.00 | |

|| 3 | kaushik | 23 | Kota | 2000.00 | |

|| 4 | Chaitali | 25 | Mumbai | 6500.00 | |

|| 5 | Hardik | 27 | Bhopal | 8500.00 | |

|| 6 | Komal | 22 | MP | 4500.00 | |

|| 7 | Muffy | 24 | Indore | 10000.00 | |

|+----+----------+-----+-----------+----------+ |

What is field?

( Every table is broken up into smaller entities called fields. The fields in the

CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY.

( A field is a column in a table that is designed to maintain specific information about every

record in the table.

What is record, or row?

( A record, also called a row of data, is each individual entry that exists in a table.

( For example there are 7 records in the above CUSTOMERS table. Following is a single

row of data or record in the CUSTOMERS table:

|+----+----------+-----+-----------+----------+ |

|| 1 | Ramesh | 32 | Ahmedabad | 2000.00 | |

|+----+----------+-----+-----------+----------+ |

A record is a horizontal entity in a table.What is column?

( A column is a vertical entity in a table that contains all information associated with a

specific field in a table.

( For example, a column in the CUSTOMERS table is ADDRESS which represents

location description and would consist of the following:

|+-----------+ |

|| ADDRESS | |

|+-----------+ |

|| Ahmedabad | |

|| Delhi | |

|| Kota | |

|| Mumbai | |

|| Bhopal | |

|| MP | |

|| Indore | |

|+----+------+ |

What is NULL value?

( A NULL value in a table is a value in a field that appears to be blank which means A field

with a NULL value is a field with no value.

( It is very important to understand that a NULL value is different than a zero value or a

field that contains spaces. A field with a NULL value is one that has been left blank

during record creation.

Introduction To Structure Query Language(SQL)

Structure Query Language (SQL) is language that provides an interface to relational database systems. SQL was developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an ISO and ANSI standard. SQL is often pronounced SEQUEL and SEQUEL means Structure English QUEry Language.

Features of SQL

1. SQL can be used by a range of users, including those with little or no programming experience.

2. It is a non procedural language.

3. It reduces the amount of time required for creating and maintaining systems.

4. It is an English-like language.

5.

Rules for SQL

1. SQL starts with a verb(i.e. a SQL action word). EXAMPLE: SELECT statements. This verb may have additional adjectives. EXAMPLE: FROM

2. Each verb is followed by number of clauses. EXAMPLE: FROM, WHERE, HAVING

3. A space separates clause. EXAMPLE: DROP TABLE EMP;

4. A Comma (,) separates parameters without a clause.

5. A ‘;’ is used to end SQL statements.

6. Statements may be split across lines but keywords may not.

7. Lexical units such as identifiers, operator names, literals are separated by one or more spaces or other delimiters that will not be confused with the lexical unit.

8. Reserved words cannot be used as identifiers unless enclosed with double quotes. Reserved words are: AS, BY, CREATE, SELECT, VARCHAR, BETWEEN etc.

9. Identifiers can contain up to 30 characters and must start with an alphabetic character.

10. Character and date literals must be enclosed within single quotes.

11. Numeric literals can be represented by simple values such as 0.32,-34,01991, and so on, scientific notation as 2E5 meaning 2x10 to power of 5=200,000.

12. Comments may be enclosed between /* and */ symbols and may be multi line. Single line comments may be prefixed with a – symbol.

SQL Delimiters

Delimiters are symbol or compound symbols, which have a special meaning within SQL and PL/SQL statements.

|+ |Addition |“ |Quote identifier |

|- |Subtraction |: |Host Variable |

|* |Multiplication |** |Exponential |

|/ |Division | != ^= |Relational |

|=> < |Relational |= |Relational |

|() |Expression or list |:= |Assignment |

|; |Terminator |=> |Association |

|% |Attribute indicator ||| |Concatenation |

|, |Item separator |> |Label |

|@ |Remote access indicator |-- |Comment |

|‘ |Character string delimiter |/* */ |Comment (Multi-line) |

Components of SQL

1. DDL(Data Definition Language) :

• It is a set of SQL commands used to create, modify and delete database structures but not data.

• These commands are normally not used by a general user, who should be accessing the database via an application.

• They are normally used by the DBA to a limited extent, a database designer or application developer.

• DDL must have the CREATE object privilege and a Tablespace area in which to create objects.

2. DML(Data Manipulation Language) :

• It is the area of SQL that allows changing data within the database.

3. DCL(Data Control Language) :

• It is the component of SQL statement that control access to data and to the database. Occasionally DCL statements are grouped with DML statements.

4. DQL(Data Query Language) :

• It is the component of SQL statement that allows getting data from the database and imposing ordering upon it. In includes the SELECT statement. This command is the heart of SQL. It allows getting the data out of the database perform operations with it. When a SELECT is fired against a table or tables the result is compiled into a further temporary table, which is displayed or perhaps received by the program i.e. a front-end.

Examples of DDL, DML and DCL commands

DDL: Data Definition Language statements

Examples:

• CREATE To create objects in the database

• ALTER Alters the structure of the database

• DROP Delete objects from the database

• TRUNCATE Remove all records from a table, including all

spaces allocated for the records are removed

• COMMENT Add comments to the data dictionary

• GRANT Gives user’s access privileges to database

• REVOKE Withdraw access privileges given with the GRANT Command

DML: Data Manipulation Language statements

Examples:

• INSERT Insert data into a table

• UPDATE Updates existing data within a table

• DELETE DELETE Deletes all records from a table, the space for the records remain

• CALL Call a PL/SQL or JAVA subprogram

• EXPLAINPLAN Explain access path to data

• LOCK TABLE Control concurrency

DCL: Data Control Language statements

Examples:

• COMMIT Save work done

• SAVEPOINT Identify a point in a transaction to which you can later roll back

• ROLLBACK Restore database to original since the last COMMIT

• SET TRANSACTION Change transaction options like what rollback segment to use

• GRANT / REVOKE EGrant or take back permissions to or from the oracle users.

DQL: Data Query Language statements

Examples:

• SELECT Retrieve data from the a database

TABLESPACE IN ORACLE

Logical Structure of a Database

An Oracle database comprises of a number of physical files called data files. These files are logically grouped together into oracle(logical) structure called a Tablespace.

Tablespace

User data in an Oracle database is logically stored in tablespace and physically stored in data files, which are bound to the corresponding tablespace.

Tablespace are a mechanism or a means by which logical objects such as tables, indexes and views are mapped to a specific data file. Tablespaces are also used to group different types of (logical) database objects together.

SQL Constraints:

Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

There are two types of constraints:

1).Input output constraints.

o Primary key constraint

o Foreign key constraints

o Unique key constraints

2).Business rule constraints.

o Not null constraints

o Check constraints

o Default constraints

Constraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL:

1. NOT NULL Constraint:

• Ensures that a column cannot have NULL value.

• By default, a column can hold NULL values. If you do not want a column to have a NULL value then you need to define such constraint on this column specifying that NULL is now not allowed for that column.

• A NULL is not the same as no data, rather, it represents unknown data.

EXAMPLE:

For example, the following SQL creates a new table called CUSTOMERS and adds five columns, three of which, ID and NAME and AGE, specify not to accept NULLs:

|CREATE TABLE CUSTOMERS( |

|ID INT NOT NULL, |

|NAME VARCHAR (20) NOT NULL, |

|AGE INT NOT NULL, |

|ADDRESS CHAR (25) , |

|SALARY DECIMAL (18, 2), |

|PRIMARY KEY (ID) |

|); |

If CUSTOMERS table has already been created, then to add a NOT NULL constraint to SALARY column in Oracle, you would write a statement similar to the following:

|ALTER TABLE CUSTOMERS |

|MODIFY SALARY DECIMAL (18, 2) NOT NULL; |

2. DEFAULT Constraint :

• Provides a default value for a column when none is specified.

• The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value.

EXAMPLE:

For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here SALARY column is set to 5000.00 by default, so in case INSERT INTPO statement does not provide a value for this column then by default this column would be set to 5000.00.

|CREATE TABLE CUSTOMERS( |

|ID INT NOT NULL, |

|NAME VARCHAR (20) NOT NULL, |

|AGE INT NOT NULL, |

|ADDRESS CHAR (25) , |

|SALARY DECIMAL (18, 2) DEFAULT 5000.00, |

|PRIMARY KEY (ID) |

|); |

If CUSTOMERS table has already been created, then to add a DFAULT constraint to SALARY column, you would write a statement similar to the following:

|ALTER TABLE CUSTOMERS |

|MODIFY SALARY DECIMAL (18, 2) DEFAULT 5000.00; |

Drop Default Constraint:

To drop a DEFAULT constraint, use the following SQL:

|ALTER TABLE CUSTOMERS |

|ALTER COLUMN SALARY DROP DEFAULT; |

3. UNIQUE Constraint:

• Ensures that all values in a column are different.

• The UNIQUE Constraint prevents two records from having identical values in a particular column. In the CUSTOMERS table, for example, you might want to prevent two or more people from having identical age.

EXAMPLE:

For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here AGE column is set to UNIQUE, so that you can not have two records with same age:

|CREATE TABLE CUSTOMERS( |

|ID INT NOT NULL, |

|NAME VARCHAR (20) NOT NULL, |

|AGE INT NOT NULL UNIQUE, |

|ADDRESS CHAR (25) , |

|SALARY DECIMAL (18, 2), |

|PRIMARY KEY (ID) |

|); |

If CUSTOMERS table has already been created, then to add a UNIQUE constraint to AGE column, you would write a statement similar to the following:

|ALTER TABLE CUSTOMERS |

|MODIFY AGE INT NOT NULL UNIQUE; |

You can also use following SYNTAX, which supports naming the constraint and multiple columns as well:

|ALTER TABLE CUSTOMERS |

|ADD CONSTRAINT myUniqueConstraint UNIQUE(AGE, SALARY); |

DROP a UNIQUE Constraint:

To drop a UNIQUE constraint, use the following SQL:

|ALTER TABLE CUSTOMERS |

|DROP CONSTRAINT myUniqueConstraint; |

If you are using MySQL then you can use following SYNTAX:

|ALTER TABLE CUSTOMERS |

|DROP INDEX myUniqueConstraint; |

4. PRIMARY Key:

• Uniquely identified each rows/records in a database table.

• A primary key is a field in a table which uniquely identifies the each rows/records in a database table.

• Primary keys must contain unique values. A primary key column cannot have NULL values.

• A table can have only one primary key which may consist of single or multiple fields.

• When multiple fields are used as a primary key, they are called a composite key.

• If a table has a primary key defined on any field(s) then you can not have two records having the same value of that field(s).

Note: You would use these concepts while creating database tables.

Create Primary Key:

Here is the SYNTAX to define ID attribute as a primary key in a CUSTOMERS table.

|CREATE TABLE CUSTOMERS( |

|ID INT NOT NULL, |

|PRIMARY KEY (ID) |

|); |

To create a PRIMARY KEY constraint on the "ID" column when CUSTOMERS table already exists, use the following SQL SYNTAX:

|ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID); |

NOTE: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).

For defining a PRIMARY KEY constraint on multiple columns, use the following SQL SYNTAX:

|CREATE TABLE CUSTOMERS( |

|ID INT NOT NULL, |

|NAME VARCHAR (20) NOT NULL, |

|AGE INT NOT NULL, |

|ADDRESS CHAR (25) , |

|SALARY DECIMAL (18, 2), |

|PRIMARY KEY (ID, NAME) |

|); |

To create a PRIMARY KEY constraint on the "ID" and "NAMES" columns when CUSTOMERS table already exists, use the following SQL SYNTAX:

|ALTER TABLE CUSTOMERS |

|ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME); |

Delete Primary Key:

You can clear the primary key constraints from the table, Use SYNTAX:

|ALTER TABLE CUSTOMERS DROP PRIMARY KEY ; |

5. FOREIGN Key:

• Uniquely identified a rows/records in any another database table.

• A foreign key is a key used to link two tables together. This is sometimes called a referencing key.

• Primary key field from one table and insert it into the other table where it becomes a foreign key ie. Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.

The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

If a table has a primary key defined on any field(s) then you can not have two records having the same value of that field(s).

EXAMPLE:

Consider the structure of the two tables as follows:

CUSTOMERS table:

|CREATE TABLE CUSTOMERS( |

|ID INT NOT NULL, |

|NAME VARCHAR (20) NOT NULL, |

|AGE INT NOT NULL, |

|ADDRESS CHAR (25) , |

|SALARY DECIMAL (18, 2), |

|PRIMARY KEY (ID) |

|); |

ORDERS table:

|CREATE TABLE ORDERS ( |

|ID INT NOT NULL, |

|DATE DATETIME, |

|CISTOMER_ID INT references CUSTOMERS(ID), |

|AMOUNT double, |

|PRIMARY KEY (ID) |

|); |

If ORDERS table has already been created, and the foreign key has not yet been, use the SYNTAX for specifying a foreign key by altering a table.

|ALTER TABLE ORDERS |

|ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID); |

DROP a FOREIGN KEY Constraint:

To drop a FOREIGN KEY constraint, use the following SQL:

|ALTER TABLE ORDERS |

|DROP FOREIGN KEY; |

6. CHECK Constraint:

• The CHECK constraint ensures that all values in a column satisfy certain conditions.

• The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn’t entered into the table.

EXAMPLE:

For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here we add a CHECK with AGE column, so that you can not have any CUSTOMER below 18 years:

|CREATE TABLE CUSTOMERS( |

|ID INT NOT NULL, |

|NAME VARCHAR (20) NOT NULL, |

|AGE INT NOT NULL CHECK (AGE >= 18), |

|ADDRESS CHAR (25) , |

|SALARY DECIMAL (18, 2), |

|PRIMARY KEY (ID) |

|); |

If CUSTOMERS table has already been created, then to add a CHECK constraint to AGE column, you would write a statement similar to the following:

|ALTER TABLE CUSTOMERS |

|MODIFY AGE INT NOT NULL CHECK (AGE >= 18 ); |

You can also use following SYNTAX, which supports naming the constraint and multiple columns as well:

|ALTER TABLE CUSTOMERS |

|ADD CONSTRAINT myCheckConstraint CHECK(AGE >= 18); |

DROP a CHECK Constraint:

To drop a CHECK constraint, use the following SQL.

|ALTER TABLE CUSTOMERS |

|DROP CONSTRAINT myCheckConstraint; |

INDEX:

• Use to create and retrieve data from the database very quickly.

• The INDEX is used to create and retrieve data from the database very quickly. Index can be created by using single or group of columns in a table. When index is created it is assigned a ROWID for each rows before it sort out the data.

EXAMPLE:

For example, the following SQL creates a new table called CUSTOMERS and adds five columns:

|CREATE TABLE CUSTOMERS( |

|ID INT NOT NULL, |

|NAME VARCHAR (20) NOT NULL, |

|AGE INT NOT NULL, |

|SALARY DECIMAL (18, 2), |

|PRIMARY KEY (ID) |

|); |

Now you can create index on single or multiple columns using the folloiwng SYNTAX:

|CREATE INDEX index_name |

|ON table_name ( column1, column2.....); |

To create an INDEX on AGE column, to optimize the search on customers for a particular age, following is the SQL SYNTAX:

|CREATE INDEX idx_age |

|ON CUSTOMERS ( AGE ); |

DROP a INDEX Constraint:

To drop a INDEX constraint, use the following SQL:

|ALTER TABLE CUSTOMERS |

|DROP INDEX idx_age; |

SQL is followed by unique set of rules and guidelines called SYNTAX. This tutorial gives you a quick start with SQL by listing all the basic SQL SYNTAX:

All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;).

Important point to be noted is that SQL is case insensitive which means SELECT and select have same meaning in SQL statements but MySQL make difference in table names. So if you are working with MySQL then you need to give table names as they exist in the database.

SQL SELECT Statement:

|SELECT column1, column2....columnN |

|FROM table_name; |

SQL DISTINCT Clause:

|SELECT DISTINCT column1, column2....columnN |

|FROM table_name; |

SQL WHERE Clause:

|SELECT column1, column2....columnN |

|FROM table_name |

|WHERE CONDITION; |

SQL AND/OR Clause:

|SELECT column1, column2....columnN |

|FROM table_name |

|WHERE CONDITION-1 {AND|OR} CONDITION-2; |

SQL IN Clause:

|SELECT column1, column2....columnN |

|FROM table_name |

|WHERE column_name IN (val-1, val-2,...val-N); |

SQL BETWEEN Clause:

|SELECT column1, column2....columnN |

|FROM table_name |

|WHERE column_name BETWEEN val-1 AND val-2; |

SQL Like Clause:

|SELECT column1, column2....columnN |

|FROM table_name |

|WHERE column_name LIKE { PATTERN }; |

SQL ORDER BY Clause:

|SELECT column1, column2....columnN |

|FROM table_name |

|WHERE CONDITION |

|ORDER BY column_name {ASC|DESC}; |

SQL GROUP BY Clause:

|SELECT SUM(column_name) |

|FROM table_name |

|WHERE CONDITION |

|GROUP BY column_name; |

SQL COUNT Clause:

|SELECT COUNT(column_name) |

|FROM table_name |

|WHERE CONDITION; |

SQL HAVING Clause:

|SELECT SUM(column_name) |

|FROM table_name |

|WHERE CONDITION |

|GROUP BY column_name |

|HAVING (arithematic function condition); |

SQL DROP TABLE Statement:

|DROP TABLE table_name; |

SQL CREATE INDEX Statement :

|CREATE UNIQUE INDEX index_name |

|ON table_name ( column1, column2,...columnN); |

SQL DROP INDEX Statement:

|ALTER TABLE table_name |

|DROP INDEX index_name; |

SQL DESC Statement:

|DESC table_name; |

SQL TRUNCATE TABLE Statement:

|TRUNCATE TABLE table_name; |

SQL ALTER TABLE Statement:

|ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype}; |

SQL ALTER TABLE Statement (Rename) :

|ALTER TABLE table_name RENAME TO new_table_name; |

SQL INSERT INTO Statement:

|INSERT INTO table_name |

|( column1, column2....columnN) |

|VALUES ( value1, value2....valueN); |

SQL UPDATE Statement:

|UPDATE table_name |

|SET column1 = value1, column2 = value2....columnN=valueN |

|[ WHERE CONDITION ]; |

SQL DELETE Statement:

|DELETE FROM table_name |

|WHERE {CONDITION}; |

SQL CREATE DATABASE Statement:

|CREATE DATABASE database_name; |

SQL DROP DATABASE Statement:

|DROP DATABASE database_name; |

SQL COMMIT Statement:

|COMMIT; |

SQL ROLLBACK Statement:

|ROLLBACK; |

SQL Loader

SQL LOADER utility is used to load data from other data source into Oracle. For example, if you have a table in FOXPRO, ACCESS or SYBASE or any other third party database, you can use SQL Loader to load the data into Oracle Tables. SQL Loader will only read the data from Flat files. So If you want to load the data from FoxPro or any other database, you have to first convert that data into Delimited Format flat file or Fixed length format flat file, and then use SQL loader to load the data into Oracle.

Following is procedure to load the data from Third Party Database into Oracle using SQL Loader.

1. Convert the Data into Flat file using third party database command.

2. Create the Table Structure in Oracle Database using appropriate data types

3. Write a Control File, describing how to interpret the flat file and options to load the data.

4. Execute SQL Loader utility specifying the control file in the command line argument

SQL*Loader (sqlldr ) is the utility to use for high performance data loads.  The data can be loaded from any text file and inserted into the database.

 

Figure 4.2 depicts the SQL*Loader architecture.  SQL*Loader reads a data file and a description of the data which is defined in the control file.  Using this information and any additional specified parameters (either on the command line or in the PARFILE), SQL*Loader loads the data into the database. 

 

During processing, SQL*Loader writes messages to the log file, bad rows to the bad file, and discarded rows to the discard file.

[pic]

 

 Figure 4.2 – SQL*Loader Architecture 

The Control File

The SQL*Loader control file contains information that describes how the data will be loaded.  It contains the table name, column datatypes, field delimiters, etc.  It simply provides the guts for all SQL*Loader processing. 

 

Manually creating control files is an error-prone process.  The following SQL script (controlfile.sql) can be used to generate an accurate control file for a given table.  The script accepts a table name and a date format (to be used for date columns), and generates a valid control file to use with SQL*Loader for that table.   

 

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download