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 eWHERE (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;
0 comments:
Post a Comment