Database Access using SQL Very basic introduction James Brucker
Download
Report
Transcript Database Access using SQL Very basic introduction James Brucker
Database Access using SQL
Very basic introduction
James Brucker
Database Management System
SELECT * FROM
city WHERE name
LIKE Ban%
User
User Interface &
communications
protocol
Database
Manager
controls access to the
database & provides:
• authentication
• enforce permissions
• data integrity
• access services
Database: a structured,
self-describing collection
of data.
Client - Server Structure
The database server is a separate process running on
a host.
The database client can be on a different machine.
Many programs may be client using standard API.
client using "mysql" utility
client using Java+JDBC
client using Excel
mysqld
(server)
Exercise
Use the "mysql" command
if machine doesn't have "mysql" then use MySQL
Query Browser GUI.
What is the version number?
Look at help message: how do you connect to server?
dos> mysql --version
mysql Ver 14.12 Distrib 5.0.16, for Win32
dos> mysql --help
displays a long help message: very useful
Exercise
Connect to MySQL server on host "se.cpe.ku.ac.th".
user: student password: student
What MySQL version is the server running?
dos> mysql -h se.cpe.ku.ac.th -u student -p
Enter password: student
mysql> SELECT version();
mysql>
Structure of a Database
A database system may contain many databases.
Each database is composed of schema and tables.
sql> SHOW databases;
+--------------+
| Database
|
+--------------+
| mysql
|
| test
|
| bank
|
| world
|
+--------------+
MySQL only shows databases that
a user has permission to access.
sql> USE bank;
sql> SHOW tables;
+----------------+
| Tables_in_bank |
+----------------+
| accounts
|
| clients
|
+----------------+
Structured Query Language
The standard language for manipulating a database is
Structured Query Language (SQL).
SQL is case-insensitive and free format.
Commands are entered interactively or in a script file.
SQL statements can span multiple lines:
end a statement with a semi-colon ;
sql> USE Bank;
database changed.
SQL statements end with a semi-colon.
sql> SHOW tables;
sql> SHOW columns FROM accounts;
sql> SELECT accountNumber, accountName,
clientID, balance FROM accounts;
A Simple Database Structure
SQL commands to show the structure of Bank database
sql> SHOW tables;
+----------------+
| Tables_in_bank |
+----------------+
| accounts
|
| clients
|
+----------------+
SQL statements must end with a semi-colon.
If you omit semi-colon, SQL will prompt for
more input.
sql> SHOW columns FROM accounts;
+------------------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| accountNumber
| varchar(8) |
|
|
|
|
| accountName
| varchar(40) |
|
|
|
|
| clientID
| int(11)
|
| MUL | 0
|
|
| balance
| double
|
|
| 0
|
|
| availableBalance | double
|
|
| 0
|
|
+------------------+-------------+------+-----+---------+-------+
Exercise
Connect to MySQL server on host "se.cpe.ku.ac.th".
user: student password: student
What databases are there?
What tables are in the world database?
dos> mysql -h se.cpe.ku.ac.th -u student -p
Enter password: student
mysql> SHOW databases;
mysql> SHOW tables from world;
mysql> USE world;
mysql> SHOW tables;
Exercise
Omit the semi-colon. What happens?
mysql> SHOW tables
No semi-colon.
Enter a command on several lines
mysql> SHOW
tables
from
world
;
Structure of a Database Table
A table contains records (rows) of data.
A record is composed of several columns (fields).
A database schema can contain index files, views,
and other information in addition to tables.
sql> SELECT * FROM Accounts;
+---------------+---------------+----------+---------+
| accountNumber | accountName
| clientID | balance |
+---------------+---------------+----------+---------+
| 11111111
| J.Brucker
| 11111111 |
35000 |
| 11111112
| Vacation Fund | 11111111 |
22500 |
| 11111113
| P.Watanapong | 11111120 | 300000 |
| 11111114
| CPE Fund
| 11111120 | 1840000 |
+---------------+---------------+----------+---------+
DESCRIBE
DESCRIBE also shows a description of a table.
output is same as "SHOW COLUMNS FROM ...".
sql> DESCRIBE accounts;
+------------------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| accountNumber
| varchar(8) |
|
|
|
|
| accountName
| varchar(40) |
|
|
|
|
| clientID
| int(11)
|
| MUL | 0
|
|
| balance
| double
|
|
| 0
|
|
| availableBalance | double
|
|
| 0
|
|
+------------------+-------------+------+-----+---------+-------+
Exercise:
For the world database:
what columns does each table have?
what information do you suppose is in the columns?
What are the names of some data types in SQL?
mysql> describe city;
+-------------+----------+------+-----+---------+-----------+
| Field
| Type
| Null | Key | Default | Extra
|
+-------------+----------+------+-----+---------+-----------+
| ID
| int(11) | NO
| PRI | NULL
| auto_incr |
| Name
| char(35) | NO
|
|
|
|
| CountryCode | char(3) | NO
|
|
|
|
| District
| char(20) | NO
|
|
|
|
| Population | int(11) | NO
|
| 0
|
|
+-------------+----------+------+-----+---------+-----------+
Exercise: O-O Analogy of a Table?
Database
table
record (row)
fields (columns)
Object Oriented
__________________
__________________
__________________
sql> SELECT * FROM Accounts;
+---------------+---------------+----------+---------+
| accountNumber | accountName
| clientID | balance |
+---------------+---------------+----------+---------+
| 11111111
| J.Brucker
| 11111111 |
35000 |
| 11111112
| Vacation Fund | 11111111 |
22500 |
| 11111113
| P.Watanapong | 11111120 | 300000 |
| 11111114
| CPE Fund
| 11111120 | 1840000 |
+---------------+---------------+----------+---------+
Qualifying Names
SQL uses "." to qualify elements of a hierarchy
just like most O-O languages
Bank.accounts
"accounts" table in Bank db
accounts.balance
balance field in accounts
Bank.accounts.balance
sql> DESCRIBE bank.accounts;
+------------------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| accountNumber
| varchar(8) |
|
|
|
|
| accountName
| varchar(40) |
|
|
|
|
| clientID
| int(11)
|
| MUL | 0
|
|
| balance
| double
|
|
| 0
|
|
| availableBalance | double
|
|
| 0
|
|
+------------------+-------------+------+-----+---------+-------+
Diagram of Database Structure
Database
Catalog
Catalog
Schema
Schema
Schema
Schema
Table
Table
Table
Table
Table
Table
Table
Table
field1: t1
field2: t2
field3: t3
field1: t1
field2: t2
field3: t3
field1: t1
field2: t2
field3: t3
field1: t1
field2: t2
field3: t3
field1: t1
field2: t2
field3: t3
field1: t1
field2: t2
field3: t3
field1: t1
field2: t2
field3: t3
field1: t1
field2: t2
field3: t3
indexes
indexes
indexes
indexes
indexes
indexes
indexes
indexes
In MySQL the words "database" and "schema" are used inconsistently.
Database Operations
Operations you can perform on a database include:
USE
choose a database
SELECT
query (search) the data
INSERT
add new records to a table(s)
UPDATE modify information in existing records
DELETE
delete records from a table
sql> USE bank;
sql> SELECT * FROM clients WHERE firstname = 'james';
| clientID | firstName | lastName | email
|
+----------+-----------+----------+--------------------+
| 11111111 | James
| Brucker | [email protected] |
sql> UPDATE accounts SET balance=100000 WHERE
clientID='11111111';
Query OK, 1 row affected (0.09 sec)
SQL SELECT statement
Select columns from a table and display them:
SELECT
field1, field2, field3 FROM tablename ;
This displays ALL rows from the table.
sql> SELECT * from accounts;
+---------------+---------------+----------+---------+
| accountNumber | accountName
| clientID | balance |
+---------------+---------------+----------+---------+
| 11111111
| J.Brucker
| 11111111 |
35000 |
| 11111112
| Vacation Fund | 11111111 |
22500 |
| 11111113
| P.Watanapong | 11111120 | 300000 |
| 11111114
| CPE Fund
| 11111120 | 1840000 |
+---------------+---------------+----------+---------+
Qualifying SELECT
Select columns from a table that match some criteria:
SELECT field1, field2, field3 FROM table
WHERE condition ORDER BY field1,... [ASC|DESC];
sql> SELECT accountNumber, ... FROM accounts
WHERE balance > 100000 ORDER by balance DESC;
+---------------+---------------+----------+---------+
| accountNumber | accountName
| clientID | balance |
+---------------+---------------+----------+---------+
| 11111114
| CPE Fund
| 11111120 | 1840000 |
| 11111113
| P.Watanapong | 11111120 | 300000 |
+---------------+---------------+----------+---------+
SQL statements and field names are case insensitive, but the field
values may not be! It depends on the data type of the field.
Counting with SELECT
Select can be used with functions, such as COUNT:
SELECT
COUNT(*) from Accounts
WHERE condition;
sql> SELECT count(*) from accounts;
+----------+
| count(*) |
+----------+
|
4 |
+----------+
sql> SELECT count(*) from accounts
WHERE balance > 100000;
Exercise:
Use the world database to answer these questions:
How many countries and cities are in the database?
What is the country code for China?
use the condition WHERE name = 'China'
How many cities are in China?
What languages are spoken in China?
what is the official language?
List the cities in China, sorted by population (largest to
smallest). Use "ORDER BY ..."
Exercise:
What is the country code for Thailand?
What is the population of Thailand?
How many cities are in Thailand?
what are the names of the cities?
What languages are used in Thailand?
Strings and wildcards in SELECT
Use single quote marks for String data.
For exact matches use field = 'value'
SELECT * FROM city
WHERE CountryCode = 'THA';
For pattern matches, use: field LIKE 'pattern'
SELECT * FROM city WHERE name LIKE 'Ba%';
In SQL, '%' means "match anything".
SELECT * FROM clients WHERE
firstName LIKE 'J%' ORDER BY lastName;
SQL statements and field names are case insensitive, but the field
values may not be! It depends on the data type of the field.
Logical operations
OR
SELECT * FROM City WHERE
District='Songkhla' OR District='Bangkok';
AND
SELECT Name, SurfaceArea FROM Country WHERE
Continent = 'Africa' AND SurfaceArea > 1000000;
NOT
SELECT * FROM Accounts WHERE
NOT AvailableBalance = 0;
Set operations
IN
SELECT * FROM City WHERE
District IN ('Songkhla', 'Bangkok');
Exercise: who lives longest?
How many countries are in Asia?
In which countries in Asia do people live longest?
List the countries sorted by life expectancy.
Where can you expect to live > 80 years?
What country in Asia has the shortest life expectancy?
How does Thailand rank for life expectancy?
COUNT the countries in Asia with life expectancy
less than in Thailand.
COUNT the countries in Asia with life expectancy
greater than in Thailand.
Exercise
Find all cities in Thailand, ranked by Population
use:
"SELECT ... ORDER BY Population DESC"
do you see any errors in the data?
Using Functions in SELECT
How many people are in the world?
SELECT SUM(population) FROM Country;
What is the largest surface area of any country?
SELECT MAX(SurfaceArea) FROM Country;
How many cities are from Thailand?
SELECT COUNT(*) FROM City
WHERE countrycode = 'THA';
What is the version of MySQL?
SELECT version();
Exercise
What is the total population of the world?
What is the total population of Asia?
What is the average and maximum population per
sq.km. of the countries in the world?
Country.SurfaceArea is the area in sq. km.
Getting Help
Built-in help for
how to use the mysql command
SQL statements
mysql> HELP
mysql> HELP SELECT
If mysql doesn't have help on SQL commands, then load the "help
tables" data onto the server. Download help table data from:
http://dev.mysql.com/downloads in the "Documentation" section.
Using Subqueries
Use the result of one query as part of another query.
Which account has the greatest balance?
SELECT AccountNumber, AccountName
FROM Accounts
WHERE balance =
( SELECT max(balance) FROM Accounts );
Where do people live the longest?
SELECT Name, LifeExpectancy FROM Country
WHERE LifeExpectancy =
( SELECT max(LifeExpectancy) FROM Country );
To use SQL subqueries in MySQL you need version 4.1 or newer.
Exercise
Where do people live longer than in Thailand?
ORDER the results by DESCending life expectancy.
SELECT Name, LifeExpectancy FROM Country
WHERE LifeExpectancy >
( insert subquery here )
ORDER BY ... ;
Exercise
Which nation is the most crowded?
Find the country with maximum population density
(population per sq. km.)
Show the name and the population density
Hint: create an alias for a computed field:
sql> SELECT name,
population/surfaceArea AS density
WHERE ...
Alias:
density := population/surfaceArea
Exercise
Is Thailand more crowded than neighbor countries?
List the name and population density of all
countries in the same region as Thailand.
use a subquery for "same region as Thailand":
SELECT ... FROM Country
WHERE Region = (SELECT Region ... Code=...)
ORDER ...;
Order the results by population density.
Exercise
How does Thailand's economic output per capita
compare with other countries in Asia?
Compare 1,000,000 * GNP / population.
Modify data with UPDATE
UPDATE changes data in one or more tables:
UPDATE accounts SET balance=100000
WHERE clientID='11111111';
sql> UPDATE accounts SET balance=100000
WHERE clientID='11111111';
+---------------+---------------+----------+---------+
| accountNumber | accountName
| clientID | balance |
+---------------+---------------+----------+---------+
| 11111111
| J.Brucker
| 11111111 | 1000000 |
| 11111112
| Vacation Fund | 11111111 | 1000000 |
| 11111113
| P.Watanapong | 11111120 | 300000 |
| 11111114
| CPE Fund
| 11111120 | 1840000 |
+---------------+---------------+----------+---------+
UPDATE statement
you can change multiple columns:
UPDATE table
SET field1=value1, field2=value2
WHERE condition;
sql> UPDATE clients
SET email='[email protected]', firstName='Jim'
WHERE clientID='11111111';
Query OK, 1 row affected (0.09 sec)
| clientID | firstName | lastName | email
|
+----------+-----------+----------+--------------------+
| 11111111 | Jim
| Brucker | [email protected]
|
Warning: UPDATE is immediate!
There is no "undo". Changes take effect immediately.
Be Careful! If you forget the WHERE clause it will
change all the rows in the table!
sql> UPDATE accounts SET balance=100000 ; /* oops! */
+---------------+---------------+----------+---------+
| accountNumber | accountName
| clientID | balance |
+---------------+---------------+----------+---------+
| 11111111
| J.Brucker
| 11111111 | 1000000 |
| 11111112
| Vacation Fund | 11111111 | 1000000 |
| 11111113
| P.Watanapong | 11111120 | 1000000 |
| 11111114
| CPE Fund
| 11111120 | 1000000 |
+---------------+---------------+----------+---------+
Exercise
Get the current population for a city in Thailand.
Search the web or www.ttt.go.th
Update the population in the world database.
Question:
what happens if two people change the same city data
(same record) at the same time?
Inserting new records
INSERT adds a new record to a table
INSERT INTO tablename VALUES ( value1, value2, ...);
sql> INSERT INTO Accounts VALUES
('22222222', 'Ample Rich', '00000000' 10000000);
Query OK, 1 row affected.
+---------------+---------------+----------+---------+
| accountNumber | accountName
| clientID | balance |
+---------------+---------------+----------+---------+
| 22222222
| Ample Rich
| 00000000 |10000000 |
+---------------+---------------+----------+---------+
Insert into columns by name
You can specify which columns will receive the values:
INSERT INTO tablename (field1, field2, ...)
VALUES ( data1, data2, ...);
sql> INSERT INTO Accounts
(accountNumber, balance, accountName)
VALUES ('22222222', 10000000, 'Ample Rich');
Query OK, 1 row affected.
+---------------+---------------+----------+---------+
| accountNumber | accountName
| clientID | balance |
+---------------+---------------+----------+---------+
| 20000000
| Ample Rich
|
|10000000 |
+---------------+---------------+----------+---------+
Exercise
Add your home town to the City table
or, add another City to the City table.
sql> INSERT INTO city
(name, countryCode, district, population)
VALUES ('Bangsaen', 'THA', 'Chonburi', 100000);
Query OK, 1 row affected.
The ID field has a qualifier of "AUTO_INCREMENT".
(use "DESCRIBE City")
This means MySQL will assign the ID value itself.
Exercise
View the city data that you just added!
Use UPDATE to change the population of the city you
added.
sql> UPDATE City SET population = 95000
WHERE city.name = 'Bangsaen';
Query OK, 1 row affected.
sql> SELECT * FROM City WHERE City.name = 'Bangsaen';
How do you get data into a table?
Use INSERT commands (boring).
Put INSERT commands in a Script (text file) and
"source" the file (better).
Import command (may depend on DBMS):
LOAD DATA INFILE 'filename' INTO table ...
BCP ("bulk copy" - MS SQL server)
Copying Data Between Tables
Suppose we have another table named NewAccts
NewAccts has accountNumber, accountName, ...
INSERT INTO tablename (field1, field2, ...)
SELECT field1, field2, field3
FROM othertable WHERE condition;
sql> INSERT INTO Accounts
SELECT * FROM NewAccts
WHERE accountNumber NOT NULL;
Relating Tables
The power of a relational database is the ability to
selectively combine data from tables. You can use:
select data from multiple tables by matching a field
relations can be
1-to-1
1-to-many
many-to-1
many-to-many
student -> photograph
country -> city
city -> country
language -> country
Relational Structure
The Bank.clients table contains bank client information.
The primary key is clientID.
sql> DESCRIBE clients;
+-----------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| clientID | int(11)
|
| PRI | 0
|
|
| firstName | varchar(40) |
|
|
|
|
| lastName | varchar(40) |
|
|
|
|
| email
| varchar(40) | YES |
| NULL
|
|
+-----------+-------------+------+-----+---------+-------+
Accounts
accountNumber
accountName
clientID
balance
availableBalance
clientID
Clients
clientID (KEY)
firstName
lastName
email
Joining Tables
The power of a database comes from the ability to
relate or "join" tables using a condition.
Use "table.field" to qualify a field name:
Accounts.balance, Clients.clientID, ...
The clientID joins the Accounts table and Clients table.
Accounts
accountNumber
accountName
clientID
balance
availableBalance
*
1
Clients
clientID (KEY)
firstName
lastName
email
Accounts.clientID = Clients.clientID
Joining Tables (2)
Show the balance of all accounts owned by J. Brucker.
the account balance is in the Accounts table.
the client name is the Clients table.
relate the tables using clientID field.
SELECT accountNumber, firstName, balance
FROM Accounts, Clients
WHERE Accounts.clientID = Clients.clientID
AND clients.firstName = 'James';
+---------------+---------------+---------+
| accountNumber | firstName
| balance |
+---------------+---------------+---------+
| 11111111
| James
| 100000 |
| 11111112
| James
|
22500 |
Exercise
What fields can we use to relate tables in the world
database?
City
Country
Code
Name
Continent
Region
SurfaceArea
Population
GNP
LocalName
Capital
ID
Name
CountryCode
District
Population
CountryLanguage
CountryCode
Language
isOfficial
Percentage
Example: Join Country and City
SELECT Country.Name, City.Name
FROM Country, City
WHERE Country.Code = City.CountryCode
AND
Continent = 'South America';
Country
Code
Name
Continent
Region
SurfaceArea
Population
GNP
LocalName
Capital
City
Country.Code = City.CountryCode
ID
Name
CountryCode
District
Population
Exercise
In what countries is the Thai language used?
By what percentage of the people?
Example:
SELECT ...
FROM Country, CountryLanguage
WHERE Country.Code = CountryLanguage.CountryCode
AND ... ;
Use Aliases to Reduce Typing
In what countries is Chinese the official language?
SELECT C.name, L.language, L.percentage
FROM Country C, CountryLanguage L
WHERE C.code = L.countrycode
AND language = 'Chinese'
AND isOfficial = 'T';
you can omit table
name when there is
no ambiguity
L is an alias for
CountryLanguage
Exercise
What countries use English?
ORDER the results by percentage spoken, from
largest to smallest %.
In how many countries is English the official
language?
Harder
In the world, approximately how many people speak
English?
sum( population * Language.percentage / 100 )
"JOIN"
JOIN joins tables
Many forms:
INNER JOIN (include only matching columns)
OUTER JOIN (include all columns)
LEFT OUTER JOIN
NATURAL JOIN
CONDITION JOIN
Learn them all in a course on database...
Example of a Condition Join
JOIN the CountryLanguage and Language tables
using the country code:
SELECT * FROM Country C
JOIN CountryLanguage L
ON C.code = L.countrycode
;
Exercise
JOIN the Country and Language tables.
View Country name and language with "SELECT ..."
How many times is Thailand listed in the results?
How many times is China listed in the results?
How can you order the results by language ?
Multiple Condition Join
You can join many tables at one time:
SELECT CO.name, CI.*, L.language
FROM Country CO
JOIN CountryLanguage L
ON CO.code = L.countrycode
JOIN City CI
ON CO.code = CI.countrycode
WHERE ...; /* more conditions */
Data Definition Commands
CREATE
create a Table, Index, or Database
ALTER
modify structure of a Database or Table
DROP
delete an entire Table, Index, or Database
RENAME
rename a Table
Creating a Table
To add a table to a database:
CREATE TABLE tablename ( field1, field2, ... )
options ;
sql> CREATE TABLE CUSTOMER (
accountNumber VARCHAR(8) NOT NULL,
clientID
VARCHAR(40) NOT NULL,
balance
DOUBLE DEFAULT '0',
availableBalance DOUBLE DEFAULT '0'
) ;
Query OK, 0 rows affected.
Productivity Hint
Type the "CREATE TABLE" statement into a file.
Read the file into mysql: source filename;
File: /temp/create-table.sql
CREATE TABLE CUSTOMER (
accountNumber VARCHAR(8) NOT NULL,
clientID
VARCHAR(40) NOT NULL,
balance
DOUBLE DEFAULT '0',
availableBalance DOUBLE DEFAULT '0',
PRIMARY KEY( clientID )
) ;
sql> SOURCE /temp/create-table.sql;
Query OK, 0 rows affected.
Deleting Records
Use DELETE to delete one or more records
DELETE FROM tablename WHERE condition;
Delete all bank accounts with zero balance
sql> DELETE FROM Accounts WHERE balance <= 0;
Query OK, 0 row affected.
Deleting a Table
To delete a table to a database:
DROP TABLE tablename ;
sql> DROP TABLE CUSTOMER ;
Exercise
Delete the city you added to the City table.
On your friend's machine, is it deleted immediately?
Views
A View acts like a "virtual table" containing selected
data from one or more real tables.
Country
Name
Continent
Code
...
CountryLan
guage
Language
Percentage
isOfficial
...
CREATE VIEW MyView AS ...
MyView
Name
Language
Percentage
View Example
Create a view that shows country names, languages,
and percentages.
sql> CREATE VIEW lang
AS
SELECT name, language, percentage
FROM Country C, CountryLanguage L
WHERE C.code = L.countrycode
ORDER BY language ASC;
Query OK, 0 rows affected.
Use a view similar to using a table:
sql> SELECT * FROM lang WHERE language='Thai';
Productivity Hint
Type the "CREATE VIEW" statement into a file.
Read the file into mysql: source filename;
File: /temp/makeview.sql
CREATE VIEW lang AS
SELECT name, language, percentage
FROM Country C, CountryLanguage L
WHERE C.code = L.countrycode;
sql> SOURCE /temp/makeview.sql;
Query OK, 0 rows affected.
Exercise
Create a view that shows these fields:
City.name as name
Country.name as country
Region
Population of the city
Official language
id of the city
Each person should use a different name for his view,
to avoid interfering with each other.
List the tables in world ( show tables ).
Exercise
List the cities in Southest Asia where English is the
official language and population is over 100,000.
Exercise
Ask MySQL to "describe" your view.
Delete your view:
DROP VIEW viewname ;
Review
What is the command to ...
list all the databases that you have access to?
use a database?
get information about the structure of a table?
query data in one or more tables?
add new row(s) of data to a table?
change some existing data in rows?
delete some rows of data?
delete an entire table?
SQL Quiz
Database Game
Rules
"largest" and "smallest" refer to size (surfaceArea).
"most populous", "least populous" refer to population
"richest", "poorest" means GNP per capita
(population), not total GNP, but the GNP must not be
zero (GNP = 0 means no data).
What is the World's Smallest Nation?
How big is it?
How many people live there?
What is the Largest Country in Africa?
Show the SQL
How big is it?
In what countries is Thai spoken?
SELECT c.name, l.language
FROM Country c
JOIN CountryLanguage l
ON c.code = l.countrycode
WHERE ...
How many nations speak English?
Is English spoken in Thailand?
Fix the database.
SELECT COUNT(*)
FROM Country c
JOIN CountryLanguage l
ON c.code = l.countrycode
WHERE ...
What cities have a population > 6M ?
Print a list of city name, population, country name
sorted by population -- largest first
+-----------------+------------+-------------------------+
| name
| population | country_name
|
+-----------------+------------+-------------------------+
...
| Bangkok
|
6320174 | Thailand
|
...
What cities are in Thailand ?
Sort them by District
+------+-------------------+-------------------+------------+
| ID
| Name
| District
| Population |
+------+-------------------+-------------------+------------+
| 3320 | Bangkok
| Bangkok
|
6320174 |
...
Resources
MySQL
http://dev.mysql.com/tech-resources/articles/
Learning SQL
http://www.w3schools.com/sql/
nice tutorial and command reference