COUNTRY and CITY Table Practice
Excellent — you’re asking for a complete SQL practice guide built around two related tables:COUNTRY and CITY.
Let’s make this a full structured learning sheet — from CREATE TABLE → Basic Queries → Joins → Subqueries → Aggregation → Views → Advanced — all using your given schema.
🧱 1️⃣ Create Tables
-- Create COUNTRY table
CREATE TABLE COUNTRY (
country_id INT PRIMARY KEY AUTO_INCREMENT,
country_name VARCHAR(100) NOT NULL,
population BIGINT,
area DECIMAL(15,2)
);
-- Create CITY table
CREATE TABLE CITY (
city_id INT PRIMARY KEY AUTO_INCREMENT,
city_name VARCHAR(100) NOT NULL,
country_id INT,
population BIGINT,
rating DECIMAL(3,2),
FOREIGN KEY (country_id) REFERENCES COUNTRY(country_id)
);
🧩 2️⃣ Insert Sample Data
INSERT INTO COUNTRY (country_name, population, area) VALUES
('India', 1400000000, 3287263.0),
('USA', 331000000, 9833520.0),
('Japan', 125000000, 377975.0),
('Australia', 26000000, 7692024.0);
INSERT INTO CITY (city_name, country_id, population, rating) VALUES
('Delhi', 1, 32000000, 4.5),
('Mumbai', 1, 21000000, 4.3),
('New York', 2, 18800000, 4.6),
('Los Angeles', 2, 13000000, 4.2),
('Tokyo', 3, 37400000, 4.8),
('Osaka', 3, 19200000, 4.1),
('Sydney', 4, 5300000, 4.7);
🧮 3️⃣ Basic Queries
▶️ Select All
SELECT * FROM COUNTRY;
SELECT * FROM CITY;
▶️ Select Specific Columns
SELECT country_name, population FROM COUNTRY;
SELECT city_name, rating FROM CITY;
▶️ WHERE Clause
SELECT * FROM CITY WHERE rating > 4.5;
SELECT * FROM COUNTRY WHERE population > 500000000;
▶️ ORDER BY
SELECT * FROM CITY ORDER BY rating DESC;
SELECT * FROM COUNTRY ORDER BY area ASC;
▶️ LIMIT / TOP / FETCH
SELECT * FROM CITY ORDER BY rating DESC LIMIT 3; -- MySQL
🧩 4️⃣ Filtering and Conditions
▶️ AND / OR / NOT
SELECT * FROM CITY WHERE rating > 4.2 AND population > 10000000;
SELECT * FROM COUNTRY WHERE population BETWEEN 100000000 AND 1500000000;
SELECT * FROM CITY WHERE city_name LIKE 'N%';
▶️ IN / NOT IN
SELECT * FROM COUNTRY WHERE country_name IN ('India', 'Japan');
▶️ IS NULL
SELECT * FROM CITY WHERE rating IS NULL;
🧮 5️⃣ Aggregate Functions
▶️ COUNT / SUM / AVG / MAX / MIN
SELECT COUNT(*) AS total_countries FROM COUNTRY;
SELECT AVG(rating) AS avg_rating FROM CITY;
SELECT MAX(population) AS largest_city FROM CITY;
SELECT MIN(area) AS smallest_country_area FROM COUNTRY;
🔗 6️⃣ Grouping and HAVING
-- Total population per country
SELECT country_id, SUM(population) AS total_city_population
FROM CITY
GROUP BY country_id;
-- Average city rating per country (only if avg > 4.3)
SELECT country_id, AVG(rating) AS avg_rating
FROM CITY
GROUP BY country_id
HAVING AVG(rating) > 4.3;
🤝 7️⃣ Joins (Most Important)
▶️ INNER JOIN
SELECT c.city_name, c.population, co.country_name
FROM CITY c
INNER JOIN COUNTRY co ON c.country_id = co.country_id;
▶️ LEFT JOIN
SELECT co.country_name, c.city_name
FROM COUNTRY co
LEFT JOIN CITY c ON co.country_id = c.country_id;
▶️ RIGHT JOIN
SELECT co.country_name, c.city_name
FROM COUNTRY co
RIGHT JOIN CITY c ON co.country_id = c.country_id;
▶️ FULL JOIN (if DB supports; else UNION)
SELECT co.country_name, c.city_name
FROM COUNTRY co
LEFT JOIN CITY c ON co.country_id = c.country_id
UNION
SELECT co.country_name, c.city_name
FROM COUNTRY co
RIGHT JOIN CITY c ON co.country_id = c.country_id;
🔁 8️⃣ Subqueries
▶️ Subquery in WHERE
SELECT * FROM CITY
WHERE country_id = (SELECT country_id FROM COUNTRY WHERE country_name = 'Japan');
▶️ Subquery in SELECT
SELECT country_name,
(SELECT COUNT(*) FROM CITY WHERE CITY.country_id = COUNTRY.country_id) AS total_cities
FROM COUNTRY;
🧮 9️⃣ Set Operations
-- Example UNION
SELECT country_name AS name FROM COUNTRY
UNION
SELECT city_name AS name FROM CITY;
-- INTERSECT (if supported)
SELECT city_name FROM CITY
INTERSECT
SELECT country_name FROM COUNTRY;
-- EXCEPT / MINUS
SELECT country_name FROM COUNTRY
EXCEPT
SELECT city_name FROM CITY;
⚙️ 🔟 Update & Delete
▶️ Update
UPDATE COUNTRY
SET population = population + 1000000
WHERE country_name = 'Australia';
▶️ Delete
DELETE FROM CITY WHERE city_name = 'Osaka';
🧰 11️⃣ Constraints and Keys
ALTER TABLE COUNTRY ADD UNIQUE (country_name);
ALTER TABLE CITY ADD CONSTRAINT fk_country FOREIGN KEY (country_id) REFERENCES COUNTRY(country_id);
🧠 12️⃣ Views
CREATE VIEW v_country_city AS
SELECT co.country_name, c.city_name, c.rating
FROM COUNTRY co
JOIN CITY c ON co.country_id = c.country_id;
SELECT * FROM v_country_city WHERE rating > 4.5;
🚀 13️⃣ Advanced Queries
▶️ Window Functions (if DB supports)
SELECT city_name, country_id,
RANK() OVER (PARTITION BY country_id ORDER BY rating DESC) AS rank_in_country
FROM CITY;
▶️ Correlated Subquery
SELECT city_name, population
FROM CITY c1
WHERE population > (
SELECT AVG(population) FROM CITY c2 WHERE c2.country_id = c1.country_id
);
▶️ CTE (Common Table Expression)
WITH CountryAvg AS (
SELECT country_id, AVG(rating) AS avg_rating
FROM CITY
GROUP BY country_id
)
SELECT c.city_name, co.country_name, c.rating, a.avg_rating
FROM CITY c
JOIN COUNTRY co ON c.country_id = co.country_id
JOIN CountryAvg a ON c.country_id = a.country_id
WHERE c.rating > a.avg_rating;
🧮 14️⃣ DDL / DML / DCL / TCL Summary
| Type | Operation | Example |
| DDL | CREATE, ALTER, DROP | CREATE TABLE, ALTER TABLE |
| DML | SELECT, INSERT, UPDATE, DELETE | INSERT INTO CITY... |
| DCL | GRANT, REVOKE | GRANT SELECT ON CITY TO user; |
| TCL | COMMIT, ROLLBACK, SAVEPOINT | COMMIT; ROLLBACK; |
🧾 15️⃣ Real-world Practice Examples
Find top 2 cities by population per country
SELECT city_name, country_id, population FROM ( SELECT city_name, country_id, population, ROW_NUMBER() OVER (PARTITION BY country_id ORDER BY population DESC) AS rnk FROM CITY ) t WHERE rnk <= 2;Find countries having average city rating > 4.5
SELECT co.country_name FROM COUNTRY co JOIN CITY c ON co.country_id = c.country_id GROUP BY co.country_name HAVING AVG(c.rating) > 4.5;Find the total population (country + cities combined)
SELECT co.country_name, co.population + IFNULL(SUM(c.population), 0) AS total_population FROM COUNTRY co LEFT JOIN CITY c ON co.country_id = c.country_id GROUP BY co.country_name;
Would you like me to create this as a formatted SQL reference PDF (with sections, examples, and explanation lines for each query) — great for revision before interviews?
You said:
giva more complex questions list only
ChatGPT said:
Perfect 💪 — here’s a list of advanced and complex SQL question topics (specially for automation testers, data QA, and backend test engineers).
These questions go beyond basics — covering subqueries, analytics, joins, data integrity, performance, and business logic challenges.
🔹 Complex SQL Question List
Find the second highest / Nth highest population city for each country.
Find countries with no cities registered in the CITY table.
Find duplicate city names across different countries.
Retrieve countries having more than 3 cities with rating above 4.5.
Find city with maximum population per country (using subquery or window function).
Find average population per country and list only those above the global average.
Identify country with the highest total city population.
Find top 3 rated cities globally and their country names.
Show city ranking inside each country based on rating and population.
Find percentage contribution of each city’s population to its country’s total.
Write query to swap country names between two country IDs.
Find countries whose total population (sum of cities) differs greatly from the COUNTRY table population (data mismatch validation).
Retrieve list of countries with no city having rating < 4.0.
Find average rating of cities in each country using CTE and filter out below-average countries.
Write a query to normalize data — move city population > 10M into a separate “MegaCity” list.
Find latest inserted record from each table (based on timestamp).
Identify orphan cities (cities with invalid country_id not existing in COUNTRY table).
Write a query using EXISTS / NOT EXISTS to find countries without cities.
Find country with the most cities sharing the same rating.
Calculate running total of city populations ordered by rating.
Write a query to pivot country population data into columns (Asia, Europe, etc.) based on region (if added).
Write a recursive CTE to traverse hierarchical relationships (like country → state → city).
Find cities above average rating in their own country but below global average.
Find city names appearing in more than one country (e.g., "Springfield").
Write query to find ratio of area to population density for each country and rank them.
Find top-rated city per country and also show difference between top and lowest rating in that country.
Identify countries with uneven population distribution (stddev of city populations > threshold).
Find median rating of all cities (without using built-in median function).
Write a query to update city ratings based on population brackets (e.g., if pop > 10M, +0.1).
Delete duplicate city entries keeping only one (highest rated or latest).
Compare country population vs sum of all its cities and highlight mismatch > 5%.
Create a materialized view of top 5 cities by population per continent.
Simulate pagination — show records 6–10 from city table ordered by rating.
Count number of cities with same name but different ratings.
Find country that contributes the maximum % of global city population.
Display country having maximum average city rating difference between its best and worst city.
Identify cities whose rating > country’s average rating + global average rating / 2.
Detect missing sequence IDs in city_id or country_id (data integrity check).
Compute city density = population / (country area / number_of_cities) for each city.
Find top 2 cities by population per country using window functions.
Create query to auto-detect outlier cities by population using statistical boundaries.
Identify countries having cities in top 10 and bottom 10 ratings simultaneously.
Find difference in population between most and least populous city for each country.
Create trigger simulation: if city added with population > 50M, log into alert table.
Generate country-wise summary report — total population, avg rating, number of cities.
Write query to unpivot population and area columns into rows.
Write a stored procedure to dynamically find top N cities by rating.
Create view that lists all cities with their country details and rank by rating.
Write query to merge duplicate countries (same name) into one.
Write a complex join involving self-joins to find cities sharing same population but different countries.