Lesson 2
1. SELECT last_name, salary
FROM employees
WHERE salary > 12000;
2. SELECT last_name, department_id
FROM employees
WHERE employee_id=176;
3. SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 5000 and 12000;
4. SELECT last_name, job_id, hire_date
FROM employees
WHERE last_name IN ('Matos','Taylor');
5. SELECT last_name, department_id
FROM employees
WHERE department_id IN (20,50)
ORDER BY last_name ASC;
6. SELECT last_name AS "Employee", salary AS "Monthly Salary"
FROM employees
WHERE salary between 5000 and 12000;
7. SELECT last_name, hire_date
FROM employees
WHERE hire_date like '%94';
8. SELECT last_name, job_id
FROM employees
WHERE manager_id is null;
9. SELECT last_name, salary,commission_pct
FROM employees
WHERE commission_pct is not null
ORDER BY 2 DESC, 3 DESC;
10. SELECT last_name, salary
FROM employees
WHERE salary > &SAL;
11. SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE manager_id=&mgr_id;
12. SELECT last_name
FROM employees
WHERE last_name like '__a%';
13. SELECT last_name
FROM employees
WHERE last_name like '%a%'
AND last_name like '%e%';
14. SELECT last_name, job_id, salary
FROM employees
WHERE job_id IN('SA_REP','ST_CLERK')
AND salary not in (2500,3500,7000);
15. SELECT last_name "Employee", salary "Monthly Salary", commission_pct
FROM employees
WHERE commission_pct=0.2;
LESSON 3
1. SELECT sysdate as "Date"
FROM dual;
2. SELECT employee_id,last_name, salary, round(salary * 1.115) as "New Salary"
FROM employees;
3. SELECT employee_id,last_name, salary, round(salary * 1.115) as "New Salary", round(salary * 1.115) - salary as "Increase"
FROM employees;
4. SELECT initcap(last_name), length(last_name)
FROM employees
WHERE substr(last_name,1,1) IN ('J','A','M');
5. SELECT initcap(last_name), length(last_name)
FROM employees
WHERE substr(last_name,1,1) ='&FIRST_LETTER';
SELECT initcap(last_name), length(last_name)
FROM employees
WHERE upper(substr(last_name,1,1)) =upper('&FIRST_LETTER');
6. SELECT last_name,round(months_between(sysdate,hire_date),0) as months_worked
FROM employees
order by 2;
7. SELECT last_name, lpad(salary,15,'$') as salary
FROM employees;
8. SELECT substr(last_name,1,8), salary,lpad('*', trunc(salary/1000),'*')
FROM employees;
SELECT substr(last_name,1,8) || ' ' || lpad('*', trunc(salary/1000),'*') as EMPLOYEES_AND_THEIR_SALARIES
FROM employees;
9. SELECT last_name, trunc((sysdate - hire_date)/7,0) as TENTURE
FROM employees
WHERE department_id=90
ORDER BY 2 DESC;
Lesson 4
1. SELECT last_name || ' earns' || to_char(salary,'fm$99,999.99') || 'monthly but wants ' || to_char(salary * 3,'fm$99,999.99') || '.'
FROM employees;
2. SELECT last_name,hire_date,
to_char(next_day(add_months(hire_date,6),'Monday'),'fmDay, "the" Ddspth "of" Month, YYYY')
FROM employees;
3. SELECT last_name, hire_date, to_char(hire_date,'DAY') as DAY
FROM employees
Order by mod(to_char(hire_date,'D') + 5, 7);
4. SELECT last_name, nvl(to_char(commission_pct),'No Commission') as COMM
FROM employees;
5. SELECT job_id,decode(job_id,
'AD_PRES','A',
'ST_MAN','B',
'IT_PROG','C',
'SA_REP','D',
'ST_CLERK','E',
'O')
FROM employees;
SELECT job_id,
case job_id WHEN 'AD_PRES' then 'A'
WHEN 'ST_MAN' then 'B'
WHEN 'IT_PROG' then 'C'
WHEN 'SA_REP' then 'D'
WHEN 'ST_CLERK' then 'E'
ELSE 'O' END as GRADE
FROM employees;
Lesson 5
1. True
2. False
3. True
4. SELECT max(salary) as "Maximum",
min(salary) as "Minimum",
sum(salary) as "Sum",
round(avg(salary)) as "Average"
FROM employees;
5. SELECT Job_id,
max(salary) as "Maximum",
min(salary) as "Minimum",
sum(salary) as "Sum",
round(avg(salary)) as "Average"
FROM employees
group by job_id;
6. SELECT job_id,count(job_id)
FROM employees
group by job_id;
SELECT job_id,count(job_id)
FROM employees
where upper(job_id)=upper('&job_id')
group by job_id;
7. SELECT count(DISTINCT manager_id ) as "No of Managers"
FROM employees;
8. SELECT max(salary)-min(salary) as DIFFERENCE
FROM employees;
9. SELECT manager_id, min(salary)
FROM employees
WHERE manager_id is not null
GROUP BY manager_id
HAVING min(salary) > 6000
ORDER BY 2 DESC;
10. SELECT count(employee_id) as TOTAL,
count(decode(substr(hire_date,-2),'01',1)) as "2001",
count(decode(substr(hire_date,-2),'02',1)) as "2002",
count(decode(substr(hire_date,-2),'03',1)) as "2003",
count(decode(substr(hire_date,-2),'04',1)) as "2004",
count(decode(substr(hire_date,-2),'05',1)) as "2005",
count(decode(substr(hire_date,-2),'06',1)) as "2006",
count(decode(substr(hire_date,-2),'07',1)) as "2007",
count(decode(substr(hire_date,-2),'08',1)) as "2008"
FROM employees;
11. SELECT job_id,
sum(decode(department_id,20,salary)) as "Dept 20",
sum(decode(department_id,50,salary)) as "Dept 50",
sum(decode(department_id,80,salary)) as "Dept 80",
sum(decode(department_id,90,salary)) as "Dept 90",
sum(salary)
FROM employees
GROUP BY job_id;
Lesson 6
1. SELECT location_id,street_address,city,state_province,country_name
FROM locations NATURAL JOIN countries;
2. SELECT last_name,department_id, department_name
FROM employees NATURAL JOIN departments;
3. SELECT e.last_name, e.job_id, department_id, d.department_name
FROM employees e JOIN departments d
using (department_id)
JOIN locations l
on (l.location_id=d.location_id)
WHERE initcap(l.city)='Toronto';
4. SELECT e.last_name as "Employee",e.employee_id as "Emp#",m.last_name as "Manager" ,m.employee_id as "Mgr#"
FROM employees e JOIN employees m
on (e.manager_id=m.employee_id);
5. SELECT e.last_name as "Employee",e.employee_id as "Emp#",m.last_name as "Manager" ,m.employee_id as "Mgr#"
FROM employees e left JOIN employees m
on (e.manager_id=m.employee_id);
6. SELECT e.department_id as department,e.last_name as employee,c.last_name as colleague
FROM employees e JOIN employees c
on (e.department_id=c.department_id
and e.employee_id <> c.employee_id);
7. DESC job_grades;
SELECT last_name, job_id, department_name, salary, grade_level
FROM employees e NATURAL JOIN departments d
JOIN job_grades g
on e.salary between g.lowest_sal and g.highest_sal;
8. SELECT e.last_name, e.hire_date
FROM employees e JOIN employees d
ON (e.hire_date > d.hire_date
and d.last_name='Davies');
9. SELECT e.last_name, e.hire_date,m.last_name,m.hire_date
FROM employees e
JOIN employees m
on (e.manager_id=m.employee_id
and e.hire_date < m.hire_date);
Lesson 7
1. SELECT last_name, hire_date
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
WHERE last_name='&&lname')
AND last_name <> '&lname';
UNDEFINE last_name;
2. SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT
avg(salary) from employees)
ORDER BY 3 ASC;
3. SELECT employee_id, last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM employees
WHERE last_name like '%u%');
4. SELECT last_name, department_id, job_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id=1700);
SELECT last_name, department_id, job_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id=&lid);
5. SELECT last_name,salary
FROM employees
WHERE manager_id = (SELECT
employee_id
FROM employees
WHERE last_name='King' AND manager_id is NULL);
6. SELECT department_id, last_name, job_id
FROM employees where department_id = (
SELECT department_id FROM departments WHERE department_name='Executive');
7.SELECT employee_id, last_name, salary
FROM employees
WHERE department_id IN (
SELECT department_id
FROM employees
WHERE last_name like '%u%')
and salary > (select avg(salary) from employees);
Lesson 8
1. SELECT department_id FROM departments
minus
SELECT department_id FROM employees where job_id <> 'ST_CLERK';
2. SELECT country_id, country_name FROM countries
minus
SELECT country_id, country_name FROM countries join locations using (country_id) join departments using (location_id);
3. SELECT DISTINCT job_id,department_id FROM employees WHERE department_id=10
UNION ALL
SELECT DISTINCT job_id,department_id FROM employees WHERE department_id=50
UNION ALL
SELECT DISTINCT job_id,department_id FROM employees WHERE department_id=20;
4. SELECT employee_id,job_id FROM employees
intersect
SELECT employee_id,job_id FROM job_history;
5. SELECT last_name,department_id, to_char(null) as "Departments"
FROM employees
union
SELECT to_char(null) as "Dummy last_name", department_id,department_name FROM departments;
Lesson 9
1. create table hr.my_employee(
id number(4) not null,
last_name varchar2(25),
first_name varchar2(25),
userid varchar2(8),
salary number(9,2)
);
2. desc my_employee;
3. insert into my_employee
values(1,'Patel','Ralph','rpatel',895);
4. insert into my_employee (id,last_name,first_name,userid,salary)
values(2,'Dancs','Betty','bdancs',860);
5. select * from my_employee;
6. insert into my_employee (id,last_name,first_name,userid,salary)
values(&id,'&lname','&fname','&uid',&sal);
10. update my_employee
set last_name='Drexler'
where id=3;
11. update my_employee
set salary=1000
where salary < 900;
12. select * from my_employee;
13. delete from my_employee where id=2;
14. select * from my_employee;
15. commit;
18. savepoint mentor;
19. delete from my_employee;
20. select * from my_employee;
21. rollback to mentor
22. select * from my_employee;
23. commit;
25. insert into my_employee (id,last_name,first_name,userid,salary)
values(&id,'&&lname','&&fname',lower(concat(substr('&fname',1,1),substr('&lname',1,7))),&sal);
undefine fname;
undefine lname;
26. select * from my_employee;
Lesson 10
id number(7) primary key,
name varchar2(25));
2. desc dept;
insert into dept
select department_id,department_name
from departments;
3. create table emp(
id number(7),
last_name varchar2(25),
first_name varchar2(25),
dept_id number(7));
desc emp;
4. create table employees2
as select employee_id as ID,first_name, last_name,salary,department_id as dept_id
from employees;
5. alter table employees2 read only;
6. insert into employees2
values(32,'Grant','Marcie',5678,10);
7. alter table employees2 read write;
8. drop table employees2;
Lesson 11
1. CREATE OR REPLACE VIEW employees_vu
AS SELECT employee_id,last_name as employee,department_id
FROM employees;
2. SELECT * FROM employees_vu;
3. SELECT employee, department_id
FROM employees_vu;
4. CREATE OR REPLACE VIEW dept50
(empno,employee,deptno)
AS SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id=50
WITH CHECK OPTION;
5. DESC dept50;
SELECT *
FROM dept50;
6. UPDATE dept50
SET deptno=80
WHERE employee='Matos';
7. CREATE SEQUENCE dept_id_seq
start with 300
maxvalue 1000
increment by 10;
8. INSERT INTO dept(id,name)
VALUES(dept_id_seq.NEXTVAL,'Education');
INSERT INTO dept(id,name)
VALUES(dept_id_seq.NEXTVAL,'Administration');
SELECT * FROM dept;
9. CREATE INDEX indx_dept_name
ON dept(name);
10. CREATE SYNONYM emp FOR employees;
1. System Privileges
2. Create table, Create any table
3. Owner, Grantee with Grant option
4. role
5. passw , alter users
6. User1 only
7. grant update on departments to scott with grant option;
8. grant select on regions to ora2 with grant option;
select * from ora1.regions;
grant select on ora1.regions to ora3;
revoke select on regions from ora2;
9 grant select,insert,update,delete on countries to ora2;
10. revoke all on contries from ora2;
11. grant select,insert on departments to ora2;
grant select,insert on departments to ora1;
12. select * from departments;
13. insert into departments
values(500,'Education');
insert into ora1.departments
values(501,'Human Resource');
14. create synonym dept for ora2.departments
15. select * from dept;
16. revoke select,insert on departments from ora2;
17. delete from ora1.departments where department_id >=500;
insert into dilli_emp values(100,NULL);
insert into dilli_emp values(101,100);
insert into dilli_emp values(102,101);
create table dept2(
id number(7),
name varchar2(25)
);
desc dept2;
insert into dept2
select department_id,department_name from departments;
Create table emp4(
id number(7),
last_name varchar2(25),
first_name varchar2(25),
dept_id number(7)
);
5. create table employees2
(id,first_name,last_name,salary,dept_id)
as select employee_id,first_name,last_name,salary,department_id
from employees;
6. drop table emp4;
7. select * from recyclebin;
8. flashback table emp4 to before drop;
9. alter table employees2
drop column first_name;
desc employees2;
10. alter table employees2
set unused column dept_id;
DESC employees2;
11. alter table employees2
drop unused column;
12. Alter table emp4
add constraint pk_emp4 primary key (id);
13. Alter table dept2
add constraint my_dept_id_pk primary key (id);
alter table emp3
add constraint my_emp_dept_id_fk foreign key(department_id) references dept2(id);
15. alter table emp
add comission number(2,2)
constraint chk_emp_comm check (comission > 0);
16. drop table emp purge;
drop table dept2 purge;
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'DEPT2';
17. create table dept_named_index(
deptno number(4) primary key using index(
create index dept_pk_idx on dept_named_index(deptno)),
dname varchar2(30));
18. create directory home_dir as '/home/oracle/dir';
CREATE TABLE oldemp (
fname char(25), lname CHAR(25))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY emp_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
NOBADFILE
NOLOGFILE
FIELDS TERMINATED BY ','
(fname POSITION ( 1:20) CHAR,
lname POSITION (22:41) CHAR))
LOCATION ('emp.dat'))
PARALLEL 5
REJECT LIMIT 200;
drop table library_items_ext;
CREATE TABLE library_items_ext
(category_id number, book_id number, book_price number(7,2),quantity number(5))
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY home_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
NOBADFILE
NOLOGFILE
FIELDS TERMINATED BY ','
)
LOCATION('library_items.dat')
);
select * from library_items_ext;
19. CREATE TABLE dept_add_ext
(location_id, street_address, city,state_province,country_name)
ORGANIZATION EXTERNAL
(
TYPE oracle_Datapump
DEFAULT DIRECTORY home_dir
LOCATION('ora21_emp4.exp','ora21_emp5.exp')
)
as select l.location_id,l.street_address,city,l.state_province,c.country_name
from countries c natural join locations l;
select * from DEPT_ADD_EXT;
select * from library_items_ext;
desc emp2;
drop table emp_books;
20. create table emp_books(sn number constraint pk_sn primary key deferrable initially immediate);
insert into emp_books
values(1);
insert into emp_books
values(1);
insert into emp_books
values(1);
set constraint pk_sn deferred;
insert into emp_books
values(1);
insert into emp_books
values(1);
insert into emp_books
values(1);
