MySQL basics and database
Revising the Select Query I
Query all columns for all American cities in CITY with populations larger than 100000. The CountryCode for America is USA.
select * from City c where c.CountryCode='USA' and c.population>100000 order by ID;
Query the names of all American cities in CITY with populations larger than 120000. The CountryCode for America is USA.
select c.Name from City c where c.CountryCode='USA' and c.Population>120000 order by c.ID;
Query all columns (attributes) for every row in the CITY table.
Select * from City
Query all columns for a city in CITY with the ID 1661.
Select * from City where id="1661";
Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN.
Select * from City where CountryCode="JPN";
Query a list of CITY and STATE from the STATION table.
Select CITY,STATE from STATION order by CITY,STATE ;
Query a list of CITY names from STATION with even ID numbers only. You may print the results in any order, but must exclude duplicates from your answer.
Select Distinct CITY from STATION where ID%2=0 order by CITY;
Let N be the number of CITY entries in STATION, and let N’ be the number of distinct CITY names in STATION; query the value of N-N’ from STATION. In other words, 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(*)-count(distinct city) from STATION