ZCW-MesoLabs-QueryingPersons.SQL/src:main:resources:script.sql

zipcoders-MacBook-Pro:Labs ryans$ cd ..
zipcoders-MacBook-Pro:~ ryans$ mysql.server --start
Usage: mysql.server  {start|stop|restart|reload|force-reload|status}  [ MySQL server options ]
zipcoders-MacBook-Pro:~ ryans$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
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> DROP TABLE IF EXISTS people;
ERROR 1046 (3D000): No database selected
mysql>
mysql> CREATE TABLE people (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   first_name VARCHAR(255) NOT NULL DEFAULT '',
    ->   last_name VARCHAR(255) NOT NULL DEFAULT '',
    ->   mobile VARCHAR(20),
    ->   birthday DATE DEFAULT NULL,
    ->   home_id INT DEFAULT NULL,
    ->   PRIMARY KEY (id));
ERROR 1046 (3D000): No database selected
mysql>
mysql>
mysql> DROP TABLE IF EXISTS homes;
ERROR 1046 (3D000): No database selected
mysql>
mysql> CREATE TABLE homes (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   address VARCHAR(255) NOT NULL DEFAULT '',
    ->   homenumber VARCHAR(255) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (id)
    -> );
ERROR 1046 (3D000): No database selected
mysql>
mysql> DROP TABLE IF EXISTS movies;
ERROR 1046 (3D000): No database selected
mysql>
mysql> CREATE TABLE movies (
    ->   id INT PRIMARY KEY AUTO_INCREMENT,
    ->   title VARCHAR(100) NOT NULL UNIQUE,
    ->   runtime SMALLINT NOT NULL,
    ->   genre VARCHAR(50),
    ->   imdb_score DECIMAL(10,1),
    ->   rating VARCHAR(10)
    -> );
ERROR 1046 (3D000): No database selected
mysql>
mysql>   ALTER TABLE people
    ->   ADD FOREIGN KEY (home_id)
    ->   REFERENCES homes(id);
ERROR 1046 (3D000): No database selected
mysql>
mysql>
mysql> create database database;
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 'database' at line 1
mysql> mysqul -u root -p database <src/main/resources/schema-h2.sql>;
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 'mysqul -u root -p database <src/main/resources/schema-h2.sql>' at line 1
mysql> mysqul -u root -p database <src/main/resources/schema-h2.sql;>
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 'mysqul -u root -p database <src/main/resources/schema-h2.sql' at line 1
    -> mysql -u root -p database <src/main/resources/schema-h2.sql>
    -> mysql -u root -p database <src/main/resources/schema-h2.sql;>
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 '>
mysql -u root -p database <src/main/resources/schema-h2.sql>
mysql -u root -p ' at line 1
    -> > mysql -u root -p database <src/main/resources/schema-h2.sql> mysql -u root -p database <src/main/resources/schema-h2.sql>
    -> create
    -> q
    -> /q
    -> \q
Bye
zipcoders-MacBook-Pro:~ ryans$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
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> > mysql -u root -p database <src/main/resources/schema-h2.sql>
    -> q\
    -> \q
Bye
zipcoders-MacBook-Pro:~ ryans$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
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>  create database dLab;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dLab               |
| mysql              |
| orm_lab            |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.02 sec)

mysql> mysql -u root -p dLab <src/main/resources/schema-h2.sql
    ->
    -> mysql -u root -p dLab <src/main/resources/schema-h2.sql>;
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 'mysql -u root -p dLab <src/main/resources/schema-h2.sql

mysql -u root -p dLab <' at line 1
mysql> mysql -u root -p dLab <src/main/resources/schema-h2.sql>
    ->
    -> show databases;
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 'mysql -u root -p dLab <src/main/resources/schema-h2.sql>

show databases' at line 1
mysql> use dLab;
Database changed
mysql> show tables
    -> show tables;
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 'show tables' at line 2
mysql> show tables;
Empty set (0.01 sec)

