Oracle 11g SQL Fundamental II Answers

Lesson 3

1. select table_name from user_tables;

2. select table_name,owner from all_tables;

3. select column_name,data_type, data_length,data_precision,data_scale, nullable

from user_tab_columns where table_name='DEPARTMENTS';

4. select column_name, constraint_name, constraint_type, search_condition, status

from user_constraints Natural join user_cons_columns;

5. Comment on table departments is 'Company department information including name, code, and loction';

select comments from user_tab_comments;

6. Create synonym emp for employees;

select synonym_name, table_owner, table_name, db_link

from user_synonyms;

7. create view dept50

as select employee_id empno, last_name employee, department_id deptno from employees where department_id=50;

select view_name, text from user_views;

8. select sequence_name,min_value, max_value,increment_by, last_number from user_sequences;

select DEPARTMENTS_SEQ.nextval from dual;

create table dept2 as select * from departments;

create table emp2 as select * from employees;

alter table dept2 add constraint my_dept_id_pk primary key(department_id);

alter table emp2 add constraint my_emp_id_pk primary key(employee_id);

alter table emp2 add constraint my_emp_dept_id_fk foreign key(department_id) references departments(department_id);

9. select table_name from user_tables where table_name in ('EMP2','DEPT2');

10. select constraint_name, constraint_type from user_constraints where table_name in ('EMP2','DEPT2');

11. select object_name, object_type from user_objects where object_name in ('EMP2','DEPT2');

12. Create table sales_dept(

team_id number(3) primary key using index(

create index sales_pk_idx on sales_dept(team_id)),

location varchar2(30)

);

select index_name, table_name, uniqueness

from user_indexes where table_name='SALES_DEPT';

Lesson 4

1. CREATE TABLE sal_history(

employee_id number(6,0),

hire_date date,

salary number(8,2));

2. SELECT c.column_name, c.data_type, c.nullable, 

c.data_default, c.column_id, decode(uc.constraint_type,'P','Primary',NULL) "Primary Key"

FROM user_tab_columns c left join user_cons_columns cc

on (c.table_name=cc.table_name and c.column_name=cc.column_name)

left join user_constraints uc

on (cc.constraint_name=uc.constraint_name)

where c.table_name='SAL_HISTORY';

3. CREATE TABLE mgr_history(

employee_id number(6),

manager_id number(6),

salary number(8,2));

4. SELECT c.column_name, c.data_type, c.nullable, 

c.data_default, c.column_id, decode(uc.constraint_type,'P','Primary',NULL) "Primary Key"

FROM user_tab_columns c left join user_cons_columns cc

on (c.table_name=cc.table_name and c.column_name=cc.column_name)

left join user_constraints uc

on (cc.constraint_name=uc.constraint_name)

where c.table_name='MGR_HISTORY';

5. CREATE TABLE special_sal(

employee_id number(6),

salary number(8,2));

6. SELECT c.column_name, c.data_type, c.nullable, 

c.data_default, c.column_id, decode(uc.constraint_type,'P','Primary',NULL) "Primary Key"

FROM user_tab_columns c left join user_cons_columns cc

on (c.table_name=cc.table_name and c.column_name=cc.column_name)

left join user_constraints uc

on (cc.constraint_name=uc.constraint_name)

where c.table_name='SAL_HISTORY';

7. INSERT ALL

when salary > 20000 then

into special_sal values(employee_id,salary)

when 1=1 then

into sal_history values(employee_id,hire_date,salary)

into mgr_history values(employee_id,manager_id,salary)

SELECT employee_id,hire_date,salary,manager_id FROM employees where employee_id < 125;

SELECT * FROM special_sal;

SELECT * FROM sal_history;

SELECT * FROM mgr_history;

8. 

a.CREATE TABLE sales_week_data(

id number(6),

week_id number(2),

qty_mon number(8,2),

qty_tue number(8,2),

qty_wed number(8,2),

qty_thur number(8,2),

qty_fri number(8,2));

