Database Management Practical File

 

INDEX

SR NO.	EXPERIMENT	DATE	SIGNATURE
01	INTRODUCTION TO ORACLE
02	DATA TYPES OF SQL
03	SQL COMMANDS
04	ENHANCING PERFORMANCE
05	GRANTING AND REVOKING PERMISSIONS
06	JOINS
07	CONSTRAINTS
08	FUNCTIONS

INTRODUCTION TO ORACLE

CLIENT SERVER COMPUTING ARCHITECTURE

It consists of two or more computers designated as client machine running an application program which communicates with remote computer designated as server machine which serves request from client machine. In basic model of client server computing, RDBMS resides on server machine. The application program which resides on client machine interfaces with client software called middleware that are responsible for communicating request & results between their application programs and RDBMS.

CLIENT 

 The Client is the front END application of a database which interacts 

with the server. The Client has more responsibility for accessing the data, it concentrates on the requests. The processing and presentation is managed by the server.

SERVER 

The Server executes the Oracle software and these with the function required for simultaneous and shared data excess. The server receives and processes the SQl and PL/SQL declaration and that originates in the client application.

There are two types of Client Server Model are 

• Two tier Client Server Model

• Three tier client Server Model 

• Two tier Client Server Model: It improves the scalability by allowing upto 100’s of user and is very flexible for data sharing. Client requests services whereas the server it provides its services. There is one limitation of this tier i.e. the design of this architecture scales up. There are three components of client of this tier:

• User System interface

• Processing Management

• Database Management

•  Three Tier Client Server Model:  When the less software is on the client, there is less worry about security. Since the importance software is on  a server and in a 

more controlled environment. The support and installation cost of maintaining the software on a single server is much less than trying to maintain the same software on 100’s of PC’s.

ADVANTAGES OF CLIENT SERVER MODEL

• It allows the user to work in extensible form.

• It can work on any type of operating system eg. UNIX, LINUX, Win 98.

• It provides the facility to establish the link between remote databases.

• It provides the facility of fast accessing of information and processing of data.

• Better application performance and network usage i.e. it reduces the network time.

• It is multiplatform and centralized.

• Distributed computing as the servers act as a focal point for data management.

• Provides flexibility and full utilization of all resources.

• The same database is shared by different clients.

DISADVANTAGES OF CLIENT SERVER MODEL

• Bottleneck: If a significant portion of application layer is moved to a server, the server may become a bottle neck in the processing and distribution of data to the client 

• Server limited resources will be in over higher demanding by the increasing no. of resource consumers.

• Security features: It has to take care of the security of the database from unauthorized access.

ORACLE 

      Oracle is one of the powerful RDBMS product that provide efficient solutions for database applications. Oracle is the product of Oracle Corporation which was founded by LAWRENCE ELLISION in 1977. The first commercial product of oracle was delivered in 1970. The first version of oracle 2.0 was written in assembly language. Nowadays commonly used versions of oracle are ORACLE 8, 8i & 9i Oracle 8 and onwards provide tremendous increase in performance, features and functionality.

FEATURES OF ORACLE

• Client/Server Architecture

• Large database and Space Management

• Concurrent Processing

• High transaction processing performance

• High Availability

• Many concurrent database users

• Controlled availability

• Openness industry standards

• Manageable security

• Database enforced integrity

• Distributed systems

• Portability

• Compatibility

ORACLE SERVER TOOL

 Oracle is a company that produces most widely used server based multi-user RDBMS. Oracle server is a program installed on server hard-disk drive. This program must be loaded in RAM to that it can process the user requests. Oracle server takes care of following functions. Oracle server tools are also called as back end. Functions of server tool:

• Updates the data 

• Retrieves the data sharing

• Manages the data sharing

• Accepts the query statements PL/SQL and SQL

• Enforce the transaction consistency

ORACLE CLIENT TOOL

 Once Oracle engine is loaded into sever memory user 

