create table star_wars2 as 
select * from star_wars;

alter table star_wars2
add constraints "star_wars2_pk" primary key (episode_id);

create table characters2 (
charater_id number(5),
charater_name varchar2(30));

alter table characters2
add constraints "characters_fk" foreign key(charater_id)
references characters (character_id);



select *
from emp
where salary = (select max(salary)
                from emp);
                
select empid, fname
from emp
where salary > (select avg(salary)    
                from emp);
                
select city
from loc
where locid = (select locid
            from dept
            where deptid = '10');
            
select deptname
from dept
where mgrid = (select mgrid
              from emp
              where empid = '107');
              
              
              
»ç¹øÀÌ 115¹øÀÌ ÇÏ´Â Á÷¹«¿¡¼­ ÃÖ¼Ò, ÃÖ´ë »ø·¯¸® ±Ý¾×

select min_salary, max_salary
from job
where jobid = (select jobid
               from emp
                where empid = '115');

±Þ¿©°¡ 15000 ÀÌ»óÀÎ »ç¶÷°ú °°Àº ÀÏÀ» ÇÏ´Â »ç¶÷ÀÇ ¸ðµç Á¤º¸

select *
from emp
where salary IN (select salary
                form emp
                where salary > 15000);

loc id °¡ 1700ÀÎ ºÎ¼­¿¡ ±Ù¹«ÇÏ´Â »ç¶÷µéÀÇ ¸ðµçÁ¤º¸

select *
from emp
where deptid IN (select deptid
              from dept
              where locid = '1700');

¾÷¹«°¡ ITÀÎ Á÷¿øµéÀÇ sal º¸´Ù ¸¹Àº salÀ» ¹Þ´Â Á÷¿ø ´©±¸³ª Ãâ·Â (x)

select *
from emp
where jobid > ANY (select jobid
                    from job
                    where jobid = 'IT_PROG'); 

¾÷¹«°¡ 'MANAGER' ÀÎ Á÷¿øÀÇ ÃÖ°í sal º¸´Ù 
¸¹ÀÌ ¹Þ´Â »ç¶÷ÀÇ Á÷¿ø¹øÈ£, À̸§, ¾÷¹«, ±Þ¿© Ãâ·Â

select empid, fname, jobid, salary
from emp
where salary > ALL (select salary
                  from emp
                  where jobid = 'IT_PROG');


select deptid, deptname
from dept
where exists (select *
              from emp 
              where deptid = 30);


1.
select *
from characters
where character_name like '´Ù%';

2.
select real_name
from casting
where episode_id = (select episode_id
                    from star_wars
                    where episode_id = 4);



3. ¿¡ÇǼҵå 4,5 µÑ´Ù °°Àº ¹è¿ªÀ¸·Î Ã⿬ÇÑ »ç¶÷À̸§
SELECT a.real_name
   FROM casting a, casting b
   WHERE a.episode_id = 5
   AND b.episode_id = 4
   AND a.character_id = b.character_id;


4.
select real_name
from casting 
where episode_id =5

minus

select real_name
from casting 
where episode_id =4;

5. 
select real_name
from casting
where episode_id = (select episode_id
                    from star_wars
                    where episode_name = 'Á¦±¹ÀÇ ¿ª½À(The Empire Strikes Back)');

6. 

select episode_id, count(*)
from casting
group by episode_id;


                    
7.
select episode_id, real_name
from casting
where character_id = (select character_id
                      from characters 
                      where character_name = '¾Æ¹Ì´Þ¶ó ¿©¿Õ');

8.
select episode_id 
from casting
group by episode_id
having count (*) > 10;


9.
select episode_id, episode_name
from star_wars
where episode_id IN (select episode_id
                    from casting
                    where real_name = 'Çظ®½¼ Æ÷µå');

10. 

select episode_id, count (*)
from casting
group by episode_id
having count (*) <= ALL (select count (*)
                          from casting
                          group by episode_id);
                          
13.

select real_name, episode_id
from casting
where episode_id IN (select episode_id
                    from casting
                    group by episode_id
                    having count (*) >=  ALL (select count (*)
                                              from casting
                                              group by episode_id));


