11.DQL Commands

SQL
Administrator

DQL – select all or using where condition

SQL> select * from emp_new;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID D
   115 CHANDU          25500 manager    10-MAR-12        222
   105 kumar           12200                             555
   111 RAM             12000 A          12-DEC-14        222
   112 RAMESH          25000 B          29-DEC-14        333
   113 KRISHNA         30000 C          03-JAN-14        555
   114 SHEKAR          56500 manager    01-JAN-14        555

6 rows selected.

SQL> SELECT ENO,ENAME,SAL FROM EMP_NEW;

   ENO ENAME             SAL
   115 CHANDU          25500
   105 kumar           12200
   111 RAM             12000
   112 RAMESH          25000
   113 KRISHNA         30000
   114 SHEKAR          56500

6 rows selected.

Apply arithmatical operators in select

SQL> SELECT ENO,ENAME,SAL, SAL*12 FROM EMP_NEW;

   ENO ENAME             SAL     SAL*12
   115 CHANDU          25500     306000
   105 kumar           12200     146400
   111 RAM             12000     144000
   112 RAMESH          25000     300000
   113 KRISHNA         30000     360000
   114 SHEKAR          56500     678000

6 rows selected.

SQL> SELECT ENO,ENAME,SAL,SAL12, (SAL12)*10/100 FROM EMP_NEW;

   ENO ENAME             SAL     SAL*12 (SAL*12)*10/100
   115 CHANDU          25500     306000           30600
   105 kumar           12200     146400           14640
   111 RAM             12000     144000           14400
   112 RAMESH          25000     300000           30000
   113 KRISHNA         30000     360000           36000
   114 SHEKAR          56500     678000           67800

6 rows selected.

Using Column Aliases: If alias has spaces then it should be between ” “

SQL> SELECT ENO “EMP NO” FROM EMP_NEW; — EMPNO is column alias

EMP NO

   115
   105
   111
   112
   113
   114

6 rows selected.

SQL> SELECT ENO EMPNO FROM EMP_NEW;

EMPNO

   115
   105
   111
   112
   113
   114

6 rows selected.

SQL> SELECT ENO “EMP NO” from emp_new;

EMP NO

   115
   105
   111
   112
   113
   114

6 rows selected.

SQL> select eno,sal “Monthly SAlary” from emp_new;

   ENO Monthly SAlary
   115          25500
   105          12200
   111          12000
   112          25000
   113          30000
   114          56500

6 rows selected.

SQL> select eno,sal monthly_sal from emp_new;

   ENO MONTHLY_SAL
   115       25500
   105       12200
   111       12000
   112       25000
   113       30000
   114       56500

6 rows selected.

SQL>

Table Alias

SQL> select e.eno, e.ename from emp_new e;

   ENO ENAME
   115 CHANDU
   105 kumar
   111 RAM
   112 RAMESH
   113 KRISHNA
   114 SHEKAR

e is called table alias

Using Where Condition and other operators

SQL> select * from emp_new;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   115 CHANDU          25500 manager    10-MAR-12        222
   105 kumar           12200                             555
   111 RAM             12000 A          12-DEC-14        222
   112 RAMESH          25000 B          29-DEC-14        333
   113 KRISHNA         30000 C          03-JAN-14        555
   114 SHEKAR          56500 manager    01-JAN-14        555

6 rows selected.

SQL> select * from emp_new where eno=111;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   111 RAM             12000 A          12-DEC-14        222

SQL> select * from emp_new where eno=111 or eno=114;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   111 RAM             12000 A          12-DEC-14        222
   114 SHEKAR          56500 manager    01-JAN-14        555

SQL> select * from emp_new where sal>=15000;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   112 RAMESH          25000 B          29-DEC-14        333
   115 CHANDU          25500 manager    10-MAR-12        222
   113 KRISHNA         30000 C          03-JAN-14        555
   114 SHEKAR          56500 manager    01-JAN-14        555

SQL> select * from emp_new where sal>=20000 and sal<=40000;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   112 RAMESH          25000 B          29-DEC-14        333
   115 CHANDU          25500 manager    10-MAR-12        222
   113 KRISHNA         30000 C          03-JAN-14        555

SQL>
SQL> select * from emp_new where sal between 20000 and 40000;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   112 RAMESH          25000 B          29-DEC-14        333
   115 CHANDU          25500 manager    10-MAR-12        222
   113 KRISHNA         30000 C          03-JAN-14        555

SQL> select * from emp_new where sal not between 20000 and 40000;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   114 SHEKAR          56500 manager    01-JAN-14        555
   111 RAM             12000 A          12-DEC-14        222
   105 kumar           12200                             555

SQL> select * from emp_new where eno in (114);

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   114 SHEKAR          56500 manager    01-JAN-14        555

SQL> select * from emp_new where eno in (114,111);

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   111 RAM             12000 A          12-DEC-14        222
   114 SHEKAR          56500 manager    01-JAN-14        555

