ZCW-MesoLabs-QueryingPersons.SQL/Sql

Your MySQL connection id is 7
Server version: 5.7.22 Homebrew

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables
    -> ;
+---------------+
| Tables_in_db2 |
+---------------+
| homes         |
| movies        |
| people        |
+---------------+
3 rows in set (0.00 sec)

mysql> exit
Bye
zipcoders-MacBook-Pro-45:ZCW-Money-Converter-BlueJ randallg$ mysql -u root -p db2
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.22 Homebrew

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables
    -> ;
+---------------+
| Tables_in_db2 |
+---------------+
| homes         |
| movies        |
| people        |
+---------------+
3 rows in set (0.00 sec)

mysql> select * from people
    -> ;
+----+------------+-----------+----------+------------+---------+
| id | first_name | last_name | mobile   | birthday   | home_id |
+----+------------+-----------+----------+------------+---------+
|  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
|  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
|  3 | Raj        | Sharam    | 186-5223 | 1980-08-31 |       3 |
|  4 | Noelle     | Durand    | 395-6161 | 1960-07-06 |       1 |
|  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |
|  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
|  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
+----+------------+-----------+----------+------------+---------+
7 rows in set (0.01 sec)

mysql> UPDATE people SET mobile = '333-3333', last_name = 'Johnson'
    -> WHERE first_name = 'Noelle' OR first_name = 'Raj';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from people
    -> ;
+----+------------+-----------+----------+------------+---------+
| id | first_name | last_name | mobile   | birthday   | home_id |
+----+------------+-----------+----------+------------+---------+
|  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
|  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
|  3 | Raj        | Johnson   | 333-3333 | 1980-08-31 |       3 |
|  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 |
|  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |
|  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
|  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
+----+------------+-----------+----------+------------+---------+
7 rows in set (0.00 sec)

mysql> select sum(id), avg(id) from people;
+---------+---------+
| sum(id) | avg(id) |
+---------+---------+
|      28 |  4.0000 |
+---------+---------+
1 row in set (0.01 sec)

mysql> select cout(homenumber) from homes;
ERROR 1305 (42000): FUNCTION db2.cout does not exist
mysql> show tables
    -> ;
+---------------+
| Tables_in_db2 |
+---------------+
| homes         |
| movies        |
| people        |
+---------------+
3 rows in set (0.01 sec)

mysql> select * from homes
    -> ;
+----+----------------------------------------+------------+
| id | address                                | homenumber |
+----+----------------------------------------+------------+
|  1 | 36 E. Bayberry Rd.Savannah, GA 31404   | 565-6895   |
|  2 | 11 Essex Dr.Farmingdale, NY 11735      | 454-4544   |
|  3 | 920 Arlington Street Clifton, NJ 07011 | 985-4515   |
|  4 | 234 High Street, PA 19159              | 267-3940   |
+----+----------------------------------------+------------+
4 rows in set (0.00 sec)

mysql> select count(homenumber) from homes;
+-------------------+
| count(homenumber) |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.00 sec)

mysql> select count(*) from homes
    -> ;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.01 sec)

mysql> select count(distinct last_name) from people;
+---------------------------+
| count(distinct last_name) |
+---------------------------+
|                         4 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select * from people
    -> ;
+----+------------+-----------+----------+------------+---------+
| id | first_name | last_name | mobile   | birthday   | home_id |
+----+------------+-----------+----------+------------+---------+
|  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
|  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
|  3 | Raj        | Johnson   | 333-3333 | 1980-08-31 |       3 |
|  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 |
|  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |
|  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
|  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
+----+------------+-----------+----------+------------+---------+
7 rows in set (0.00 sec)

mysql> select sum(id) as sum, avg(id) as avg from people;
+------+--------+
| sum  | avg    |
+------+--------+
|   28 | 4.0000 |
+------+--------+
1 row in set (0.00 sec)

mysql> select sum(id), avg(id) from people;
+---------+---------+
| sum(id) | avg(id) |
+---------+---------+
|      28 |  4.0000 |
+---------+---------+
1 row in set (0.00 sec)

mysql> select min(birthday) from people;
+---------------+
| min(birthday) |
+---------------+
| 1954-12-07    |
+---------------+
1 row in set (0.01 sec)

mysql> Select upper(first_name), lower(last_name) from people;
+-------------------+------------------+
| upper(first_name) | lower(last_name) |
+-------------------+------------------+
| TONY              | smith            |
| SHEERI            | carbral          |
| RAJ               | johnson          |
| NOELLE            | johnson          |
| THOMAS            | smith            |
| JANE              | smith            |
| DOUG              | brown            |
+-------------------+------------------+
7 rows in set (0.00 sec)

mysql> select replace(last_name, 'a', '1') from people;
+------------------------------+
| replace(last_name, 'a', '1') |
+------------------------------+
| Smith                        |
| C1rbr1l                      |
| Johnson                      |
| Johnson                      |
| Smith                        |
| Smith                        |
| Brown                        |
+------------------------------+
7 rows in set (0.00 sec)