[ ¿ÀÈÄ Å¸ÀÓ]
ºÎ¼­º° ºÎ¼­ id, Æò±Õ salary

- group by ¿¹Á¦
select deptid, avg(salary)
from emp group by deptid;

1. ºÎ¼­º°·Î ºÎ¼­ ID, Àοø¼ö, Æò±Õ±Þ¿©

select deptid, count (*), avg(salary)
from emp
group by deptid;

2. ºÎ¼­º°·Î ºÎ¼­ ID, Àοø¼ö, Æò±Õ±Þ¿© and ºÎ¼­¹øÈ£°¡ 50º¸´Ù Å«

select deptid, count (*), avg(salary)
from emp
group by deptid
having deptid >50;

3. ºÎ¼­º°·Î ºÎ¼­ ID, Àοø¼ö, Æò±Õ±Þ¿© and ºÎ¼­¹øÈ£°¡ 50º¸´Ù Å«, salary°¡ 8000º¸´Ù Å«

select deptid, count (*), round(avg(salary))
from emp
where deptid >50
group by deptid
having avg(salary) > 8000 AND count (*) > 5 ;

4. ºÎ¼­º°·Î ºÎ¼­ ID, Àοø¼ö, Æò±Õ±Þ¿© , ºÎ¼­¸í
   ºÎ¼­ id°¡ 90º¸´Ù ÀÛ°í Æò±Õ±Þ¿©°¡ 10000º¸´Ù ÀûÀº
   Á¤·Ä ºÎ¼­ idº°·Î Á¤·Ä

select e.deptid AS ºÎ¼­ , count (*) AS Àοø¼ö, round(avg(e.salary)) AS Æò±Õ±Þ¿©, d.deptname AS ºÎ¼­¸í
from emp e, dept d
where e.deptid = d.deptid AND e.deptid < 90
group by e.deptid, d.petname 
having avg(e.salary) < 10000
order by e.deptid;

[index ¸¸µé±â]

create index cus_idx on customer(num);
create index cus_idx2 on customer(name);
create unique index cus_idx3 on customer(address);
drop index cus_idx3;

insert into customer values('111','dada','seoul');
insert into customer values('211','dayon','seoul');
insert into customer values('311','mel gibson','la');
insert into customer values('411','tom cruise','seoul');
insert into customer values('511','hue grant','la');
insert into customer values('611','diane soyer','london');
insert into customer values('711','tom hanks','london');
insert into customer values('811','gim carry','seattle');
insert into customer values('911','shone cornery','seattle');
insert into customer values('121','george cloony','florida');
insert into customer values('131','michel phiper','tokyo');
insert into customer values('141','allec boldwin','beijing');
commit;

delete from customer;

select * from customer;


[view]
View ¸¸µé±â

emp_view

Create or replace view emp_view
AS select empid, fname, email, deptid
from emp;

desc emp_view;
select * from emp_view;

insert into emp_view values(207, 'Brian','ABCC',110); 

Sequence ¸¸µé±â

create sequence cus_seq
minvalue 1
increment by 1
start with 20;

sequence »ç¿ëÇϱâ

select cus_seq.currval
from dual;

select * from customer order by num;
insert into customer values(cus_seq.nextval, 'rolly','la');


[Months_Between(date1,date2) ¸¦ ÀÌ¿ëÇÑ ³¯Â¥ ¾Ë¾Æ³»±â

select months_between('2013-09-01','2013-01-01')
from dual;

[AlexanderÀÇ ±Ù¼Ó ³¯Â¥ ¾Ë¾Æ³»±â]

select round(months_between(sysdate,hdate)/12,0)
from emp
where empid = 115;

[DECODE ¿¹Á¦]
select empid, fname, comm,
DECODE(comm, null,'no','yes') AS "Ä¿¹Ì¼Ç"
from emp;

select empid, fname, deptid, 
decode(deptid,90,'seattle',60,'South Lake',100,'New yourk','else') AS µµ½Ã¸í
from emp
order by empid;