ECNU数据库作业——Lab4

Lab 4

EX1

How many countries does each continent have? List the continent id, continent name and the number of countries？

SELECT COUNT(countries.CountryName) AS Num_Country, continents.Continent
FROM countries
JOIN continents ON continents.ContId = countries.Continent
GROUP BY countries.Continent

EX2

What is the average horsepower of the cars before 1980?

-- 注意horsepower属性数据类型为text，且其中有'null'值
SELECT AVG(CAST(cars_data.Horsepower AS INTEGER)) AS avg_horsepower
FROM cars_data
WHERE cars_data.Year < 1980 AND cars_data.Horsepower != 'null'

EX3

How many models does each car maker produce? List maker full name, id and the number.

SELECT car_makers.FullName, car_makers.Id, COUNT(model_list.Model) AS Num_Model
FROM model_list
JOIN car_makers ON car_makers.Id = model_list.Maker
GROUP BY model_list.Maker

EX4

What is the average edispl of the cars of model volvo?

SELECT AVG(cars_data.Edispl) AS avg_edispl
FROM cars_data
JOIN car_names ON car_names.MakeId = cars_data.Id
WHERE car_names.Model = 'volvo'

EX5

What is the model of the car with the smallest amount of horsepower?

SELECT DISTINCT car_names.Model, cars_data.Horsepower
FROM car_names
JOIN cars_data ON cars_data.Id = car_names.MakeId
WHERE CAST(cars_data.Horsepower AS INTEGER) = (
	SELECT MIN(CAST(cars_data.Horsepower AS INTEGER))
	FROM cars_data
	WHERE cars_data.Horsepower != 'null'
) 

EX6

Find the model of the car whose weight is below the average weight.

SELECT DISTINCT car_names.Model
FROM car_names
JOIN cars_data ON cars_data.Id = car_names.MakeId
WHERE cars_data.Weight < (
	SELECT AVG(cars_data.Weight)
	FROM cars_data
)

EX7

What is the maximum accelerate for different number of cylinders?

SELECT MAX(cars_data.Accelerate) AS Max_Accelerate, cars_data.Cylinders
FROM cars_data
GROUP BY cars_data.Cylinders

EX8

How many car makers are there in each continents? List the continent name and the count？

SELECT continents.Continent AS continent, COUNT(car_makers.Country) AS Num_Makers
FROM continents
JOIN countries ON countries.Continent = continents.ContId
JOIN car_makers ON car_makers.Country = countries.CountryId
GROUP BY continents.Continent

EX9

Which of the countries has the most car makers? List the country name.

SELECT countries.CountryName
FROM countries
JOIN car_makers ON car_makers.Country = countries.CountryId
GROUP BY countries.CountryId
HAVING COUNT(car_makers.Maker) = (
    SELECT MAX(count_makers)
    FROM (
        SELECT COUNT(car_makers.Maker) AS count_makers
        FROM car_makers
        JOIN countries ON car_makers.Country = countries.CountryId
        GROUP BY countries.CountryId
    )
)

EX10

What are the countries having at least one car maker? List name and id.

SELECT DISTINCT countries.CountryName AS name, countries.CountryId AS ID
FROM countries
LEFT JOIN car_makers ON car_makers.Country = countries.CountryId
WHERE car_makers.Maker IS NOT NULL

EX11

Which countries in europe have at least 3 car manufacturers?

SELECT countries.CountryName
FROM countries
JOIN car_makers ON countries.CountryId = car_makers.Country
JOIN continents ON continents.ContId = countries.Continent
GROUP BY car_makers.Country
HAVING COUNT(car_makers.Maker) >= 3 AND continents.Continent = 'europe'

EX12

What is the maximum horsepower and the make of the car models with 3 cylinders?

SELECT MAX(CAST(cars_data.Horsepower AS INTEGER)) AS Max_Horsepower, car_names.Make
FROM cars_data
JOIN car_names ON car_names.MakeId = cars_data.Id
WHERE cars_data.Cylinders = 3 AND cars_data.Horsepower != 'null'