Andre Long
						CISA 321 SQL
						December 8, 2004
						Chapter 12 homework



1)
	a)[Oracle and Access]
	   select supplier_id,
	   count(product_code) as number_of_foods_on_menu
	   from l_foods
	   group by supplier_id
	   order by supplier_id;

	b)select dept_code, 
	   count(employee_id) as number_of_employees
	   from l_employees
	   group by dept_code 
	   order by dept_code;

	c)select dept_code, credit_limit,
	   count(employee_id) as number_of_employees
	   from l_employees
	   group by dept_code , credit_limit
	   order by dept_code;

2)
	a)STEP 1
                     create or replace view ex1202a as
	   select lunch_id,
	   to_char(date_entered, 'yyyy-mm-dd') as date_entered_2
	   from l_lunches;
	   STEP 2
	   select date_entered_2 as date_entered,
	              count(lunch_id) as number_of_lunches_entered
	   from ex1202a
	   group by date_entered_2;

	b)select lunch_date,
	   count(*)
	   from l_lunches
	   group by lunch_date;

	c)select to_char( hire_date, 'yy'),
	  count(employee_id) as number_of_employees_hired
	  from l_employees
	  group by to_char( hire_date, 'yy');

3)
	a)select n,
	             count(*) as number_of_occurrences
	   from ex1203a
	   where n < 200
	   group by n
	   having count(*)>1
	   order by n desc;

	b)select first_name,
	             last_name,
	             state,
	             count(*)
	             from ex1203b
	             where (state='CA' OR state='WA' OR state='OR')
	              group by first_name, last_name, state
	              having count(*) > 1
	              order by last_name;

4)
	a)Read sections 12-9 & 12-10

	b)select complete_set,
	   N
	   from ex1204b
	   where N is null;

	c)select complete_set,
	   count(*)
	   from ex1204b
	   where N = complete_set
	   group by complete_set
	   having count(N) = 0;