linfo2172-databases/P3/queries/query11.sql

22 lines
541 B
SQL

DROP TABLE IF EXISTS ProductZeroPurchasesByProvince;
CREATE TABLE ProductZeroPurchasesByProvince AS
SELECT
c.name AS CountryName,
pro.name AS ProvinceName,
COUNT(pro.name) AS cnt
FROM
Purchases pur
JOIN
Province pro ON pur.product = 0 AND pur.province = pro.rowid
JOIN
Country c ON pro.country = c.code
GROUP BY
c.code, pro.name;
DROP INDEX IF EXISTS Province_index;
CREATE INDEX Province_index ON Province(name,country)
DROP INDEX IF EXISTS Country_index;
CREATE INDEX Country_index ON Country(code)