SQL> select * from emp_new where eno not in (114,111);

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   115 CHANDU          25500 manager    10-MAR-12        222
   105 kumar           12200                             555
   112 RAMESH          25000 B          29-DEC-14        333
   113 KRISHNA         30000 C          03-JAN-14        555

SQL> select * from emp_new where ename like ‘C%’; — shows all records where ename starts with C

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   115 CHANDU          25500 manager    10-MAR-12        222

SQL> select * from emp_new where ename like ‘%A’; — Shows all records where ename ends with ‘A’

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   113 KRISHNA         30000 C          03-JAN-14        555

SQL> select * from emp_new where ename like ‘%R%’; — eNAME contains R

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   111 RAM             12000 A          12-DEC-14        222
   112 RAMESH          25000 B          29-DEC-14        333
   113 KRISHNA         30000 C          03-JAN-14        555
   114 SHEKAR          56500 manager    01-JAN-14        555

SQL> select * from emp_new where ename not like ‘R%’; — Shows only records where ename not started with ‘R’

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   115 CHANDU          25500 manager    10-MAR-12        222
   105 kumar           12200                             555
   113 KRISHNA         30000 C          03-JAN-14        555
   114 SHEKAR          56500 manager    01-JAN-14        555

SQL> select * from emp_new where ename LIKE ‘_R%’; — where 2nd letter is ‘R’

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   113 KRISHNA         30000 C          03-JAN-14        555

sql> update emp_new set sal=1000 where ename like ‘_R%’;

Assignment 1 : Apply all where condition with UPDATE command

SQL> update emp set sal=1000 where eno=111;
sql> update emp set sal=1500 where eno=222 or eno=333;
sql> update emp set sal=sal+200 where sal>=1000 and sal<5000; sql> update emp set sal=sal+100 where sal between 10000 and 20000;
sql> update emp set sal=sal+500 where sal not between 10000 and 20000;
sql> update emp set sal=sal+100 where eno in (111);
sql> update emp set sal=sal+200 where eno not in (111,222);
sql> update emp set job=’MANAGER’ where sal>=20000;
sql> update emp set job=’CLERK’ where ename like ‘R%’;
sql> update emp set job=’ANALYST’ WHERE ENAME NOT LIKE ‘R%’;

Assignment 2 : Apply where condition with Delete command

SQL> DELETE FROM EMP; — It removes all records
sql> delete from emp where eno=111;
sql> delete from emp where eno=111 or eno=222;
sql> delete from emp where eno in (111,222);
sql> delete from emp where eno not in (111,222);
sql> delete from emp where ename like ‘R%’;
sql> delete from emp where ename not like ‘R%’;

Assignment 3 : Update tot,avg,res in student table

SNO Sname S1 S2 S3 Tot Avg Res

1 hema 30 44 55

Update vs delete vs select

update emp set sal=sal+10000 where job=’manager’; — Will update the salary where the job is manager
delete from emp where job=’manager’; — Will delete where the job is manager
select * from emp where job=’manager’; — will show where the job is manager

DDL-
DML-
TCL –
DQL – Data Query Language (Data Retreival Language)

SQL> select * from emp_new;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID D
   115 CHANDU          25500 manager    10-MAR-12        222
   105 kumar           12200                             555
   111 RAM             12000 A          12-DEC-14        222
   112 RAMESH          25000 B          29-DEC-14        333
   113 KRISHNA         30000 C          03-JAN-14        555
   114 SHEKAR          56500 manager    01-JAN-14        555

6 rows selected.

SQL> SELECT ENO,ENAME,SAL FROM EMP_NEW;

   ENO ENAME             SAL
   115 CHANDU          25500
   105 kumar           12200
   111 RAM             12000
   112 RAMESH          25000
   113 KRISHNA         30000
   114 SHEKAR          56500

6 rows selected.

Apply arithmatical operators in select

SQL> SELECT ENO,ENAME,SAL, SAL*12 FROM EMP_NEW;

   ENO ENAME             SAL     SAL*12
   115 CHANDU          25500     306000
   105 kumar           12200     146400
   111 RAM             12000     144000
   112 RAMESH          25000     300000
   113 KRISHNA         30000     360000
   114 SHEKAR          56500     678000

6 rows selected.

SQL> SELECT ENO,ENAME,SAL,SAL12, (SAL12)*10/100 FROM EMP_NEW;

   ENO ENAME             SAL     SAL*12 (SAL*12)*10/100
   115 CHANDU          25500     306000           30600
   105 kumar           12200     146400           14640
   111 RAM             12000     144000           14400
   112 RAMESH          25000     300000           30000
   113 KRISHNA         30000     360000           36000
   114 SHEKAR          56500     678000           67800

6 rows selected.

Using Column Aliases: If alias has spaces then it should be between ” “

SQL> SELECT ENO “EMPNO” FROM EMP_NEW; — EMPNO is column alias

