SQL

Problem 01

Revising the Select Query I

Figure: CITY table

Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for Ameriza is USA. The ‘CITY’ table is described as follows:

Solution 
SQL Server
SELECT *
FROM CITY
WHERE POPULATION > 100000 AND COUNTRYCODE ='USA';
Oracle Database
SELECT *
FROM CITY
WHERE POPULATION > 100000 AND COUNTRYCODE = 'USA';
"MySQL
SELECT *
FROM CITY
WHERE POPULATION > 100000 AND COUNTRYCODE = 'USA';
DB2
SELECT *
FROM CITY
WHERE POPULATION > 100000 AND COUNTRYCODE = 'USA';

Problem 02

Revising the Select Query II

Figure: CITY table

Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA

Solution 
SQL Server
SELECT NAME
FROM CITY
WHERE POPULATION > 120000 AND COUNTRYCODE = 'USA';
Oracle Database
SELECT NAME
FROM CITY
WHERE POPULATION > 120000 AND COUNTRYCODE = 'USA';
MySQL
SELECT NAME
FROM CITY
WHERE POPULATION > 120000 AND COUNTRYCODE = 'USA';
DB2
SELECT NAME
FROM CITY
WHERE POPULATION > 120000 AND COUNTRYCODE = 'USA';

Problem 03

Select All

Figure: CITY table

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

Solution 
SQL Server
SELECT *
FROM CITY;
Oracle Database
SELECT *
FROM CITY;
MySQL
SELECT *
FROM CITY;
DB2
SELECT *
FROM CITY;

Problem 04

Select By ID

Figure: CITY table

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

Solution 
SQL Server
SELECT *
FROM CITY
WHERE ID = 1661;
SQL Server
SELECT *
FROM CITY
WHERE ID = 1661;
SQL Server
SELECT *
FROM CITY
WHERE ID = 1661;
SQL Server
SELECT *
FROM CITY
WHERE ID = 1661;

Problem 05

Japanese Cities’ Attributes

Figure: CITY table

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

Solution 
SQL Server
SELECT *
FROM CITY
WHERE COUNTRYCODE = 'JPN';
Oracle Database
SELECT *
FROM CITY
WHERE COUNTRYCODE = 'JPN';
MySQL
SELECT *
FROM CITY
WHERE COUNTRYCODE = 'JPN';
DB2
SELECT *
FROM CITY
WHERE COUNTRYCODE = 'JPN';