1. create table employee2 ( -- fewer columns than table employee
fname varchar(15) not null,
lname varchar(15) not null,
ssn char(9) not null,
salary decimal(10,2),
super_ssn char(9),
dno int not null,
primary key (ssn),
foreign key (super_ssn) references employee(ssn),
foreign key (dno) references department(dnumber)
);
2. create table department2 ( -- again, fewer columns than department
dname varchar(15) not null,
dnumber int not null,
mgr_ssn char(9) not null,
primary key (dnumber),
foreign key (mgr_ssn) references employee(ssn)
);
3. insert into employee2 values
('peter', 'dordal', '123456789', 29000.01, '012345678', 55);
4. delete from employee2 where fname='peter';
5. update employee2 set salary = 1.10 * salary where salary >= 50000;
6. select e.lname from employee2 e where e.dno = 5;
7a. select e.lname from employee2 e join department2 d on e.dno = d.dnumber where d.dname="maintenance";
Same as previous, but old-style join:
7b. select e.lname from employee2 e, department2 d where e.dno = d.dnumber and d.dname="maintenance";
8. select e.lname from employee2 e where e.salary in (select e.salary from employee2 e where e.dno = 5);
9. select e.dno, count(*) from employee2 e GROUP BY e.dno;
10. select e.dno, sum(e.salary) from employee2 e GROUP BY e.dno
11. select
d.dname, sum(e.salary) from employee2 e join
department2 d on e.dno = d.dnumber
group by d.dname HAVING count(*) >=
3;