EMPNO

   115
   105
   111
   112
   113
   114

6 rows selected.

SQL> SELECT ENO EMPNO FROM EMP_NEW;

EMPNO

   115
   105
   111
   112
   113
   114

6 rows selected.

SQL> SELECT ENO “EMP NO” from emp_new;

EMP NO

   115
   105
   111
   112
   113
   114

6 rows selected.

SQL> select eno,sal “Monthly SAlary” from emp_new;

   ENO Monthly SAlary
   115          25500
   105          12200
   111          12000
   112          25000
   113          30000
   114          56500

6 rows selected.

SQL> select eno,sal monthly_sal from emp_new;

   ENO MONTHLY_SAL
   115       25500
   105       12200
   111       12000
   112       25000
   113       30000
   114       56500

6 rows selected.

SQL>

Table Aliase

SQL> select e.eno, e.ename from emp_new e;

   ENO ENAME
   115 CHANDU
   105 kumar
   111 RAM
   112 RAMESH
   113 KRISHNA
   114 SHEKAR

e is called table alias

Using Where Condition and other operators

SQL> select * from emp_new;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   115 CHANDU          25500 manager    10-MAR-12        222
   105 kumar           12200                             555
   111 RAM             12000 A          12-DEC-14        222
   112 RAMESH          25000 B          29-DEC-14        333
   113 KRISHNA         30000 C          03-JAN-14        555
   114 SHEKAR          56500 manager    01-JAN-14        555

6 rows selected.

SQL> select * from emp_new where eno=111;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   111 RAM             12000 A          12-DEC-14        222

SQL> select * from emp_new where eno=111 or eno=114;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   111 RAM             12000 A          12-DEC-14        222
   114 SHEKAR          56500 manager    01-JAN-14        555

SQL> select * from emp_new where sal>=15000;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   112 RAMESH          25000 B          29-DEC-14        333
   115 CHANDU          25500 manager    10-MAR-12        222
   113 KRISHNA         30000 C          03-JAN-14        555
   114 SHEKAR          56500 manager    01-JAN-14        555

SQL> select * from emp_new where sal>=20000 and sal<=40000;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   112 RAMESH          25000 B          29-DEC-14        333
   115 CHANDU          25500 manager    10-MAR-12        222
   113 KRISHNA         30000 C          03-JAN-14        555

SQL>
SQL> select * from emp_new where sal between 20000 and 40000;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   112 RAMESH          25000 B          29-DEC-14        333
   115 CHANDU          25500 manager    10-MAR-12        222
   113 KRISHNA         30000 C          03-JAN-14        555

SQL> select * from emp_new where sal not between 20000 and 40000;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   114 SHEKAR          56500 manager    01-JAN-14        555
   111 RAM             12000 A          12-DEC-14        222
   105 kumar           12200                             555

SQL> select * from emp_new where eno in (114);

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   114 SHEKAR          56500 manager    01-JAN-14        555

SQL> select * from emp_new where eno in (114,111);

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   111 RAM             12000 A          12-DEC-14        222
   114 SHEKAR          56500 manager    01-JAN-14        555

SQL> select * from emp_new where eno not in (114,111);

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   115 CHANDU          25500 manager    10-MAR-12        222
   105 kumar           12200                             555
   112 RAMESH          25000 B          29-DEC-14        333
   113 KRISHNA         30000 C          03-JAN-14        555

SQL> select * from emp_new where ename like ‘C%’; — shows all records where ename starts with C

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   115 CHANDU          25500 manager    10-MAR-12        222

SQL> select * from emp_new where ename like ‘%A’; — Shows all records where ename ends with ‘A’

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   113 KRISHNA         30000 C          03-JAN-14        555

SQL> select * from emp_new where ename like ‘%R%’; — eNAME contains R

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   111 RAM             12000 A          12-DEC-14        222
   112 RAMESH          25000 B          29-DEC-14        333
   113 KRISHNA         30000 C          03-JAN-14        555
   114 SHEKAR          56500 manager    01-JAN-14        555

SQL> select * from emp_new where ename not like ‘R%’; — Shows only records where ename not started with ‘R’

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   115 CHANDU          25500 manager    10-MAR-12        222
   105 kumar           12200                             555
   113 KRISHNA         30000 C          03-JAN-14        555
   114 SHEKAR          56500 manager    01-JAN-14        555

SQL> select * from emp_new where ename LIKE ‘_R%’; — where 2nd letter is ‘R’

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY
   113 KRISHNA         30000 C          03-JAN-14        555

Assignment 1 : Apply all where condition with UPDATE command
Assignment 2 : Apply where condition with Delete command
Assignment 3 : Update tot,avg,res in student table

Post Views: 379
Administrator

http://www.databaselearners.com

Related Posts

27.Indexes

Administrator

14.TCL Commands

Administrator

1.Introduction to SQL

Administrator