would have to log in to engine in order to work done. Client tools are more useful in commercial application development. It provides facilities to work on database objects. These are more commonly used in commercial applications. Oracle client tools are also called front end.

DDL – DATA DEFINATION LANGUAGE

The SQL sentences that are used to create these objects are called 

DDL’s or Data Definition Language. The sql provides various commands for defining relation schemas, deleting relations, creating indexes and modify relation schemas. DDL is part of sql which helps a user in defining the data structures into the database. Following are the various DDL commands are 

• Alter table & Create table & drop table

• Create index & drop index

• Create view & drop view

DML – DATA MANIPULATION LANGUAGE

The SQL sentences used to manipulate data within these objects are called DML’s or Data Manipulation Language. It is language that enables users to 

access or manipulate data as organized by appropriate data model. By data manipulation we have 

• Retrieval of information stored in database.

• Insertion of new information into database.

• Deletion of information from database.

• Modification of data stored in database.

Two types of DML are

• Procedural DML

• Non-procedural DML

Following are DML commands are

• Select

• Update

• Delete

• Insert

DCL – DATA CONTROL LANGUAGE

The SQL sentences, which are used to control the behavior of 

these objects, are called DCL’s or Data Control Language. It is language used to control data and access to the database. Following are some DCL commands are 

• Commit

• Rollback

• Save point

• Set transaction

DATA TYPES OF SQL

• CHAR : This data type is used to store character strings values of fixed length. The size in brackets determines the number of characters the cell can hold. The maximum number of characters (i.e. the size) this data type can hold is 255 characters. Syntax is CHAR(SIZE)

 Example is CHAR (20)

• VARCHAR : This data type is used to store variable length alphanumeric data. The maximum this data type can hold is 4000 characters. One difference between this data type and the CHAR data type is ORACLE compares VARCHAR values using non-padded comparison semantics i.e. the inserted values will not be padded with spaces.  Syntax is VARCHAR(SIZE) 

 Example is VARCHAR (20) OR VARCHAR2 (20)

• NUMBER : 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. Numbers as large as 9.99 * 10 to the power of 124, i.e. followed by 125 zeros can be stored. The precision, (P), determines the maximum length of the data, whereas the scale, (S), determines the number of places to the right of the decimal. If scale is omitted then the default is zero. If precision is omitted values are stored with their original precision up to the maximum of 38 digits.

Syntax is NUMBER (P, S) Example is NUMBER (10, 2)

• 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. 

         Syntax is LONG (SIZE) Example is LONG (20)

• DATE : This data type is used to represent data and time. The standard format id DD-MM-YY as in 13-JUL-85. To enter dates other than the standard format, use the appropriate functions. Date Time stores date in the 24-hour format. By default, the time in a date field is 12:00:00 am, if no time portion is specified. The default date for a date field is the first day of the current month. Syntax is  DATE

• LONG RAW : LONG RAW data types are 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. LONG RAW data type can contain up to 2GB. Values stored in columns having LONG RAW data type cannot be indexed. Syntax is LONGRAW (SIZE) 

• RAW : It is used to hold strings of byte oriented data. Data type can have a maximum length of 255 bytes. Syntax is RAW(SIZE)

TRANSACTION STATEMENTS 

• COMMIT: A COMMIT ends the current transaction and makes permanent any changes made during the transaction. All transactional locks acquired on tables are released. Syntax is 

COMMIT [work] [comment text]; 

Where 

Work is optional and comment text is used to specify comment

• ROLLBACK : A ROLLBACK does exactly the opposite of COMMIT. It ends the transaction but undoes any changes made during the transaction. All transactional locks acquired on tables are released. Syntax is 

ROLLBACK [WORK] [TO [SAVEPOINT] save point]

Where 

WORK is optional and is provided for ANSI compatibility. SAVEPOINT is optional and is used to rollback a partial transaction, as far as  the specified save point. SAVEPOINT is a save point created during the current transaction.