mysql> select last_name from people;
+-----------+
| last_name |
+-----------+
| Smith     |
| Carbral   |
| Johnson   |
| Johnson   |
| Smith     |
| Smith     |
| Brown     |
+-----------+
7 rows in set (0.00 sec)

mysql> select * from people;
+----+------------+-----------+----------+------------+---------+
| id | first_name | last_name | mobile   | birthday   | home_id |
+----+------------+-----------+----------+------------+---------+
|  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
|  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
|  3 | Raj        | Johnson   | 333-3333 | 1980-08-31 |       3 |
|  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 |
|  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |
|  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
|  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
+----+------------+-----------+----------+------------+---------+
7 rows in set (0.00 sec)

mysql> INSERT INTO people (first_name, last_name, mobile)
    -> VALUES ('Otto', 'Von Count', '656-6548');
Query OK, 1 row affected (0.01 sec)

mysql> select * from people;
+----+------------+-----------+----------+------------+---------+
| id | first_name | last_name | mobile   | birthday   | home_id |
+----+------------+-----------+----------+------------+---------+
|  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
|  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
|  3 | Raj        | Johnson   | 333-3333 | 1980-08-31 |       3 |
|  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 |
|  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |
|  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
|  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
| 13 | Otto       | Von Count | 656-6548 | NULL       |    NULL |
+----+------------+-----------+----------+------------+---------+
8 rows in set (0.00 sec)

mysql> SELECT CONCAT(first_name, last_name) FROM people
    -> WHERE last_name = 'Smith';
+-------------------------------+
| CONCAT(first_name, last_name) |
+-------------------------------+
| TonySmith                     |
| ThomasSmith                   |
| JaneSmith                     |
+-------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT CONCAT(first_name, ' ', last_name)
    -> FROM people
    -> WHERE last_name = 'Smith';
+------------------------------------+
| CONCAT(first_name, ' ', last_name) |
+------------------------------------+
| Tony Smith                         |
| Thomas Smith                       |
| Jane Smith                         |
+------------------------------------+
3 rows in set (0.01 sec)

mysql> SELECT CONCAT_WS(' ',first_name, last_name, mobile)
    -> FROM people WHERE last_name= 'Smith';
+----------------------------------------------+
| CONCAT_WS(' ',first_name, last_name, mobile) |
+----------------------------------------------+
| Tony Smith 152-9854                          |
| Thomas Smith 152-9854                        |
| Jane Smith 152-9854                          |
+----------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT homenumber, LEFT(homenumber, 3), RIGHT(homenumber, 2) FROM homes;
+------------+---------------------+----------------------+
| homenumber | LEFT(homenumber, 3) | RIGHT(homenumber, 2) |
+------------+---------------------+----------------------+
| 565-6895   | 565                 | 95                   |
| 454-4544   | 454                 | 44                   |
| 985-4515   | 985                 | 15                   |
| 267-3940   | 267                 | 40                   |
+------------+---------------------+----------------------+
4 rows in set (0.00 sec)

mysql> select * from homes
    -> ;
+----+----------------------------------------+------------+
| id | address                                | homenumber |
+----+----------------------------------------+------------+
|  1 | 36 E. Bayberry Rd.Savannah, GA 31404   | 565-6895   |
|  2 | 11 Essex Dr.Farmingdale, NY 11735      | 454-4544   |
|  3 | 920 Arlington Street Clifton, NJ 07011 | 985-4515   |
|  4 | 234 High Street, PA 19159              | 267-3940   |
+----+----------------------------------------+------------+
4 rows in set (0.00 sec)

mysql> SELECT LENGTH(address), CHAR_LENGTH(address) FROM homes;
+-----------------+----------------------+
| LENGTH(address) | CHAR_LENGTH(address) |
+-----------------+----------------------+
|              36 |                   36 |
|              33 |                   33 |
|              38 |                   38 |
|              26 |                   26 |
+-----------------+----------------------+
4 rows in set (0.00 sec)

mysql> SELECT first_name, last_name, YEAR(birthday) FROM people WHERE birthday >= '1970-07-06' AND
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> select * from people;
+----+------------+-----------+----------+------------+---------+
| id | first_name | last_name | mobile   | birthday   | home_id |
+----+------------+-----------+----------+------------+---------+
|  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
|  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
|  3 | Raj        | Johnson   | 333-3333 | 1980-08-31 |       3 |
|  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 |
|  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |
|  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
|  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
| 13 | Otto       | Von Count | 656-6548 | NULL       |    NULL |
+----+------------+-----------+----------+------------+---------+
8 rows in set (0.00 sec)

mysql> SELECT first_name, last_name, YEAR(birthday) FROM people WHERE birthday >= '1970-07-06' AND birthday<='1987-07-06';
+------------+-----------+----------------+
| first_name | last_name | YEAR(birthday) |
+------------+-----------+----------------+
| Tony       | Smith     |           1973 |
| Raj        | Johnson   |           1980 |
| Thomas     | Smith     |           1987 |
+------------+-----------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT first_name, birthday FROM people WHERE first_name='Thomas' OR first_name='Raj' OR first_name='Sheeri';
+------------+------------+
| first_name | birthday   |
+------------+------------+
| Sheeri     | 1970-02-23 |
| Raj        | 1980-08-31 |
| Thomas     | 1987-07-06 |
+------------+------------+
3 rows in set (0.00 sec)

