select last_name, first_name, salary
	from employee
	where salary > 
		(select avg(salary) from employee);
		

select last_name
	from borrower
	where last_name in (select author from book);
	

select firstnme, midinit, lastname, salary 
	from employee 
	where salary > all 
		(select salary from employee 
			where workdept = 'E11');
			

select firstnme, midinit, lastname, salary 
	from employee 
	where salary > any 
		(select salary from employee 
			where workdept = 'A00');
			

select book.call_number, title, 
		borrower_id, date_due
	from book natural join checked_out;		-- NOT VALID IN DB2!
	

select book.call_number, title, 
		borrower_id, date_due
	from book, checked_out
	where book.call_number = checked_out.call_number;
		

select b.call_number, title, 
		borrower_id, date_due
	from book b, checked_out c
	where b.call_number = c.call_number;


select b.call_number, title, 
		borrower_id, date_due
	from book b join checked_out c 
		on b.call_number = c.call_number;


select b.call_number, title, 
		borrower_id, date_due
	from book b left outer join checked_out c 
		on b.call_number = c.call_number;
		

select * from person;


select name
    from person
    where parent = 'adam';


select p.name
	from person p join person q 
	  on p.parent = q.name
	where q.parent = 'lamech';

select * from person;

select name
    from person
    where parent = 'adam';

select p.name
	from person p join person q 
	  on p.parent = q.name
	where q.parent = 'lamech';

with descendant(name) as
  (	select name
		from person
		where parent = 'adam'
   union all
	select person.name
		from person, descendant
		where person.parent = descendant.name
  )
select *
	from descendant;

insert into borrower values ('98765','raccoon', 'ralph');

select * from borrower;


insert into borrower (first_name,last_name, borrower_id)
	values ('ursula', 'unicorn', '87654');

select * from borrower;


insert into book (call_number, title)
	values ('ABC', 'Author is unknown');

select * from borrower;


insert into borrower (last_name, borrower_id)
	values ('xerus', '55555');

select * from borrower;


insert into borrower 
	select right(ssn, 4), last_name, first_name
		from employee 
		where not (last_name, first_name) in
		  (select last_name, first_name from borrower);

select * from borrower;


select * from employee;

update employee
	set salary = salary * 1.1
	where supervisor_ssn =
		(select ssn
			from employee
			where last_name = 'aardvark');

select * from employee;


delete from borrower where last_name = 'raccoon';

select * from borrower;


delete from employee;

select * from employee;


create table book ( 
	call_number call_number_type not null,
	copy_number smallint not null,
	title char(30) not null,
	author char(20),
	primary key (call_number, copy_number)
);


create table checked_out (
	borrower_id char(5) not null 
		references borrower,
	call_number char(10) not null,
	copy_number smallint not null,
	date_due date,
	foreign key (call_number, copy_number) 
		references book
);


create table employee (
	ssn char(11) not null primary key,
	last_name char(20) not null,
	first_name char(20) not null,
	salary integer,
	supervisor_ssn char(11),
	foreign key (last_name, first_name) 
	  references borrower(last_name, first_name)
);


create table fine (
	borrower_id char(10) references borrower,



create table fine (
	borrower_id char(10) references borrower
		on delete cascade,
	 ... 




create table enrolled_in (
	student_id student_id_type,
	course_id course_id_type,
	grade char(2) check 
		(grade is null or 
		 grade in ('A', 'A-', 'B+', 'B', 'B-', 
			'C+', 'C', 'C-', 'D+', 'D', 'D-',
			'F') )
);


... constraint employee_borrower 
		foreign key (last_name, first_name)
		references borrower(last_name, first_name)


create trigger book_arrived after insert on book
	-- appropriate action 
		- syntax implementation-specific


grant select on foo to user aardvark;


grant select on foo to public;


create view viewname as query;


create view books_out as
	select title from book join checked_out on
		book.call_number = checked_out.call_number;