Section 2 SQL Practice

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