ASSIGNMENT 30

1.Query all columns for a city in CITY with the ID 1661.

SELECT * FROM CITY WHERE ID = 1661;

Explanation
SELECT * --> Selects all columns from the table
FROM CITY --> Specifies the table name
WHERE ID = 1661 --> Filters the row with ID 1661

2.Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA.

SELECT * FROM CITY WHERE POPULATION > 100000 AND COUNTRYCODE = 'USA';

Explanation

SELECT * --> Retrieves all columns from the table
FROM CITY --> Specifies the table
WHERE POPULATION > 100000 --> Filters cities with population greater than 100000
AND COUNTRYCODE = 'USA' --> Ensures only cities in the USA are selected

1. Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN.

SELECT * FROM CITY WHERE COUNTRYCODE = 'JPN';

Explanation
SELECT * --> Retrieves all columns (ID, NAME, COUNTRYCODE, DISTRICT, POPULATION)
FROM CITY --> Specifies the table
WHERE COUNTRYCODE = 'JPN' --> Filters only Japanese cities

4.Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

SELECT COUNT(CITY) - COUNT(DISTINCT CITY) FROM STATION;

Explanation
COUNT(CITY) --> Counts all city entries (including duplicates)
COUNT(DISTINCT CITY) --> Counts only unique city names
Subtracting them gives --> number of duplicate city entries

5.Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.

SELECT DISTINCT CITY
FROM STATION
WHERE CITY NOT LIKE 'A%'
AND CITY NOT LIKE 'E%'
AND CITY NOT LIKE 'I%'
AND CITY NOT LIKE 'O%'
AND CITY NOT LIKE 'U%';

Explanation
SELECT DISTINCT CITY --> Ensures no duplicate city names
FROM STATION --> Specifies the table
CITY NOT LIKE 'A%' --> Excludes cities starting with A
Similarly excludes E, I, O, U
% --> Represents any characters after the first letter

6.Query all columns (attributes) for every row in the CITY table.

SELECT * FROM CITY;

Explanation
SELECT * --> The * means select all columns
FROM CITY --> Specifies the table name

7.Query a list of CITY and STATE from the STATION table.

SELECT CITY, STATE FROM STATION;

Explanation
SELECT CITY, STATE --> Retrieves only the CITY and STATE columns
FROM STATION --> Specifies the table