b. insert into sales_week_data

values(200,6,2050,2200,1700,1200,3000);

c. desc sales_week_data;

d. SELECT * FROM sales_week_data;

e. CREATE TABLE emp_sales_info(

id number(6),

week number(2),

qty_sales number(8,2));

f. DESC emp_sales_info;

g. INSERT ALL

into emp_sales_info values(id,week_id,qty_mon)

into emp_sales_info values(id,week_id,qty_tue)

into emp_sales_info values(id,week_id,qty_wed)

into emp_sales_info values(id,week_id,qty_thur)

into emp_sales_info values(id,week_id,qty_fri)

SELECT id,

week_id,

qty_mon,

qty_tue,

qty_wed,

qty_thur,

qty_fri

FROM sales_week_data;

h. SELECT * FROM emp_sales_info;

9. CREATE TABLE EMP_DATA (

name varchar2(25), 

email varchar2(25))

ORGANIZATION EXTERNAL

(TYPE ORACLE_LOADER

DEFAULT DIRECTORY emp_dir

ACCESS PARAMETERS

(RECORDS DELIMITED BY NEWLINE

NOBADFILE

NOLOGFILE

FIELDS TERMINATED BY ','

)

LOCATION ('emp.dat'));

SELECT * FROM EMP_DATA;

10. CREATE TABLE emp_hist(

name varchar2(25), 

email varchar2(25));

a. alter table emp_data

modify email varchar2(45);

truncate table emp_hist;

insert into emp_hist values('arun','email_arun');

insert into emp_hist values('rajendra','rajendra_email');

insert into emp_hist values('riya','email_riya');

insert into emp_hist values('sujan','s_email');

insert into emp_hist values('pradeep','pr_email');

insert into emp_hist values('dilip','dilip_email');

insert into emp_hist values('dhirendra','dndra_email');

COMMIT;

b. merge into emp_hist eh

using emp_data ed

on (eh.name=ed.name)

when matched then

update set eh.email=ed.email

when not matched then

insert  values(ed.name,ed.email);

c. SELECT * FROM emp_hist;

11. CREATE TABLE EMP3

as SELECT * FROM employees;

SELECT * FROM emp3;

update emp3

set department_id=60

where last_name='Kochhar';

update emp3

set department_id=90

where last_name='Kochhar';

COMMIT;

SELECT * FROM emp3

where last_name='Kochhar';

SELECT versions_starttime "START_DATE",

versions_endtime "END_DATE",department_id FROM emp3

versions between scn minvalue and maxvalue

where last_name='Kochhar';

Lesson 5

1. ALTER session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS.';

2. SELECT tz_offset('US/PACIFIC-NEW') FROM dual;
SELECT tz_offset('SINGAPORE') FROM dual;
SELECT tz_offset('EGYPT') FROM dual;

b. ALTER SESSION SET time_zone='US/Pacific-New';

c. SELECT current_date, current_timestamp, localtimestamp
FROM dual;

d. ALTER SESSION SET time_zone='SINGAPORE';

e. SELECT current_date, current_timestamp, localtimestamp
FROM dual;

3. SELECT dbtimezone, sessiontimezone FROM dual;

4. SELECT last_name, extract(year FROM hire_date) FROM employees;

5. ALTER SESSION set nls_date_format='DD-MON-YYYY';

6. CREATE TABLE sample_dates(
date_col date);
insert into sample_dates
values(sysdate);
commit;
SELECT * FROM sample_dates;

ALTER table sample_dates
modify date_col timestamp;

SELECT * FROM sample_dates;

ALTER table sample_dates
modify date_col timestamp with time zone;

7. SELECT last_name, hire_date,CASE when extract(year FROM hire_date) = '2006' THEN 
'Needs Review' 
ELSE
'Not this year'
END
FROM employees;

