Big Data 2015 - SQL Lab

Today, we will work on SQL queries.

Before the class, please download mySQL from from http://dev.mysql.com/downloads. There are many different versions, select MySQL Community Server.

Installing and starting mySQL

mySQL has an extensive documentation, including installation instructions for different OSes, for example, MacOS: https://dev.mysql.com/doc/refman/5.7/en/macosx-installation.html and for Windows: http://www.mysql.com/why-mysql/windows/

I run mySQL on MacOS, so after the installation was complete, I 'started' the database using System Preferences.

To connect to the MySQL server, on a terminal, invoke /usr/local/mysql/bin/mysql.

You can add aliases to your shell's resource file to make it easier to access commonly used programs such as `mysql' and `mysqladmin' from the command line. I have added the following 2 lines to my .bashrc:

    alias mysql=/usr/local/mysql/bin/mysql
    alias mysqladmin=/usr/local/mysql/bin/mysqladmin

You can also modified my PATH environment variable to include /usr/local/mysql/bin:

export PATH=$PATH:./:/usr/local/mysql/bin:/usr/local/mysql/bin:/usr/local/hadoop-1.2.1/bin:/usr/local/pig-0.12.0/bin

Our running example

We will work with three tables. Here are the table definitions:

   create table Sailors(
       sid int PRIMARY KEY,
       sname varchar(30),
       rating int,
       age int);

   create table Reserves(
       sid int,
       bid int,
       day date,
       PRIMARY KEY (sid, bid, day));

   create table Boats(
       bid int PRIMARY KEY,
       bname char(20),
       color char(10));

You can download the sample file with these statements as well as some sample data from http://vgc.poly.edu/~juliana/courses/BigData2015/Lectures/Lab-SQL/sailors-mysql.sql

Write the following queries in SQL

1. Find the name and ages of all sailors

2. Find all sailors with a rating above 7

3. Find the names of sailors who have reserved boat number 103

4. Find the sids of sailors who have reserved a red boat

5. Find the names of sailors who have reserved a red boat

6. Find the names of sailors who have reserved at least one boat

7. Find the names of sailors who have reserved a red or a green boat

8. Find the names of sailors who have reserved both a red and a green boat

9. Find the sids of all sailors who have reserved red boats but not a green boats

10. Find the names of sailors who have not reserved boat number 103

11. Find the names of sailors whose rating is better than some sailor called Horatio

12. Find the names of sailors whose rating is better than all sailors called Horatio

13. Find the names of sailors who have reserved all boats

14. Find the average age of sailors with a rating of 10

15. Find the name and age of the oldest sailor

16. Find the age of the youngest sailor who is eligible to vote (i.e., is at least 18 years old) for each rating level with at least two such sailors

17. find the sids, snames and bnames of sailors and the boats they have reserved, if any

SQL Cheat Sheet

SELECT AcctNo, Amount FROM ATMWithdrawal WHERE Amount < 50;

A SQL query is evaluated as follows:

• First the FROM clause tells us the input tables
• Second, the WHERE clause is evaluated for all possible combinations from the input tables
• Third, the SELECT clause tells us which attributes to keep in the query answer