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
-
Run the following command from the terminal to install mysql
sudo apt-get install mysql-server
- When prompted for a password, type something in. Make sure there are no errors displayed.
- Make sure you remember this password, which will be used to start mysql.
Exercise
-
Make sure the server is working
sudo netstat -tap | grep mysql
-
Access MySql via the command line (where
-u
is for user, and-p
is password)sudo mysql -u root -p
-
Now inside the MySQL command line, run the following:
create database lab;
-
Now tell it to use that database:
use lab;
-
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
.
- List all the items sorted alphabetically.
- Then list only the first 3.
- Then list the last 3.
- List only the items that are more than $1 per unit price
- List all the items with their extended price (quantity * price)
- List the total cost of all the items in the store
- How many different items do we have in the store?
- List all the CS classes.
- What is the total enrollment over all the classes?
- How many different classes are taught?
- How many different departments are there?
- 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
- List the name of the CS classes so that they are output as “CSC111”, “CSC112”, etc… (in other words, concatenate department with class number.)
- List all the information in the database, where each class appears on one line, along with its department, and its enrollment.
- 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.