mysql> SELECT first_name, birthday FROM people WHERE first_name IN ('Noelle', 'Thomas', 'Raj');
+------------+------------+
| first_name | birthday   |
+------------+------------+
| Raj        | 1980-08-31 |
| Noelle     | 1960-07-06 |
| Thomas     | 1987-07-06 |
+------------+------------+
3 rows in set (0.00 sec)

mysql> SELECT first_name FROM people WHERE RIGHT(first_name,1)='e';
+------------+
| first_name |
+------------+
| Noelle     |
| Jane       |
+------------+
2 rows in set (0.00 sec)

mysql> select * from people;
+----+------------+-----------+----------+------------+---------+
| id | first_name | last_name | mobile   | birthday   | home_id |
+----+------------+-----------+----------+------------+---------+
|  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
|  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
|  3 | Raj        | Johnson   | 333-3333 | 1980-08-31 |       3 |
|  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 |
|  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |
|  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
|  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
| 13 | Otto       | Von Count | 656-6548 | NULL       |    NULL |
+----+------------+-----------+----------+------------+---------+
8 rows in set (0.00 sec)

mysql> SELECT first_name FROM people WHERE first_name LIKE '%j';
+------------+
| first_name |
+------------+
| Raj        |
+------------+
1 row in set (0.00 sec)

mysql> SELECT first_name FROM people WHERE first_name LIKE 'j';
Empty set (0.00 sec)

mysql> SELECT first_name FROM people WHERE first_name LIKE 'J';
Empty set (0.00 sec)

