SQl With 10 steps(Basics)

//

Step1:

Create table in SQL &Display the table

SQL> create table Employee (Eno int,Ename char(20),job char(20),grade char(5),Salary decimal(10,2));

Table created.
SQL> insert into Employee values (10,'Tom','Manager','a',10000);
1 row created.
SQL> insert into Employee values (12,'Anoop','Manager','a',20000);
1 row created.
SQL> insert into Employee values (14,'Raju','Programer','b',80000);
1 row created.
SQL> insert into Employee values (15,'Sabu','Analyst','c',70000);
1 row created.
SQL> insert into Employee values (16,'Shalih','clerk','d',1000);
1 row created.
SQL> insert into Employee values (17,'Narju','Manager','a',15000);
1 row created.

SQL> Select*from Employee;

ENO ENAME JOB GRADE SALARY
--------- -------------------- -------------------- ----- ---------
10 Tom Manager a 10000
12 Anoop Manager a 20000
14 Raju Programer b 80000
15 Sabu Analyst c 70000
16 Shalih clerk d 1000
17 Narju Manager a 15000
6 rows selected.

Step2:

Select with condition

SQL> Select eno,ename,salary from employee;
ENO ENAME SALARY
--------- -------------------- ---------
10 Tom 10000
12 Anoop 20000
14 Raju 80000
15 Sabu 70000
16 Shalih 1000
17 Narju 15000
6 rows selected.

SQL> select eno,ename
2 from employee
3 where salary>=10000;

ENO ENAME
--------- --------------------
10 Tom
12 Anoop
14 Raju
15 Sabu
17 Narju

SQL> select*from Employee
2 where salary>700;

ENO ENAME JOB GRADE SALARY
--------- -------------------- -------------------- ----- ---------
10 Tom Manager a 10000
12 Anoop Manager a 20000
14 Raju Programer b 80000
15 Sabu Analyst c 70000
16 Shalih clerk d 1000
17 Narju Manager a 15000
6 rows selected.

Step3:
Select with relation & logical operator

SQL> select ename,job,salary
2 from employee
3 where job='Manager';

ENAME JOB SALARY
-------------------- -------------------- ---------
Tom Manager 10000
Anoop Manager 20000
Narju Manager 15000

SQL> Select job,salary
2 from Employee
3 where job<>'Analyst';

JOB SALARY
-------------------- ---------
Manager 10000
Manager 20000
Programer 80000
clerk 1000
Manager 15000

SQL> Select *from Employee
2 where (salary>10000 and job='Manager');

ENO ENAME JOB GRADE SALARY
--------- -------------------- -------------------- ----- ---------
12 Anoop Manager a 20000
17 Narju Manager a 15000

SQL> Select *from Employee
2 Where (job='analyst' or job='Programer');

ENO ENAME JOB GRADE SALARY
--------- -------------------- -------------------- ----- ---------
14 Raju Programer b 80000

Step4:
Select statement with order by And Distict clauses

SQL> Select eno,ename,salary
2 from employee
3 where salary>7500
4 order by eno desc;

ENO ENAME SALARY
--------- -------------------- ---------
17 Narju 15000
15 Sabu 70000
14 Raju 80000
12 Anoop 20000
10 Tom 10000

SQL> Select *from employee
2 order by ename;

ENO ENAME JOB GRADE SALARY
--------- -------------------- -------------------- ----- ---------
12 Anoop Manager a 20000
17 Narju Manager a 15000
14 Raju Programer b 80000
15 Sabu Analyst c 70000
16 Shalih clerk d 1000
10 Tom Manager a 10000

6 rows selected.

SQL> Select distinct job from employee;

JOB
--------------------
Analyst
Manager
Programer
Clerk

Step5:
Group function

SQL> select sum(salary) from employee;

SUM(SALARY)
-----------
196000

SQL> select sum(salary) from employee;

SUM(SALARY)
-----------
196000

SQL> select count(*) from Employee ;

COUNT(*)
---------
6

SQL> Select max(salary) from employee;

MAX(SALARY)
-----------
80000
SQL> Select min(salary) from employee;

MIN(SALARY)
-----------
1000

Step6:
Alter table command

SQL> Alter table Employee
2 add phoneNo int;
Table altered.

SQL> desc Employee;
Name Null? Type
------------------------------- -------- ----
ENO NUMBER(38)
ENAME CHAR(20)
JOB CHAR(20)
GRADE CHAR(5)
SALARY NUMBER(10,2)
PHONENO NUMBER(38)

SQL> Alter table employee
2 modify (job char(25));
Table altered.

SQL> desc Employee;
Name Null? Type
------------------------------- -------- ----
ENO NUMBER(38)
ENAME CHAR(20)
JOB CHAR(25)
GRADE CHAR(5)
SALARY NUMBER(10,2)
PHONENO NUMBER(38)

Step7:
Update command

SQL> Update Employee
2 set phoneNo=2546238
3 where Eno=10;
1 row updated.

SQL> Update Employee
2 set phoneNo=2708089

3 where Eno=12;

1 row updated.

SQL> Update Employee
2 set phoneNo=2705836
3 where Eno=14;
1 row updated.

SQL> Update Employee
2 set phoneNo=2705134
3 where Eno=15;
1 row updated.

SQL> Update Employee
2 set phoneNo=2441232
3 where Eno=16;
1 row updated.

SQL> Update Employee
2 set phoneNo=2787896
3 where Eno=17;
1 row updated.

SQL> select *from Employee;

ENO ENAME JOB GRADE SALARY PHONENO
--------- -------------------- ------------------------- ----- --------- ---------
10 Tom Manager a 10000 2546238
12 Anoop Manager a 20000 2708089
14 Raju Programer b 80000 2705836
15 Sabu Analyst c 70000 2705134
16 Shalih clerk d 1000 2441232
17 Narju Manager a 15000 2787896
6 rows selected.

SQL> Update Employee
2 set salary=5000
3 where job='clerk';
1 row updated.

SQL> update Employee
2 set Salary=Salary+500;
6 rows updated.

SQL> select *from Employee;

ENO ENAME JOB GRADE SALARY PHONENO
--------- -------------------- ------------------------- ----- --------- ---------
10 Tom Manager a 10500 2546238
12 Anoop Manager a 20500 2708089
14 Raju Programer b 80500 2705836
15 Sabu Analyst c 70500 2705134
16 Shalih clerk d 5500 2441232
17 Narju Manager a 15500 2787896
6 rows selected.

Step8:
Delete And Drop Table

SQL> Delete from Employee
2 Where Salary>8000;

5 rows deleted.

SQL> delete from Employee;

1 row deleted.

SQL> Drop table Employee;

Table dropped.

Step9:
Some Other operations

*)Select null row

SQL> select Eno,ename
2 from employee
3 where eno is null;

no rows selected

*)Simple Calculation

SQL> select 4*3 from dual;

4*3
---------
12
*)Print system date

SQL> select sysdate from dual;

SYSDATE
---------
25-SEP-08

Step10:
Having And Group By Clause

SQL> select job,count(*)

2 from Employee

3 group by job

4 having count(*)<3;

JOB COUNT(*)

-------------------- ---------

Analyst 1

Programer 1

clerk 1

-->