# 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**

```java
-- 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**

```java
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

```java
SELECT * FROM COUNTRY;
SELECT * FROM CITY;
```

### ▶️ Select Specific Columns

```java
SELECT country_name, population FROM COUNTRY;
SELECT city_name, rating FROM CITY;
```

### ▶️ WHERE Clause

```java
SELECT * FROM CITY WHERE rating > 4.5;
SELECT * FROM COUNTRY WHERE population > 500000000;
```

### ▶️ ORDER BY

```java
SELECT * FROM CITY ORDER BY rating DESC;
SELECT * FROM COUNTRY ORDER BY area ASC;
```

### ▶️ LIMIT / TOP / FETCH

```java
SELECT * FROM CITY ORDER BY rating DESC LIMIT 3;   -- MySQL
```

---

## 🧩 **4️⃣ Filtering and Conditions**

### ▶️ AND / OR / NOT

```java
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

```java
SELECT * FROM COUNTRY WHERE country_name IN ('India', 'Japan');
```

### ▶️ IS NULL

```java
SELECT * FROM CITY WHERE rating IS NULL;
```

---

## 🧮 **5️⃣ Aggregate Functions**

### ▶️ COUNT / SUM / AVG / MAX / MIN

```java
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**

```java
-- 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

```java
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

```java
SELECT co.country_name, c.city_name
FROM COUNTRY co
LEFT JOIN CITY c ON co.country_id = c.country_id;
```

### ▶️ RIGHT JOIN

```java
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)

```java
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

```java
SELECT * FROM CITY
WHERE country_id = (SELECT country_id FROM COUNTRY WHERE country_name = 'Japan');
```

### ▶️ Subquery in SELECT

```java
SELECT country_name,
       (SELECT COUNT(*) FROM CITY WHERE CITY.country_id = COUNTRY.country_id) AS total_cities
FROM COUNTRY;
```

---

## 🧮 **9️⃣ Set Operations**

```java
-- 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

```java
UPDATE COUNTRY
SET population = population + 1000000
WHERE country_name = 'Australia';
```

### ▶️ Delete

```java
DELETE FROM CITY WHERE city_name = 'Osaka';
```

---

## 🧰 **11️⃣ Constraints and Keys**

```java
ALTER TABLE COUNTRY ADD UNIQUE (country_name);
ALTER TABLE CITY ADD CONSTRAINT fk_country FOREIGN KEY (country_id) REFERENCES COUNTRY(country_id);
```

---

## 🧠 **12️⃣ Views**

```java
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)

```java
SELECT city_name, country_id, 
       RANK() OVER (PARTITION BY country_id ORDER BY rating DESC) AS rank_in_country
FROM CITY;
```

### ▶️ Correlated Subquery

```java
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)

```java
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**

1. Find top 2 cities by population per country
    
    ```java
    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;
    ```
    
2. Find countries having average city rating &gt; 4.5
    
    ```java
    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;
    ```
    
3. Find the total population (country + cities combined)
    
    ```java
    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**

1. Find the **second highest / Nth highest** population city for each country.
    
2. Find **countries with no cities** registered in the CITY table.
    
3. Find **duplicate city names** across different countries.
    
4. Retrieve **countries having more than 3 cities** with rating above 4.5.
    
5. Find **city with maximum population** per country (using subquery or window function).
    
6. Find **average population per country** and list only those above the global average.
    
7. Identify **country with the highest total city population**.
    
8. Find **top 3 rated cities globally** and their country names.
    
9. Show **city ranking** inside each country based on rating and population.
    
10. Find **percentage contribution** of each city’s population to its country’s total.
    
11. Write query to **swap country names** between two country IDs.
    
12. Find **countries whose total population (sum of cities)** differs greatly from the COUNTRY table population (data mismatch validation).
    
13. Retrieve **list of countries** with **no city having rating &lt; 4.0**.
    
14. Find **average rating** of cities in each country using **CTE** and **filter out below-average** countries.
    
15. Write a query to **normalize data** — move city population &gt; 10M into a separate “MegaCity” list.
    
16. Find **latest inserted record** from each table (based on timestamp).
    
17. Identify **orphan cities** (cities with invalid country\_id not existing in COUNTRY table).
    
18. Write a query using **EXISTS / NOT EXISTS** to find countries without cities.
    
19. Find **country with the most cities sharing the same rating**.
    
20. Calculate **running total** of city populations ordered by rating.
    
21. Write a query to **pivot** country population data into columns (Asia, Europe, etc.) based on region (if added).
    
22. Write a **recursive CTE** to traverse hierarchical relationships (like country → state → city).
    
23. Find **cities above average rating** in their own country but **below global average**.
    
24. Find **city names appearing in more than one country** (e.g., "Springfield").
    
25. Write query to find **ratio of area to population density** for each country and rank them.
    
26. Find **top-rated city per country** and also show **difference** between top and lowest rating in that country.
    
27. Identify **countries with uneven population distribution** (stddev of city populations &gt; threshold).
    
28. Find **median rating** of all cities (without using built-in median function).
    
29. Write a query to **update** city ratings based on population brackets (e.g., if pop &gt; 10M, +0.1).
    
30. Delete **duplicate city entries** keeping only one (highest rated or latest).
    
31. Compare **country population** vs **sum of all its cities** and highlight mismatch &gt; 5%.
    
32. Create a **materialized view** of top 5 cities by population per continent.
    
33. Simulate **pagination** — show records 6–10 from city table ordered by rating.
    
34. Count **number of cities with same name but different ratings**.
    
35. Find **country that contributes the maximum % of global city population**.
    
36. Display **country having maximum average city rating difference** between its best and worst city.
    
37. Identify **cities whose rating &gt; country’s average rating + global average rating / 2.**
    
38. Detect **missing sequence IDs** in city\_id or country\_id (data integrity check).
    
39. Compute **city density = population / (country area / number\_of\_cities)** for each city.
    
40. Find **top 2 cities by population per country** using window functions.
    
41. Create query to **auto-detect outlier cities** by population using statistical boundaries.
    
42. Identify **countries having cities in top 10 and bottom 10 ratings** simultaneously.
    
43. Find **difference in population between most and least populous city** for each country.
    
44. Create **trigger simulation**: if city added with population &gt; 50M, log into alert table.
    
45. Generate **country-wise summary report** — total population, avg rating, number of cities.
    
46. Write query to **unpivot** population and area columns into rows.
    
47. Write a **stored procedure** to dynamically find top N cities by rating.
    
48. Create **view** that lists all cities with their country details and rank by rating.
    
49. Write query to **merge** duplicate countries (same name) into one.
    
50. Write a **complex join** involving self-joins to find cities sharing same population but different countries.