mysql> SELECT first_name FROM people WHERE first_name LIKE '%J;
    '> ;
    '> ;
    '> exit
    '> SELECT first_name FROM people WHERE first_name LIKE '%o%';
    '> ';
    mysql> SELECT first_name FROM people WHERE first_name LIKE '%J';
    +------------+
    | first_name |
    +------------+
    | Raj        |
    +------------+
    1 row in set (0.00 sec)

    mysql> SELECT first_name FROM people WHERE first_name LIKE '%o%';
    +------------+
    | first_name |
    +------------+
    | Tony       |
    | Noelle     |
    | Thomas     |
    | Doug       |
    | Otto       |
    +------------+
    5 rows in set (0.00 sec)

    mysql> SELECT first_name FROM people WHERE first_name LIKE 'J%';
    +------------+
    | first_name |
    +------------+
    | Jane       |
    +------------+
    1 row in set (0.00 sec)

    mysql> SELECT first_name FROM people WHERE first_name LIKE 'J*';
    Empty set (0.00 sec)

    mysql> SELECT first_name FROM people WHERE first_name NOT LIKE '%o%';
    +------------+
    | first_name |
    +------------+
    | Sheeri     |
    | Raj        |
    | Jane       |
    +------------+
    3 rows in set (0.00 sec)

    mysql> select * from people;
    +----+------------+-----------+----------+------------+---------+
    | id | first_name | last_name | mobile   | birthday   | home_id |
    +----+------------+-----------+----------+------------+---------+
    |  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
    |  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
    |  3 | Raj        | Johnson   | 333-3333 | 1980-08-31 |       3 |
    |  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 |
    |  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |
    |  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
    |  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
    | 13 | Otto       | Von Count | 656-6548 | NULL       |    NULL |
    +----+------------+-----------+----------+------------+---------+
    8 rows in set (0.00 sec)

    mysql> SELECT COUNT(*) FROM people;
    +----------+
    | COUNT(*) |
    +----------+
    |        8 |
    +----------+
    1 row in set (0.00 sec)

    mysql> SELECT last_name, COUNT(*) FROM people GROUP BY last_name;
    +-----------+----------+
    | last_name | COUNT(*) |
    +-----------+----------+
    | Brown     |        1 |
    | Carbral   |        1 |
    | Johnson   |        2 |
    | Smith     |        3 |
    | Von Count |        1 |
    +-----------+----------+
    5 rows in set (0.01 sec)

    mysql> SELECT last_name, GROUP_CONCAT(mobile) FROM people GROUP BY last_name;
    +-----------+----------------------------+
    | last_name | GROUP_CONCAT(mobile)       |
    +-----------+----------------------------+
    | Brown     | 466-6241                   |
    | Carbral   | 230-4233                   |
    | Johnson   | 333-3333,333-3333          |
    | Smith     | 152-9854,152-9854,152-9854 |
    | Von Count | 656-6548                   |
    +-----------+----------------------------+
    5 rows in set (0.00 sec)

    mysql> SELECT last_name, GROUP_CONCAT(mobile SEPARATOR ' and ') FROM people GROUP BY last_name;
    +-----------+----------------------------------------+
    | last_name | GROUP_CONCAT(mobile SEPARATOR ' and ') |
    +-----------+----------------------------------------+
    | Brown     | 466-6241                               |
    | Carbral   | 230-4233                               |
    | Johnson   | 333-3333 and 333-3333                  |
    | Smith     | 152-9854 and 152-9854 and 152-9854     |
    | Von Count | 656-6548                               |
    +-----------+----------------------------------------+
    5 rows in set (0.00 sec)

    mysql> SELECT last_name, GROUP_CONCAT(mobile SEPARATOR ' and ') FROM people GROUP BY last_name  HAVING COUNT(*)>1;
    +-----------+----------------------------------------+
    | last_name | GROUP_CONCAT(mobile SEPARATOR ' and ') |
    +-----------+----------------------------------------+
    | Johnson   | 333-3333 and 333-3333                  |
    | Smith     | 152-9854 and 152-9854 and 152-9854     |
    +-----------+----------------------------------------+
    2 rows in set (0.00 sec)

    mysql> SELECT last_name, GROUP_CONCAT(mobile SEPARATOR ' and ') FROM people WHERE last_name != 'Cabral' GROUP BY last_name  HAVING COUNT(*)>1;
    +-----------+----------------------------------------+
    | last_name | GROUP_CONCAT(mobile SEPARATOR ' and ') |
    +-----------+----------------------------------------+
    | Johnson   | 333-3333 and 333-3333                  |
    | Smith     | 152-9854 and 152-9854 and 152-9854     |
    +-----------+----------------------------------------+
    2 rows in set (0.00 sec)

    mysql> SELECT first_name, birthday FROM people ORDER BY birthday;
    +------------+------------+
    | first_name | birthday   |
    +------------+------------+
    | Otto       | NULL       |
    | Doug       | 1954-12-07 |
    | Noelle     | 1960-07-06 |
    | Sheeri     | 1970-02-23 |
    | Tony       | 1973-01-23 |
    | Raj        | 1980-08-31 |
    | Thomas     | 1987-07-06 |
    | Jane       | 1987-12-06 |
    +------------+------------+
    8 rows in set (0.00 sec)

    mysql> SELECT first_name, birthday FROM people ORDER BY birthday DESC;
    +------------+------------+
    | first_name | birthday   |
    +------------+------------+
    | Jane       | 1987-12-06 |
    | Thomas     | 1987-07-06 |
    | Raj        | 1980-08-31 |
    | Tony       | 1973-01-23 |
    | Sheeri     | 1970-02-23 |
    | Noelle     | 1960-07-06 |
    | Doug       | 1954-12-07 |
    | Otto       | NULL       |
    +------------+------------+
    8 rows in set (0.00 sec)

    mysql> SELECT first_name, last_name FROM people ORDER BY last_name, first_name;
    +------------+-----------+
    | first_name | last_name |
    +------------+-----------+
    | Doug       | Brown     |
    | Sheeri     | Carbral   |
    | Noelle     | Johnson   |
    | Raj        | Johnson   |
    | Jane       | Smith     |
    | Thomas     | Smith     |
    | Tony       | Smith     |
    | Otto       | Von Count |
    +------------+-----------+
    8 rows in set (0.00 sec)

    mysql> SELECT first_name, birthday FROM people ORDER BY birthday DESC LIMIT 3;
    +------------+------------+
    | first_name | birthday   |
    +------------+------------+
    | Jane       | 1987-12-06 |
    | Thomas     | 1987-07-06 |
    | Raj        | 1980-08-31 |
    +------------+------------+
    3 rows in set (0.00 sec)

    mysql> SELECT first_name, MONTHNAME(birthday) as mon, birthday FROM people ORDER BY MONTH(birthday);
    +------------+----------+------------+
    | first_name | mon      | birthday   |
    +------------+----------+------------+
    | Otto       | NULL     | NULL       |
    | Tony       | January  | 1973-01-23 |
    | Sheeri     | February | 1970-02-23 |
    | Noelle     | July     | 1960-07-06 |
    | Thomas     | July     | 1987-07-06 |
    | Raj        | August   | 1980-08-31 |
    | Jane       | December | 1987-12-06 |
    | Doug       | December | 1954-12-07 |
    +------------+----------+------------+
    8 rows in set (0.01 sec)

    mysql> SELECT last_name, COUNT(*) FROM  people GROUP BY last_name;
    +-----------+----------+
    | last_name | COUNT(*) |
    +-----------+----------+
    | Brown     |        1 |
    | Carbral   |        1 |
    | Johnson   |        2 |
    | Smith     |        3 |
    | Von Count |        1 |
    +-----------+----------+
    5 rows in set (0.00 sec)

    mysql> SELECT last_name, COUNT(*) FROM  people GROUP BY last_name ORDER BY NULL;
    +-----------+----------+
    | last_name | COUNT(*) |
    +-----------+----------+
    | Smith     |        3 |
    | Carbral   |        1 |
    | Johnson   |        2 |
    | Brown     |        1 |
    | Von Count |        1 |
    +-----------+----------+
    5 rows in set (0.00 sec)

    mysql> INSERT INTO people (first_name, last_name, birthday, home_id)
        -> VALUES ('John', 'Smith', '1998-04-07', 4),
        -> ('Maya', 'Wasserman' , NULL, 4),
        -> ('Paul', 'Thompson', '1996-05-27', 1);
    Query OK, 3 rows affected (0.01 sec)
    Records: 3  Duplicates: 0  Warnings: 0

    mysql> select * from people;
    +----+------------+-----------+----------+------------+---------+
    | id | first_name | last_name | mobile   | birthday   | home_id |
    +----+------------+-----------+----------+------------+---------+
    |  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
    |  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
    |  3 | Raj        | Johnson   | 333-3333 | 1980-08-31 |       3 |
    |  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 |
    |  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |
    |  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
    |  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
    | 13 | Otto       | Von Count | 656-6548 | NULL       |    NULL |
    | 14 | John       | Smith     | NULL     | 1998-04-07 |       4 |
    | 15 | Maya       | Wasserman | NULL     | NULL       |       4 |
    | 16 | Paul       | Thompson  | NULL     | 1996-05-27 |       1 |
    +----+------------+-----------+----------+------------+---------+
    11 rows in set (0.00 sec)

    mysql> DELETE FROM people WHERE first_name='Maya';
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from people;
    +----+------------+-----------+----------+------------+---------+
    | id | first_name | last_name | mobile   | birthday   | home_id |
    +----+------------+-----------+----------+------------+---------+
    |  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
    |  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
    |  3 | Raj        | Johnson   | 333-3333 | 1980-08-31 |       3 |
    |  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 |
    |  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |
    |  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
    |  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
    | 13 | Otto       | Von Count | 656-6548 | NULL       |    NULL |
    | 14 | John       | Smith     | NULL     | 1998-04-07 |       4 |
    | 16 | Paul       | Thompson  | NULL     | 1996-05-27 |       1 |
    +----+------------+-----------+----------+------------+---------+
    10 rows in set (0.00 sec)

    mysql> INSERT INTO people (first_name, last_name, birthday)
        -> VALUES ('Eli', 'Kramer', '1984-01-15');
    Query OK, 1 row affected (0.01 sec)

    mysql> select * from people;
    +----+------------+-----------+----------+------------+---------+
    | id | first_name | last_name | mobile   | birthday   | home_id |
    +----+------------+-----------+----------+------------+---------+
    |  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
    |  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
    |  3 | Raj        | Johnson   | 333-3333 | 1980-08-31 |       3 |
    |  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 |
    |  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |
    |  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
    |  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
    | 13 | Otto       | Von Count | 656-6548 | NULL       |    NULL |
    | 14 | John       | Smith     | NULL     | 1998-04-07 |       4 |
    | 16 | Paul       | Thompson  | NULL     | 1996-05-27 |       1 |
    | 17 | Eli        | Kramer    | NULL     | 1984-01-15 |    NULL |
    +----+------------+-----------+----------+------------+---------+
    11 rows in set (0.00 sec)

    mysql> select * from homes;
    +----+----------------------------------------+------------+
    | id | address                                | homenumber |
    +----+----------------------------------------+------------+
    |  1 | 36 E. Bayberry Rd.Savannah, GA 31404   | 565-6895   |
    |  2 | 11 Essex Dr.Farmingdale, NY 11735      | 454-4544   |
    |  3 | 920 Arlington Street Clifton, NJ 07011 | 985-4515   |
    |  4 | 234 High Street, PA 19159              | 267-3940   |
    +----+----------------------------------------+------------+
    4 rows in set (0.01 sec)

    mysql> SELECT p.first_name, h.address
        -> FROM people p
        -> INNER JOIN homes h on (p.home_id = h.id);
    +------------+----------------------------------------+
    | first_name | address                                |
    +------------+----------------------------------------+
    | Noelle     | 36 E. Bayberry Rd.Savannah, GA 31404   |
    | Thomas     | 36 E. Bayberry Rd.Savannah, GA 31404   |
    | Paul       | 36 E. Bayberry Rd.Savannah, GA 31404   |
    | Sheeri     | 11 Essex Dr.Farmingdale, NY 11735      |
    | Raj        | 920 Arlington Street Clifton, NJ 07011 |
    | Jane       | 920 Arlington Street Clifton, NJ 07011 |
    | Doug       | 920 Arlington Street Clifton, NJ 07011 |
    | John       | 234 High Street, PA 19159              |
    +------------+----------------------------------------+
    8 rows in set (0.01 sec)

    mysql> SELECT first_name, last_name
        -> FROM people p
        -> INNER JOIN homes h on (p.home_id = h.id)
        -> WHERE p.HOME_ID = 1;
    +------------+-----------+
    | first_name | last_name |
    +------------+-----------+
    | Noelle     | Johnson   |
    | Thomas     | Smith     |
    | Paul       | Thompson  |
    +------------+-----------+
    3 rows in set (0.01 sec)

    mysql> SELECT p.*, h.address, h.homenumber
        -> FROM people p
        -> INNER JOIN homes h  on (p.home_id = h.id)
        -> WHERE p.first_name  LIKE '%e%';
    +----+------------+-----------+----------+------------+---------+----------------------------------------+------------+
    | id | first_name | last_name | mobile   | birthday   | home_id | address                                | homenumber |
    +----+------------+-----------+----------+------------+---------+----------------------------------------+------------+
    |  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 | 11 Essex Dr.Farmingdale, NY 11735      | 454-4544   |
    |  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 | 36 E. Bayberry Rd.Savannah, GA 31404   | 565-6895   |
    |  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 | 920 Arlington Street Clifton, NJ 07011 | 985-4515   |
    +----+------------+-----------+----------+------------+---------+----------------------------------------+------------+
    3 rows in set (0.00 sec)

    mysql> select * from people;
    +----+------------+-----------+----------+------------+---------+
    | id | first_name | last_name | mobile   | birthday   | home_id |
    +----+------------+-----------+----------+------------+---------+
    |  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
    |  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
    |  3 | Raj        | Johnson   | 333-3333 | 1980-08-31 |       3 |
    |  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 |
    |  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |
    |  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
    |  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
    | 13 | Otto       | Von Count | 656-6548 | NULL       |    NULL |
    | 14 | John       | Smith     | NULL     | 1998-04-07 |       4 |
    | 16 | Paul       | Thompson  | NULL     | 1996-05-27 |       1 |
    | 17 | Eli        | Kramer    | NULL     | 1984-01-15 |    NULL |
    +----+------------+-----------+----------+------------+---------+
    11 rows in set (0.00 sec)

    mysql> select * from people
        -> ;
    +----+------------+-----------+----------+------------+---------+
    | id | first_name | last_name | mobile   | birthday   | home_id |
    +----+------------+-----------+----------+------------+---------+
    |  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
    |  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
    |  3 | Raj        | Johnson   | 333-3333 | 1980-08-31 |       3 |
    |  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 |
    |  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |
    |  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
    |  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
    | 13 | Otto       | Von Count | 656-6548 | NULL       |    NULL |
    | 14 | John       | Smith     | NULL     | 1998-04-07 |       4 |
    | 16 | Paul       | Thompson  | NULL     | 1996-05-27 |       1 |
    | 17 | Eli        | Kramer    | NULL     | 1984-01-15 |    NULL |
    +----+------------+-----------+----------+------------+---------+
    11 rows in set (0.00 sec)

    mysql> select * from people where birthday is not null;
