Unit III
oracle for B.SC final year student.
SQL is the heart of RDBMS. SQL is the language used for all operation in a relational data base. The minimum operation required for handling the database are creation of a table ,inserting data, updating data, deleting data & retriever of data. Beside this, while working in multi-user data disk a user may wants to square his data from another user may want to secure his data from another user. This will be possible by controlling the access of data in a multi-user database through permission of adequate security. In most of the RDBMS, the operation are specified using SQL
Only & that is the only the language interprets by an RDBMS. YOU HAVE TO ISSUE a command to RDBMS in SQL, the RDBMS will interrupt your command & take necessary relation SQL is very powerful language in the sense that most of the operation in RDBMS can be performed language. In a non procedure language, you have to describe what to do rather than how to-do. How to do the job is the responsibility of the RDBMS. But in a procedural language, you have to give the complete procedure of doing the job. Suppose you want to get the railway reservation made by your friend. The procedural way of doing this is giving him complete instructions as follows -1> catch bus 2> go to railway station 3> fill up the reservation form 4> get the reservation 5> catch the bus again 6> come back home. Thus you are specifying a complete set of instructions. The non-procedural way is to describe what to do rather than how to do. Thus you can just specify your requirement to your friend as “get me a railway reservation “once you specify your requirement, it becomes the responsibility of your friend to find output
how to fulfill it , how to go to railway station & make the reservation.
*RDBMS: A relational database is a body of related information stared in two dimension format called table. a modern RDBMS can perform a wide array of task. In general, it acts as a transparent interface between the physical structure & the logical presentation of data. In practice it provides a tool handling information. You can use these tools to: (1) define a database. (2) to read the database. (3) query the database. (4) Add, edit & delete data. (5) Modify structure of database (6) secure data from public access. (7) Communication within network. (8) Expert & imparted the data because it gives you so mush control over your data. A RDBMS can also serve as the foundation for products, generate application & abstract data
DATA BASE ADMINISTRATION: To avoid chase in any complex system someone has to be in changed, the person responsible for a data base is called the database Administrator [DBA]. It has a specifically set of privilege s that gives him as a complete control over the database. Any system with more than one user should have a DBA. For e single user such as a professional oracle, the user has a multiple roles. Sometimes acting as the system DBA sometimes as an ordinary user with limited database privileges. The database administrators job includes (1) creation primary data base storage structure (2) Modifying the structure of database.(3) Backing up & restoring the database. (4) Monitoring & reestablishing database consistency. (5) Transferring data between data base & external file. (6) Maintaining a database. (7) Controlling & monitoring user access to the database. (8) Manipulating the physical location of the database.
How oracle access database? Oracle access data by using SQL it is a structure query language that you use to communicate with oracle. It consists of set of common English word such as a select, create, insert, delete edit etc, which you can arrange in highly structure commands & statement in order to access & manipulate data stored in a relational database.
The SQL commands falls into four categories components of SQL are:
(1) DDL [data definition language]: It is a set of SQL commands used to create, modify & delete database structure. In oracle the table is the only way of storing a data. All information has to be arranged in the form of stable i.e. a combination of rows & columns. Rows are called records & columns are called fields. Suppose you want to store information about the company then you can create the table.
Syntax: create Table company (name char (10), city (10))
This is called table definition language. You can create other objects such as index; view etc by using DDL statement. Using a DDL you can also drop objects.
(2) DML (data manipulation Language):
Using data manipulation language you can carry out operation such as insertion, Updating and deletion on a table. The insert statement is used to add a row that is record to a table.
Insert into company value (“ACC”, “Mumbai”); you can also update data in an existing table as Update Company.
Set City = “Nagpur”;
Where City Name = “ACC”;
You can delete rows using delete statement. Delete company set for (Name = “ACC”);
Thus using insert delete and update statement you can carry out any type of data manipulation.
(3) Query language:
Query language is use for retrieving data from a table. For Ex: To get all the data from a table you can write,
select * from company;
Suppose you want to retrieve the location of the company ACC you can write,
Select city from company where (Name = “ACC”);
(4) DCL (Data control language):
Using data control language you can control access to the data. Suppose user U1 has created a table company and he wants to give access of the same table to user U2, then you can write,
SQL > Grant select on company to U2;
Now user U2 can select the data form the table company which is own by user U1. U2 can access the data by writing,
SQL > select * from U1. Company.
*Data access and manipulation tools:
All of Oracle data access and manipulation tools have one vital thing in common. They are firmly based on anti-standard SPL. These programs are your get way to oracle, the tools that you will use to access and manipulate data as well as to design or used application. It provides a separate point of entry and a unique approach to the oracle system SQL * PLUS allows direct access to the data base with SQL commands. SQL * forms offers a users friendly way to create & use forms SQL * report writer lets you create formulated output. SQL * menu provide a way for you to integrate your application using the menu.
SQL * PLUS DATA TYPES
As you know, the data in an oracle data base is stored in table that contains column i.e. fields. Each field is reserved for a particular type of data i.e. decides upon when the table is created. The more kinds of data or data types language allows the more option you have as you create table. The oracle SQL * PIUS interface language provides a rich varity of data types they are:
i) CHAR: Character data can consist of characters, digits & special characters. The maximum field size is 240 characters; char (size).
(ii) DATE: Date field contains valid dates in the range of Jan to Dec. Dates displayed in the form of DD-MMM-YY
E.g. 17-Oct-06
(iii) DECIMAL: Decimal, is same as a number does not accept size or decimal digits as an argument.
(iv) Float: Same as number.
(v) Integer (size): Integer of specified size digits wide.
(vi) LONG: Character data of variable size upto 2 GB
Small: Same as integer having range of 1 byte in length. Only one long column may be defined per table. It may not be used in sub query, function, expression, where clause or index.
(vii)Long Row: Row binary data otherwise same as long.
(viii) Long varchar: Same as long.
(ix) Number (size): For number column of specified size.
(x) Number (size, d): For number column of specified size with d digits after decimal points. For ex: Number (5, 2) 999.99
(xi)Varchar2 (size): Variable length character string having a maximum of size byte unto 4000.
*CREATING A TABLE:
In any RDBMS, tables are the basic units of the data storage. Tables hold all of the user accessible data. To create a table it is necessary to name the table and all the attributes that compose it. In addition, for every attribute, the user needs to define its data types and if necessary the appropriate constraints. The name of the table identifies it as a unique object with the RDBMS. Columns or attribute names reach to differentiate the attributes form one another. Attribute name must be unique within the table. The data type of each attribute defines the characteristics of its underline domain. The constraints that may be defined for a column impose conditions that need to be satisfied by all the value stored in the column. The table in a sphere are create by using the create table statement. Syntax:
Create table table Name
(Column_Name 1 Data type 1 [constraints],
Column_Name 2 Data type 2 [constraints],
.
.
.
Column_Name n Data type n [constraints]);
Creating Table Table Name
(Column_Name1 Data type1 [constraintName], References parent table column–of–parent table)
In the above table the optional elements in a square bracket. Every column definition line requires a column name and a data type. Constraints are optional. After defining all columns complete the command by using a closing parenthesis with a semicolon. The SQL standard required that whenever a constraint is defined, the constraints be define. It can be named explicitly by the user at the time a table is created or modified. Otherwise the constraints are named internally by the RDBMS. Constraints that are named by the user are called name constraint constraints name by the RDBMS are vendor dependent and are called unnamed constraints. The constraints are used as a following format for name constraints.
CONSTRAINT TABLE–NAME–COLUMN–NAME suffix
Where, the clause constraint is mandatory and the suffix is one or two letter sequences that indicate the time of constraint are shown below. Unnamed constraint must not be processed by constraint clause.
Ex: Creating calling – card
(Company – Name Varchar 2 (25);
Card – Number Varchar (15);
Starting – Value Number (4, 2);
Value – Left Number (4, 2);
Pin – Number char (2));
In the above table the attribute company name is obviously of type char. Since not all company names are 25 char long, the data type of this column is varchar (25). The card no. and pin no. column are chara types because they are not involved in any type of compilation. Since card no. may vary in length its data type is varchar2 (15), the data type of pin no is char (12), since this column has fix length. The starting value and value-left columns are both numerical quantities that may have up to four digits with two decimals.
Ex: Creating Table calling – card
(Company Name Varchar 2 (25);
Card Number Varchar (15); Primary Key
Starting – Value Number (4, 2);
Value – Left Number (4, 2);
Pin–Number Char (2); Unique);
*DESCRIBING THE STRUCTURE OF TABLE: After table has been created it may be necessary to determine the name, data type and some of the constraints and attributes that computer table. The SQL * PLUS command that allows us to find this information is the described command.
Syntax: Describe Table – Name
Ex: the command
Described calling – code
Will display,
Name Null?? Type
Company Name Varchar 2 (25);
Card Number Primary Key Varchar (15);
Starting_Value Number (4, 2);
Value_Left Number (4, 2);
Pin_Number Unique Char (12);
The output of the above describe calling_code command which consists of three columns name, null? And type. The name column consists of name different attribute of table. The null? Column indicates whether or not the attribute can accept a null value. The type column describes the data type of the attribute table.
*INSERTING VALUES INTO A TABLE:
After creating a table, the user may add rows to the table using insert into command. The process of adding rows to a table for the first time is called populating the table. In its simplest form, this common allows the user to add rows to a table, one row at a time.
Syntax:
Insert into table_Name (column-1, column-2, column-3……, column-N)
Values (value-1, value-2… value-N) OR
Insert into table-name
Values (value-1, value-2… value-N);
In above syntax column-1 up to column–N are the tables column and value -1 up to value– N are the values that will be inserted into their corresponding column. Notice that the value to be inserted into a column must be of the same data type that was specified for the column when its table was created. It is important to keep in mind that we must specify a value in the value clause. For each column that appears in the column list. For Ex: Insert into calling–code (company–name, card-number, starting–value, value-left, Pin–number )
Values (‘ABC’, ‘123456789’, 20.00, 12.45, 987234569817);In the above example one record is added in the table calling–code. If you want add another more record apply the same process again and again.
*SELECT STATEMENT :
It is the most frequently used SQL statement. The select statement is used primarily to query or retrieve data from the data base. For this reason it is customary to call a select statement a query.
Syntax:
Select [*] / [column-1, column-2… column-N]
Form table-1, table-2… table–N
[Where condition]
[Order by column-1 [ASC/DESC] [column2 [ASE/DESC]………];
The select statement is formed by at least two clause, the select clause and the from clause. The clause where and order by are optional. Observe that the select statement like any SQL statement ends in a semicolon. The function of each clause are
(1) The select clause: List the column to display the attribute list in this clause are the columns of the resulting relation.
(2) The from clause: List the tables from which to obtain the data. The column mentioned in the select clause must be the table listed in the from clause.
(3) The where clause: Specified the condition or condition that needs to be specified by the rows of the table indicated by from clause.
(4) The Order by clause: Indicates the criteria used to sort the rows that specify in the where clause. The order by clause only affects the display of data retrieve not the internal ordering of the row within that table.
Example:
(i) Select * From calling_code;
(ii) Select company_Name, Pin_Numbers
From calling_code;
(iii) Select * from calling_code
Where company_Name = ‘ABC’;
(iv) Select company_Name, Pin_Number
From calling code
Where company_Name = ‘ABC’;
(v) Select * form calling_code
Order by Pin_Number;
* UPDATING AND DELETING ROW OF TABLE :
Sometimes it is necessary to modify existing value of one or more rows in a table as a result of the normal activities that occur in a database. Remember that a database is intended to capture a repeatation of real words and some of its dynamics.
UPDATE COMMAND: As the name of this command suggest, its primary function is to update the rows the table. The basic syntax of this SQL command to update one or more values of a single node is as follows.
UPSATE table_name SET col-1 = New – Val1 [……, ……Col-N = New –Val N] [Where condition];
Where column1, column2,…………. column N, standards for column name and new_val1, New_val2………..New_val N stands for new value that will be stored in their corresponding column. The where clause allows us to change the values of selected rows.
For Example:
UPDATE S_customer SET
Phone = ‘407-345-1265’
Address = ‘2905, Plot no-20’
City = ‘Wardha’
State = ‘Ms’
Zip code = ‘442001’
Where = ‘NEHA’
In above the value of the columns address, city, state, zip code, phone are changed by new values of the record that contains the name NEHA.
* DELETE ANA TRUNCATE COMMAND:
As a part of the normal operation, one or more rows may need to be removed or deleted from the table. For such SQL allows the data command. This command can be used to remove rows that meet certain condition, or it can be used remove all rows from a particular table.
Syntax:
DELETE FROM table_Name WHERE condition;
If we want to remove all records by using delete command, use simply delete and table name.
DELETE table_Name;
Example:
DELETE FROM S_customer where
Name = ‘NEHA’;
In above example one record is delete that contains name NEHA of customer table. An easier and faster way be to delete all rows from a table is using the truncate command.
Syntax:
TRUNCATE TABLE table_Name;
The record should be aware that there are some limitations in the use of this command (1) we cannot truncate the rows of the table if there are reference integrity constraints for which this table is the parent table. (2) we cannot roll back a truncate command.
*DROP TABLE COMMAND:
Sometimes it is necessary to remove a table and all its data from our data base. The SQL command that allows us to do this is the drop table command.
Syntax:
DROP TABLE table_Name [CASCASDE CONSTRAINS];
The optional clause cascade constraints is used whenever referential integrity constrains that refer either to primary key or to a drop i.e. if we try to delete a particular table and this table is the parent table for some other table, we will get an error unless we use a cascade constraints clause. The drop table cascade constraints command is commonly used before a create table command. A result for this is to avoid any error generated by the RDBNS when trying to create a table that has the same name to another that already exist in the schema owed by the user.
*JOIN OPERATIONS:
The joins operation allows us to bring data from two or more table together into a single table. The table participate in that table are concatenate through their common attribute according to their condition specified in their clause. Very frequently these common attribute key foreign key relationship. Joins are classified based upon the types of condition involved, the number of table involved, and the type of rows that are retrieved. Some joining are:
(1) Equi join: A join between two tables connected through common equal values. (2) Self join: A joins between a table and it self. (3) Outer join: Join between two tables to determine all the rows of one table that do not have a match in the other table.
(1) THE EQUI JOIN: An equi join is the join of two tables through their common equal values. The syntax of the select command to implement this type of joint as follows:
SELECT table_1.col2------ table1.colN,-----,table_2.colN
FROM table_1, table_2
Where table_1.common – column = table_2.common-column;
This command can be easily generalized to join more than one table. Notice that in the select clause we have preceded each column name with the name of its table. It is necessary if the same column name appears in more than one table. Columns whose names are preceded by the name of their tables are said to be qualified. If there are no common names in two tables, then there is no need to qualify the columns. However to input the performance it is better to qualify the columns name that appear in select clause must appear as columns of at least one of the tables mentioned in the from clause.
Example:
SELECT S_emp.Last_Name, S-emp.First_Name, S_dept.Name
FROM S_emp, S_dept
WHERE S_emp.dept_Id = S_dept.dept_Id;
OR
SELECT A_Last_Name, A.First_Name, B. Name
FROM S_emp A, S_dept B
WHERE A_dept_Id = B.dept_Id;
Where A and B are table allies.
(2) SELF JOIN: A self join is the join of a table with it self. It is easier to work with this type of join. If we give to the same table allies. This way we can treat the same table as a two different tables.
To answer this query we need to joint the table it itself. To equi join, this type of query is easier to work with if we use two different aliases for the same table. In this case we assign to S_emp table. The aliases E and M. the query to retrieve the information requested and its output as shown below.
SELECT m.Last_Name || ‘Work For’||
E.Last_Name || ‘ ’|| E. First name As
“Employee and their Manager”
FROM S_emp M, S_emp E
WHERE E. Id = M.Id;
(3) OUTER JOINT: Given a join of two tables some time we would like to know the role of one of the tables that do not mean a specified condition in the other table; for instance we would like to know the employee who do not have a manager. The operation that allow us to answer this type of question is the outer join operation.
Syntax
SELECT table 1.col 1 …….. tableN.col.N
Table 2.col 1 …….. table2.col.N
FROM table 1, table 2
Where table 1.column _ Name (+) =
Table 2. column_Name;
SELECT table 1.col 1 …….. tableN.col.N
table 2.col 1 …….. table2.col.N
FROM table 1, table 2
Where table 1.column _ Name =
Table 2. column_Name (+);
The outer join operator is indicated by (+) enclosing parenthesis. This operator is used in WHERE clause and follows the column_Name of that side of join condition that doesn’t have matching rows in other table. Thus according to syntax of operator (+) can be replace on either side of WHERE condition but not in both.
Example: SELECT M.LAST_Name || “ ” ||
‘worker For’ || “ ” || E. Last_Name ||
“ ” || E.First_Name AS “Manager and their Employee”
FROM S_EMP M, S_EMPS
WHERE e Id (+) = M.Manager_Id.
<<<< Previous PAGE Next >>>>