# SQL Notes: Exercise 2 - SELECT Using Inner Joins

Name: ANSWERS

Write a SELECT statement which produces the results. Put each clause (FROM, WHERE, ...) on a separate line. Unless otherwise specified, use the example database from the book SQL: The Complete Reference.

1. List all order numbers, amounts, and the name of the customer (company) that placed the order. (Similar to p 141)
   Answer:

           SELECT order_num, amount, company
              FROM Orders, Customers
              WHERE cust = cust_num;

2. List all employees by name and the city they work in. (Similar to p 142)

           SELECT name, city
              FROM Salesreps, Offices
              WHERE rep_office = office;

3. List all offices (city) and the manager's name for each office. (Similar to p 144)

           SELECT name, city
              FROM Salesreps, Offices
              WHERE mgr = empl_num;

4. List all the order numbers, amounts, sales rep names, and customers who placed the orders. (Same as p 147)

           SELECT order_num, amount, name company
              FROM Orders, Customers
              WHERE cust = cust_num AND rep = empl_num;

5. Print the woman's first name, man's first name, and their common last name for all pairs of people who have the same last name. Use the definitions below.

       CREATE TABLE People (
          userID      INTEGER PRIMARY KEY,
          first_name  VARCHAR(20) NOT NULL,
          last_name   VARCHAR(50) NOT NULL,
          gender      TINYINT NOT NULL    /* 1=Male, 2=Female */
       );

       INSERT INTO People
         VALUES(0, 'Micky', 'Mouse', 1);
       INSERT INTO People
         VALUES(1, 'Brenda', 'Beegoode', 2);
       INSERT INTO People
         VALUES(2, 'Minnie', 'Mouse', 2);
       . . .  /* and many more */

           SELECT F.first_name, M.first_name, F.last_name
              FROM People F, People M
              WHERE F.last_name = M.last_name
                AND F.gender = 2 AND M.gender = 1;