CA 30 - Basic Select SQL Querie

basic hackerank problems sql queries

When you're starting with SQL, the SELECT statement is the most important command. It allows you to retrieve data from database tables using different conditions.

• FROM
• WHERE
• COUNT(column)
• COUNT(DISTINCT column)
• LEFT(column, n)

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

SELECT * FROM CITY WHERE ID = 1661;

2. Query all American cities with population larger than 100000

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

3. Query all attributes of every Japanese city

SELECT * FROM CITY WHERE COUNTRYCODE = 'JPN';

4. Difference between total CITY entries and distinct CITY entries

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

5. Query NAME of American cities with population larger than 120000

SELECT NAME FROM CITY WHERE COUNTRYCODE = 'USA' AND POPULATION > 120000;

6. Query names of all Japanese cities

SELECT NAME FROM CITY WHERE COUNTRYCODE = 'JPN';

7. List CITY names that do not start with vowels (no duplicates)

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

8. Query all columns from CITY table

SELECT * FROM CITY;

9. Query CITY and STATE from STATION table

SELECT CITY, STATE FROM STATION;