INTRODUCTION TO SQL-PLSQL

Introduction to SQL-PLSQL



How to Write and execute sql, pl/sql commands/programs:

1). Open your oracle application by the following navigation
Start->all programs->Oracle Database 10g Express Edition
->Run SQL Command Line

2). You will be asked for user name, password.
You have to enter user name, pass word.

3). Upon successful login you will get SQL prompt (SQL>).
In two ways you can write your programs:
a) directly at SQL prompt (or)
b) in sql editor.

If you type your programs at sql prompt then screen will look like follow:
SQL> SELECT ename,empno,
2 sal from
3 emp;
where 2 and 3 are the line numbers and rest is the command
/program……

to execute above program/command you have to press ‘/’ then enter.

Here editing the program is somewhat difficult; if you want to edit the previous command then you have to open sql editor (by default it displays the sql buffer contents). By giving ‘ed’ at sql prompt.(this is what I mentioned as a second method to type/enter the program).
in the sql editor you can do all the formatting/editing/file operations directly by selecting menu options provided by it.


To execute the program which saved; do the following
SQL> @ programname.sql (or)
SQL> Run programname.sql
Then press ‘\’ key and enter.
To save the day`s session ;do the following
SQL>commit;

This how we can write, edit and execute the sql command and
programs.

Always you have to save your programs in your own logins.

Background Theory

Oracle workgroup or server is the largest selling RDBMS product.it is
estimated that the combined sales of both these oracle database product
account for aroud 80% of the RDBMSsystems sold worldwide.
These products are constantly undergoing change and evolving. The
natural language of this RDBMS product is ANSI SQL,PL/SQL a superset
of ANSI SQL.oracle 8i and 9i also under stand SQLJ.

Oracle corp has also incorporated a full-fledged java virtual machine
into its database engine.since both executable share the same memory
space the JVM can communicate With the database engine with ease
and has direct access to oracle tables and their data.

SQL is structure query language.SQL contains different data types those
are
1. char(size)
2. varchar(size)
3. varchar2(size)
4. date
5. number(p,s)                 //** P-PRECISION           S-SCALE **//
6. number(size)
7. raw(size)
8. raw/long raw(size)


Different types of commands in SQL:

A).DDL commands: - To create a database objects
B).DML commands: - To manipulate data of a database objects
C).DQL command: - To retrieve the data from a database.
D).DCL/DTL commands: - To control the data of a database…


DDL commands:

1. The Create Table Command: - it defines each column of the table
uniquely. Each column has minimum of three attributes, a name , data
type and size.

Syntax:

Create table <table name> (<col1> <datatype>(<size>),<col2>
<datatype><size>));

Ex:
create table emp(empno number(4) primary key, ename char(10));

2. Modifying the structure of tables.
a)add new columns

Syntax:

Alter table <tablename> add(<new col><datatype(size),<new
col>datatype(size));

Ex:
alter table emp add(sal number(7,2));

3. Dropping a column from a table.

Syntax:
Alter table <tablename> drop column <col>;

Ex:
alter table emp drop column sal;

4. Modifying existing columns.

Syntax:
Alter table <tablename> modify(<col><newdatatype>(<newsize>));

Ex:
alter table emp modify(ename varchar2(15));





5. Renaming the tables

Syntax:

Rename <oldtable> to <new table>;

Ex:
rename emp to emp1;

6. truncating the tables.

Syntax:

Truncate table <tablename>;

Ex:

trunc table emp1;

7. Destroying tables.

Syntax:

Drop table <tablename>;

Ex:

drop table emp;

DML commands:

8. Inserting Data into Tables: - once a table is created the most
natural thing to do is load this table with data to be manipulated later.

Syntax 1:

insert into <tablename> (<col1>,<col2>…..<col n>) values(<val 1>,
<val 2>…….<val n>);

Syntax 2:

insert into <tablename> values(&<col1>,&<col2>……,&<col n>);



Syntax 3:

insert into <tablename> values(<val 1>,<val 2>…….,<val n>);

Ex 1:

Insert into skc (sname,rollno,class,dob,fee_paid)
values(‘sri’,’104B’,’cse’,’27-feb-05’,10000.00);

Ex 2:

insert into skc values(&sname,&roll no,&class);
enter sname:’sri’
enter roll no:’104B’
enter class:’cse’
1 row created.

Ex 3:

insert into skc values(‘sri’,’104B’,cse’,’27-feb-05’,10000.00);

9. Delete operations.

a) remove all rows

Syntax:

delete from <tablename>;

b) removal of a specified row/s

Syntax:

delete from <tablename> where <condition>;

10. Updating the contents of a table.

a) updating all rows

Syntax:
Update <tablename> set <col>=<exp>,<col>=<exp>;




b) updating seleted records.

Syntax:
Update <tablename> set <col>=<exp>,<col>=<exp>
where <condition>;


11. Types of data constrains.

a) not null constraint at column level.

Syntax:

<col><datatype>(size)not null

b) unique constraint

Syntax:

Unique constraint at column level.
<col><datatype>(size)unique;

c) unique constraint at table level:

Syntax:

Create table
tablename(col=format,col=format,unique(<col1>,<col2>);

d) primary key constraint at column level

Syntax:

<col><datatype>(size)primary key;

e) primary key constraint at table level.

Syntax:

Create table tablename(col=format,col=format
primary key(col1>,<col2>);




f) foreign key constraint at column level.

Syntax:

<col><datatype>(size>) references <tablename>[<col>];

g) foreign key constraint at table level

Syntax:

foreign key(<col>[,<col>]) references
<tablename>[(<col>,<col>)

h) check constraint

check constraint constraint at column level.

Syntax: <col><datatype>(size) check(<logical expression>)

i) check constraint constraint at table level.

Syntax: check(<logical expression>)

DQL Commands:

12. Viewing data in the tables: - once data has been inserted into a
table, the next most logical operation would be to view what has been
inserted.

a) all rows and all columns

Syntax:
Select <col> to <col n> from tablename;
Select * from tablename;

13. Filtering table data: - while viewing data from a table, it is rare
that all the data from table will be required each time. Hence, sql must
give us a method of filtering out data that is not required data.

a) Selected columns and all rows:
Syntax:
select <col1>,<col2> from <tablename>;



b) selected rows and all columns:
Syntax:
select * from <tablename> where <condition>;

c) selected columns and selected rows
Syntax:
select <col1>,<col2> from <tablename> where<condition>;

14. Sorting data in a table.

Syntax:
Select * from <tablename> order by <col1>,<col2> <[sortorder]>;

DCL commands:

Oracle provides extensive feature in order to safeguard information
stored in its tables from unauthoraised viewing and damage.The rights
that allow the user of some or all oracle resources on the server are
called privileges.

a) Grant privileges using the GRANT statement
The grant statement provides various types of access to database
objects such as tables,views and sequences and so on.

Syntax:
GRANT <object privileges>
ON <objectname>
TO<username>
[WITH GRANT OPTION];

b) Reoke permissions using the REVOKE statement:
The REVOKE statement is used to deny the Grant given on an object.

Syntax:
REVOKE<object privilege>
ON
FROM<user name>;







                                 WEEK-1

CREATING,ALTERING AND DROPPING TABLES AND INSERTING ROWS INTO A TABLE (USE CONSTRAINTS WHILE CREATING TABLES) EXAMPLES USING
                                  SELECT COMMAND .

EXAMPLE 1:

CREATING A STUDENT RELATION TABLE WITH ALL DATATYPES:

SQL> create table student252(
sid number(5),
sname varchar(20),
sbranch char(5),
dob date,
spercent number(3,2));

Table created.

RELATIONAL SCHEMA FOR STUDENT RELATION :
SQL> desc student252;
Name                                         Null?                   Type
----------------------------------------- -------- ----------------------------
SID                                                                              NUMBER(5)
SNAME                                                                      VARCHAR2(20)
SBRANCH                                                                 CHAR(5)
DOB                                                                           DATE
SPERCENT                                                              NUMBER(5,2)

INSERT THE RECORDS INTO STUDENT RELATION:

METHOD 1:
SQL>Insert into
Student252(sid,sname,sbranch,dob,spercent) values(104,‘sri’,,’cse’,’27-
feb-05’,70);
1 row created.

METHOD 2:
SQL>Insert into
Student252 values(104,‘sri’,,’cse’,’27-feb-05’,70);
1 row created.



METHOD 3:
SQL>Insert into
Student252(sid,sname,sbranch,dob,spercent)
values(&sid, &sname,&sbranch,&dob,&spercent);
1 row created.

METHOD 4:
SQL>Insert into
Student252(sid,sname,sbranch,dob,spercent)
values(&sid, ‘&sname’,’&sbranch’,’&dob’,&spercent);
1 row created.

QUERY THE TABLE VALUES:

ALL ROWS AND ALL COLUMNS:

SQL> select * from student252;
SID      SNAME          SBRANCH     DOB               SPERCENT
------ --------------- --------------------- --------------- --------------------
130     ravi                              it         30-1-95                      60
131     teja                             cse      21-07-87                    55
129     kiran                            mech   12-05-92                    60
104     sri                                cse     30-07-90                    70
133     sajith                           eee     12-06-89                    55
137     ram                             ece      07-07-85                    40



WEEK 2 (cont…1)
1) Creation, altering and dropping tables and inserting rows into a table (use constraints while creating tables) examples using SELECT command.

MODIFYING THE STRUCTURE OF TABLE

 ADDING A NEW COLUMN

SQL> ALTER TABLE Emp252
ADD (age number(3),
phno number(10));

Table altered.




 MODIFYING  EXISTING COLUMN

SQL> ALTER TABLE Emp252
MODIFY (phno varchar(20));

Table altered.

DROPING  A COLUMN

SQL> ALTER TABLE Emp252
DROP COLUMN phno;

Table altered.

QUERY FOR THE TABLE VALUES
SQL> SELECT *
FROM Emp252;

ENO   ENAME        ESAL     DEPTNO        AGE                    
----- -------------------- ---------- ---------- ----------                    
30    ravi                      51000          3                               
31    teja                       31000          2                               
29    kiran                     31200          1                               
45    allen                      41000          3                               
33    sajith                     51000          4                               
46    geetha                    11000          4                               
90    veena                     16000          3                               
85    pragna                    61000          1                               
84    harsha                    91000          3                               
40    sanjeev                     1500         13                               

10 rows selected.

UPDATING  ENTIRE COLUMN

SQL> UPDATE Emp252
SET age=18;

10 rows updated.

QUERY THE TABLE VALUES
SQL> SELECT *
FROM Emp252;



ENO   ENAME         ESAL    DEPTNO    AGE                    
----- -------------------- ---------- ---------- ----------                    
30    ravi                      51000          3         18                    
31    teja                      31000          2          18                    
29    kiran                     31200          1         18                    
45    allen                     41000          3         18                    
33    sajith                    51000          4         18                    
46    geetha                    11000          4         18                    
90    veena                     16000          3         18                    
85    pragna                    61000          1         18                    
84    harsha                    91000          3         18                    
40    sanjeev                    1500         13         18                    

10 rows selected.

RENAMING THE TABLE:

SQL> RENAME Emp252
TO Emp1252;

Table renamed.

SELECTING THE TABLE VALUES
SQL> SELECT *
FROM Emp1252;


Example 3

CREATING A DEPARTMENT RELATION TABLE

CREATING A DEPARTMENT TABLE

SQL> CREATE TABLE Dept252(
dname VARCHAR(10),
dno CHAR(5),
dloc VARCHAR(25));

Table created.







 DESCRIBE A STUDENT TABLE

SQL> desc Dept252;
 Name                                              Null?    Type
 ----------------------------------------- -------- ----------------------------
 DNAME                                                      VARCHAR2(10)
 DNO                                                            CHAR(5)
 DLOC                                                          VARCHAR2(25)

DROPING THE TABLE
SQL> DROP TABLE Dept252;

Table dropped.

WEEK 3 (cont…1)

1) Creation, altering and dropping tables and inserting rows into a table (use constraints while creating tables) examples using SELECT command.

CREATING A TABLE WITH KEY CONSTRAINTS

Example 1

CREATING A TABLE WITH ‘UNIQUE ‘,  ‘NOT NULL’,  ‘CHECK’ AND ‘DEFAULT’  CONSTRAINT:

SQL> CREATE TABLE emp252
(eid NUMBER(5) UNIQUE,
 ename VARCHAR(10) DEFAULT(‘UNKNOWN’),
 age NUMBER(3) NOT NULL,
 esal NUMBER(7) CHECK(esal > 1000));

Table created.

INSERTING RECORDS INTO TABLE:

SQL> INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal);
Enter value for eid: 1
Enter value for ename: 'ravi'
Enter value for age: 18
Enter value for esal: 10000
old   1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
new   1: INSERT INTO emp252 VALUES (1, 'ravi', 18, 10000)

1 row created.

SQL> /
Enter value for eid: 2
Enter value for ename: 'teja'
Enter value for age: 18
Enter value for esal: 20000
old   1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
new   1: INSERT INTO emp252 VALUES (2, 'teja', 18, 20000)

1 row created.

SQL> /
Enter value for eid: 3
Enter value for ename: 'kiran'
Enter value for age: 19
Enter value for esal: 25000
old   1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
new   1: INSERT INTO emp252 VALUES (3, 'kiran', 19, 25000)

1 row created.

SQL> /
Enter value for eid: 4
Enter value for ename: 'srinivas'
Enter value for age: 19
Enter value for esal: 30000
old   1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
new   1: INSERT INTO emp252 VALUES (4, 'srinivas', 19, 30000)

1 row created.

SQL> /
Enter value for eid: 1
Enter value for ename: 'alan'
Enter value for age: 19
Enter value for esal: 29000
old   1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
new   1: INSERT INTO emp252 VALUES (1, 'alan', 19, 29000)
INSERT INTO emp252 VALUES (1, 'alan', 19, 29000)
[SHOWING AN ERROR WHILE VIOLATING UNIQUE KEY CONSTRAINT]
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C003875) violated
   
SQL> /
Enter value for eid: 7
Enter value for ename: 'dravid'
Enter value for age: null
Enter value for esal: 100000
old   1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
new   1: INSERT INTO emp252 VALUES (7, 'dravid', null, 100000)
INSERT INTO emp252 VALUES (7, 'dravid', null, 100000)
 [SHOWING AN ERROR AS NOT NULL KEY CONSTRAINT IS VIOLATED]                                    *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYSTEM"."EMP230"."AGE")


SQL> /
Enter value for eid: 8
Enter value for ename: 'sachin'
Enter value for age: 35
Enter value for esal: 100
old   1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
new   1: INSERT INTO emp252 VALUES (8, 'sachin', 35, 100)
INSERT INTO emp252 VALUES (8, 'sachin', 35, 100)
*
[NOT ALLOWING AS IT VOILATES CHECK CONSTRAINT FOR esal > 1000  VALUE]
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.SYS_C003874) violated




Example 2

CREATING A TABLE WITH ‘PRIMARY KEY’ CONSTRAINT:

SQL> CREATE TABLE mdept252
(dno NUMBER(5),
 dname CHAR(10),
 dloc VARCHAR(10),
 PRIMARY KEY (dno));

Table created.

SQL> desc mdept252;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DNO                                       NOT NULL NUMBER(5)
 DNAME                                              CHAR(10)
 DLOC                                               VARCHAR2(10)

INSERTING RECORDS INTO MASTER DEPARTMENT TABLE:

SQL> INSERT INTO mdept252 VALUES (&dno, &dname, &dloc);
Enter value for dno: 1
Enter value for dname: 'ravi'
Enter value for dloc: 'hyd'
old   1: INSERT INTO mdept252 VALUES (&dno, &dname, &dloc)
new   1: INSERT INTO mdept252 VALUES (1, 'ravi', 'hyd')

1 row created.

SQL> /
Enter value for dno: 1
Enter value for dname: 'teja'
Enter value for dloc: 'sec'
old   1: INSERT INTO mdept252 VALUES (&dno, &dname, &dloc)
new   1: INSERT INTO mdept252 VALUES (1, 'teja', 'sec')
INSERT INTO mdept252 VALUES (1, 'teja', 'sec')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C003876) violated


SQL> /
Enter value for dno: null
Enter value for dname: 'sajithulhuq'
Enter value for dloc: 'kmm'
old   1: INSERT INTO mdept252 VALUES (&dno, &dname, &dloc)
new   1: INSERT INTO mdept252 VALUES (null, 'sajithulhuq', 'kmm')
INSERT INTO mdept252 VALUES (null, 'sajithulhuq', 'kmm')
                            *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYSTEM"."MDEPT230"."DNO")

ADDING A PRIMARY KEY TO AN EXISTING TABLE:

SQL> ALTER TABLE student252 ADD PRIMARY KEY (sid);

Table altered.

SQL> ALTER TABLE emp252 ADD PRIMARY KEY (eid);
ALTER TABLE emp252 ADD PRIMARY KEY (eid)
                       *
[ GIVING AN ERROR AS ONE TABLE CAN HAVE A SINGLE PRIMARY KEY AT COLUMN LAVEL]
ERROR at line 1:
ORA-02261: such unique or primary key already exists in the table

Example 3

CREATING A TABLE WITH ‘FORIEGN KEY’ CONSTRAINT:

SQL> CREATE TABLE detailemp252
(eid NUMBER(5) REFERENCES mdept230 (dno),
 ename VARCHAR(10),
 esal NUMBER(7));

Table created.



INSERING RECORDS INTO DETAIL EMPLOYEE TABLE:

SQL> INSERT INTO detailemp252 VALUES (2, 'ravi', 50000);
INSERT INTO detailemp252 VALUES (2, 'ravi', 50000)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.SYS_C003877) violated - parent key not
found


SQL> INSERT INTO detailemp252 VALUES (1, 'teja', 60000);

1 row created.

SQL> DELETE FROM mdept252 where dno=1;
DELETE FROM mdept252 where dno=1
*
ERROR at line 1:
ORA-02292: integrity constraint (SYSTEM.SYS_C003877) violated - child record
found

SQL> SELECT * FROM detailemp252;

     EID ENAME            ESAL                                               
---------- ---------- ----------                                                
         1 teja            60000                                               

SQL> SELECT * FROM mdept252;

       DNO DNAME      DLOC                                                     
---------- ---------- ----------                                                
         1 ravi       hyd      



Exercise
  
CREATING A CUSTOMER TABLE USING CONSTRAINTS   :                                       

SQL> CREATE TABLE cust252
(cnum NUMBER(5),
 cname VARCHAR(10),
 state VARCHAR(10) DEFAULT ('ap'),
 phno NUMBER(5),
 CONSTRAINT cnum_pkkey PRIMARY KEY (cnum));

Table created.

SQL> INSERT INTO cust252 VALUES (&cnum, &cname, &state, &phno);
Enter value for cnum: 1
Enter value for cname: 'ravi'
Enter value for state: 'bihar'
Enter value for phno: 001
old   2: (&cnum, &cname, &state, &phno)
new   2: (1, 'ravi', 'bihar', 001)

1 row created.

SQL> /
Enter value for cnum: 2
Enter value for cname: 'teja'
Enter value for state: 'up'
Enter value for phno: 007
old   2: (&cnum, &cname, &state, &phno)
new   2: (2, 'teja', 'up', 007)

1 row created.

SQL> /
Enter value for cnum: 2
Enter value for cname: 'yama'
Enter value for state: 'ap'
Enter value for phno: 006
old   2: (&cnum, &cname, &state, &phno)
new   2: (2, 'yama', 'ap', 006)
INSERT INTO cust252 VALUES
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.CNUM_PKKEY) violated


SQL> /
Enter value for cnum: 4
Enter value for cname: 'huu'
Enter value for state: 'ap'
Enter value for phno: 101
old   2: (&cnum, &cname, &state, &phno)
new   2: (4, 'huu', 'ap', 101)

1 row created.

SQL> SELECT * FROM cust252;

      CNUM CNAME      STATE            PHNO                                    
---------- ---------- ---------- ----------                                    
         1 ravi       bihar               1                                    
         2 teja       up                  7                                    
         4 huu        ap                101           

 CREATING AN ITEM TABLE USING CONSTRAINTS:                  

SQL> CREATE TABLE itm252
(ino NUMBER(3),
 iname VARCHAR(10),
 iprice NUMBER(4,3),
 qtyonhand VARCHAR(5),
 CONSTRAINT itm252_ino_pkkey PRIMARY KEY (ino),
 CONSTRAINT itm230_qtyoh_chk CHECK (qtyonhand>1));

Table created.

SQL> INSERT INTO itm252 VALUES (&ino, &iname, &iprice, &qtyonhand);
Enter value for ino: 1
Enter value for iname: 'rubber'
Enter value for iprice: 3.50
Enter value for qtyonhand: 3
old   1: INSERT INTO itm252 VALUES (&ino, &iname, &iprice, &qtyonhand)
new   1: INSERT INTO itm252 VALUES (1, 'rubber', 3.50, 3)

1 row created.

SQL> /
Enter value for ino: 1
Enter value for iname: 'pencil'
Enter value for iprice: 1.00
Enter value for qtyonhand: 3
old   1: INSERT INTO itm252 VALUES (&ino, &iname, &iprice, &qtyonhand)
new   1: INSERT INTO itm252 VALUES (1, 'pencil', 1.00, 3)
INSERT INTO itm252 VALUES (1, 'pencil', 1.00, 3)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.ITM230_INO_PKKEY) violated





SQL> /
Enter value for ino: 2
Enter value for iname: 'powder'
Enter value for iprice: 3.00
Enter value for qtyonhand: 0
old   1: INSERT INTO itm252 VALUES (&ino, &iname, &iprice, &qtyonhand)
new   1: INSERT INTO itm252 VALUES (2, 'powder', 3.00, 0)
INSERT INTO itm252 VALUES (2, 'powder', 3.00, 0)
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.ITM230_QTYOH_CHK) violated



  1* CREATE TABLE invoice252(ivnno NUMBER(5), itemno NUMBER(5), qty NOT NULL, CONSTRAINT invoice252_ivnno_pkkey PRIMARY KEY(ivnno), CONSTRAINT FOREIGN KEY(itemno) REFERENCES cust252)

SQL> desc cust252;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CNUM                                              NOT NULL NUMBER(5)
 CNAME                                             VARCHAR2(10)
 STATE                                               VARCHAR2(10)
 PHNO                                                 NUMBER(5)

CREATING A INVOICE TABLE USING  CONSTRAINTS:

SQL> CREATE TABLE invoice252
(ivnno NUMBER(5),
 itemno NUMBER(5),
 qty NUMBER(5) NOT NULL,
 CONSTRAINT invoice252_ivnno_pkkey PRIMARY KEY (ivnno),
 CONSTRAINT fk_inv252 FOREIGN KEY (itemno) REFERENCES cust252 (cnum))
                                                                                                                                                           Table created.

SQL> CREATE TABLE invitm252
(invno NUMBER(5),
 itmno NUMBER(5),
 qty NUMBER(5) NOT NULL,
 CONSTRAINT invitm252_invno_itmno_pkkey PRIMARY KEY (invno, itmno));

Table created.








WEEK 4

2) Queries (along with subqueries) using ANY, ALL, IN, EXISTS, NOT EXISTS, UNIQUE, INTERSECT, Constraints.
Example: select the rollno and name of the student who secured 4th rank in the class


TABLE DEFINITIONS

SQL> CREATE TABLE Customer (
                        cust_no            NUMBER(4)               PRIMARY KEY,
                        last_name         VARCHAR2(20),
                        first_name         VARCJHAR2(20)       NOT NULL,
                        address1          VARCHAR2(20),
                        address2          VARCHAR2(20),
                        city                   VARCHAR2(3),
                        state                 VARCHAR2(20),
                        pin                    VARCHAR2(6),
                        birth_date         DATE,
                        status                VARCHAR2(1),
                                    CHECH (status IN (‘V’, ‘I’, ‘A’))
            );

Table created.

Insert the following data:

1 row created.

CUST NO
LAST NAME
FIRST NAME
ADDRESS1
ADDRESS2
CITY
STATE
PIN
BIRTH DATE
STATUS
1001
UDUPI
RAJ
UPENDRABAUG
NEAR KALPANA
UDPP
KARNARATA
576101
12-DEC-62
A
1002
KUMAR
RAJ






A
1003
BAHADUR
RAJ
SHANTHI VILLA
NEAR MALLIKA
UDP
KARNATAKA
576101
1-AUG-70
V
1004
SIMON
FELIX
M-J-56
ALTOBETIM
PJM
GOA
403002
12-FEB-71
A
1005
KUTTY
RAJAN
A1 TRADERS
NEAR RLY STATION
KNR
KERALA
67001
9-JUN-71
A
1006
PAI
SHILPA
12/4B
POLICE QUARTERS
MNG
KARNATAKA
574154
11-DEC-70
I
1007
JAIN
RAKSHIT
BOSCO
R.K PLAZA
BNG
KARNATAKA
576201
1-JAN-71
A





QUERIES

1)      To list all the fields from the table Customer.
SEELCT *
FROM Customer;

2)      To list the first name, last name.
SELECT first_name, last_name
FROM Customer;

3)      To list the first name and last name of persons in Karnataka.
SELECT first_name, last_name
FROM Customer
WHERE state = ‘KARNATAKA’;

4)      To list all the columns for invalid persons.
SELECT *
FROM Customer
WHERE status = ‘I’;

5)      To list the names of active customers.
SELECT first_name, last_name
FROM Customer
WHERE status = ‘A’;

6)      To list the name and address using concatenation.
SELECT first_name || ‘ ‘ ||  last_name, address1 || ‘,’ || address2 || ‘,’ || city || ‘,’ || state || ‘-‘ || pin
FROM Customer;

7)      To select records where the pin code has not been entered.
SELECT *
FROM Customer
WHERE pin IS NULL;

8)      To select the single occurrence of any value from the table.
SELECT DISTINCT state
FROM Customer;

9)      To select rows of valid customers from Karnataka.
SELECT *
FROM Customer
WHERE state = ‘KARNATAKA’
AND status = ‘V’;


10)   To select rows of customers from Karnataka or Kerala.
SELECT *
FROM Customer
WHERE state = KARNATAKA’
OR state = ‘KERALA’;

11)   To sort the customer data in the alphabetic order of state.
SELECT state, first_name, last_name, pin
FROM Customer
ORDER BY state;

12)  To sort in the descending order.
SELECT state, first_name, last_name, pin
FROM Customer
ORDER BY state DESC;

13)  To sort the customer data, state wise and within state by the last name.
SELECT state, first_name, last_name, pin
FROM Customer
ORDER BY state, last_name;

14)  To retrieve records of Karnataka customers who are valid.
SELECT *
FROM Customer
WHERE UPPER(state) = ‘KARNATAKA’
AND UPPER(status) = ‘V’;

15)  To retrieve records of Karnataka/Kerala customers.
SELECT *
FROM Customer
WHERE UPPER(state) = ‘KARNATAKA’
OR UPPER(state) = ‘KERALA’;

16)  To retrieve records of Karnataka/Kerala customers who are active.
SELECT *
FROM Customer
WHERE (UPPER(state) = ‘KARNATAKA’
 OR UPPER(state) = ‘KERALA’)
AND UPPER(status) = ‘A’;

17)  To retrieve records of Karnataka customers with pin code 576101.
SELECT *
FROM Customer
WHERE LOWER(state) = ‘karnataka’
AND pin = ‘576101’;

18)  To retrieve rows where the state name begins with K and followed by any other character.
SELECT first_name, last_name, state
FROM Customer
WHERE state LIKE ‘K%’;

19)   To retrieve rows where the first name contains the word RAJ embedded in it.
SELECT first_name, last_name, state
FROM Customer
WHERE first_name LIKE ‘%RAJ%’;

20)   To retrieve rows where the address2 contains the word UDUPI or UDIPI in which the 3rd character may be anything.
SELECT first_name, last_name, state
FROM Customer
WHERE address2 LIKE ‘UD_PI’;

21)   To retrieve rows where the cust_no has data representing any value between 1003 and 1005, both numbers included.
SELECT *
FROM Customer
WHERE cust_no BETWEEN 1003 AND 1005;

22)   To retrieve rows of persons born after 9-JAN-70 and before 1-AUG-96.
SELECT *
FROM Customer
WHERE birth_date BETWEEN ’10-JAN-70’ AND ’31-JUL-96’;

23)   To retrieve rows where the city has data which is equal to UDP or MNG or BNG or PJM or MAR.
SELECT *
FROM Customer
WHERE city IN (‘UDP’, ‘MNG’, ‘BNG’, ‘PJM’, ‘MAR’);




TABLE DEFINITIONS

SQL> CREATE TABLE Emp (
                        emp_no            NUMBER,
                        emp_name        VARCHAR(20),
                        join_date          DATE,
                        join_basic         NUMBER(7, 2),
                        PRIMARY KEY (emp_no)
            );
Table created.

Insert the following data:

EMP NO
EMP  NAME
JOIN DATE
JOIN BASIC
1001
Subhas bose
01-JUN-96
3000
1002
Nadeem shah
01-JUN-96
2500
1003
Charles babbage
01-JUN-96
3000
1004
Shreyas kumar
01-JUL-96
2500
1005
George boole
01-JUL-96
2800


SQL> CREATE TABLE Salary (
                                emp_no  NUMBER,
                                basic                       NUMBER(7, 2),
                                commission           NUMBER(7, 2),
                                deduction              NUMBER(7, 2),
                                salary_date           DATE,
                                FOREIGN KEY (emp_no) REFERENCES Emp
                );

Table created.

Insert the following data:

EMP NO
BASIC
COMMISSION
DEDUCTION
SALARY DATE
1001
3000
200
250
30-JUN-96
1002
2500
120
200
30-JUN-96
1003
3000
500
290
30-JUN-96
1004
2500
200
300
30-JUN-96
1005
2800
100
250
30-JUN-96
1001
3000
200
250
31-JUL-96
1002
2500
120
200
31-JUL-96
1003
3000
500
290
31-JUL-96
1004
2500
200
300
31-JUL-96
1005
2800
100
150
31-JUL-96


QUERIES

1)      To sum the salary of each employee.
SELECT emp_no, SUM(basic)
FROM salary
GROUP BY emp_no;

2)      To sum the salary of each employee and sort it on the sum of basic.
SELECT emp_no, SUM(basic)
FROM salary
GROUP BY emp_no
ORDER BY SUM(basic);

3)      To sum the salary of each employee and sort it in descending order on the sum of basic.
SELECT emp_no, SUM(basic)
FROM salary
GROUP BY emp_no
ORDER BY SUM(basic) DESC;

4)      To sum the salary of each employee and sort it in descending order on the sum of basic. Display name also
SELECT s.emp_no, e.emp_name, SUM(s.basic)
FROM salary s, emp e
WHERE s.emp_no = e.emp_no
GROUP BY s.emp_no, e.emp_no
ORDER BY SUM(s.basic) DESC;

5)      To group the data by average salary of each employee.
SELECT s.emp_no, INITCAP(e.emp_name), AVG(s.basic)
FROM salary s, emp e
WHERE s.emp_no = e.emp_no
GROUP BY s.emp_no, e.emp_no
ORDER BY AVG(s.basic);

6)      To group the basic by month.
SELECT TO_CHAR(salary_date, ‘MONTH’) “MONTH”, SUM(basic) “TOTAL BASIC”
FROM salary
GROUP BY TO_CHAR(salary_date, ‘MONTH’);

7)      To group the data by average salary of each employee and display where average  basic is more than 2000..
SELECT s.emp_no, INITCAP(e.emp_name), AVG(s.basic)
FROM salary s, emp e
WHERE s.emp_no = e.emp_no
GROUP BY s.emp_no, e.emp_no            
HAVING AVG(s.basic) >= 2000
ORDER BY AVG(s.basic);


SUBQUERIES

8)      To list the employees who earn less than the average salary.
SELECT *
FROM salary
WHERE basic < (SELECT AVG(basic)
                                               FROM salary);


9)      To list the employees whose deduction is 150.
SELECT *
FROM salary
WHERE emp_no IN (SELECT emp_no
                                    FROM salary
WHERE deduction = 150);
10)  To list the names of employees and salary details, whose basic is less than the average salary.
SELECT s.*, e.emp_name
FROM salary s, emp e
WHERE s.emp_no = e.emp_no
AND s.basic < (SELECT AVG(basic)
                             FROM salary);



WEEK 5


2)    Queries (along with subqueries) using ANY, ALL, IN, EXISTS, NOT EXISTS, UNIQUE, INTERSECT, Constraints. Example: select the rollno and name of the student who secured 4th rank in the class.

3)    Queries using Aggregate functions (COUNT, SUM, AVG, MAX and MIN), GROUP BY, HAVING and Creation and Dropping of Views.

4)    Queries using Conversions, functions (to_char, to_num, and to_date), string function (Conactenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap, length, substr, and instr), date functions (sysdate, next_day, add_months, last_day, months_between, least, greatest, trunk, round, to_char, to_date).