+----+------------+-----------+----------+------------+---------+
| id | first_name | last_name | mobile   | birthday   | home_id |
+----+------------+-----------+----------+------------+---------+
|  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
|  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
|  3 | Raj        | Johnson   | 333-3333 | 1980-08-31 |       3 |
|  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 |
|  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |
|  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
|  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
| 14 | John       | Smith     | NULL     | 1998-04-07 |       4 |
| 16 | Paul       | Thompson  | NULL     | 1996-05-27 |       1 |
| 17 | Eli        | Kramer    | NULL     | 1984-01-15 |    NULL |
+----+------------+-----------+----------+------------+---------+
10 rows in set (0.00 sec)

mysql> SELECT first_name, MONTHNAME(birthday) as mon, birthday FROM people ORDER BY MONTH(birthday);
+------------+----------+------------+
| first_name | mon      | birthday   |
+------------+----------+------------+
| Otto       | NULL     | NULL       |
| Tony       | January  | 1973-01-23 |
| Eli        | January  | 1984-01-15 |
| Sheeri     | February | 1970-02-23 |
| John       | April    | 1998-04-07 |
| Paul       | May      | 1996-05-27 |
| Noelle     | July     | 1960-07-06 |
| Thomas     | July     | 1987-07-06 |
| Raj        | August   | 1980-08-31 |
| Jane       | December | 1987-12-06 |
| Doug       | December | 1954-12-07 |
+------------+----------+------------+
11 rows in set (0.00 sec)

