219241 Programming Assignment 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 .

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.

What Tables are in the Database?

Connect to the world database. What tables are in the database?

sql> use world;

sql> ________________________________________;

Answer:

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? |Default |

|ID |integer |NO |PRIMARY | |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

Table: Country

|Field Name |Type |Can be Null? |Key? |Default |

|Code |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? |Default |

|CountryCode | |NO | | |

|Language | |NO | | |

|IsOfficial | |NO | |'F' |

|Percentage | |NO | |0.0 |

1 Are SQL commands case sensitive?

select * from tablename

SELECT * FROM tablename

Answer: _______________________________

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.

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]

1 What are the first 3 Cities in Database?

sql>

Answer:

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 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)

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:

SELECT using Expressions and Functions

You can use operations like +, -, *, /, and % (modulo) almost anyplace where you can write a field name.

1 What Countries have names beginning with 'Z'?

sql>

Answer:

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

3 How many Countries are in the World?

sql>

Answer:

4 What is the total GNP of the world (in millions USD)?

sql>

Answer:

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

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:

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>

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>

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: _______________________________________________

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

1 What Cities are in Laos?

sql> SELECT City.Name, Country.Name

FROM _________________________

WHERE _________________________

AND _____________________

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'

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 |Description |

|INTEGER |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 |FLOAT(6) |float w/ 6-digit precision, |

| |may be larger |FLOAT(6, 2) |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 |TIMESTAMP |date and time, with timezone info |

| |yyyy-mm-dd hh:mm:ss | | |

|CHAR(m) |fixed length string of size m, 1 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download