• SAVEPOINT : SAVEPOINT marks and saves the current point in the processing of a transaction. When a SAVEPOINT is used with a ROLLBACK statement, parts of a transaction can be undone. An active save point is one that is specified since the last COMMIT or ROLLBACK. Syntax is 

SAVEPOINT savepointname;

Where 

Save point is an identifier and it is not to declared in declare section.

SQL COMMANDS

• CREATE TABLE:  A table is basic unit of storage. It is composed of rows and columns. To create a table we will name the table and the columns of the table. We follow the rules to name tables and columns:-

• It must begin with a letter and can be up to 30 characters long.

• It must not be duplicate and not any reserved word.

  Syntax to create a table is 

CREATE TABLE tablename (column_name1 datatype (size), column_name2 datatype (size) …);

Example is 

CREATE TABLE student (rollno number (4), name varchar2 (15));

• SELECT : The select command of sql lets you make queries on the database. A query is a command that is given certain specified information from the database tables. It can be used to retrieve a subset of rows or columns from one or more tables. 

Syntax to create a table is 

SELECT <column_name1>,<column_name2> FROM <tablename>;

Example is 

SELECT empno, ename, sal from emp;

• CREATE TABLE FROM ANOTHER TABLE : We can create a table by applying as subquery clause from another clause. It will create the table as well as insert the rows returned from subquery. Syntax to create table from another table is

CREATE TABLE tablename(<column1>,<column2>,……) 

AS SELECT <column1>,<column2> FROM <tablename>;

Example is 

CREATE TABLE stud(empno,ename,salary) 

AS SELECT empno,ename,sal FROM emp;

• ALTER TABLE : After creating a table one may have need to change the table either by add new columns or by modify existing columns. One can do so by using alter table command.

Syntax to add a column is

ALTER TABLE tablename ADD(col1 datatype,col2 datatype);

Syntax to modify a column is

ALTER TABLE tablename MODIFY(col1 datatype,col2 datatype);

• DROP TABLE : To remove the definition of oracle table, the drop table statement is used. Syntax to drop table is 

DROP TABLE tablename

• RENAME : One can change the name of a table by rename command Syntax to rename table is

RENAME oldname TO newname

• DELETE : One can delete data fron table by using delete from statement. The delete statement removes rows from table but it doesn’t release storage space. Syntax of delete rows from table is

DELETE FROM tablename WHERE <condition>;

• DESCRIBE : To find information about columns like column name, their data types and other attributes of a table we can use DESCRIBE command. Syntax to describe table is

DESCRIBE tablename;

• INSERT : To add new rows in an existing oracle table the insert command is used. Syntax to add new fields is

INSERT INTO tablename(col1,col2,col3,..)

VALUES(value1,value2,value3);

Example is

INSERT INTO employee(emp_id,ename,desg,basic_pay)

VALUES(100001,’MOHIT’,’MANAGER’,55000);

• UPDATE : The update command enables user to change the values of existing rows. Syntax to update value of table is

UPDATE tablename SET col1=value1,col2=value2;

Example is

UPDATE emp_info SET salary =salary +100;

• DISTINCT CLAUSE : The distinct keyword duplicates all rows from results of a select statement. Syntax to remove distinct rows is

SELECT DISTINCT <col1>,<cols2> FROM tablename;

Example is

SELECT DISTINCT job FROM emp;

• WHERE CLAUSE : The where clause specifies the criteria for selection of rows to be returned. Syntax is

SELECT <col1>,<col2> FROM tablename WHERE <cond>;

Example is

SELECT ename,sal FROM emp WHERE sal > 2000;

• ORDER BY CLAUSE : You can sort the results of query ina specific order using order by clause. It allows sorting of query results by one or more columns. It can be done either in ascending or descending. Syntax to sort result of query is

SELECT * FROM tablename ORDER BY col1,col2,col3;

Example is

SELECT * FROM emp ORDER BY ename desc;