8. SELECT last_name,hire_date,sysdate,
CASE
when hire_date + to_yminterval('15-0') <= sysdate THEN
'15 years of service'
when hire_date + to_yminterval('10-0') <= sysdate THEN
'10 years of service'
when hire_date + to_yminterval('5-0') <= sysdate THEN
'5 years of service'
ELSE
'maybe next year!'
END
FROM employees;

Lesson 6

1. SELECT last_name, department_id, salary FROM employees e
WHERE (department_id, salary) IN (SELECT department_id, salary FROM employees WHERE commission_pct is not null);

2. SELECT last_name, department_name, salary 
FROM employees e NATURAL JOIN departments d
WHERE (salary,commission_pct) IN (
SELECT salary,commission_pct FROM employees NATURAL JOIN departments WHERE location_id=1700);
SELECT * FROM departments;

3. SELECT last_name, hire_date, salary
FROM employees WHERE (salary,nvl(commission_pct,0)) =
(SELECT salary,nvl(commission_pct,0) FROM employees WHERE last_name='Kochhar')
and last_name!='Kochhar';

4. SELECT last_name, job_id, salary
FROM employees WHERE salary > all (SELECT salary FROM employees WHERE job_id='SA_MAN')
order by salary desc;

5. SELECT employee_id,last_name, department_id
FROM employees e WHERE exists (
SELECT department_id
FROM departments NATURAL JOIN locations
WHERE department_id=e.department_id
and city like 'T%');

6. SELECT last_name, salary, e.department_id, av.dept_avg
FROM employees e join (SELECT department_id,round(avg(salary),2) dept_avg FROM employees GROUP BY department_id) av
on (e.department_id=av.department_id)
and e.salary > av.dept_avg ;

7. SELECT last_name FROM employees e
WHERE not exists (SELECT 'X' FROM employees WHERE manager_id = e.employee_id);

8.SELECT last_name FROM employees e
WHERE exists (SELECT avg(salary) FROM employees 
WHERE department_id=e.department_id 
having avg(salary) > e.salary);

9. SELECT last_name FROM employees e
WHERE 2 < (SELECT count(last_name) FROM employees WHERE department_id=e.department_id 
and hire_date > e.hire_date
and salary > e.salary); 

10. SELECT employee_id,last_name, (SELECT department_name FROM departments WHERE department_id=e.department_id) AS departments
FROM employees e ;

11. with 
Summary AS (
SELECT department_id,sum(salary) dept_total FROM employees
GROUP BY department_id)
SELECT d.department_name, dept_total
FROM departments d join summary s
USING(department_id)
WHERE dept_total > (SELECT sum(salary)/8 FROM employees);

Lesson 7

1. SELECT first_name,last_name FROM employees where regexp_like(last_name,'^K(i|o)');

2. SELECT street_address,regexp_replace(street_address,' *','') FROM locations;

3. SELECT regexp_replace(street_Address,'ST$','STREET') FROM locations;
/*
Create a contacts table and add a check constraint to the p_number column to enforce the
following format mask to ensure that phone numbers are entered into the database in the
following standard format: (XXX) XXX-XXXX. The table should have the following
columns:
*/

4. DROP TABLE contacts;
CREATE TABLE contacts(
l_name varchar2(30),
p_number varchar2(30) check (regexp_like(p_number,'^\(\d\d\d\) \d\d\d-\d\d\d\d$')));


5. INSERT INTO contacts(P_NUMBER)
VALUES('(650) 555-5555');
INSERT INTO contacts(P_NUMBER)
VALUES('(215) 555-3427');
INSERT INTO contacts(P_NUMBER)
VALUES('650 555-5555');
INSERT INTO contacts(P_NUMBER)
VALUES('650 555 5555');
INSERT INTO contacts(P_NUMBER)
VALUES('(650)555-5555');
INSERT INTO contacts(P_NUMBER)
VALUES(' (650) 555-5555');



6. SELECT regexp_count('gtctcgtctcgttctgtctgtcgttctg','ctc',1,'i') FROM dual;