rem CS 541 Sqlplus Project Sample Solutions

rem Jiangtao Li
rem jtli@cs.purdue.edu


rem 
rem select name of employee with mininum salary
rem (sample of nested select)
prompt sample 1:

select last_name, first_name
from jtli.s_emp
where salary = (select min(salary)
		from jtli.s_emp);


rem
rem select employ name and their dept name
rem (sample of join)
prompt sample 2:

select e.last_name, e.first_name, d.name
from jtli.s_dept d, jtli.s_emp e
where e.dept_id = d.id;


rem 
rem create a view show the employee id and name with salary > 1400
rem (sample of view)
prompt sample 3:

create view viewA (id, last_name, first_name, salary)
as select e.id, e.last_name, e.first_name, e.salary
from jtli.s_emp e
where e.salary > 1400;

select * from viewA;


rem
rem list all view created
rem
prompt sample 4:

select view_name from user_views;


rem
rem drop view created
rem 

drop view viewA;