• GROUP BY CLAUSE : The group clause is sued in select statement to divide the table into groups. Grouping can be done by column name or wih aggregate functions in which case the aggregate producing a value for each group.

 Syntax is

SELECT col1,col2 FROM tablename GROUP BY <col>;

Example is 

SELECT deptt,avg(salary) FROM emp_infoGROUP BY deptt; 

• HAVING CLAUSE : The having clause filters the group values created by group by clause. This clause can precede the group by clause but it is more logical if we place group by first. 

Syntax is 

SELECT col1,col2 FROM tablename GROUP BY <col>

HAVING <condition>;

Example is

SELECT deptt,max(salary) FROM emp_info GROUP BY deptt

HAVING max(salary) > 12000;

• IN: In operator is used to check a value as search from a domain of multiple values to perform a query. Not In acts as reverse of In. 

Syntax is 

SELECT <col1>,<col2> FROM tablename 

   WHERE <col> IN (value1,value2,value3);

Example is 

SELECT ename,address,city,state FROM employee  

   WHERE deptt IN (‘comp’,’electronics’,’english’);

• ALL : It retains duplicate output rows. All is essentially a clarifier rather than a functional argument. 

Syntax is 

SELECT ALL <column1>,<column2> FROM <tablename>;

Example is

SELECT ALL city FROM suppliers;

SET OPERATORS

 Set keywords are used to combine information of similar type 

from one or more than one table. Set operations and the operators are based on set theory. It consumes two or more queries into one result. The types of set operators are:-

• UNION : The union clause merges the outputs of multiple queries into a single set of rows and columns. It combines rows returned by two select statements by eliminating duplicate rows. Syntax is 

SELECT <statement> UNION SELECT <statements>;

Example is

SELECT designation FROM emp_info WHERE deptt=’comp’

UNION

SELECT designation FROM emp_info WHERE deptt=’eco’;

• INTERSECT : The intersect operator combines two select statements and return only those rows that are returned by both queries. Syntax is 

SELECT <statement> INTERSECT SELECT <statements>;

Example is

SELECT designation FROM emp_info WHERE deptt=’comp’

INTERSECT

SELECT designation FROM emp_info WHERE deptt=’eco’;

• MINUS : It combines the result of two queries and returns only those values that are selected by first query but not in second query. Syntax is

SELECT <statement> MINUS SELECT <statements>;

Example is

SELECT desgination FROM emp_info WHERE deptt=’comp’

MINUS

SELECT desgination FROM emp_info WHERE deptt=’eco’;

ENHANCING PERFORMANCE

Views :

A view is very commonly used database object that is derived at runtime. A view contains data of its own. Its contents are derived from another table. The command for creating view is CREATE VIEW command. Editing in the tables are automatically reflected in the views. It is virtual table & does not have any data of its own. Syntax to create a view is 

CREATE [OR REPLACE] VIEW view name AS sub query 

[WITH CHECK OPTION] [WITH READ ONLY];

Example is CREATE VIEW mohit AS SELECT empno, ename, sal, comm FROM emp;

Types of views are as follows:

• Join View:- It is defined as view that has more than one table specified in from clause and does not contain following clauses i.e. distinct, aggregation, group by. This type of view allows update, insert and delete command to change data in table. Syntax is 

CREATE OR REPLACE VIEW mohit AS SELECT ename, empno, sal FROM

emp, dept  WHERE emp.deptno = dept.deptno;

The views to be updateable must not include the following are

• Set operators , aggregate functions

• Distinct operator , rownum pseudo columns

• Group by clause , having clause 

• Inline View: - Oracle also offers an inline view that is very handy and inline view is part of SQL statements. It allows you in body of SQL statement to define SQL for view that SQL statement will use to resolve its query.

• Materialized View: - Snapshot also called materialized view. It is defined as copy of part of table or entire table. It reflects the current status of table that is being copied. The original status table is also called master table. Two types are Read only and update. Read-only does not allow changes to be made in view. It simply publishes and subscribes the replications. It allows changes in local copy which periodically updates master table.

