SQL BAI 12

 USE SAMPLEDATABASE;

DROP TABLE IF EXISTS Customers;

DROP TABLE IF EXISTS Countries;

CREATE TABLE Countries (

ID INT PRIMARY KEY auto_increment,

    Country VARCHAR(10)

);

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,

    CustomerName VARCHAR(30),

    ContactName VARCHAR(30),

    Address VARCHAR(30),

    City VARCHAR(10),

    PostalCode VARCHAR(10),

    CountryID INT,

    FOREIGN KEY (CountryID) REFERENCES Countries(ID)

);

INSERT into Countries (Country) VALUES ('VIETNAM'), ('UNITED'), ('CHINA'), ('SING');

INSERT INTO Customers 

VALUES (2, "Alfreds Futterkiste", "Maria Anders", "Obere Str. 57", "Berlin", "f12209", 3),

(3, "A Futterkiste", "Maria Anders", "Obere Str. 57", "Berlin", "f12209", 3),

(4, "freds Futterkiste", "Maria Anders", "Obere Str. 57", "London", "f12209", 1),

(5, "Ds Futterkiste", "Maria Anders", "Obere Str. 57", "SG", "f12209", 2),

(6, "Dr Thach", "Maria Anders", "Obere Str. 57", "HN", "f12209", null);

SELECT CITY, COUNT(CITY) AS `NUMBER OF RECORDS`

FROM CUSTOMERS

GROUP BY CITY