Sale!

Section 2 SQL Practice

$30.00

Section 2

1: SQL Practice

Schema

CREATE TABLE Population ( rank INTEGER,
country VARCHAR(30) PRIMARY KEY, population DOUBLE, percentage FLOAT

Category:
Rate this product

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

Section 2 SQL Practice
$30.00
Open chat
Need help?
Hello
Can we help?