Sequences :

Oracle provides an object called a Sequence that can generate 

numeric values. The value generated can have a maximum of 38 digits. A sequence can be defined to 

• generate numbers in  ascending or descending order.

• provide intervals between numbers.

• Caching of sequence numbers in memory.

A sequence is a database object used to generate unique integers for use as primary keys. Syntax is 

CREATE SEQUENCE sequence name 

[INCREMENT BY integervalue

            START WITH integervalue 

MAXVALUE integervalue / NOMAXVALUE

            MINVALUE integervalue / NOMINVALUE 

CYCLE / NOCYCLE

            CACHE integervalue / NOCACHE 

ORDER / NOORDER]

Example is 

CREATE SEQUENCE mohit

INCREMENT BY 1

START WITH 1

MINVALUE 1

MAXVALUE 9999

CYCLE; 

Indexes :

Index is a way to store and search records in the table. These are used to improve the speed with which records can be located and retrieved from the table. Oracle retrieves rows in table in one of two ways are:

• By ROWID

• By full table scan

The creation and dropping of index doesn’t affect the storage of data in the underlying tables.

 Indexes are primarily used for 2 reasons are: 

• To ensure the uniqueness of the indexed column’s values.

• To enhance performance.

Syntax to create an index is 

CREATE INDEX <index name> ON <tablename>(column name);

Syntax to drop an index is 

DROP INDEX <index name>;

The types of indexes are:-

• UNIQUE : It doesn’t allow duplicate values for indexed columns.

 Example is 

CREATE UNIQUE INDEX mohit ON EMP (ename);

• DUPLICATE :  It allows duplicate values for indexed columns.

Example is 

CREATE DUPLICATE INDEX mohit ON EMP (ename);

• COMPOSITE : multi column indexes.

Example is 

CREATE UNIQUE INDEX mohit ON EMP (ename, sal);

• FUNCTIONAL : perform some functions on fields and then indexes it. Example is 

CREATE INDEX mohit ON EMP (sal+100);

GRANTING AND REVOKING PERMISSIONS

• GRANT : The Grant statement provides various types of access to database 

objects such as tables, views and sequences. A privilege is consent to execute an action or to access another user object. These consents can be given by grant statement. Syntax is 

GRANT {object privileges} ON object name

TO username [WITH GRANT OPTION];

The WITH GRANT OPTION allows the grantee to in turn grant object privileges to other users.

Object privileges

Each object privileges that is granted authorizes the grantee to 

Perform some operation on the object. The user can grant all the privileges or grant only specific object privileges.

ALTER   allows the grantee to change the table definition with the.

  ALTER TABLE command.

DELETE allows the grantee to remove the records from the table with the    DELETE command.

INDEX   allows the grantee to create an index on the table with the 

  CREATE INDEX command.

INSERT   allows the grantee to add records to the table with the INSERT 

  Command.

SELECT   allows the grantee to query the table with the SELECT 

  Command.

UPDATE   allows the grantee to modify the records in the tables with the 

  UPDATE command.

Example to grant select & insert privileges on table item to user named as mohit.

GRANT SELECT, INSERT ON item TO mohit;

Example to grant all privileges on table EMP to user named as chiku.

GRANT ALL ON EMP TO mohit;

• REVOKE : Privileges once given can be denied to a user using the REVOKE command. The object owner can revoke privileges granted to another user. A user of an object who is not the owner, but has been granted the GRANT privilege, has the power to REVOKE the privileges from a grantee. The REVOKE statement is used to deny the grant given on an object.

Syntax is 

REVOKE {object privileges}

ON object name

FROM username;

   Example to revoke select & insert privileges on table item to user named as mohit.

REVOKE SELECT, INSERT ON item TO mohit;

  Example to revoke all privileges on table EMP to user named as chiku.

REVOKE ALL ON EMP TO mohit;

PATTERN MATCHING 

