Lab 5—SQL

Objectives

• Install MySQL server on a Linux host
• Write SQL commands to query the database
• Please pair program today!

Note: if there is anything wrong with your computer, pair program today with someone else!

Pre-lab work

1. Run the following command from the terminal to install mysql

       sudo apt-get install mysql-server 
   

2. When prompted for a password, type something in. Make sure there are no errors displayed.
3. Make sure you remember this password, which will be used to start mysql.

Exercise

1. Make sure the server is working

       sudo netstat -tap | grep mysql
   

2. Access MySql via the command line (where -u is for user, and -p is password)

       sudo mysql -u root -p
   

3. Now inside the MySQL command line, run the following:

       create database lab;
   

4. Now tell it to use that database:

       use lab;
   

5. Create a few tables to work with. It is easiest to add them to a file then load them from the SQL CLI. Using vim, type the following SQL code into a file named db.sql.

Store

create table if not exists `store` (
  `id` int(1) not null auto_increment,
  `name` varchar(40) not null,
  `qty` int(1) not null,
  `price` float not null,
  primary key (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7;

insert into `store` (`id`, `name`, `qty`, `price`) values
  (1, 'apple', 10, 1),
  (2, 'pear', 5, 2),
  (3, 'banana', 10, 1.5),
  (6, 'lemon', 100, 0.1),
  (5, 'orange', 50, 0.2);

Course

create table if not exists `course` (
  `id` int(1) not null auto_increment,
  `name` varchar(4) not null,
  `department_id` int(1) not null,
  primary key (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=8;

insert into `course` (`id`, `name`, `department_id`) values
  (1, '111', 1),
  (2, '112', 1),
  (3, '250', 1),
  (4, '231', 1),
  (5, '111', 2),
  (6, '250', 3),
  (7, '111', 4);

Department

create table if not exists `department` (
  `id` int(1) not null auto_increment,
  `name` varchar(3) not null,
  primary key (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5;

insert into `department` (`id`, `name`) values
  (1, 'CSC'), (2, 'MTH'), (3, 'EGR'), (4, 'CHM');

Enrollment

create table if not exists `enrollment` (
  `id` int(1) not null auto_increment,
  `count` int(1) not null,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

insert into `enrollment` (`id`, `count`) values
  (1, 40),
  (2, 15),
  (3, 10),
  (4, 12),
  (5, 60),
  (6, 14),
  (7, 200);

Create the tables and add the content from the data file:

show tables;
source db.sql

Questions

Write out the query to do the following (test inside your VM).

Create another text file with all your queries in it, and use the file extension .sql.

1. List all the items sorted alphabetically.
2. Then list only the first 3.
3. Then list the last 3.
4. List only the items that are more than $1 per unit price
5. List all the items with their extended price (quantity * price)
6. List the total cost of all the items in the store
7. How many different items do we have in the store?
8. List all the CS classes.
9. What is the total enrollment over all the classes?
10. How many different classes are taught?
11. How many different departments are there?
12. 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
13. List the name of the CS classes so that they are output as “CSC111”, “CSC112”, etc… (in other words, concatenate department with class number.)
14. List all the information in the database, where each class appears on one line, along with its department, and its enrollment.
15. Use a tool to create an ER Diagram of these tables.

Credit

To get credit for this lab exercise, show the TA and sign the lab’s completion log.

Lab material by Liz Boese.