VI.1.2 Answers

Exercise 1.

1. Display everyone's first name and their age for everyone that's in table.

   select first, 
          age 
     from empinfo;

2. Display the first name, last name, and city for everyone that's not from Payson.

   select first, 
          last, 
          city 
     from empinfo
   where city <> 
     'Payson';

3. Display all columns for everyone that is over 40 years old.

   select * from empinfo
          where age > 40;

4. Display the first and last names for everyone whose last name ends in an "ay".

   select first, last from empinfo
          where last LIKE '%ay';

5. Display all columns for everyone whose first name equals "Mary".

   select * from empinfo
          where first = 'Mary';

6. Display all columns for everyone whose first name contains "Mary".

   select * from empinfo
          where first LIKE '%Mary%';

 

Exercise 2.

1.

2.

3.

SELECT customerid, order_date, item
FROM items_ordered
WHERE item LIKE 's%';

4.

SELECT DISTINCT item
FROM items_ordered;

 

Exercise 3.

1.

SELECT max(price)
FROM items_ordered;

2.

SELECT avg(price)
FROM items_ordered
WHERE order_date LIKE '%Dec%';

3.

SELECT count(*)
FROM items_ordered;

4.

SELECT min(price) FROM items_ordered WHERE item = 'Tent';

 

Exercise 4.

1.

SELECT state, count(state)
FROM customers
GROUP BY state;

2.

SELECT item, max(price), min(price)
FROM items_ordered
GROUP BY item;

3.

SELECT customerid, count(customerid), sum(price)
FROM items_ordered
GROUP BY customerid;

 

Exercise 5.

1.

SELECT state, count(state)
FROM customers
GROUP BY state
HAVING count(state) > 1;

2.

SELECT item, max(price), min(price)
FROM items_ordered
GROUP BY item
HAVING max(price) > 190.00;

3.

SELECT customerid, count(customerid), sum(price)
FROM items_ordered
GROUP BY customerid
HAVING count(customerid) > 1;

 

Exercise 7.

1.

SELECT customerid, order_date, item
FROM items_ordered
WHERE (item <> 'Snow shoes') AND (item <> 'Ear muffs');

Note: Yes, that is correct, you do want to use an AND here. If you were to use an OR here, then either side of the OR will be true, and EVERY row will be displayed. For example, when it encounters 'Ear muffs', it will evaluate to True since 'Ear muffs' are not equal to 'Snow shoes'.

2.

SELECT item, price
FROM items_ordered
WHERE (item LIKE 'S%') OR (item LIKE 'P%') OR (item LIKE 'F%');

 

Exercise 8.

1.

SELECT order_date, item, price
FROM items_ordered
WHERE price BETWEEN 10.00 AND 80.00;

 

2.

SELECT firstname, city, state
FROM customers
WHERE state IN ('Arizona', 'Washington', 'Oklahoma', 'Colorado', 'Hawaii');

 

Exercise 9.

select item, sum(price)/sum(quantity)
from items_ordered
group by item;