Basic Select SQL Queries

** The basic queries used in select statements are **

• From - it is used to specify the table
• where - is used to check constraint before returning the records
• count( 'column name') can be used to return no. of elements in a column
• count( distinct 'column name') can be used to return the number of unique elements in the column
• left ('column name' , 'no of charecters') is used to check constraint in where to check for one charecter from left in a record

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

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.

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

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.

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.

SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY,1) NOT IN ('A','E','I','O','U');

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

SELECT * FROM CITY;

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

SELECT CITY, STATE FROM STATION;