Oracle: can get free version from 
http://www.oracle.com/

create table student (
    id int,
    name varchar(20),
    email varchar(20),
    primary key(id)
);

create table course (
    id int,
    name varchar(20),
    description varchar2(2000),
    credits number(3,1),
    primary key(id)
);

create table faculty (
    id int,
    name varchar(20),
    email varchar(20),
    paygrade varchar(10),
    primary key(id)
);


create table section (
    id int,
    code varchar(10),
    semester varchar(10),
    year int,
    facultyid int,
    primary key(id)
);

create table sectiontimes (
    id int,
    sectionid int,
    str number(4),	-- HHMM
    end number(4),	-- HHMM
    room varchar(10),
    primary key(id)
);

create table registration (
    studentid int,
    sectionid int,
    grade varchar(4),
    primary key(studentid,sectionid)
);

create table prereq (
    courseid int,
    needscourseid int,
    primary key(courseid,needscourseid)
);


insert into student (id,name,email) 
 values(1,'bob','bob@rife.com');
insert into student (id,name,email) 
 values(2,'bill','bill@msn.com');
insert into student (id,name,email) 
 values(3,'jane','jane@yahoo.com');
insert into student (id,name,email) 
 values(4,'john','john@doe.com');

insert into faculty (id,name,email) 
 values(1,'mike','mike@oracle.com');
insert into faculty (id,name,email) 
 values(2,'bill','bill@msn.com');
insert into faculty (id,name,email) 
 values(3,'johnson','johnson@yahoo.com');
insert into faculty (id,name,email) 
 values(4,'jane','jane@bc.com');



select name,email
from student
union all
select name,email
from faculty
/



select name,email
from student
union
select name,email
from faculty
/


select name,email
from student
intersect
select name,email
from faculty;


select name,email
from student
minus
select name,email
from faculty;


---set membership---

select * 
from student 
where id in (2,3,4,5);


select *
from student
where email in ( select email from faculty )


select *
from student
where id > all (1,2,3)


select *
from student
where id > some (1,2,3)



Homework:

employee(employee-name, street, city)
works(employee-name, company-name, salary)
company(company-name, city)
manages(employee-name, manager-name)


1) Find ’John Doe’s Manager’s Name.

SELECT MANAGER_NAME
FROM MANAGES
WHERE EMPLOYEE_NAME = 'JOHN DOE';


2) Find employee’s whom ’John Doe’ manages.

SELECT EMPLOYEE_NAME
FROM MANAGES
WHERE MANAGER_NAME = 'JOHN DOE';

3) Find all companies that have exactly 75 employees.

SELECT COMPANY_NAME,COUNT(EMPLOYEE_NAME)
FROM WORKS
GROUP BY COMPANY_NAME
HAVING COUNT(EMPLOYEE_NAME) = 75

4) Find in how many cities is ’First Bank Corp.’ 
located.

SELECT COUNT(CITY)
FROM COMPANY
WHERE COMPANY-NAME = 'FIRST BANK CORP.'


5) Find how many employees work for ’CityBank’ 
in New York.

SELECT count(w.employee_name)
FROM works w, company c
WHERE w.company_name = c.company_name 
and c.city = 'New York' 
and c.company_name = 'CityBank'

6) Find the company with the most employees.

SELECT COMPANY_NAME,COUNT(EMPLOYEE_NAME)
FROM WORKS
GROUP BY COMPANY_NAME
HAVING COUNT(EMPLOYEE_NAME) >= ALL (
    SELECT COUNT(EMPLOYEE_NAME)
    FROM WORKS
    GROUP BY COMPANY_NAME
)


7) Find companies with above average salaries

-- average from per company average
SELECT COMPANY_NAME
FROM WORKS 
GROUP BY COMPANY_NAME
HAVING AVG(SALARY) >= ALL
    SELECT AVG(SALARY)
    FROM WORKS
    GROUP BY COMPANY_NAME
);


--average from everyone's average
SELECT COMPANY_NAME
FROM WORKS 
GROUP BY COMPANY_NAME
HAVING AVG(SALARY) >= ALL
    SELECT AVG(SALARY)
    FROM WORKS
);


8) Find the comapny with the most 
   non-managing employees.

select employee_name
from manages
minus
select manager_name
from manages



employee(employee-name, street, city)
works(employee-name, company-name, salary)
company(company-name, city)
manages(employee-name, manager-name)