SQL

This webpage lists all the SQL statements that a student is expected to understand after finishing the E-learning material.
For each topic, we list some example statements, the corresponding outputs, as well as the related E-learning materials (follow the link).
The SQL tutorial prepared by W3School (http://www.w3schools.com/sql/) is selected as the complementary material.

All the SQL statements included in this website are based on a simple database model, as shown in the following (Testing Data).

The sample code to create the tables and insert data can be downloaded here(create_code, insert_value_code).

Elements of Databases
Data Definition Language
- Create relations
- Common Data Types Supported by MySQL
- Change the structure of a relation
- Drop relations
Manage the Data in the Relations
- Insert Statement
- Delete data from a relation
- Update Statement
Query a Relation -SELECT Statement
- Simple SELECT Statement
- Using WHERE clause
- Using IN keyword
- Using LIKE clause
- Using Aliases
- Using ORDER BY clause
- Using Functions
- Common Functions Supported by MySQL
- Using GROUP BY clause
Query Multiple Relations: using JOIN
Query Multiple Relations: using sub-select Statements
Ready for Exercises

Elements of Databases:

A database management system (DBMS) can have multiple databases.

For each database, it is composed of relations (tables). For each table, it has one or more rows, with each corresponding to a record.

The relationship between a table and multiple rows is similar as that between an entity type and multiple entity instances.

To create a database, you can start WAMP server and MySQL WorkBench. Create a connection to link MySQL WorkBench to the SQL Server (Snapshot 1, Snapshot 2, Snapshot 3).
MySQL WorkBench provides a shortcut to create a database (A snapshot), and you can enter the name PineValley (A snapshot).

Once you create the database PineValley, you can find it in the database list, the drop down list in the left (A snapshot).
Please select PineValley as the database (A snapshot), and all the following statements create tables, insert values, and select values from PineValley database ONLY.
Please note SQL statement is CASE-INSENSITIVE.

Once you call the drop database statement, everything inside this database will be gone.
In SQL Query Browser, you can select the database and then click the DROP SCHEMA to remove the database (A snapshot).

Back to Top

Data Definition Language:

When a database is created, it does not contain any relation.
We can use CREATE TABLE statement to create new relations, and DROP TABLE statement to delete those useless relations.
In case the structure of a relation has to be changed, ALTER TABLE statement can be used.

Back to Top

Create Relations: (link)

Each DBMS supports different data types.
Even for the same data type (like string), different DBMS may use different names and/or different format.
A summary of the common data types available in MySQL is listed at the end of this page.
A complete description of all the data types available in MySQL is provided here.

When create tables inside a database, be careful about the order.
Like in our example, table ORDER_t and table PRODUCT_t must be created before the creation of table ORDER_LINE_t.
The reason is ORDER_LINE_t table uses primary keys of PRODUCT_t and ORDER_t as foreign keys.
SQL Query Browser provides a user-friendly interface to run SQL statement (A snapshot).

Back to Top

Common Data Type supported by MySQL

Data Type	Description	Examples
INT	An integer with value between and	-1278, 87600, 14
NUMERIC	A floating point number	-897.23, 0.984
DECIMAL(w(,n))	A floating point number with w the display width and n the number of digits after the decimal point (Note:w and n are optional)	DECIMAL(4,2): 89.12
CHAR(n)	fixed-length non-unicode character data with n the fixed length
VARCHAR(n)	variable-length non-unicode data with n the maximal length
DATE	date in 'YYYY-MM-DD' (Note: the single quotation mark is compulsory)	'1996-02-25'
DATETIME	date/time in '`YYYY-MM-DD HH:MM:SS' format`	'`2007-05-23 09:15:28'`

Want to insert data into a table -> INSERT Statement
Want to update the values of certain data in a table -> UPDATE Statement
Want to delete certain data from a table -> DELETE Statement

SELECT statement is used to retrieve data from the database based on the conditions specified in the SELECT statement.
There are six building blocks for SELECT statement, as shown in the following figure.
Among them, SELECT and FROM clauses are necessary to form one valid SELECT statement.

String Functions
Function

Purpose

concat(s1, s2, ...)

Concatenate the strings in s1, s2, ....

length(s)

Returns the length in characters of the string s

lower(s) and upper(s)

Convert the string s to lowercase or uppercase.

replace(target, find, replace)

Returns a string based on target with all incidences of find replaced with replace.

substring (s, position, length)

Returns length characters from s starting at position.
STRCMP(s1, s2)
Compares two strings s1 and s2, and returns the following values:

0 if the strings are equal

-1 if s1 is less than s2?that is, if it comes before s2 in the sort order

1 if s1 is greater than s2?that is, if it comes after s2 in the sort order

Date and Time Functions

Function

Purpose

curdate()

Returns the current date

curtime()

Returns the current time

now()

Returns the current date and time

extract(type FROM date)

This returns the value of type in date. For example, if you specify YEAR, it will return the year from date.

The type can be SECOND, MINUTE, HOUR, DAY, MONTH, YEAR, MINUTE:SECOND (the format of n should be 'm:s'), HOUR:MINUTE ('h:m'), DAY_HOUR ('d h'), YEAR_MONTH ('y-m'), HOUR_SECOND ('h:m:s'), DAY_MINUTE ('d h:m'), DAY_SECOND ('d h:m:s').

Customer_Id	Customer_Name	Order_Id
1	Contemporary Casuals	1001
1	Contemporary Casuals	1010
2	Value Furniture	1006
3	Home Furnishings	1005
4	Eastern Furniture	1009
5	Impressions	1004
6	Furniture Gallery	NULL
7	Period Furniture	NULL
8	Calfornia Classics	1002
9	M and H Casual Furniture	NULL
10	Seminole Interiors	NULL
11	American Euro Lifestyles	1007
12	Battle Creek Furniture	1008
13	Heritage Furnishings	NULL
14	Kaneohe Homes	NULL
15	Mountain Scenes	1003

SQL

Create Relations: (link)

Change a structure of a relation: (link)

Drop a relation: (link)

Insert statements: (link)

Delete data from a Relation: (link)

Update statements: (link)

Simple select statement: (link)

Using WHERE clause: (link)

Using IN keyword: (link)

Using LIKE clause: (link)

Using Aliases: (link)

Using ORDER BY clause: (link)

Using Functions: (link)

Using GROUP BY: (link)

Query multiple relations: using JOIN (link)

Query multiple relations: using sub-select statements:

Maintained by Baihua ZHENG. Revised: 02/14/11.

Maintained by Baihua ZHENG.
Revised: 02/14/11.