Lab #3: SQL queries involving multiple tables

 

Purpose: In this lab, you should establish solid understanding and skills to

·        relate three of the basic relational algebra operations: cross product, selection operation, and projection operation to the FROM clause, the WHERE clause, and SELECT clause of the SQL language, and

·        write SQL queries involving two or more tables precisely based on the understanding of the three basic relational algebra operations above.

 

Step 1: Login into MySQL local server in Metzger 100 lab with the password mysql.

 

Step 2: Use the buildUpDB.txt script to build up the records of the database containing the salespeople, customers, and orders tables as described in Homework#2. Follow the example queries in lab3.txt to interact with MySQL database server to establish solid understanding and skills as described in the purpose section above.

 

Step 3 Write SQL SELECT statements to find

  1. The names of salespeople who have customers (assigned customers) from London
  2. The names of salespeople who have customers (assigned customers) whose rating is higher than 150.
  3. The names of customers whose assigned salespeople have a commission rate higher than 0.12.
  4. The names of customers whose assigned salespeople work in the office in London
  5. The names of customers and their assigned salespeople
  6. The name of every customer whose assigned salesperson does NOT work in the same city the customer live
  7. The name of every salesperson who is assigned to a customer in the same city the salesperson works
  8. The names of all customers who have bought more than $2000.00 on a single order
  9. The names of all salespeople who participate in any order more than $2000.00
  10. The name of every customer who has one or more orders done NOT with his/her assigned salespeople
  11. The name of every salesperson one of whose assigned customers has an order done NOT with this salesperson

 

Step 4: Write down the SQL queries you have for the questions above in this week’s progress report.

 

Useful references for doing this homework:

· Simple tutorial on the very basics of SQL from W3 SCHOOL

· The SQL overview handout and the relational algebra handout