Set of A-Z SQL Command 2

1. 
   
2. Single row Function
3. Conversion Function (to_char with date)
4.  
5. 1.   select to_char(sysdate,’dd-mm-yyyy’) from dual;
6. 2.   select to_char(sysdate,’dd-month-year’) from dual;
7. 3.   select to_char(sysdate,’dd-mon-year’) from dual;
8. 4.   select to_char(sysdate,’dd-DAY-month-year’) from dual;
9. 5.   select to_char(sysdate,’dd-DY-month-year’) from dual;
10. 6.   select to_char(sysdate,’dd-mon-yyyy, HH:MI:SS’) from dual;
11. 7.   select to_char(sysdate,’dd-mon-yyyy, HH24:MI:SS’) from dual;
12. 8.   select to_char(sysdate,’dd-mon-yyyy, HH:MI:SS AM’) from dual;
13. 9.   select to_char(sysdate,’DDth-mon-yyyy’) from dual;
14. 10.        select to_char(sysdate,’DDth “of” mon-yyyy, HH:MI:SS’) from dual;
15. 11.        select to_char(sysdate,’DDsp-mon-yyyy, HH:MI:SS’) from dual;
16. 12.        select to_char(sysdate,’DDspth-mon-yyyy, HH:MI:SS’) from dual;
17. 13.        select to_char(hire_date,’DDspth-mon-yyyy, HH:MI:SS’) from employees;
18.  
19.  
20. Conversion Function (to_char with Number)
21.  
22. 14.        select to_char(salary,’99999.99’) from employees;
23. 15.        select to_char(salary,’$99999.99’) from employees;
24. 16.        select to_char(salary,’09999.99’) from employees;
25. 17.  select to_char(salary,’L99999.99’) from employees; //L use to local currency //
26. 18.        select to_char(salary,’99,999.99’) from employees;
27. 19.  select to_char(salary-3000,’099999MI’) from employees; // MI use for minus//
28. 20.  select to_char(salary-3000,’099999PR’) from employees; //PR to use the paraththice//
29. 21.  select to_char(salary,’99999EEEE’) from employees; //EEEE to see the exponencial  //
30. 22.  select to_char(salary,’99999V99’) from employees; //after salary 00 use to V//
31.  
32.  
33. Conversion Function (to char with Characters)
34.  
35. 23.        select to date(’05-09.2010’,’dd-mm-yyyy’) from dual;
36. 24.        select to date(’05-09.2010’,’fxdd-mm-yyyy’) from dual;  //fix to fix the date //
37. 25.        select to date(’05-09.2010’,’fxdd-mm.yyyy’) from dual;
38. 26.        select sysdate-to_date(’05-09-2010’,’dd-mm-yyyy’) from dual;
39. 27.        alter session set nls_currency=’Rs’;
40. 28.        select to_char(salary,’L99999.99’) from employees;
41. 29.        select (sysdate-hire_date) from employees;
42. 30.        select (sysdate-12-may-10’) from employees;
43.  
44.  
45.  
46.  
47. Displaying Data from Multiple Table
48.  
49.  
50. 1.   select * from employees;
51. 2.   desc employees;
52. 3.   select * from departments;
53. 4.   desc departments;
54. 5.   select last_name,department_name from employees,departments; (Cartesion product)
55. 6.   select e.employee_id,e.last_name,d.department_name from employees e,departments d;
56. 7.   select e.employee_id,e.last_name,d.department_name from employees e,departments d where e.department_id=d.department_id;
57. 8.   select last_name,department_name from employees,departments  where employees.department_id=departments.department_id;
58. 9.   select e.employee_id,e.last_name,d.department_name from employees e,departments d where e.department_id=d.department_id and last_name='Smith';
59. 10.        select e.last_name,e.employee_id,d.department_name,d.department_id,l.location_id,l.city from employees e,departments d,locations l where e.department_id=d.department_id and d.location_id=l.location_id;
60.  
61. Non Equi join (Cartesion Product)
62.  
63. 11.        select e.last_name,e.hire_date,e.job_id,e.department_id,j.employee_id from employees e,job_history j where e.hire_date between j.start_date and j.end_date;   
64. 12.        select e.last_name,e.hire_date,e.job_id,e.department_id,j.employee_id from employees e,job_history j where e.hire_date between j.start_date and j.end_date and last_name='Gee';          
65.  
66. Ooter Join
67.  
68. 13.        select e.employee_id,e.last_name,d.department_name from employees e,departments d where e.department_id=d.department_id;
69. 14.        select e.employee_id,e.last_name,d.department_name from employees e,departments d where e.department_id(+)=d.department_id;
70. 15.        select e.employee_id,e.last_name,d.department_name from employees e,departments d where e.department_id=d.department_id(+);
71.  
72. Self Join
73.  
74. 16.        select e.employee_id,e.last_name,m.last_name "Manager" from employees e,employees m where e.manager_id=m.employee_id;
75. 17.        select e.last_name||'  work for  '||m.last_name "Manager" from employees e,employees m where e.manager_id=m.employee_id;
76.  
77. Displaying Data from Multiple Table
78.  
79. Join with Oracle’s 1999 Syntax
80.  
81. Cross join (cartesion Product)
82.  
83. 18.    select last_name,salary,department_name from employees cross join departments;
84.  
85. Natural Join
86.  
87. 19.    select last_name,salary,department_name from employees natural join departments;
88.  
89. With the on clause
90.  
91. 20.    select e.employee_id,e.last_name,d.department_name from employees e join departments d on e.department_id=d.department_id;
92. 21.    select e.last_name,d.department_name,d.department_id,l.city from employees e join departments d on e.department_id=d.department_id join locations l on d.location_id=l.location_id;
93. Left outer Join
94.  
95. 22.    select e.employee_id,e.last_name,d.department_name from employees e left outer join departments d on e.department_id=d.department_id;
96.  
97. Right outer Join
98.  
99. 23.    select e.employee_id,e.last_name,d.department_name from employees e right outer join departments d on e.department_id=d.department_id;
100.  
101. Full outer Join
102.  
103. 24.    select e.employee_id,e.last_name,d.department_name from employees e full outer join departments d on e.department_id=d.department_id;
104. 25.    select e.employee_id,e.last_name,d.department_name from employees e  join departments d on e.department_id=d.department_id and last_name=’Smith’;
105.  
106. Aggregation Data using Group Functions
107.  
108. Group Function (operate on set of row to give one result per group)
109.  
110. 1.   select max(salary) from employees;
111. 2.   select max(salary),min(salary),avg(salary),sum(salary) from employees;
112. 3.   select sum(salary+salary+salary) from employees;
113. 4.   select max(salary) max_sal,min(salary) min_sal,avg(salary) avg_sal,sum(salary) sum_sal from employees;
114. 5.   select min(hire_date),max(hire_date) from employees;
115. 6.   select min(last_name),max(last_name) from employees;
116. 7.   select count(*) from employees;
117. 8.   select count(commission_pct),count(job_id),count(*) from employees;
118. 9.   select count(*) from employees where department_id=50;
119. 10.        select count(department_id) from employees;
120. 11.        select count(distinct department_id) from employees;
121. 12.        select avg(commission_pct) from employees;
122. 13.        select avg(nvl(commission_pct,0)) from employees;
123.  
124. Creating Group of data
125.  
126. 14.        select department_id,avg(salary) from employees group by department_id;
127. 15.        select avg(salary) from employees group by department_id;
128. 16.        select job_id,avg(salary),sum(salary),count(*) from employees group by job_id;
129.  
130. Group by more then one column
131.  
132. 17.        select department_id,job_id,avg(salary) from employees group by department_id,job_id;
133. 18.        select department_id,job_id,avg(salary) from employees; (illegal Query)
134.  
135. Having Clause
136.  
137. 19.        select department_id,avg(salary) from employees where avg(salary)>5000 group by department_id; (illegal Query)
138. 20.        select department_id,avg(salary) from employees where department_id<60 group by department_id;
139. 21.        select department_id,avg(salary) from employees  group by department_id having avg(salary) >5000;
140. 22.        select department_id,avg(salary) from employees  group by department_id having avg(salary)>5000 order by avg(salary);
141.  
142. Nesting Group Function
143.  
144. 23.        select max(avg(salary)) from employees  group by department_id;