SQL includes string matching operator LIKE for comparison on character string using patterns. The LIKE predicate allows for a comparison of one string with another string value, which is not identical. This is achieved by using wildcard characters. The patterns are case sensitive and that is uppercase letters do not match lower case characters. The keyword NOT LIKE is used to select those rows that do not match the specified pattern of characters.

Two wildcard characters that are available are: 

For character data types: The percent sign (%) matches any string

    The Underscore (_) matches any single character

Example is 

SELECT * FROM EMP WHERE ename LIKE ‘_B%’;

SELECT * FROM EMP WHERE ename LIKE ‘MOH__’;

RANGE SEARCHING 

BETWEEN operator acts as range searching operator. To get a range of values from a query, you may use between operators. You must specify the lower and upper limit for range search. Syntax is 

SELECT <col1>,<col2> FROM table name 

   WHERE <col> BETWEEN lower limit and upper limit;

Example is

SELECT empname, deptt, basic pay FROM employee 

   WHERE basic pay BETWEEN 5000 and 10000;

JOINS

A JOIN can be recognized in sql select statement if its has more than one table after from keyword. This join condition is based on primary keys and foreign keys. There must be n-1 join conditions for n joins to tables. If join condition is omitted then the result is Cartesian product.

 Syntax is

SELECT “list of columns” FROM table1, table2 WHERE “condition”;

Types of joins are 

• Equi join : It returns all rows from tables where there is a match. Tables are joined on columns that have the same datatype & size in table. It is also known as equality join or simple join or inner join. Syntax is 

SELECTfield1,field2 FROM table1,table2 WHERE table1.field=table2.field;

Example is 

SELECTename, dname FROM emp, dept WHERE emp.deptno=dept.deptno;

• Cartesion join : When the join condition is omitted the result is Cartesian join of two or more tables in which all the combinations of rows will be displayed. All the rows are joined to all rows of the second table. Syntax is

SELECT field1, field2 FROM table1, table2;

Example is SELECTename, dname FROM emp, dept;

• Outer join : While using equi join we see that if there exists certain rows in one table which don’t have corresponding values in the second table thn those rows will not be selected. We can forcefully select those rows by outer join. The rows for those columns will have NULL values. Syntax is

SELECT table1.col, table2.col FROM table1, table2 

WHERE table1.col (+) = table2.col;

Example is 

SELECT empno, ename, emp.deptno, dname FROM emp, dept

WHERE emp.deptno (+) = dept.deptno;

• Self Join : The self join can be seen as join of two copies of the same table. The table is not actually copied but sql performs the command as though it were. Example is

SELECT e.ename, m.ename FROM emp e, emp m

WHERE e.mgr=e.empno;

SUBQUERIES

A sub query is a form of an SQL statement that appears inside another SQL statement. It is also termed as nested query. The statement containing a sub query is called a parent statement. The parent statement uses the rows returned by the sub query. It can be used by the following commands:

• To insert records in a target table.

• To create tables and insert records in the table created.

• To update records in a target table.

• To create views.

• To provide values for conditions in WHERE, HAVING, IN etc. used with SELECT, UPDATE, and DELETE statements.

Types of sub queries are 

• Single row : It returns one row from inner nested query. Example is 

SELECT deptno FROM emp WHERE ename =’MILLER’;

• Multiple row : Subqueries that return more than one row called multiple row queries. Operators like IN,ALL,ANY are used. Example is 

SELECT ename,sal,deptno FROM emp WHERE sal IN

(SELECT min(sal) FROM emp GROUP BY deptno);

• Multiple column : It returns more than one column .Example is 

SELECT ordid , prodid , qty FROM item WHERE (qty,prodid) IN

(SELECT prodid,qty FROM item WHERE ordid = 605 AND ordid <> 605);

CONSTRAINTS

            Constraints are the rules that restrict the data values inserted in columns

of a table. It helps in maintaining integrity of table at database level instead of application level.

