Section 2
1: SQL Practice
Schema
CREATE TABLE Population ( rank INTEGER,
country VARCHAR(30) PRIMARY KEY, population DOUBLE, percentage FLOAT
);
CREATE TABLE GDP (
rank INTEGER,
country VARCHAR(30) PRIMARY KEY, gdp DOUBLE
);
CREATE TABLE Airport (
code VARCHAR(30) PRIMARY KEY, name VARCHAR(30),
country VARCHAR(30)
);
Problems
— What is the total population of earth? Total_Population
6778067375
— What is the percentage of the population from the top 10 populated countries? Top_Sum
58.9241749607129
— How many countries do have less than 1,000,000 population? Small_Countries
68
— How many countries have airports? Airport_Count
247
— Top 10 countries with most airports, in descending order country Count
————- ———-
United States 2238
Australia 617
Canada 533
Papua New Gui 380 Brazil 288
Indonesia 205
China 187
Colombia 167
United Kingdo 151
France 144
— Order the top 10 countries by total GDP per capita (gdp / population) country GDP_per_capita
———- —————–
Seychelles 0.282666666666667
Saint Kitt 0.256076923076923
Antigua an 0.196681818181818
Luxembourg 0.158883485309017
Dominica 0.152507462686567
Brunei 0.119825
Iceland 0.118570005575638
Grenada 0.102855769230769
Saint Vinc 0.092908256880733
Barbados 0.087
2. Join & Aggregation
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)
);
Problems
/* Review of joins */
— Who teaches CSE 451? fname lname
———- ———-
Tom Anderson
John Zahorjan
Hank Levy
— What courses does zahorjan teach? dept number
———- ———- CSE 378
CSE 451
CSE 461
— Which courses do both levy and zahorjan teach? dept number title
———- ———- ———————————
CSE 451 Introduction to Operating Systems
/* Queries using aggregation functions */
— How many classes are there in the course catalog? COUNT(*)
———- 3
— What are the highest and lowest class numbers? MIN(number) MAX(number)
———– ———– 378 461
/* Queries with both grouping and aggregation */
— How many instructors teach each class? dept number teacher_count
———- ———- ————- CSE 378 1
CSE 451 3
CSE 461 3
— Order the instructors by who teaches in the most departments username Department_Count
———- —————-
djw 1
levy 1
tom 1
zahorjan 1
