1.Query all columns for a city in CITY with the ID 1661
here we need to select all columns in city where ID value is equal to 1661.
so
SELECT * FROM CITY WHERE 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.
we to select all columns from cities where countrycode is USA and population greater than 100000.so,
SELECT * FROM CITY WHERE COUNTRYCODE = 'USA' AND POPULATION > 100000;
3.Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN.
select all columns from city where countrycode is JPN.
SELECT * FROM CITY WHERE COUNTRYCODE = 'JPN';
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 the difference between the cities and distinct cities based on names from city table.
SELECT COUNT(CITY) - COUNT(DISTINCT CITY) FROM STATION;
5.Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA.
The query selects the NAME from the CITY table for all cities where the COUNTRYCODE is USA and the population is greater than 120000.
SELECT NAME FROM CITY WHERE COUNTRYCODE = 'USA' AND POPULATION > 120000;
6.Query the names of all the Japanese cities in the CITY table. The COUNTRYCODE for Japan is JPN.
The query selects the NAME field from the CITY table for all cities where the COUNTRYCODE is 'JPN'.
SELECT NAME FROM CITY WHERE COUNTRYCODE = 'JPN';
7.Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.
The query selects the distinct CITY names from the STATION table where the city names do not start with any vowels (A, E, I, O, U).
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%';
8.Query all columns (attributes) for every row in the CITY table.
The query selects all columns from the CITY table for every row.
SELECT * FROM CITY;
9.Query a list of CITY and STATE from the STATION table.
this query is to select city and state columns form the station table.
SELECT CITY, STATE FROM STATION;
Top comments (0)