All application can take advantage of these global constraints ,  rather   than     building  similar

logic in  individual  application . Constraints  can  constrain  single  column or  group          of

columns in a table. Constraints can be specified as two types:

• Column level constraint

• Table level constraint

Column level constraint : Column constraints are specified as part of column 

definition to constrain single column.

Table level constraint  : Table constraints is specified at end of create table 

statement to constraint more than one column.

Following are the constraints used in tables are

• NULL/NOT NULL : Specifies if the column must contain value or might not contain any. By default all columns in a table allows nulls, ie. Absence of a value in a column. NOT NULL specifies that all rows in the table to have value for specified column. All NOT NULL columns are mandatory fields. Syntax is 

COLUMN_NAME  DATA_TYPE(SIZE) NOT NULL 

      Example is

CREATE TABLE employee(empname varchar2(25) NOT NULL) ;

• UNIQUE : Specifies that all values in a column must be unique. Values in field cannot be duplicated. More than one Null value can be inserted in the column. Unique constraint can be placed on multiple columns. Syntax is 

COLUMN_NAME  DATA_TYPE(SIZE) UNIQUE     -- at column level

UNIQUE COLUMN_NAME  DATA_TYPE(SIZE)     -- at table level

      Example is

CREATE TABLE dept(deptt varchar2(25) UNIQUE);

CREATE TABLE dept(deptt varchar2(25),UNIQUE(dept));

• PRIMARY KEY : Primary key refers to one or more column values in a table that can uniquely identify each row in a table. A primary key column cannot contain nulls and is unique by default. Syntax is 

COLUMN DATA_TYPE(SIZE) PRIMARY KEY  -- at column level

PRIMARY KEY COLUMN DATA_TYPE(SIZE)  -- at table level

      Example is

CREATE TABLE dept(deptt varchar2(25) PRIMARY KEY);

CREATE TABLE dept(deptt varchar2(25),PRIMARY KEY(dept));

• FOREIGN KEY : A foreign key is a one or more column with values based on the primary key values from another table. A foreign key constraint specifies that the values stored in foreign column correspond to values of the primary key in the other table. Foreign key is also known as referential integrity constraint. The table containing foreign key is known as Dependent table and the table that is referenced by foreign key is called Referenced table. Syntax is 

COLUMN DATA_TYPE(SIZE) REFERENCES TABLE -- at column level

FOREIGN KEY COLUMN DATA_TYPE(SIZE) REFERENCES TABLE  

      Example is

CREATE TABLE dept(deptt varchar2(25) REFERENCES emp_info);

CREATE TABLE dept(deptt varchar2(25),PRIMARY KEY(dept) FOREIGN KEY(dept) REFERENCES emp_info);

FUNCTIONS

Functions make the result of the query easier and are used to manipulate the data values. Following are the functions are 

• String or character  functions 

• Number functions

• Aggregate functions or group functions 

• Date functions

• Conversion functions

Character Manipulation functions

• Ascii : It returns the ASCII decimal equivalent of a character passed as an argument. Example is  select ascii(‘b’)from dual;                    

• Chr : This function gives the result as a character corresponding to the value in the database set. Example is select char (97) from dual;

• concat(x1,x2) : This function concates the string x1 with x2. This function is equivalent to the | | operator. Examples select concat(‘mohit’,’garg’)from dual;

• instr :This function returns the first position of the character x in the string.

Example is select instr(‘mohit’,’o’) from dual;    

• length : It returns the length of the string x. 

Example is select length (‘mohit’) from dual;

• lpad : It pads the leading spaces to the left side of the column char1 with the char2, so that the total width becomes equal to the value n. If char2 is absent, it pads the char1 on the left side with blank spaces to the total width of n.

Example is select lpad(dname,12,’*’) from dept;

• Ltrim : It removes all the blank spaces from the left side of the stringif no char is specified. If we give a char, then it removes the leading occurrences of that charcter from the string. Example is select ltrim(dname.’S’) from dept;                  

