grupo_8_o_12_tp1.sql

-- ejercicio 2
use LaboSQL_12;
select authors.au_lname, authors.au_fname, titles.title from authors
inner join titleauthor on authors.au_id = titleauthor.au_id
inner join titles on titles.title_id = titleauthor.title_id;

-- ejercicio 3
select * from authors
where state = 'CA' and au_lname not like 'M%';

-- ejercicio 4
insert into authors (au_id, au_lname, au_fname, phone, address, city, state, zip, contract)
values ('999-55-3333', 'Doe', 'Carlos', '555 555-5555', 'False Road 123', 'Springfield', 'WA', '90125', 1);

insert into titles (title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate)
values ('BU1234', 'My life as a spy', 'horror', 1389, 10.00, 100, 5, 1324, NULL, '1995-05-21');

insert into titleauthor (au_id, title_id) values
('999-55-3333','BU1234');

-- ejercicio 5
select sum(ytd_sales), type from titles where ytd_sales is not null group by type;

-- ejercicio 6
select count(*) as cantidad, avg(price) as precio_promedio, type from titles where price is not null group by type;

-- ejercicio 7
select avg(3*price) as precio_promedio_triple from titles where price is not null;

-- ejercicio 8
select sales.stor_id, sum(sales.qty*titles.price) as total_ventas, avg(sales.qty*titles.price) as promedio_ventas from sales inner join
titles on sales.title_id = titles.title_id group by sales.stor_id;

-- ejercicio 9
select ytd_sales, authors.au_lname, authors.au_fname, 
ytd_sales * royalty/100 * price as ganancia_autor, 
ytd_sales * (1-royalty/100) * price as ganancia_publisher 
from titles 
inner join titleauthor on titles.title_id = titleauthor.title_id
inner join authors on authors.au_id = titleauthor.au_id
where ytd_sales is not null and price is not null and royalty is not null order by
ytd_sales desc, au_lname asc, au_fname asc;

-- ejercicio 10
-- varsion fea
select job_lvl from (select top 1 job_lvl, count(*) as cant from employee group by job_lvl order by cant desc) as lvl_con_mas_empleados;

-- vesion no tan fea (pero fea igual)
select job_lvl from employee group by job_lvl having count(*) = 
(select max(cant) from (select count(*) as cant from employee group by job_lvl) as x);

-- ejercicio 11
select publishers.pub_id, publishers.pub_name 
    from employee 
    inner join publishers on publishers.pub_id = employee.pub_id 
    where job_lvl = (select max(job_lvl) from employee);

-- ejercicio 12
-- Version 1
select pub_id, pub_name 
    from publishers 
    where pub_id in (select pub_id from titles where type = 'business');

-- Version 2
select pub_id, pub_name 
    from publishers 
    where exists 
        (select 1 from titles 
            where type = 'business' and titles.pub_id = publishers.pub_id);

-- ejercicio 13
select type, max(advance) 
    from titles 
    group by type 
    having max(advance) > (select 2*avg(advance) from titles);

-- ejercicio 14
select publishers.pub_id, publishers.pub_name 
    from employee  
    inner join publishers on employee.pub_id = publishers.pub_id 
    where employee.hire_date = (select min(hire_date) from employee);

-- ejercicio 15

-- ejercicio 16
CREATE PROCEDURE BooksByAuthor @PubId smallint = NULL
AS
BEGIN
    select title, au_ord, au_lname, price, ytd_sales, pub_id
    from authors, titles, titleauthor
    where authors.au_id = titleauthor.au_id
       AND titles.title_id = titleauthor.title_id order by title;
END;

-- ejercicio 17
create table employee_lvl_history (history_id int auto_increment, emp_id  char(9), old_job_lvl smallint, change_date date, primary key (history_id));

-- ejercicio 18
create trigger employee_lvl_update update of job_lvl on employee
begin
    insert into employee_lvl_history (emp_id, old_job_lvl,change_date) values (OLD.emp_id, OLD.job_lvl, date());
end;

-- ejercicio 19
select * from employee 
    where emp_id in (select emp_id from employee_lvl_history group by emp_id having count(*) = 
        (select max(cant) from (select count(*) as cant from employee_lvl_history group by emp_id) as cantidad)
    );

-- ejercicio 20
select * from publishers where pub_id in (select pub_id from employee where emp_id in (select emp_id from employee_lvl_history group by emp_id having count(*) > (select avg(cant) from (select count(*) as cant from employee_lvl_history group by emp_id) as cantidad)));

-- ejercicio 21
select job_id from employee group by job_id having count(distinct pub_id) = (select count(*) from publishers);

-- ejercicio 22
CREATE PROCEDURE BooksByAuthor @PubId smallint = NULL
AS
BEGIN
    if @PubId is null
        select authors.au_lname, authors.au_fname, count(titles.title_id) as cantidad
        from authors 
        inner join titleauthor on authors.au_id = titleauthor.au_id 
        inner join titles on titles.title_id = titleauthor.title_id 
        group by authors.au_id;
    else 
        select authors.au_lname, authors.au_fname, count(titles.title_id) as cantidad
        from authors 
        inner join titleauthor on authors.au_id = titleauthor.au_id 
        inner join titles on titles.title_id = titleauthor.title_id 
        where title.pub_id = @PubId
        group by authors.au_id;
END