SQL Basics And Advance

What is a database

It is a coherent collection of data with some inherent meaning, built and populated with data for a specific purpose. A database stores data that is useful to us. This data is only a part of the entire data available in the world around us.

To be able to successfully design and maintain database we have to do the following:

Ø  Identify which part of the word’s data is of interest to us.

Ø  Identify what specific objects in that part of the word’s data are of interest to us.

Ø  Identify a relationship between the objects.  

Software houses took up the challenge of designing a system that would help users in managing data in a database. These systems were called Database Management Systems (DBMS). Some of the DBMS developed by software houses were Oracle, Ingress, Sybase, etc. Let us look at Oracle as a Database Management System (DBMS).

Introduction to Oracle and Its Tools:

The Oracle product is primarily divided into  

• Oracle server tools
• Oracle Client tools

Oracle Server :-

Oracle is company that produces the most widely used, Server based, Multi user RDBMS. The Oracle Server is a program installed on the server’s hard disk drive. This program must be loaded in RAM so that it can process user requests.

This Oracle Server product is either called

Oracle Workgroup Server

        OR

Oracle Enterprise Server

The functionality of both these products is identical. However, the Oracle Workgroup Server restricts  the number of concurrent users who can query the Server. Oracle Enterprise Server has no such restrictions. Either product must be loaded on a multi-user operating system.

The Oracle Server takes care of the following:

Ø  Updating the database.

Ø  Retrieving information from the database.

Ø  Accepting query language statements.

Ø  Enforcing security specifications.

Ø  Enforcing data integrity specifications.

Ø  Enforcing transaction consistency.

Ø  Managing data sharing.

Ø  Optimizing queries.

Ø  Managing system catalogs.

 Oracle Database Administrator (DBA) :-

Anyone who logs onto the multi-user operating System as ‘internal’ or ‘system’ with the appropriate ‘password’ is recognized by the o/s as the legitimate owner of ‘Oracle’ resources on the Hard disk drive. This person them becomes the Oracle DBA.

Oracle’s Resource Control tool :- 

The tool set is generally called Oracle Enterprise Manager. It include the Server Manager Tool, which is used to startup the Oracle Engine, security Manager that allows the DBA to create user and grant users permissions to use resources of the Oracle database.

The tool protected :-

The tools included in the Oracle Enterprise Manager can be installed on different Operating Systems. The user authentication for starting these tools varies from Operating System to Operating System.

The user authentication varies based on the tool invoke from the Oracle Enterprise Manager tool set.

The Server Manager Tool :-

The Server Manager Tool is used to load the Oracle engine from the Server’s hard disk drive into the Server’s memory. Oracle users will be allowed to access Oracle resources only when the Oracle engine is loaded in memory.

The Security Manager Tool :-

Once the Oracle engine is running, the Security Manager tool can be used by the DBA to create user accounts and grant permission to the user accounts. Creation of a user account will provide user’s with:

1. Login identity.
2. A password.
3. Permission to use Oracle resources  

The login identity and the password will allow a user to connect to the Oracle Database. Once connected to the Oracle Database, a user can create or use the Oracle Resources like tables, views etc. Depending on the permission granted by the DBA.

Oracle Client Tools:-

Once the Oracle Engine is loaded into a Server’s memory, users would have to log into the engine to get work done. Oracle Corporation has several client-based tools that facilitate this. The client tool most commonly used for Commercial Application Development.

A Data Storage System:-  

All commercial applications manipulate data. Prior the manipulation of data, it has to be stored . hence logically the lowest layer in any commercial applications is a data storage system. The data storage product, create and marketed by Oracle is either Oracle Workgroup Server or Oracle Enterprise Server.

A Menu System:- 

It is impossible to create an entire commercial application using a single data capture and validation, from object. This is because a data capture and validation, from object, used to capture

 Oracle Graphics:-

Some of the data can be better represented in the form of pictures. The Oracle Graphics Tool allows programmers to prepare graphs using data from Oracle Structures like tables, views etc. Oracle Graphics can also be considered as a part of the reporting section of a commercial application.

What is SQL Used for:

Using SQL one can create and maintain data manipulation objects such as tables, views, sequences etc. These data manipulation objects will be created and stored on the server’s hard disk drive, in a table space, to which the user has been assigned.

DML, DCL, DDL: 

In addition to the creation of data manipulation objects, the actual manipulation of data within these objects is done using SQL.

The SQL sentences that are used to create these objects are called DDL’s or Data Definition Language. The SQL sentence used to manipulate data within these objects are called DML’s or Data Manipulation Language. The SQL sentence, which are used to control the behavior of these objects, are called DCL’s or Data Control Language.

Table/Entity:

To represent this information one can use DBMS modeling. In a DBMS a group of similar information or data which is of interest to an organization is called an Entity. Entity information is stored in an object called Table. For example, a client is considered an entity. Information about the client entity can be stored in a client_master  table. A table is really a two dimensional matrix that consists of rows and columns.

Attributes/Columns/Fields:

Each entity can have a number of characteristics. A client can have characteristics like name, address, telephone number, fax number, balance due etc. The characteristics of an entity are called Attributes. The values for these characteristics are called Attribute Values.

When entity information is stored in a table, the attributes associates with the entity are stored in the table columns / table fields. The client_master table can have columns like name, address, telephone_number, fax_number, bal due in with the user can store attributes values like ‘Datamatics’, ‘Vindhya Valley’, ‘614572’, ‘6122897’, ‘2200.00’.

Thus information in the client_master table will be

Name	Address	Telephone Number	Fax Number	Balance Due
Datamatics	Vindhya Valley	614572	6122897	2200.00

  

THE DATA TYPES THAT A CELL CAN HOLD

