Lab #2: Database creation and the very basics of SQL queries

Purpose: In this lab, you should establish solid understanding and skills to use

· CREATE DATABASE, CREATE TABLE statements to create a database and tables,

· INSERT INTO statements to insert records into the tables,

· SHOW TABLES and DESCRIBE to gain information about databases and tables,

· SELECT statements to write simple queries involving only a single table using

1. relational operators such as =, <>, >, >=, <, <= , string operator such as LIKE, logical operators such as AND, OR, NOT
2. features like ORDER BY and DISTINCT
3. GROUP BY clauses and HAVING clauses together with aggregate functions such as COUNT,AVG, MAX, and MIN.

Step 1: Login into MySQL local server in Metzger 100 lab with the password mysql.

Step 2: Follow the examples in this sample script to interact with MySQL database server to establish solid understanding and skills as described in the purpose section above.

Step 3: Create the customer table and the order table in the same database, and insert the records into the tables.

Step 4: Write SELECT statements to find

• Customers who live in London and has a rating greater than 150
• Find the average customer rating
• Display the customer information ordered by ascending order of the ratings
• Find the average rating of customers from each city using GROUP BY
• Find all customers whose personal names and city names contain the letter n.

Step 5: Report your progress in this lab in this week’s progress report.

Useful references for doing this homework:

· Simple tutorial on the use of CREATE TABLE and INSERT INTO, and others from W3 SCHOOL

· Full syntax of CREATE DATABASE, CREATE TABLE and INSERT INTO from the MySQL online reference manual

· The overview handout on SQL