NOTE: You are to use the
hr schema
Let us refresh our minds on what a
table is, in terms of columns, rows and field.
before we start with the drill, you should be able to know all the columns a table has by issuing this instruction:
desc table_name example desc employees1. select * from employees;
This displays all the columns and rows in the table employees
2. select last_name, salary from employees;
This will display two columns ie last_name and salary and all their
corresponding rows in the table
3. select last_name, salary+200 from employees;
This will display two columns ie last_name and 200 will be added to all the
values in the salary column giving a new column salary + 200
4. select last_name, 12*(salary+100) from employees;
Kindly describe the result
5. Read on NULL value
select first_name,commission_pct from employees where last_name in ('Hall','Bull');
select first_name,commission_pct*2 from employees where last_name in ('Hall','Bull');
6. select last_name name from employees;
the column name last_name is replaced with name
7. select salary*12 "Annual Salary" from employees;
8. select last_name||' '||job_id AS "Employees" from employees;
select department_name||',it's assigned manager id:'||manager_id as
"department and manager" from departments;
select department_name||q'[,it's assigned manager id: ]' || manager_id as
"department and manager" from departments
9. select department_id from employees;
10. select distinct department_id from employees;
11. describe employees
12. desc employees
13. select employee_id, last_name from employees where department_id=90;
14. select last_name, department_id from employees where last_name='Whalen';
15. select last_name, salary from employees where salary <=3000; 16.select last_name, salary from employees where salary between 2500 and 3500; 17. select last_name, salary from employees where manager_id in (100,101,201); 18. select last_name from employees where first_name like 'S%'; 19. select last_name from employees where last_name like '_o%'; 20. select employee_id, last_name, job_id from employees where job_id like '%SA\_%' ESCAPE '\'; 21. select last_name, manager_id from employees where manager_id is NULL; 22. select employee_id, last_name, from employees where salary>10000 AND job_id like '%MAN%';
23. select employee_id, last_name, from employees where salary>10000 OR job_id like '%MAN%';
24. select employee_id, last_name, from employees where job_id NOT IN ('IT_PROG','SA_REP');
25. select last_name, job_id from employees order by hire_date;
26. select last_name, job_id from employees order by hire_date desc;
27. select last_name, job_id from employees order by department_id, salary DESC;
28. select employee_id, last_name from employees where employee_id=&emp_num;
29. select employee_id,last_name, &colum_name from employees where &condition order by &order_column;
30. select lower('SQL Course') from dual;
31. select upper('SQL Course') from dual;
32. select initcap('SQL Course') from dual;
33. select concat('Hello','World') from dual;
34. select substr('HelloWorld',1,5) from dual;
35. select substr(concat('Hello','World'),1,5) from dual;
36. select length('Mother') from dual;
37. select length(substr(concat('Hello','World'),1,5)) from dual;
38. select instr('monkey','k') from dual;
39. select lpad('elvis',10,'¢') from dual;
40. select rpad('elvis',10,'¢') from dual;
41. select replace ('Pencil','n','a') from dual;
42. select trim('H' from 'HelloWorld') from dual;
43. select trim('d' from 'HelloWorld') from dual;
44. select trim('W' from 'HelloWorld') from dual;
45. select trim('World' from 'HelloWorld') from dual;
46. select upper(replace(substr(trim('a' from rpad(lpad('Kenkey',12,'a'),15,'a')),1,3),'K','P')) from dual;
47. select round(45.926,2) from dual;
48. select trunc(45.926,2) from dual;
49. select mod(5,2) from dual;
50. select sysdate from dual;
51. select to_char(hire_date,'dd-mm-yy') from employees;
52. select to_char(hire_date,'day-mm-yy') from employees;
53. select to_char(hire_date,'dy-mm-yy') from employees;
54. select to_char(hire_date,'dd-mon-yy') from employees;
55. select to_char(hire_date,'dd-month-yy') from employees;
56.select to_char(hire_date,'dd-month-yyyy') from employees;
57.select to_char(hire_date,'dd-month-year') from employees;
58.select to_char(hire_date,'month-yyyy') from employees;
59.select to_char(hire_date,'fmmonth-yyyy') from employees;
60.select to_char(hire_date,'month/yyyy') hiredate from employees;
61.select to_char(hire_date,'fmmonthyyyy') from employees;
62.select to_char(salary,'99,999.99') salary from employees;
63.select last_name,commission_pct commission from employees where last_name like 'A%'
64.select last_name,commission_pct+100 commission from employees where last_name like 'A%'
65.select last_name,nvl(commission_pct,0) commission from employees where last_name like 'A%'
66.select last_name,nvl(commission_pct,0)+100 commission from employees where last_name like 'A%'
67. select last_name,commission_pct,nvl2(commission_pct,'Earned Commission','No Commission') from employees where last_name like 'A%'
68.create table test
(
name varchar2(10),
69.salary number,
allowance number
);
70.select * from test;
71.insert into test values('Kofi',120,100);
72.insert into test values('Paul',90,90);
73.insert into test values('Raymoon',60,60);
74.insert into test values('Elvis',110,110);
75.insert into test values('Baby',80,150);
commit;
76.select * from test;
77.select name,salary,allowance, nullif(salary,allowance) from test;
78select name,salary,allowance, nullif(allowance,salary) from test;
79.select last_name,coalesce(commission_pct,1) from employees where last_name like 'A%'
80.select last_name,salary,job_id,
case job_id when 'IT_PROG' THEN 1.1*salary
when 'ST_CLERK' THEN 1.15*salary
when 'SA_REP' THEN 1.2*salary
else salary
end "Revised Salary"
from employees;
81.select last_name,salary,job_id,
decode(job_id,'IT_PROG',1.1*salary,
'ST_CLERK',1.15*salary,
'SA_REP',1.2*salary,
salary) "Revised Salary"
from employees;
82.select avg(salary),max(salary),min(salary),sum(salary),stddev(salary),variance(salary) from employees;
83.select count(*) from employees;
84.select count(commission_pct) from employees;
85.select count(employee_id) from employees;
86.select count(department_id) from employees;
87.select count(distinct department_id) from employees;
Questions
1.Create a report to display the manager number and the salary of the lowest paid employee for that manager.
Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is 6000 or less. Sort the output in descending order
of salary.
88. select department_id,department_name,location_id,city
from departments
natural join locations;
89. select department_id,department_name,a.location_id,city
from departments a, locations b
where a.location_id=b.location_id;
90. select employee_id,last_name,department_id,manager_id,department_name
from employees
natural join departments;
91. select employee_id,last_name,a.department_id,a.manager_id,department_name
from employees a, departments b
where a.department_id=b.department_id
and a.manager_id=b.manager_id;
92. select employee_id,last_name,department_id,b.manager_id,department_name
from employees a join departments b
using(department_id);
93. select employee_id,last_name,department_id,a.manager_id,department_name
from employees a join departments b
using(department_id);
94. select employee_id,last_name,department_id,a.manager_id,department_name
from employees a join departments b
using(department_id)
where a.department_id=50;
95. select employee_id,last_name,a.department_id,manager_id,department_name
from employees a join departments b
using(manager_id);
96. select employee_id,last_name,department_id,a.manager_id,department_name
from employees a join departments b
on (a.department_id=b.department_id);
97. select employee_id,last_name,a.department_id,a.manager_id,department_name
from employees a join departments b
on (a.department_id=b.department_id);
98. select e.last_name,m.last_name
from employees e join employees m
on (e.manager_id=m.employee_id);
99. select e.last_name,e.department_id,d.department_name
from employees e left outer join departments d
on (e.department_id=d.department_id);
100. select e.last_name,e.department_id,d.department_name
from employees e right outer join departments d
on (e.department_id=d.department_id);
101. select e.last_name,e.department_id,d.department_name
from employees e full outer join departments d
on (e.department_id=d.department_id);
102. select last_name,department_name
from employees
cross join departments;
103. select last_name
from employees
where salary=
(
select min(salary) from employees
);
104. select last_name,department_id,salary
from employees
where salary =
(select min(salary) from employees group by department_id);
105. select last_name,department_id,salary
from employees
where salary in
(select min(salary) from employees group by department_id);
106. select last_name,department_id,salary
from employees
where salary < (select min(salary) from employees group by department_id); select name,salary from test where salary > any(select salary from test where name in('Kofi','Paul'));
107. select name,salary from test
where salary > all(select salary from test where name in('Kofi','Paul'));
108. select last_name,department_id,salary
from employees
where salary < id="1;" id="1;" id="1;">500));
156. insert into box values(1,200);
157. insert into box values(1,500);
158. insert into box values(1,501);
159. create table dept100
as
select employee_id,salary from employees where department_id=100;
160. create table dept101
as
select employee_id,salary*10 from employees where department_id=101;
161. create table dept101
as
select employee_id,salary*10 sal from employees where department_id=101;
162. select * from dept101;
163. create table dept110 (emp, sal)
as
select employee_id,salary*10 from employees where department_id=110;
164. select * from dept110;
165. alter table dept110
add (job_id varchar2(10),hire_date date default sysdate);
166. select * from dept110;
167. desc dept110
168. alter table dept110 modify (emp varchar2(10));
169. alter table dept110 modify (job_id number(10));
170. alter table dept110 modify (job_id varchar2(10));
171. truncate table dept110;
172. alter table dept110 modify (emp varchar2(10));
173. desc dept110
174. select * from dept110;
175. alter table dept110 drop column job_id;
176. select * from dept110;
177. alter table dept110 set unused column sal;
178. select * from dept110;
179. alter table dept110 drop unused columns;
180. select * from user_unused_col_tabs;
181. log in as sys and create table emp
182. create table hr.emp
as select * from scott.emp;
183. create table hr.dept
as select * from scott.dept;
184. Now log in as hr
185. CREATE VIEW myview
AS SELECT empno EMPLOYEE_NO, ename NAME, sal SALARY, job JOB
FROM emp
WHERE deptno=20;
186. CREATE force VIEW myview
AS SELECT empno EMPLOYEE_NO, ename NAME, sal SALARY, job JOB
FROM emps
WHERE deptno=20;
187. select * from myview;
188. CREATE or replace VIEW myview
AS SELECT empno EMPLOYEE_NO, ename NAME, sal SALARY, job JOB
FROM emp
WHERE deptno=20;
/* the same as
189. CREATE VIEW myview(EMLOYEE_NO,NAME,SALARY,JOB)
AS SELECT empno,ename,sal,job
FROM emp
WHERE deptno=20;
*/
190. select * from myview;
191. CREATE VIEW v2
AS SELECT d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;
192. CREATE VIEW v2
AS SELECT d.dname, MIN(e.sal) mini, MAX(e.sal) maxi , AVG(e.sal) aver
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;
193. select * from v2