mysql> SELECT first_name, birthday FROM people ORDER BY birthday DESC;
+------------+------------+
| first_name | birthday   |
+------------+------------+
| John       | 1998-04-07 |
| Paul       | 1996-05-27 |
| Jane       | 1987-12-06 |
| Thomas     | 1987-07-06 |
| Eli        | 1984-01-15 |
| Raj        | 1980-08-31 |
| Tony       | 1973-01-23 |
| Sheeri     | 1970-02-23 |
| Noelle     | 1960-07-06 |
| Doug       | 1954-12-07 |
| Otto       | NULL       |
+------------+------------+
11 rows in set (0.00 sec)

mysql> SELECT p.first_name, h.address
    -> FROM people p
    -> INNER JOIN homes h on (p.home_id = h.id);
+------------+----------------------------------------+
| first_name | address                                |
+------------+----------------------------------------+
| Noelle     | 36 E. Bayberry Rd.Savannah, GA 31404   |
| Thomas     | 36 E. Bayberry Rd.Savannah, GA 31404   |
| Paul       | 36 E. Bayberry Rd.Savannah, GA 31404   |
| Sheeri     | 11 Essex Dr.Farmingdale, NY 11735      |
| Raj        | 920 Arlington Street Clifton, NJ 07011 |
| Jane       | 920 Arlington Street Clifton, NJ 07011 |
| Doug       | 920 Arlington Street Clifton, NJ 07011 |
| John       | 234 High Street, PA 19159              |
+------------+----------------------------------------+
8 rows in set (0.00 sec)

Excercise

