CS 418/518 - Web Programming Fall 2012: Tues/Thurs 11am-12:15pm, E&CS 2120

Staff

• Dr. Michele Weigle
• mweigle at cs.odu.edu
• E&CS 3214
• Office Hours:
Tues/Thurs 9:30-11am
• TA: Harshith Bandi (hbandi)

# Lab 1 - MySQL - SOLUTIONS

September 6, 2012

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

### 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
```

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.