ESCUELA SUPERIOR POLITECNICA DEL LITORAL

FACULTAD DE INGENIERIA EN ELECTRICIDAD Y

COMPUTACION

SISTEMAS DE BASES DE DATOS I

TRABAJO EN CLASE

EJERCICIOS SQL

ALUMNO.

GUZMAN CONTRERAS MAURICIO

PROFESOR. ING. FABRICIO ECHEVERRIA

PARALELO. 01

GUAYAQUIL, JULIO 22 DE 2009

Ejercicios 1.

1.

select emp_id, emp_fname, emp_lname, salary

from employee

where salary<(select avg(salary)

from employee)

2.

select emp_id, emp_fname, emp_lname

from employee

where dept_id in(select dept_id

from department where dept_name='R & D')

3.

select emp_id, emp_fname, emp_lname, salary

from employee

where salary>some(select salary

from employee

where city='Milton')

4.

select emp_id, emp_fname, emp_lname, salary

from employee

where salary>all(select salary

from employee

where city='Milton')

5.

select prod.name, prod.quantity, clte.fname, clte.lname

from product prod, customer clte, sales_order salo, sales_order_items sali

where clte.id=salo.cust_id and salo.id=sali.id and sali.prod_id=prod.id

6.

SELECT dep1.dept_name, sum(emp1.salary) as total

FROM employee emp1, department dep1

where emp1.dept_id = dep1.dept_id

group by dep1.dept_name

Ejercicios 2.

1.

Para la clausula some, la clausula equivalente es between.

2.

Para la clausula all, la clausula equivalente es max.

3.

SELECT emp1.emp_fname, emp1.emp_lname

FROM employee emp1, department dep1

where exists (select * from employee where employee.salary > '80000' and employee.city =

'Milton')

4.

SELECT emp1.emp_fname, cus.fname

FROM employee emp1, customer cus

SELECT employee.emp_fname from employee

union all

select customer.fname from customer

5.

SELECT employee.city from employee

intersect all

select customer.city from customer

6.

SELECT employee.city from employee

except all

select contact.city from contact

1.

insert into contact_copy

select *

from contact

2.

insert into contact_copy(id, last_name, first_name, title, street, city, state, zip, phone, fax)

values (12,'Hildebrand','Jane','ma','280 Washington

St.','Kanata','CA','94608','5105551309','5105554209')

3.

delete from contact_copy

4.

delete from contact_copy

where city like'm%' or city like'l%' or city like'k%'

5.

insert into employee_copy

select *

from employee

6.

insert into employee_copy(emp_id, manager_id, emp_fname, emp_lname, dept_id, street, city,

state, zip_code, phone, status, ss_number, salary, start_date, termination_date, birth_date,

bene_health_ins, bene_life_ins, bene_day_care, sex)

values(310,702,'mauricio','guzman','521','urdesanorte','guay','guaya',012457128,042156789,'z',01

24578,400000,'1984-09-12',null,'1972-02-12','d','j','f','m')

7.

delete

from employee_copy

where salary<(select 0.5*avg(salary)

from employee_copy)

8.

update employee_copy set salary= salary+salary*0.2

9.

update employee_copy set salary= salary+salary*0.1

where sex= 'f'

10.

update employee_copy set salary= salary-1000

where salary>(select avg(salary)

from employee_copy)

11.

select distinct empy.emp_id, empy.emp_fname

from employee empy, employee mana

where empy.emp_id= mana.manager_id