Data Type		Description
CHAR(size)	:	This data type is used to store character string values of fixed length. The size in brackets determines the number of characters(i.e. the size) this data type can hold is 255 characters.
VARCHAR(size) / VARCHAR2(size)	:	The data type used to store variable length alphanumeric data. The maximum this data type can hold is 2000 characters.
NUMBER(P, S)	:	The NUMBER data type is used to store numbers (fixed or floating point). Numbers of virtually any magnitude maybe stored up to 38 digits of precision. Number as large as 9.99*10 to the power of 124, i.e. I followed by 125 zeros can be stored.
DATE	:	This data type is used to represent date and time. The standard format is DD-MM-YY as in 20-MAY-99.
LONG	:	This data type is used to store variable length character strings containing up to 2GB. LONG data can be used to store arrays of binary data in ASCII format. LONG values cannot be indexed, and the normal character functions such as SUBSTR cannot be applied to LONG values.
RAW/ LONG RAW	:	The RAW /LONG RAW data types is used to store binary data, such as digitized picture or image. Data loaded into columns of these data types are stored without any further conversion. RAW data type can contain up to 2GB. Values stored in columns having LONG RAW data type cannot be indexed.

  The Create Table Command:

Syntax:

CREATE TABLE tablename

                   (columnname datatype(size), columnnamedatatype(size));

As seen in the CREATE TABLE syntax, the SQL statement starts with ‘CREATE’ i.e. a verb, followed by ‘TABLE’ i.e. a noun and ‘<tablename>’ i.e. adjective.

The CREATE TABLE command includes a single clause for the column definition. Each column is a parameter for the clause and thus it is separated by comma.

Finally, the SQL statement with a semi colon.

Example:   

Create a client_master table who structure is:

Column Name	Data Type	Size
Client_no name address1 address2 city state pincode remarks bal_due	varchar2 varchar2 varchar2 varchar2 varchar2 varchar2 number varchar2 number	6 20 30 30 15 15 6 60 10,2

 

CREATE TABLE client_master

(client_no varchar2(6), name varchar2(20), address1 varchar2(30), address2 varchar2(30), city varchar2(15), state varchar2(15), pincode number(6), remarks varchar2(60),bal_due number(10,2));

INSERTION OF DATA INTO TABLES

Once a table is created the most natural thing to do is load this table with data to be manipulated later. The appropriate SQL sentence syntax is:

When inserting a single row of data into the table, the insert operation:

Ø  Create a new row in the database table

Ø  Loads the values passed into all the columns specified.

Syntax:

INSERT INTO tablename

           (columnname, columnname)

VALUES (expression, expression);

Example:

Insert the following values into client_master table

Column Name             Values

Client_no                                           C02000,

name                                                   Sudhakar Pandey

address1                                            A-5, Jay Apartments,

address2                                            Service Road, Vindhy Valley,

city                                                        Mumbai

state                                                    Maharashtra

pincode                                              400057;

INSERT INTO client_master

       (client_no, name, address1, address2, city, state, pincode)

        VALUES (‘C02000’, ’Sudhakar Pandey’, ‘A-5,Jay Apartments’, ‘Service Road Vindhy Valley ’,

               ‘Mumbai’, ‘Maharashtra’, 400057);   

Filtering Table Data: 

While viewing data from a table it is rare that all the data from the table will be required each time. Hence, SQL must give us a method of filtering out data that is not required.

The ways of filtering table data will be

Ø  Selected columns and all rows

Ø  Selected rows and all columns

   

Ø  Selected columns and selected rows

 Selected Columns and Selected Rows:

To view a specific data set from the table and also a select number of columns the syntax will be:

Syntax:

SELECT columnname,columnname

     FROM tablename

     WHERE search condition;

Example:

Retrieve the client_no, name from the table client_master where the value in the bal_due field is greater than 5000;

        SELECT client_no, name

              FROM client_master

              WHERE bal_due>5000; 

INSERTING DATA INTO A TABLE FROM ANOTHER TABLE

In addition to inserting data one row at a time into a table, it is quite possible to populate a table with data that already exists in another table. The syntax for doing so is described under:

Syntax:

         INSERT INTO tablename

                 SELECT columnname, columnname,

                 FROM tablename;

Example:

    Insert records into table supplier_master from the table client_master;

              INSERT INTO supplier_master

              SELECT client_no, name, address1, address2,city, state, pincode, remarks

              FROM client_master;

Insertion of a data set into a table from another table:

Syntax:

INSERT INTO tablename

          SELECT columnname, columnname

            FROM tablename

            WHERE column = expression;

Example:

     Insert records into the table supplier_master from the table client_master where the field  

     client_no contains the value ‘C01001’;

INSERT INTO supplie_master

      SELECT client_no, name, address1, address2,

            city, pincode, state, remarks

       FROM client_master

       WHERE CLIENT_NO = ‘C01001’;

DELETE OPERATIONS

The verb DELETE in SQL is used to remove rows from table. To remove

Ø  All the rows from a table

       Or

Ø  A select set of rows from a table

Removal of All Rows:

Syntax:

DELETE FROM tablename;

Example:

 Delete all rows from the table client_master;

       DELETE FROM client_master;

Syntax:

        DELETE FROM tablename WHERE search condition;

Example:

        Delete rows from the table client _master where the value in the bal_due field is less than

        500;

       DELETE FROM client_master WHERE bal_due < 500;

 UPDATING THE CONDITION OF A TABLE

The UPDATE command is used to change or modify data values in a table. Update

Ø  All the rows from a table

       Or

Ø  A select set of rows from a table

Updating of All Rows:

Syntax:

        UPDATE tablename

              SET columnname = expression, columnname = expression;