Set of A-Z SQL Command 3

Aggregation Data using Group Functions

Sub Queries (Operators like =,<,>,>=,<=,<>) can be used in nested Queries

1.   select last_name,job_id,salary from employees where salary>(select salary from employees where last_name=’Feeney’);

2.   select last_name,job_id,salary from employees where salary>(select salary from employees where last_name=’Feeney’) and job_id=(select job_id from employees where last_name=’Bell’);

3.   select last_name,job_id,salary from employees where salary>(select min(salary) from employees);

4.   select last_name,job_id,salary from employees where salary>(select avg(salary) from employees);

5.   select department_id,min(salary) from employees group by department_id having min(salary) > (select min(salary) from employees where department_id=50);

6.   select department_id,job_id from employees where salary= (select min(salary) from employees group by department_id); (wrong statement)

Operator (in,any,all)

7.   select last_name,job_id from employees where salary in (select min(salary) from employees group by department_id);

8.   select last_name,job_id from employees where salary >any (select min(salary) from employees group by department_id);

9.   select last_name,job_id from employees where salary > all (select min(salary) from employees group by department_id);

Null Value in Sub Query

10.        select last_name,job_id from employees where employee_id  in (select manager_id from employees);

11.        select last_name,job_id from employees where employee_id not in (select manager_id from employees);

12.        select * from employees where nvl(manager_id,0) not in (select employee_id from employees);

Producing Readable Output with isql*plus

1.    select employee_id,last_name,job_id from employees where employee_id=&empno;   (&empno=employee_id)

2.    select employee_id,last_name,job_id from employees where last_name=’&name’;  (Smith)

3.    select employee_id,last_name,job_id from employees where last_name=&name;    (&name=‘Smith’)

4.    select employee_id,last_name,&job from employees where last_name=’&name’;  (&job=job_id,&name=Bell)

5.    select employee_id,&col,&col2 from employees where last_name=’&name’; (&col=last_name,&col2=last_name,&name=Bell)

6.    select &one,&two from &table where &col;  (&one=last_name,&two=job_id,&col=employee_id=178)

7.    select &one,&two from &table where &col order by &order; (&one=last_name,&two=job_id,&col=employee_id=178,&order=job_id)

Defining a Veriable:

8.    define jobt=ST_MAN;

9.    define jobt;

10. undefined jobt;  //define jobt(to check whether the variable is undefined on not//

11. define sal=3000;

12. select * from employees where salary=&sal;

13. select job_id,last_name,salary,&col from employees order by &&col;//only one time and one session only//

14. set verify on;

15. set verify off;

Column Format (you can use column/col)

16. column last_name heading ‘NAAM’;

17. col salary format $99,999;

18. column salary format 099999;

19. select LAST_NAME,SALARY,MANAGER_ID from employees;

20. column salary clear;

21. col manager_id format 99999 NULL 'NO MANAGER';

22. select LAST_NAME,SALARY,MANAGER_ID from employees;

23. break on manager_id;

24. break on job_id;

25. select LAST_NAME,SALARY,MANAGER_ID,job_id from employees order by manager_id,job_id;

Report

26. set feedback off;

27. Ttitle 'EMPLOYEES DETAILS';  // also show page no 1,2,3,4,5,etc//

28. Btitle 'EMPLOYEES Confidential';

29. col salary heading 'monthly Sal' format 99,999;

30. select LAST_NAME,SALARY,MANAGER_ID,job_id from employees order by job_id;

Manipulating Data

1.   create table tinku (sno number(4),name varchar2(25),DBO date,sal number(6));

2.   insert into tinku values (101,’Tinku’,’4-oct-1985’,6000);

3.   insert into tinku (sno,name,sal) values (102,’Vishal’,7000);

4.   insert into tinku values (103,’Rajeev’,Null,2500);

5.   insert into tinku values (104,’Praveen’,sysdate,3000);

6.   insert into tinku values (105,’Amit’,to_date(‘4-4-99’,’dd-mm-yy’),2600);

7.   insert into tinku values (&sno,’&name’,’&dbo’,&sal);

Copying rows from another table:

8.   insert into tinku (sno,name,dbo,sal) select employee_id,last_name,hire_date,salary from employees where last_name=’Bell’;

Using sub Query in an insert Statement

9.   insert into (select name,dbo,sal from tinku where sno=105) values (‘kajol’,sysdate,5000);

10.                insert into (select name,dbo,sal,sno from tinku where sno=105 with check option) values ('kajol',sysdate,5000,106); //with check option clause

using explict default value

11.                insert into tinku values (106,’Tinku’,default,6000);

12.                select * from tinku;

13.                create table vishal (sno number(4),dbo date default sysdate);

14.                insert into vishal values (101,default);

15.                insert into vishal values (102,04-oct-85);

16.                select * from vishal;

Changing data in the table

17.        update tinku set sal=8000 where name=’Tinku’;

18.        update tinku set sal=8000,dbo=’04-oct-1985’ where name=’Tinku’;

19.        update tinku set sal=(select salary from employees where last_name=’Bell’),dbo=(select hire_date from employees where last_name=’Feeney’) where name=’Tinku’;

20.        update vishal set dbo=default where sno=101;

integrity constraint error

21.        update employees set employee_id=205 where employee_id=206;   //employee_id is unique constraint//