CSE 344 section 02 Problems

==========================================================================================
1. More SQL Practice

1.1 Schema

	CREATE TABLE Population (
	  rank INTEGER,
	  country VARCHAR(30),
	  population INTEGER,
	  percentage FLOAT		
	);

	CREATE TABLE GDP (
	  rank INTEGER,
	  country VARCHAR(30),
	  gdp INTEGER	
	);

	CREATE TABLE Airport (
	  code VARCHAR(30),
	  name VARCHAR(30),
	  country VARCHAR(30)
	);

1.2 Problems

	What is the total population of earth?
	What is the percentage of the population from the top 10 populated countries?
	How many countries do have less than 1,000,000 population?
	How many countries have airports?
	Top 10 countries with most international airports
	Order the top 10 countries by total GDP in billion dollars

1.3 Extra subquery problems -- practice problems

	What is the GDP ratio of the average top/bottom 10 countries?
	Which country over 10 million has the biggest GDP per capita?
	What percentage of the total GDP does USA have?
	Top 10 countries with most international airports/population
	How many countries more than 1000 airports?

==========================================================================================
2. Join & Aggregation example queries from section 1

2.1 Schema

	CREATE TABLE Class (
		   dept VARCHAR(6),
		   number INTEGER,
		   title VARCHAR(75),
		   PRIMARY KEY (dept, number)
	);

	CREATE TABLE Instructor (
		   username VARCHAR(8),
		   fname VARCHAR(50),
		   lname VARCHAR(50),
		   started_on CHAR(10),
		   PRIMARY KEY (username)
	);

	CREATE TABLE Teaches (
		   username VARCHAR(8),
		   dept VARCHAR(6),
		   number INTEGER,
		   PRIMARY KEY (username, dept, number),
		   FOREIGN KEY (username) REFERENCES Instructor(username),
		   FOREIGN KEY (dept, number) REFERENCES Class(dept, number)
	);

2.2 Problems

	Review of joins
	  Who teaches CSE 451?
	  What courses does Dr. Zahorjan teach?
	  Which courses do both Dr. Levy and Dr. Zahorjan teach?

	Queries using aggregation functions
	  How many classes are there in the course catalog?
	  What are the highest and lowest class numbers?
	  How many classes are being taught by at least one instructor?
		Special case: assume every class has the same department
		General case: tricky, solution involves both subqueries and grouping,
		  because SQL aggregate functions only allow a single column

	Queries with both grouping and aggregation
	  How many instructors teach each class?
	  Which instructors teach more than 1 class?
		try this with and without grouping