mysql> select * from people where birthday is not null;
+----+------------+-----------+----------+------------+---------+
| id | first_name | last_name | mobile   | birthday   | home_id |
+----+------------+-----------+----------+------------+---------+
|  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
|  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
|  3 | Raj        | Johnson   | 333-3333 | 1980-08-31 |       3 |
|  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 |
|  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |
|  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
|  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
| 14 | John       | Smith     | NULL     | 1998-04-07 |       4 |
| 16 | Paul       | Thompson  | NULL     | 1996-05-27 |       1 |
| 17 | Eli        | Kramer    | NULL     | 1984-01-15 |    NULL |
+----+------------+-----------+----------+------------+---------+
10 rows in set (0.00 sec)


mysql> SELECT p.first_name, p.last_name,monthname(birthday) as mon,h.address from people p inner join homes h on(p.home_id =h.id)where birthday order by month(birthday);
+------------+-----------+----------+----------------------------------------+
| first_name | last_name | mon      | address                                |
+------------+-----------+----------+----------------------------------------+
| Sheeri     | Carbral   | February | 11 Essex Dr.Farmingdale, NY 11735      |
| John       | Smith     | April    | 234 High Street, PA 19159              |
| Paul       | Thompson  | May      | 36 E. Bayberry Rd.Savannah, GA 31404   |
| Noelle     | Johnson   | July     | 36 E. Bayberry Rd.Savannah, GA 31404   |
| Thomas     | Smith     | July     | 36 E. Bayberry Rd.Savannah, GA 31404   |
| Raj        | Johnson   | August   | 920 Arlington Street Clifton, NJ 07011 |
| Jane       | Smith     | December | 920 Arlington Street Clifton, NJ 07011 |
| Doug       | Brown     | December | 920 Arlington Street Clifton, NJ 07011 |
+------------+-----------+----------+----------------------------------------+
8 rows in set (0.00 sec)

mysql> select * from people left join homes on (people.home_id =homes.id);
+----+------------+-----------+----------+------------+---------+------+----------------------------------------+------------+
| id | first_name | last_name | mobile   | birthday   | home_id | id   | address                                | homenumber |
+----+------------+-----------+----------+------------+---------+------+----------------------------------------+------------+
|  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 |    1 | 36 E. Bayberry Rd.Savannah, GA 31404   | 565-6895   |
|  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |    1 | 36 E. Bayberry Rd.Savannah, GA 31404   | 565-6895   |
| 16 | Paul       | Thompson  | NULL     | 1996-05-27 |       1 |    1 | 36 E. Bayberry Rd.Savannah, GA 31404   | 565-6895   |
|  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |    2 | 11 Essex Dr.Farmingdale, NY 11735      | 454-4544   |
|  3 | Raj        | Johnson   | 333-3333 | 1980-08-31 |       3 |    3 | 920 Arlington Street Clifton, NJ 07011 | 985-4515   |
|  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |    3 | 920 Arlington Street Clifton, NJ 07011 | 985-4515   |
|  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |    3 | 920 Arlington Street Clifton, NJ 07011 | 985-4515   |
| 14 | John       | Smith     | NULL     | 1998-04-07 |       4 |    4 | 234 High Street, PA 19159              | 267-3940   |
|  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL | NULL | NULL                                   | NULL       |
| 13 | Otto       | Von Count | 656-6548 | NULL       |    NULL | NULL | NULL                                   | NULL       |
| 17 | Eli        | Kramer    | NULL     | 1984-01-15 |    NULL | NULL | NULL                                   | NULL       |
+----+------------+-----------+----------+------------+---------+------+----------------------------------------+------------+
11 rows in set (0.00 sec)

Mini Movies

+----+--------------------+---------+-------------+------------+--------+
| id | title              | runtime | genre       | imdb_score | rating |
+----+--------------------+---------+-------------+------------+--------+
|  1 | Howard the duck    |     110 | Sci-Fi      |        4.6 | PG     |
|  2 | Lavalantula        |      83 | Horror      |        4.7 | TV-14  |
|  3 | Star Ship Troopers |     129 | Sci-Fi      |        7.2 | PG-13  |
|  4 | Waltz With Bashir  |      90 | Documentary |        8.0 | R      |
|  5 | Spaceballs         |      96 | Comedy      |        7.1 | PG     |
|  6 | Monster Inc.       |      92 | Animation   |        8.1 | G      |
+----+--------------------+---------+-------------+------------+--------+

mysql> select * from movies where genre = 'sci-fi';
+----+--------------------+---------+--------+------------+--------+
| id | title              | runtime | genre  | imdb_score | rating |
+----+--------------------+---------+--------+------------+--------+
|  1 | Howard the duck    |     110 | Sci-Fi |        4.6 | PG     |
|  3 | Star Ship Troopers |     129 | Sci-Fi |        7.2 | PG-13  |
+----+--------------------+---------+--------+------------+--------+

mysql> select * from movies where imdb_score >='6.5';
+----+--------------------+---------+-------------+------------+--------+
| id | title              | runtime | genre       | imdb_score | rating |
+----+--------------------+---------+-------------+------------+--------+
|  3 | Star Ship Troopers |     129 | Sci-Fi      |        7.2 | PG-13  |
|  4 | Waltz With Bashir  |      90 | Documentary |        8.0 | R      |
|  5 | Spaceballs         |      96 | Comedy      |        7.1 | PG     |
|  6 | Monster Inc.       |      92 | Animation   |        8.1 | G      |
+----+--------------------+---------+-------------+------------+--------+

