TABLE CREATION:
SQL>CREATE TABLE employee(e_no number(10),e_name varchar(15),e_age number(5),d_no number(10),salary number(10));
Table created.
INSERTION:
SQL> insert into employee
values (&e_no,'&e_name', &e_age, &d_no,&salary);
Enter value for e_no: 5025
Enter value for e_name: John
Enter value for e_age: 21
Enter value for d_no: 02
Enter value for salary: 35000
Old 1: values (&e_no,'&e_name', &e_age, &d_no, &salary)
New 1: values (1,'John', 22, 02, 35000)
1 row created.
SQL>/
Enter value for e_no: 5032
Enter value for e_name: Jithin
Enter value for e_age: 20
Enter value for d_no: 02
Enter value for salary: 20000
Old 2: values (&e_no,'&e_name', &e_age, &d_no, &salary)
New 2: values (1,'Jithin', 21, 02, 20000)
1 row created.
Enter value for e_no: 5033
Enter value for e_name: Midhu
Enter value for e_age: 19
Enter value for d_no: 03
Enter value for salary: 20000
Old 3: values (&e_no,'&e_name', &e_age, &d_no, &salary)
New 3: values (1,' Midhu', 21, 03, 20000)
1 row created.
SQL>/
Enter value for e_no: 5047
Enter value for e_name: Stephen
Enter value for e_age: 20
Enter value for d_no: 01
Enter value for salary: 25000
Old 4: values (&e_no,'&e_name', &e_age, &d_no, &salary)
New 4: values (1,'Stephen', 21, 01, 25000)
1 row created.
Enter value for e_no: 5001
Enter value for e_name: Aswin
Enter value for e_age: 19
Enter value for d_no: 04
Enter value for salary: 30000
Old 5: values (&e_no,'&e_name', &e_age, &d_no, &salary)
New 5: values (1,'Aswin', 20, 04, 30000)
1 row created.
SQL> select * from employee;
E_NO E_NAME E_AGE D_NO SALARY
---------- ---------- ---------- ---------- ---------- ---------------
5025 John 21 02 35000
5032 Jithin 20 02 20000
5033 Midhu 19 03 20000
5047 Stephen 20 01 25000
5001 Aswin 19 04 30000
SQL>UPDATE employee SET e_name=’ Midhun’
WHERE e_no=5033;
Updated.
SQL> select * from employee;
E_NO E_NAME E_AGE D_NO SALARY
---------- ---------- ---------- ---------- ---------- ---------------
5025 John 21 02 35000
5032 Jithin 20 02 20000
5033 Midhun 19 03 20000
5047 Stephen 20 01 25000
5001 Aswin 19 04 30000
SQL>DELETE from employee WHERE employee=5001;
Deleted.
SQL> select * from employee;
E_NO E_NAME E_AGE D_NO SALARY
---------- ---------- ---------- ---------- ---------- ---------------
5025 John 21 02 35000
5032 Jithin 20 02 20000
5033 Midhun 19 03 20000
5047 Stephen 20 01 25000
SQL> select e_name
from employee
order by e_name DESC;
E_NAME
--------------
Stephen
Midhun
Jithin
John
SQL> select e_name, e_age
from employee
order by e_age desc, e_name asc;
E_NAME E_AGE
---------- -------------------
Midhun 19
Stephen 20
Jithin 20
John 21
SQL>SELECT * from employee WHERE salary>20000 AND salary<35000;
E_NO E_NAME E_AGE D_NO SALARY
---------- ---------- ---------- ---------- ---------- ---------------
5047 Stephen 20 01 25000
SQL> select e_name,e_no,salary
from employee
where e_age>20and salary>20000;
E_NAME E_NO SALARY
---------- ---------- -------------------------
John 5025 35000
GROUP FUNCTIONS:
SQL> select avg (salary)
from employee;
AVG (SALARY)
----------------------
25000
SQL> select max (e_age)
from employee;
MAX (E_AGE)
---------------------
21
SQL> select min (salary)
from employee;
MIN (SALARY)
---------------------
20000
SQL> select sum (salary)
from employee;
SUM (SALARY)
-----------
100000
COUNT FUNCTION:
SQL> select count (d_no)
from employee;
COUNT (D_NO)
-----------
4
SQL> select count (distinct (e_no))
from employee;
COUNT (DISTINCT (D_NO))
---------------------
3
CHARACTER FUNCTION:
SQL> select lower (e_name)
from employee;
LOWER (E_NA
----------
John
jithin
midhun
Stephen
SQL> select upper (e_name)
from employee;
UPPER (E_NA
----------
JOHN
JITHIN
MIDHUN
STEPHEN
SQL> select length (e_name)
from employee;
LENGTH (E_NAME)
--------------
8
6
11
6
EmoticonEmoticon