• Rpad : This function pads the leading spaces to the right side of the column char1 with the char2, so that the total width becomes equl to the value n.

Example is select rpad(dname,12,’*’)from dept;

• Rtrim : This function removes all the blank spaces from the right side if no char is specified. Example is select rtrim(dname,’S’) from dept; 

• Replace : Where c1,c2,c3 and c3 are all character string .this function returns c1 with all occurenes of c2 replaced with c3, the default value of c3 is NULL.

Example is select replace (uptown,’up’,down’)from dual;

• Substr : It returns the substring from z, of length equal to y starting at position x . Example is select substr(‘mohitgarg’2,5) from dual;

• Initcap : This function returns the string with letter in caps.

Example is  select initcap (dname) from dept;

• Lower : This function converts the string into lower case.

Example is select lower (dname)from dept;

• Upper : It converts the string into upper case.

Example is select upper(dname)from dept;

• Translate : This function is used to find a char and replace it with new character. Example is select dname, translate (dname,’E’,2) from dept;

Group  functions 

• Count : This function returns the number of rows or non-null values for column x. Syntax is count([distinct|all]column name)

Example is select count(empno)from emp;

• Sum : This function ireturns the sum of values for the column x. This function is applied on columns having numeric datatype and it returns the numeric value.

Syntax is sum([distinct|all]column name)

Example is select sum(sal) from emp;

• Avg : Ths function returns the average of values for the column x. It ignores the null values in the column x. Syntax is avg([distinct|all]column name)

Example is select avg(sal),count(sal) from emp;

• Min : This function returns the minimum of values for the column x for all the rows. Syntax is min([distinct|all]column name)

Example is select min(sal) from emp;

• Max : This function returns the maximum of values for the column x for all the rows. Syntax  is max([distinct|all]column name)

Example is  select min(sal),max(sal) from emp;

Number functions 

• Abs : It obtains the absolute value for the number x. 

Example is 

Select abs(-10) from dual;

• Ceil : Where is a number. This function returns the smallest integer that is greater than or equal to n. 

Example is 

select ceil (9.8), ceil (-32.85) from dual;

• Cos : It returns trigonometric cosine of the number n.

Example is

select cos(45) from dual;    

• Exp : It returns the e raised to the x power. 

Example is 

select exp (4) from dual;

• Floor : This function returns the largest integer that is less than or equal to n. 

Example is 

select floor (9.8), floor (-32.85) from dual;     

• Mod : It returns the remainder when x is divided by the number y.

Example is 

select mod (10.3),mod (10.5)from dual;

• Power : This function returns the value of x raised to the power of y. The argument y should be an integer. 

Example is 

select power (2, 3) from dual;

• Round : It rounds off x to the decimal precision of y. If y is negative, rounds to the precision of y places to the left of the decimal point.

Example is 

select round (55.849,1 ), round(55.849) from dual;

• Sign : This function returns the sign of the value x. If x is a negative number it returns -1 otherwise it returns 1. 

Example is 

select sign (-2) from dual;

• Sqrt : This function returns the square root of the given number x.

Example is 

select sqrt(36) from dual;

Date functions 

• Add_months : It adds the months in specified date with the function. Syntax is

ADD_MONTHS(date,m) where date refers to date & m is number value.

Example is 

select add_months(sysdate,2) from dual;

• Last_day : It returns the last day of given month which is specified with function. 

Syntax is 

LAST_DAY(d) where d is date

Example is 

select last_day(sysdate) from dual;

• Months_between : It computes & returns the number of months b/w two dates. 

Syntax is 

MONTHS_BETWEEN(date1,date2) 

Example is 

select months_between(’13-mar-05’,’01-dec-05’) from dual;

• Next_day : It returns the date of first day of the week specified in a string after the beginning date. Syntax is NEXT_DAY(date,ch) where ch refers to character value. Example is select next_day(’13-mar-05’,’sunday’) from dual;