mysql> src/main/resources/schema-h2.sql
    ->
    -> DROP TABLE IF EXISTS people;
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 'src/main/resources/schema-h2.sql

DROP TABLE IF EXISTS people' at line 1
mysql>
mysql> CREATE TABLE people (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   first_name VARCHAR(255) NOT NULL DEFAULT '',
    ->   last_name VARCHAR(255) NOT NULL DEFAULT '',
    ->   mobile VARCHAR(20),
    ->   birthday DATE DEFAULT NULL,
    ->   home_id INT DEFAULT NULL,
    ->   PRIMARY KEY (id));
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql>
mysql> DROP TABLE IF EXISTS homes;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TABLE homes (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   address VARCHAR(255) NOT NULL DEFAULT '',
    ->   homenumber VARCHAR(255) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> DROP TABLE IF EXISTS movies;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TABLE movies (
    ->   id INT PRIMARY KEY AUTO_INCREMENT,
    ->   title VARCHAR(100) NOT NULL UNIQUE,
    ->   runtime SMALLINT NOT NULL,
    ->   genre VARCHAR(50),
    ->   imdb_score DECIMAL(10,1),
    ->   rating VARCHAR(10)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>   ALTER TABLE people
    ->   ADD FOREIGN KEY (home_id)
    ->   REFERENCES homes(id);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> INSERT INTO people (last_name, first_name, mobile, birthday)
    -> VALUES ('Smith', 'John', '230-4293', '1973-01-23');
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> INSERT INTO homes (address, homenumber) VALUES ('36 E. Bayberry Rd.Savannah, GA 31404', '565-6895');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO homes (address, homenumber) VALUES ('11 Essex Dr.Farmingdale, NY 11735', '454-4544');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO homes (address, homenumber) VALUES ('920 Arlington Street Clifton, NJ 07011', '985-4515');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO homes (address, homenumber) VALUES ('234 High Street, PA 19159 ', '267-3940');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql>
mysql> INSERT INTO people (last_name, first_name, mobile, birthday, home_id)
    -> VALUES ('Carbral', 'Sheeri', '230-4233', '1970-02-23', 2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO people (last_name, first_name, mobile, birthday, home_id)
    -> VALUES ('Sharam', 'Raj', '186-5223', '1980-08-31', 3);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO people (last_name, first_name, mobile, birthday, home_id)
    -> VALUES ('Durand', 'Noelle', '395-6161', '1960-07-06', 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO people (last_name, first_name, mobile, birthday, home_id)
    -> VALUES ('Smith', 'Thomas', '395-6181', '1987-07-06', 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO people (last_name, first_name, mobile, birthday, home_id)
    -> VALUES ('Smith', 'Jane', '393-6181', '1987-12-06', 3);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO people (last_name, first_name, mobile, birthday, home_id)
    -> VALUES ('Brown', 'Doug', '466-6241', '1954-12-07', 3);
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from people;
+----+------------+-----------+----------+------------+---------+
| id | first_name | last_name | mobile   | birthday   | home_id |
+----+------------+-----------+----------+------------+---------+
|  1 | John       | Smith     | 230-4293 | 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     | 395-6181 | 1987-07-06 |       1 |
|  6 | Jane       | Smith     | 393-6181 | 1987-12-06 |       3 |
|  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
+----+------------+-----------+----------+------------+---------+
7 rows in set (0.01 sec)

mysql> UPDATE people SET first_name = 'Tony' WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE people SET mobile = '152-9854' WHERE last_name = 'Smith';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> UPDATE people SET birthday = '1955-01-25'
    -> WHERE last_name = 'Smith'
    -> AND id = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

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>
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 COUNT(homenumber) FROM homes;
+-------------------+
| COUNT(homenumber) |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.02 sec)

mysql> SELECT homenumber FROM homes WHERE id = 1;
+------------+
| homenumber |
+------------+
| 565-6895   |
+------------+
1 row in set (0.01 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.01 sec)

mysql> show tables home;
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 'home' at line 1
mysql> show home;
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 'home' at line 1
mysql> select * from homes
    -> select * from homes;
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 'select * from homes' at line 2
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  SUM(id), AVG(id) FROM people;
+---------+---------+
| SUM(id) | AVG(id) |
+---------+---------+
|      28 |  4.0000 |
+---------+---------+
1 row in set (0.01 sec)

mysql> SELECT SUM(id) AS sum, AVG(id) AS avg FROM people;
+------+--------+
| sum  | avg    |
+------+--------+
|   28 | 4.0000 |
+------+--------+
1 row in set (0.01 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.01 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.01 sec)

mysql> SELECT last_name FROM people;
+-----------+
| last_name |
+-----------+
| Smith     |
| Carbral   |
| Johnson   |
| Johnson   |
| Smith     |
| Smith     |
| Brown     |
+-----------+
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 CONCAT(first_name, last_name) FROM people
    -> WHERE last_name = 'Smith';
+-------------------------------+
| CONCAT(first_name, last_name) |
+-------------------------------+
| TonySmith                     |
| ThomasSmith                   |
| JaneSmith                     |
+-------------------------------+
3 rows in set (0.01 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 homenumber, LEFT(homenumber, 3), RIGHT(homenumber, 1) FROM homes; +------------+---------------------+----------------------+
| homenumber | LEFT(homenumber, 3) | RIGHT(homenumber, 1) |
+------------+---------------------+----------------------+
| 565-6895   | 565                 | 5                    |
| 454-4544   | 454                 | 4                    |
| 985-4515   | 985                 | 5                    |
| 267-3940   | 267                 | 0                    |
+------------+---------------------+----------------------+
4 rows in set (0.00 sec)

mysql> SELECT homenumber, LEFT(homenumber, 3), lefT(homenumber, 1) FROM homes;
+------------+---------------------+---------------------+
| homenumber | LEFT(homenumber, 3) | lefT(homenumber, 1) |
+------------+---------------------+---------------------+
| 565-6895   | 565                 | 5                   |
| 454-4544   | 454                 | 4                   |
| 985-4515   | 985                 | 9                   |
| 267-3940   | 267                 | 2                   |
+------------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql> SELECT homenumber, LEFT(homenumber, 3), lefT(homenumber, 3) FROM homes;
+------------+---------------------+---------------------+
| homenumber | LEFT(homenumber, 3) | lefT(homenumber, 3) |
+------------+---------------------+---------------------+
| 565-6895   | 565                 | 565                 |
| 454-4544   | 454                 | 454                 |
| 985-4515   | 985                 | 985                 |
| 267-3940   | 267                 | 267                 |
+------------+---------------------+---------------------+
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.01 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.01 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.01 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>
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 |
|  8 | 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.01 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 NOT LIKE '%o%';
+------------+
| first_name |
+------------+
| Sheeri     |
| Raj        |
| Jane       |
+------------+
3 rows in set (0.01 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 last_name, GROUP_CONCAT(mobile SEPARATOR ' and ') FROM people WHERE last_name != 'johnson' GROUP BY last_name  HAVING COUNT(*)>1;
+-----------+----------------------------------------+
| last_name | GROUP_CONCAT(mobile SEPARATOR ' and ') |
+-----------+----------------------------------------+
| Smith     | 152-9854 and 152-9854 and 152-9854     |
+-----------+----------------------------------------+
1 row 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.01 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> 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 |
|  8 | Otto       | Von Count | 656-6548 | NULL       |    NULL |
+----+------------+-----------+----------+------------+---------+
8 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.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DELETE FROM people WHERE first_name='Maya';
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 |
|  8 | Otto       | Von Count | 656-6548 | NULL       |    NULL |
|  9 | John       | Smith     | NULL     | 1998-04-07 |       4 |
| 11 | 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 |
|  8 | Otto       | Von Count | 656-6548 | NULL       |    NULL |
|  9 | John       | Smith     | NULL     | 1998-04-07 |       4 |
| 11 | Paul       | Thompson  | NULL     | 1996-05-27 |       1 |
| 12 | 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 * 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 |
|  8 | Otto       | Von Count | 656-6548 | NULL       |    NULL |
|  9 | John       | Smith     | NULL     | 1998-04-07 |       4 |
| 11 | Paul       | Thompson  | NULL     | 1996-05-27 |       1 |
| 12 | Eli        | Kramer    | NULL     | 1984-01-15 |    NULL |
+----+------------+-----------+----------+------------+---------+
11 rows in set (0.00 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.02 sec)

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 'people' at line 1
mysql> select * from birthday;
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 |
|  8 | Otto       | Von Count | 656-6548 | NULL       |    NULL |
|  9 | John       | Smith     | NULL     | 1998-04-07 |       4 |
| 11 | Paul       | Thompson  | NULL     | 1996-05-27 |       1 |
| 12 | Eli        | Kramer    | NULL     | 1984-01-15 |    NULL |
+----+------------+-----------+----------+------------+---------+
11 rows in set (0.00 sec)

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

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

mysql> select * from people order by birthday desc where birthday not like %;
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 'where birthday not like %' at line 1
mysql> select * from people order by birthday DESC LIMIT 11;
+----+------------+-----------+----------+------------+---------+
| id | first_name | last_name | mobile   | birthday   | home_id |
+----+------------+-----------+----------+------------+---------+
|  9 | John       | Smith     | NULL     | 1998-04-07 |       4 |
| 11 | Paul       | Thompson  | NULL     | 1996-05-27 |       1 |
|  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
|  5 | Thomas     | Smith     | 152-9854 | 1987-07-06 |       1 |
| 12 | Eli        | Kramer    | NULL     | 1984-01-15 |    NULL |
|  3 | Raj        | Johnson   | 333-3333 | 1980-08-31 |       3 |
|  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
|  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
|  4 | Noelle     | Johnson   | 333-3333 | 1960-07-06 |       1 |
|  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
|  8 | Otto       | Von Count | 656-6548 | NULL       |    NULL |
+----+------------+-----------+----------+------------+---------+
11 rows in set (0.00 sec)


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

mysql> select * from people order by birthday MONTH DESC LIMIT 10;
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 'MONTH DESC LIMIT 10' at line 1
mysql> select * from people order by MONTH(birthday) DESC LIMIT 10;
+----+------------+-----------+----------+------------+---------+
| id | first_name | last_name | mobile   | birthday   | home_id |
+----+------------+-----------+----------+------------+---------+
|  6 | Jane       | Smith     | 152-9854 | 1987-12-06 |       3 |
|  7 | Doug       | Brown     | 466-6241 | 1954-12-07 |       3 |
|  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 |
| 11 | Paul       | Thompson  | NULL     | 1996-05-27 |       1 |
|  9 | John       | Smith     | NULL     | 1998-04-07 |       4 |
|  2 | Sheeri     | Carbral   | 230-4233 | 1970-02-23 |       2 |
|  1 | Tony       | Smith     | 152-9854 | 1973-01-23 |    NULL |
| 12 | Eli        | Kramer    | NULL     | 1984-01-15 |    NULL |
+----+------------+-----------+----------+------------+---------+
10 rows in set (0.00 sec)

mysql> select first_name, last_name,address, MONTHNAME(birthday) as mon, birthday from people order by MONTH(birthday) DESC LIMIT 10;
ERROR 1054 (42S22): Unknown column 'address' in 'field list'
mysql> select first_name, last_name, MONTHNAME(birthday) as mon, birthday from people order by MONTH(birthday) DESC LIMIT 10;
+------------+-----------+----------+------------+
| first_name | last_name | mon      | birthday   |
+------------+-----------+----------+------------+
| Jane       | Smith     | December | 1987-12-06 |
| Doug       | Brown     | December | 1954-12-07 |
| Raj        | Johnson   | August   | 1980-08-31 |
| Noelle     | Johnson   | July     | 1960-07-06 |
| Thomas     | Smith     | July     | 1987-07-06 |
| Paul       | Thompson  | May      | 1996-05-27 |
| John       | Smith     | April    | 1998-04-07 |
| Sheeri     | Carbral   | February | 1970-02-23 |
| Tony       | Smith     | January  | 1973-01-23 |
| Eli        | Kramer    | January  | 1984-01-15 |
+------------+-----------+----------+------------+
10 rows in set (0.00 sec)

mysql> select first_name, last_name, MONTHNAME(birthday) as mon, birthday from people order by MONTH(birthday) aSC LIMIT 10;
+------------+-----------+----------+------------+
| first_name | last_name | mon      | birthday   |
+------------+-----------+----------+------------+
| Otto       | Von Count | NULL     | NULL       |
| Tony       | Smith     | January  | 1973-01-23 |
| Eli        | Kramer    | January  | 1984-01-15 |
| Sheeri     | Carbral   | February | 1970-02-23 |
| John       | Smith     | April    | 1998-04-07 |
| Paul       | Thompson  | May      | 1996-05-27 |
| Noelle     | Johnson   | July     | 1960-07-06 |
| Thomas     | Smith     | July     | 1987-07-06 |
| Raj        | Johnson   | August   | 1980-08-31 |
| Jane       | Smith     | December | 1987-12-06 |
+------------+-----------+----------+------------+
10 rows in set (0.00 sec)

mysql> delete from people where birthday is null;
Query OK, 1 row affected (0.01 sec)

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 'people' 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 |
|  9 | John       | Smith     | NULL     | 1998-04-07 |       4 |
| 11 | Paul       | Thompson  | NULL     | 1996-05-27 |       1 |
| 12 | Eli        | Kramer    | NULL     | 1984-01-15 |    NULL |
+----+------------+-----------+----------+------------+---------+
10 rows in set (0.00 sec)

mysql> select first_name, last_name, MONTHNAME(birthday) as mon, birthday from people order by MONTH(birthday) aSC LIMIT 10;
+------------+-----------+----------+------------+
| first_name | last_name | mon      | birthday   |
+------------+-----------+----------+------------+
| Tony       | Smith     | January  | 1973-01-23 |
| Eli        | Kramer    | January  | 1984-01-15 |
| Sheeri     | Carbral   | February | 1970-02-23 |
| John       | Smith     | April    | 1998-04-07 |
| Paul       | Thompson  | May      | 1996-05-27 |
| Noelle     | Johnson   | July     | 1960-07-06 |
| Thomas     | Smith     | July     | 1987-07-06 |
| Raj        | Johnson   | August   | 1980-08-31 |
| Jane       | Smith     | December | 1987-12-06 |
| Doug       | Brown     | December | 1954-12-07 |
+------------+-----------+----------+------------+
10 rows in set (0.00 sec)

mysql> select first_name, last_name, MONTHNAME(birthday) as mon, birthday from people order by MONTH(birthday) aSC;
+------------+-----------+----------+------------+
| first_name | last_name | mon      | birthday   |
+------------+-----------+----------+------------+
| Tony       | Smith     | January  | 1973-01-23 |
| Eli        | Kramer    | January  | 1984-01-15 |
| Sheeri     | Carbral   | February | 1970-02-23 |
| John       | Smith     | April    | 1998-04-07 |
| Paul       | Thompson  | May      | 1996-05-27 |
| Noelle     | Johnson   | July     | 1960-07-06 |
| Thomas     | Smith     | July     | 1987-07-06 |
| Raj        | Johnson   | August   | 1980-08-31 |
| Jane       | Smith     | December | 1987-12-06 |
| Doug       | Brown     | December | 1954-12-07 |
+------------+-----------+----------+------------+
10 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)

>