mysql> select title from movies where rating ='PG' or rating = 'G' and runtime < 100;
+-----------------+
| title           |
| Spaceballs      |
| Monster Inc.    |
+-----------------+

mysql> select genre,avg(runtime) from movies where imdb_Score < 7.5 group by genre;
+--------+--------------+
| genre  | avg(runtime) |
+--------+--------------+
| Comedy |      96.0000 |
| Horror |      83.0000 |
| Sci-Fi |     119.5000 |
+--------+--------------+
3 rows in set (0.00 sec)

mysql> update movies set rating ='R' where title = 'Star Ship Troopers';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from movies;
+----+--------------------+---------+-------------+------------+--------+
| id | title              | runtime | genre       | imdb_score | rating |
+----+--------------------+---------+-------------+------------+--------+
|  1 | Howard the duck    |     110 | Sci-Fi      |        4.6 | PG     |
|  2 | Lavalantula        |      83 | Horror      |        4.7 | TV-14  |
|  3 | Star Ship Troopers |     129 | Sci-Fi      |        7.2 | R      |
|  4 | Waltz With Bashir  |      90 | Documentary |        8.0 | R      |
|  5 | Spaceballs         |      96 | Comedy      |        7.1 | PG     |
|  6 | Monster Inc.       |      92 | Animation   |        8.1 | G      |
+----+--------------------+---------+-------------+------------+--------+
6 rows in set (0.00 sec)

mysql> select id,rating from movies where genre = 'horror' or  genre = 'documentary';
+----+--------+
| id | rating |
+----+--------+
|  2 | TV-14  |
|  4 | R      |
+----+--------+
2 rows in set (0.00 sec)

mysql> select max(imdb_score) from movies where rating = 'pg';
+-----------------+
| max(imdb_score) |
+-----------------+
|             7.1 |
+-----------------+
1 row in set (0.00 sec)

mysql> select max(imdb_score) from movies where rating = 'g';
+-----------------+
| max(imdb_score) |
+-----------------+
|             8.1 |
+-----------------+
mysql> select max(imdb_score) from movies where rating = 'tv-14';
+-----------------+
| max(imdb_score) |
+-----------------+
|             4.7 |
+-----------------+
1 row in set (0.00 sec)

mysql> select max(imdb_score) from movies where rating = 'r';
+-----------------+
| max(imdb_score) |
+-----------------+
|             8.0 |
+-----------------+


mysql> select min(imdb_score) from movies where rating = 'pg';
+-----------------+
| min(imdb_score) |
+-----------------+
|             4.6 |
+-----------------+
1 row in set (0.00 sec)

mysql> select min(imdb_score) from movies where rating = 'tv-14';
+-----------------+
| min(imdb_score) |
+-----------------+
|             4.7 |
+-----------------+
1 row in set (0.00 sec)

mysql> select min(imdb_score) from movies where rating = 'r';
+-----------------+
| min(imdb_score) |
+-----------------+
|             7.2 |
+-----------------+
1 row in set (0.00 sec)

mysql> select min(imdb_score) from movies where rating = 'g';
+-----------------+
| min(imdb_score) |
+-----------------+
|             8.1 |
+-----------------+
1 row in set (0.01 sec)


mysql> select avg(imdb_score) from movies where rating = 'pg';
+-----------------+
| avg(imdb_score) |
+-----------------+
|         5.85000 |
+-----------------+
1 row in set (0.00 sec)

mysql> select avg(imdb_score) from movies where rating = 'tv-14';
+-----------------+
| avg(imdb_score) |
+-----------------+
|         4.70000 |
+-----------------+
1 row in set (0.00 sec)

mysql> select avg(imdb_score) from movies where rating = 'r';
+-----------------+
| avg(imdb_score) |
+-----------------+
|         7.60000 |
+-----------------+
1 row in set (0.00 sec)

mysql> select avg(imdb_score) from movies where rating = 'g';
+-----------------+
| avg(imdb_score) |
+-----------------+
|         8.10000 |
+-----------------+
1 row in set (0.00 sec)

mysql> select rating from movies group by rating having count(*) > 1;;
+--------+
| rating |
+--------+
| PG     |
| R      |
+--------+
2 rows in set (0.00 sec)

mysql> delete from movies where rating ='R';
Query OK, 2 rows affected (0.01 sec)

mysql> select * from movies;
+----+-----------------+---------+-----------+------------+--------+
| id | title           | runtime | genre     | imdb_score | rating |
+----+-----------------+---------+-----------+------------+--------+
|  1 | Howard the duck |     110 | Sci-Fi    |        4.6 | PG     |
|  2 | Lavalantula     |      83 | Horror    |        4.7 | TV-14  |
|  5 | Spaceballs      |      96 | Comedy    |        7.1 | PG     |
|  6 | Monster Inc.    |      92 | Animation |        8.1 | G      |
+----+-----------------+---------+-----------+------------+--------+
4 rows in set (0.00 sec)