Lab 1 - MySQL - SOLUTIONS

September 6, 2012

Lab1-MySQL

(Exercises come from http://cs.smith.edu/dftwiki/index.php/CSC220_Exercises_with_MySQL and http://code.google.com/edu/tools101/mysql.html)

There are likely several ways that these tasks could be done, but here are my solutions.

1) Table Normalization

See http://code.google.com/edu/tools101/mysql.html

2) Store Inventory

In your MySQL database on mweigle418, create a table named lab1_store with the following contents:

 id	name	qty	price
 1	apple	10	1
 2	pear	5	2
 3	banana	10	1.5
 4	lemon	100	0.1
 5	orange	50	0.2

Using SELECT statements, complete the following tasks:

1. List all the items sorted alphabetically.

 SELECT *
 FROM lab1_store
 ORDER BY name

2. List only the first 3 items.

 SELECT *
 FROM lab1_store
 LIMIT 3

3. List only the last 3 items.

 SELECT *
 FROM lab1_store
 LIMIT 2,3

4. List only the items that are more than $1 per unit price

 SELECT *
 FROM lab1_store
 WHERE Price > 1

5. List all the items with their extended price (quantity * price)

 SELECT *, (qty * price) AS extended_price
 FROM lab1_store

6. List the total cost of all the items in the store

 SELECT SUM(qty*price) AS total_cost
 FROM lab1_store

7. List the total number of items we have in the store.

 SELECT SUM(qty) AS num_items
 FROM lab1_store

First check your SELECT statements using the MySQL command line or the phpmyadmin interface. Then, write a web page named lab1-2.php that outputs the results of each query.

3) Courses

Create the following tables:

lab1_courses

 id	name	dept_id
 1	111	1
 2	112	1
 3	250	1
 4	231	1
 5	111	2
 6	250	3
 7	111	4

lab1_dept

 id	name
 1	CSC
 2	MTH
 3	EGR
 4	CHM

lab1_enrollment

 course_id	count
 1	        40
 2	        15
 3	        10
 4	        12
 5	        60
 6	        14
 7	        200

Using SELECT statements, answer the following questions/tasks:

1. List all the CS classes.

 SELECT *, lab1_courses.name as course
 FROM lab1_courses
 JOIN lab1_dept
 ON dept_id = lab1_dept.id
 WHERE lab1_dept.name = "CSC"

2. What is the total enrollment over all the classes?

 SELECT SUM(count) AS total_enrollment
 FROM lab1_enrollment

3. How many different classes are taught?

 SELECT COUNT(id) AS num_classes
 FROM lab1_courses

4. How many different departments are there?

 SELECT COUNT(id) AS num_depts
 FROM lab1_dept

5. List all the classes in the database, with the department name and the class name on the same line, e.g. CSC 111, CSC 112, ..., EGR 250, ... CHM 111

 SELECT lab1_dept.name as dept_name, lab1_courses.name
 FROM lab1_courses
 JOIN lab1_dept
 ON dept_id = lab1_dept.id

6. List the name of the CS classes so that they are output as "CSC111", "CSC112", etc... (in other words, concatenate department with class number.)

 SELECT CONCAT(lab1_dept.name, lab1_courses.name) as course_name
 FROM lab1_courses
 JOIN lab1_dept
 ON dept_id = lab1_dept.id
 WHERE lab1_dept.name = "CSC"

7. List all the information in the database, where each class appears on 1 line, along with its department, and its enrollment.

 SELECT *, lab1_courses.name as course_name 
 FROM lab1_courses
 JOIN (lab1_dept, lab1_enrollment)  
 ON (lab1_courses.dept_id = lab1_dept.id AND lab1_courses.id = lab1_enrollment.course_id) 

Nicely formatted (w/o ids)

 SELECT lab1_dept.name as dept_name, lab1_courses.name, lab1_enrollment.count
 FROM lab1_courses
 JOIN (lab1_dept, lab1_enrollment) 
 ON (lab1_courses.dept_id = lab1_dept.id AND lab1_courses.id = lab1_enrollment.course_id) 

8. List all the information as formatted in Question 7, but only the classes with an enrollment larger than 50.

 SELECT *, lab1_courses.name as course_name
 FROM lab1_courses
 JOIN (lab1_dept, lab1_enrollment)  
 ON (lab1_courses.dept_id = lab1_dept.id AND lab1_courses.id = lab1_enrollment.course_id) 
 WHERE lab1_enrollment.count > 50

Nicely formatted (w/o ids)

 SELECT lab1_dept.name as dept_name, lab1_courses.name, lab1_enrollment.count
 FROM lab1_courses
 JOIN (lab1_dept, lab1_enrollment) 
 ON (lab1_courses.dept_id = lab1_dept.id AND lab1_courses.id = lab1_enrollment.course_id) 
 WHERE lab1_enrollment.count > 50

First check your SELECT statements using the MySQL command line or the phpmyadmin interface. Then, write a web page named lab1-3.php that outputs the results of each query.