SQL Database Lab Page 1
Worksheet for SQL
This lab introduces some common SQL commands with examples. The exercises use the World database, which is available free from MySQL.
Required Software
1. mysql command line client. This is included in the mysql-essentials package or MySQL Community Edition from www.mysql.com.
2. (Optional) Visual query tool for MySQL. The free MySQL Workbench (requires Visual C++ redistribution pack and .Net 4.0) or MySQL GUI Tools (does not require C++) are GUI tools for accessing MySQL.
2. World database. You will use the database on se.cpe.ku.ac.th (nothing to install.) But, if you want your own World database, see separate instructions for how to install.
The World Database
World is a sample database provided by MySQL. The data is from the 1990s, but it's still useful. There are three tables in the database. It's not a great database design: the Continent attribute is an enumeration of Strings, the Region is a String, and Language is a String.
Format of Exercises
Write the SQL command you use to get the data. If the result is requested, write that, too.
1 What Tables are in the Database?
Connect to the world database. What tables are in the database?
sql> use world;
sql> ______;
Answer:
2 What columns are in the City table?
There are two commands to show the columns in a table. Show both:
sql> ______;
sql> ______;
Table: City
Complete the table. For "INT(n)" just write "integer", for FLOAT(n) just write "float".
Field Name / Type / Can be Null? / Key? / DefaultID / integer / NO / PRIMARY
Table: Country
Field Name / Type / Can be Null? / Key? / DefaultCode / char(3) / NO
Name / char(52) / NO
Continent / enum / NO / 'Asia'
Region / char(26) / NO
SurfaceArea / float / NO
IndepYear / smallint / YES
Population / integer / NO
LifeExpectancy / float / YES
GNP / float / YES
LocalName / char(45) / NO
GovernmentForm / char(45) / NO
Capital / integer / YES
Code2 / char(2) / NO
Table: CountryLanguage
Field Name / Type / Can be Null? / Key? / DefaultCountryCode / NO
Language / NO
IsOfficial / NO / 'F'
Percentage / NO / 0.0
2.1 Are SQL commands case sensitive?
select * from tablename
SELECT * FROM tablename
Answer: ______
2.2 In MySQL are names case sensitive?
Write the answer that you experimentally observe.
Database names? ______
Table names? _____
Field (column) names? _____
Note: The answer for Database and Table names depends on the operating system and configuration of MySQL. It may be case sensitive or not. Write what you observe using our server.
3 Querying Data
The SELECT command returns data from tables.
SELECT * FROM tablename
SELECT * FROM tablename LIMIT n (only return n results)
SELECT field1, field2, field3 FROM tablename WHERE condition [ LIMIT n ]
SELECT fields FROM tablename WHERE condition ORDER BY field [ASC|DESC]
3.1 What are the first 3 Cities in Database?
sql>
Answer:
3.2 What are the 3 most populous countries in the World?
SELECT ... ORDER BY fieldname ASC (ascending)
SELECT ... ORDER BY fieldname DESC (descending - largest value first))
What are the 3 most populous countries in the world? Write their name and population.
sql>
Answer:
What is the smallest country in the world?
sql>
Answer: (name and size)
3.3 Select with WHERE
SELECT field1, field2, field3 FROM table WHERE condition
Example: SELECT * FROM Country WHERE Name='Japan'
What is the CountryCode for Thailand?
sql>
Answer:
What cities are in Thailand, sorted by population (largest first)?
sql>
Answer: (at least 3 cities)
3.4 SELECT using WHERE, ORDER, and LIMIT
SELECT fields FROM table
WHERE condition
ORDER BY field [ASC|DESC]
LIMIT n
What Country has the largest population in Europe? What is its population?
sql>
Answer:
4 SELECT using Expressions and Functions
You can use operations like +, -, *, /, and % (modulo) almost anyplace where you can write a field name.
4.1 What Countries have names beginning with 'Z'?
sql>
Answer:
4.2 What is the GNP of Thailand, in unit of Million Baht?
The GNP field of Country is measured in millions of US Dollars.
What is the GNP of Thailand, measured in millions of Baht. Use 1 USD =31 Baht.
sql>
Answer:
Using COUNT and other Functions
SELECT COUNT(*) FROM tablename
SELECT SUM(field) FROM tablename WHERE condition
SQL has many functions you can use in queries. Common functions are:
COUNT(field) or COUNT(*)
MAX(field)
MIN(field)
SUM(field)
AVG(field)
SUBSTR(field, start, length) e.g. SUBSTR(Name,1, 6)
You can apply a function to an expression, too. E.g. MIN(population/SurfaceArea).
4.3 How many Countries are in the World?
sql>
Answer:
4.4 What is the total GNP of the world (in millions USD)?
sql>
Answer:
4.5 What are the richest countries in the world?
"Rich" means income per person, which is GNP/population. The GNP is in millions of US Dollars, so multiply by 1,000,000 to get an answer in dollars.
What are the 2 richest countries in the world?
sql>
Answer:
Use an Alias for an Expression or Field
You can assign a name (an alias) to an expression in a SELECT statement. This saves typing and makes the output more readable.
SELECT expression AS alias FROM table WHERE ...
Example: the "density" is population per surface area. We can write:
SELECT name, population/SurfaceArea AS density FROM Country
4.6 What are the 3 most crowded countries in Asia?
Query to find the 3 most crowded nations in Asia and their population density (people/sq.km.).
sql>
Answer:
5 Adding A New City
Example: Bangsaen is in Chonburi province with a population of 30,000, except during Songkran the population is 500,000. To add Bangsaen to the database, use:
INSERT into City (name, district, population)
VALUES ('Bangsaen', 'Chonburi', 30000);
Exercise: Add your home town (or another city in Thailand) to the database:
sql>
What is the ID of the City you just added it?
Write the SQL statement to find the city you just added:
sql>
6 Modifying a Record
To change some values, use:
UPDATE tablename SET field1=value1, field2=value2 WHERE condition
Example: The "head of state" in the U.S.A. is now Barrack Obama.
UPDATE Country SET HeadOfState='Barrack Obama' WHERE code='USA';
Be Careful! UPDATE is Immediate and No "undo"
If you forget the WHERE clause or WHERE matches more than one record, it will change all matching records!
UPDATE Country SET HeadOfState='Barrack Obama';
Now Obama is head of the Entire World!!
Exercise:
6.1 What is the SQL to change the population of your city, using City and Country name to select your city?
sql>
6.2 What is the SQL to change the population of your City, using the City primary key?
sql>
7 Deleting Data
DELETE FROM tablename WHERE condition
Example: delete all cities in Thailand named "Bangsaen":
DELETE FROM City WHERE name='Bangsaen' AND countrycode='THA';
7.1 What is the command to delete the City that you added to the database?
sql>
7.2 What does this statement do?
DELETE FROM City WHERE name='Bangsaen' OR countrycode='THA';
Answer: ______
8 Using More Than One Table with WHERE
In a database, you can relate tables and query them together.
To do this, you must find a field or expression that the tables have in common.
Examples "what is the capital city of Denmark" (join County and City tables)
"what Countries speak Thai?" (join Country and CountryLanguage tables)
To connect tables using "WHERE" use:
SELECT field1, field2, ...
FROM table1, table2
WHERE table1.field1 = table2.field2
Example: Print the city names and country names for mega-cities
SELECT City.Name, Country.Name
FROM City, Country
WHERE City.countrycode = Country.code
AND City.population > 1000000
8.1 What Cities are in Laos?
sql> SELECT City.Name, Country.Name
FROM ______
WHERE ______
AND ______
9 JOIN Tables
A more semantic way of combining tables is to use JOIN
SELECT field1, field2, ...
FROM table1
JOIN table2
ON table1.field = table2.field
WHERE ... ORDER BY ...
You can assign an alias to table names to reduce typing. You can assign an alias to fields or expressions, too.
Example: Print the capital city of the countries in Europe:
SELECT c.Name, co.Name AS CountryName, co.Region
FROM Country co
JOIN City c
ON co.Capital = c.ID
WHERE co.Continent = 'Europe'
9.1 What Countries have a City named "Kingston"?
sql> SELECT c.Name, co.Name
FROM ______
JOIN ______
ON ______
WHERE ______
SQL Data Types
SQL has many data types, and some are not recognized by all databases. For example, every SQL datatype recognizes "INTEGER" and "SMALLINT", but may not allow "INT(11)".
Data Type / Description / Data Type / DescriptionINTEGER / 4 byte signed integer / FLOAT / 4-byte floating point
BIGINT / 8 bytes (like Java long) / DOUBLE / 8-byte floating point
SMALLINT / 2 bytes: -32768 to 32767 / DECIMAL / base 10, exact precision
TINYINT / 1 byte: -128 to 127 / FLOAT / 4-byte floating point
INT(n) / integer with n-digits, actual size may be larger / FLOAT(6)
FLOAT(6, 2) / float w/ 6-digit precision,
6 digits, 2 decimal places
BOOLEAN / true or false or undefined / DECIMAL(8, 2) / 8 digits, 2 decimal places e.g. 123456.78
DATE / Date as yyyy-mm-dd / TIME / Time as hh:mm:ss
DATETIME / Date & time as
yyyy-mm-dd hh:mm:ss / TIMESTAMP / date and time, with timezone info
CHAR(m) / fixed length string of size m, 1 <= m <= 255 / VARCHAR(m) / variable length string,
1 <= m <= 65,535
BLOB
BLOB(length) / binary large object, for binary data like images / CLOB
CLOB(length) / character large object, for large strings
ENUM('apple','grape'...) / enumerated type. MysQL has it, but not standard.