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