SQL 基础查询

列的重命名

去除重复行

视图

表别名

between 操作符

成员集合条件

匹配字符串

升序或降序

根据表达式排序

根据数字占位符排序

case表达式

---

查询语句 

查询子句

select	确定结果集中应该包含哪些列
from	指明所要提取数据的表，以及这些表是如何连接的
where	过滤不需要数据
group by	用于对具有相同列值的行进行分组
having	过滤掉不需要的组
order by	按一个或多个列，对最后结果进行排序

select子句

select子句用于在所有可能的列中，选择查询结果集要包含哪些列。

SELECT dept_id,name FROM department;
+---------+----------------+
| dept_id | name           |
+---------+----------------+
|       1 | Operations     |
|       2 | Loans          |
|       3 | Administration |
+---------+----------------+

SELECT name FROMdepartment;
+----------------+
| name           |
+----------------+
| Operations     |
| Loans          |
| Administration |
+----------------+

此外，select子句还可以添加以下功能：

• 字符，比如字符串和数字；
• 表达式，transaction.amount*-1
• 调用内建函数，比如round（transaction.amount,2）
• 用户自定义的函数调用

SELECT emp_id,'ACTIVE',emp_id*3.14159,upper(lname) FROM employee;

+--------+--------+----------------+--------------+
| emp_id | ACTIVE | emp_id*3.14159 | upper(lname) |
+--------+--------+----------------+--------------+
|      1 | ACTIVE |        3.14159 | SMITH        |
|      2 | ACTIVE |        6.28318 | BARKER       |
|      3 | ACTIVE |        9.42477 | TYLER        |
|      4 | ACTIVE |       12.56636 | HAWTHORNE    |
|      5 | ACTIVE |       15.70795 | GOODING      |
|      6 | ACTIVE |       18.84954 | FLEMING      |
|      7 | ACTIVE |       21.99113 | TUCKER       |
|      8 | ACTIVE |       25.13272 | PARKER       |
|      9 | ACTIVE |       28.27431 | GROSSMAN     |
|     10 | ACTIVE |       31.41590 | ROBERTS      |
|     11 | ACTIVE |       34.55749 | ZIEGLER      |
|     12 | ACTIVE |       37.69908 | JAMESON      |
|     13 | ACTIVE |       40.84067 | BLAKE        |
|     14 | ACTIVE |       43.98226 | MASON        |
|     15 | ACTIVE |       47.12385 | PORTMAN      |
|     16 | ACTIVE |       50.26544 | MARKHAM      |
|     17 | ACTIVE |       53.40703 | FOWLER       |
|     18 | ACTIVE |       56.54862 | TULMAN       |
+--------+--------+----------------+--------------+

 如果只是需要执行一个内建函数或对简单表达式求值可以完全省略from子句。

SELECT VERSION(),USER(),DATABASE();
+-----------+--------------------+------------+
| version() | user()             | database() |
+-----------+--------------------+------------+
| 8.0.13    | linlinle@localhost | bank       |
+-----------+--------------------+------------+

• 列的重命名

有时候希望使用自己定义的列名，通过在select子句中的每个元素后面增加列别名可以实现此目的。

SELECT emp_id,
    -> 'ACTIVE' _status,
    -> emp_id*3.14159 empid_x_pi,
    -> UPPER(lname) last_name_upper
    -> FROM employee;
+--------+--------+------------+-----------------+
| emp_id | _status | empid_x_pi | last_name_upper |
+--------+--------+------------+-----------------+
|      1 | ACTIVE |    3.14159 | SMITH           |
|      2 | ACTIVE |    6.28318 | BARKER          |
|      3 | ACTIVE |    9.42477 | TYLER           |
|      4 | ACTIVE |   12.56636 | HAWTHORNE       |
|      5 | ACTIVE |   15.70795 | GOODING         |
|      6 | ACTIVE |   18.84954 | FLEMING         |
|      7 | ACTIVE |   21.99113 | TUCKER          |
|      8 | ACTIVE |   25.13272 | PARKER          |
|      9 | ACTIVE |   28.27431 | GROSSMAN        |
|     10 | ACTIVE |   31.41590 | ROBERTS         |
|     11 | ACTIVE |   34.55749 | ZIEGLER         |
|     12 | ACTIVE |   37.69908 | JAMESON         |
|     13 | ACTIVE |   40.84067 | BLAKE           |
|     14 | ACTIVE |   43.98226 | MASON           |
|     15 | ACTIVE |   47.12385 | PORTMAN         |
|     16 | ACTIVE |   50.26544 | MARKHAM         |
|     17 | ACTIVE |   53.40703 | FOWLER          |
|     18 | ACTIVE |   56.54862 | TULMAN          |
+--------+--------+------------+-----------------+

• 去除重复行

查询可能会返回重复的行数据。这时可以在关键词之后加上distinct关键字。

SELECT cust_id FROM account;
+---------+
| cust_id |
+---------+
|       1 |
|       1 |
|       1 |
|       2 |
|       2 |
|       3 |
|       3 |
|       4 |
|       4 |
|       4 |
|       5 |
|       6 |
|       6 |
|       7 |
|       8 |
|       8 |
|       9 |
|       9 |
|       9 |
|      10 |
|      10 |
|      11 |
|      12 |
|      13 |
+---------+
SELECT DISTINCT cust_id FROM account;
+---------+
| cust_id |
+---------+
|       1 |
|       2 |
|       3 |
|       4 |
|       5 |
|       6 |
|       7 |
|       8 |
|       9 |
|      10 |
|      11 |
|      12 |
|      13 |
+---------+

from子句

from子句定义了查询中所使用的表，以及连接这些表的方式。表的形式有以下几种：

• 永久表：使用create table语句创建的表
• 临时表：子查询所返回的表
• 虚拟表：使用create view子句所创建的视图。

• 视图

视图是存储在数据字典中的查询，它表现的像一个表，但实际上不拥有任何数据(虚拟表)。首先定义一个查询表的视图，其中包含内建函数调用。

CREATE VIEW employee_vw AS
    -> SELECT emp_id,fname,lname,YEAR(start_date) start_year FROM employee;

当视图被创建，没有产生或存储数据，服务器只是保留了该查询以供将来使用。现在我们可以对其发出查询请求。

 SELECT emp_id,start_year FROM employee_vw;
+--------+------------+
| emp_id | start_year |
+--------+------------+
|      1 |       2005 |
|      2 |       2006 |
|      3 |       2005 |
|      4 |       2006 |
|      5 |       2007 |
|      6 |       2008 |
|      7 |       2008 |
|      8 |       2006 |
|      9 |       2006 |
|     10 |       2006 |
|     11 |       2004 |
|     12 |       2007 |
|     13 |       2004 |
|     14 |       2006 |
|     15 |       2007 |
|     16 |       2005 |
|     17 |       2006 |
|     18 |       2006 |
+--------+------------+

• 表别名

当在单个查询中连接多个表时，需要在select、where、group by、having、以及order by子句中指明所引用的是那个表。有两种在from子句外引用表的方式

• 使用完整表名。如employee.emp_id
• 为每个表指定别名，并在查询需要的地方使用别名。 

SELECT e.emp_id,e.fname,e.lname,d.name dept_name 
    -> FROM employee e INNER JOIN department d ON e.dept_id=d.dept_id;
+--------+----------+-----------+----------------+
| emp_id | fname    | lname     | dept_name      |
+--------+----------+-----------+----------------+
|      4 | Susan    | Hawthorne | Operations     |
|      6 | Helen    | Fleming   | Operations     |
|      7 | Chris    | Tucker    | Operations     |
|      8 | Sarah    | Parker    | Operations     |
|      9 | Jane     | Grossman  | Operations     |
|     10 | Paula    | Roberts   | Operations     |
|     11 | Thomas   | Ziegler   | Operations     |
|     12 | Samantha | Jameson   | Operations     |
|     13 | John     | Blake     | Operations     |
|     14 | Cindy    | Mason     | Operations     |
|     15 | Frank    | Portman   | Operations     |
|     16 | Theresa  | Markham   | Operations     |
|     17 | Beth     | Fowler    | Operations     |
|     18 | Rick     | Tulman    | Operations     |
|      5 | John     | Gooding   | Loans          |
|      1 | Michael  | Smith     | Administration |
|      2 | Susan    | Barker    | Administration |
|      3 | Robert   | Tyler     | Administration |
+--------+----------+-----------+----------------+

where子句

用于在结果集中过滤掉不需要的行。可以同时包含更多条件，利用操作符and、or或者not分隔。

SELECT emp_id,fname,lname,start_date,title 
    -> FROM employee WHERE title='Head Teller' AND start_date>'2006-01-01';
+--------+-------+---------+------------+-------------+
| emp_id | fname | lname   | start_date | title       |
+--------+-------+---------+------------+-------------+
|      6 | Helen | Fleming | 2008-03-17 | Head Teller |
|     10 | Paula | Roberts | 2006-07-27 | Head Teller |
+--------+-------+---------+------------+-------------+

SELECT emp_id,fname,lname,start_date,title FROM employee 
    -> WHERE (title='Head Teller' AND start_date>'2006-01-01') OR
    -> (title='Teller' AND start_date>'2007-01-01');
+--------+----------+---------+------------+-------------+
| emp_id | fname    | lname   | start_date | title       |
+--------+----------+---------+------------+-------------+
|      6 | Helen    | Fleming | 2008-03-17 | Head Teller |
|      7 | Chris    | Tucker  | 2008-09-15 | Teller      |
|     10 | Paula    | Roberts | 2006-07-27 | Head Teller |
|     12 | Samantha | Jameson | 2007-01-08 | Teller      |
|     15 | Frank    | Portman | 2007-04-01 | Teller      |
+--------+----------+---------+------------+-------------+

对于null关键字，要记住判断某列是否null值，要用IS NULL而不是 = NULL。反向同理要用IS NOT NULL而不是 ！=NULL。(即表达式可以为null，但是不能等于null)

SELECT emp_id,fname,lname,superior_emp_id FROM employee WHERE superior_emp_id IS NULL;
+--------+---------+-------+-----------------+
| emp_id | fname   | lname | superior_emp_id |
+--------+---------+-------+-----------------+
|      1 | Michael | Smith |            NULL |
+--------+---------+-------+-----------------+

SELECT emp_id,fname,lname,superior_emp_id FROM employee WHERE superior_emp_id = NULL;
Empty set (0.00 sec)

•  between 操作符

当需要同时限制范围的上限和下限时，可以选择使用between构建一个查询条件，而不需要利用and写两个限制条件。但必须首先指定范围的下限，然后指定上限。

SELECT emp_id,fname,lname,start_date FROM employee 
    -> WHERE start_date BETWEEN '2005-01-01' AND '2007-01-01';
+--------+---------+-----------+------------+
| emp_id | fname   | lname     | start_date |
+--------+---------+-----------+------------+
|      1 | Michael | Smith     | 2005-06-22 |
|      2 | Susan   | Barker    | 2006-09-12 |
|      3 | Robert  | Tyler     | 2005-02-09 |
|      4 | Susan   | Hawthorne | 2006-04-24 |
|      8 | Sarah   | Parker    | 2006-12-02 |
|      9 | Jane    | Grossman  | 2006-05-03 |
|     10 | Paula   | Roberts   | 2006-07-27 |
|     14 | Cindy   | Mason     | 2006-08-09 |
|     16 | Theresa | Markham   | 2005-03-15 |
|     17 | Beth    | Fowler    | 2006-06-29 |
|     18 | Rick    | Tulman    | 2006-12-12 |
+--------+---------+-----------+------------+

• 成员集合条件

某些情况，不需要限制表达式为某个特定值或某个范围的值，而是一个有限值的集合。可以使用in操作符。有时检查其是否不存在可以使用not in操作符。

SELECT account_id,product_cd,cust_id,avail_balance FROM account WHERE product_cd IN ('CD','MM');
+------------+------------+---------+---------------+
| account_id | product_cd | cust_id | avail_balance |
+------------+------------+---------+---------------+
|          3 | CD         |       1 |       3000.00 |
|         15 | CD         |       6 |      10000.00 |
|         17 | CD         |       7 |       5000.00 |
|         23 | CD         |       9 |       1500.00 |
|          8 | MM         |       3 |       2212.50 |
|         12 | MM         |       4 |       5487.09 |
|         22 | MM         |       9 |       9345.55 |
+------------+------------+---------+---------------+

除了编写自定义的表达式集合，还可以通过子查询产生中间集合。例如，在前一个查询中，所有4种产品类型的product_type_cd列都为‘ ACCOUNT'。

SELECT account_id,product_cd,cust_id,avail_balance FROM account 
    -> WHERE product_cd IN (SELECT product_cd FROM product WHERE product_type_cd ='ACCOUNT');
+------------+------------+---------+---------------+
| account_id | product_cd | cust_id | avail_balance |
+------------+------------+---------+---------------+
|          3 | CD         |       1 |       3000.00 |
|         15 | CD         |       6 |      10000.00 |
|         17 | CD         |       7 |       5000.00 |
|         23 | CD         |       9 |       1500.00 |
|          1 | CHK        |       1 |       1057.75 |
|          4 | CHK        |       2 |       2258.02 |
|          7 | CHK        |       3 |       1057.75 |
|         10 | CHK        |       4 |        534.12 |
|         13 | CHK        |       5 |       2237.97 |
|         14 | CHK        |       6 |        122.37 |
|         18 | CHK        |       8 |       3487.19 |
|         21 | CHK        |       9 |        125.67 |
|         24 | CHK        |      10 |      23575.12 |
|         28 | CHK        |      12 |      38552.05 |
|          8 | MM         |       3 |       2212.50 |
|         12 | MM         |       4 |       5487.09 |
|         22 | MM         |       9 |       9345.55 |
|          2 | SAV        |       1 |        500.00 |
|          5 | SAV        |       2 |        200.00 |
|         11 | SAV        |       4 |        767.77 |
|         19 | SAV        |       8 |        387.99 |
+------------+------------+---------+---------------+

• 匹配字符串

通配符 

_	下划线为单个字符的占位符
%	任意数目的字符（包括0）

使用搜索表达式构建条件时，可以使用like操作符。 

SELECT lname FROM employee WHERE lname LIKE '_a%e%';
+-----------+
| lname     |
+-----------+
| Barker    |
| Hawthorne |
| Parker    |
| Jameson   |
+-----------+

正则表达式

 regexp操作符接受一个正则表达式，并应用到左侧表达式

SELECT lname FROM employee WHERE lname REGEXP '^[FG]';
+----------+
| lname    |
+----------+
| Gooding  |
| Fleming  |
| Grossman |
| Fowler   |
+----------+

order by 子句

用于对结果集中的原始列数据或是根据列数据计算的表达式结果进行排序。下面首先根据open_emp_id进行排序，然后针对每个雇员按同样顺序显示账户类型。

SELECT open_emp_id, product_cd FROM account ORDER BY open_emp_id, product_cd;
+-------------+------------+
| open_emp_id | product_cd |
+-------------+------------+
|           1 | CD         |
|           1 | CD         |
|           1 | CHK        |
|           1 | CHK        |
|           1 | CHK        |
|           1 | MM         |
|           1 | MM         |
|           1 | SAV        |
|          10 | BUS        |
|          10 | CD         |
|          10 | CD         |
|          10 | CHK        |
|          10 | CHK        |
|          10 | SAV        |
|          10 | SAV        |
|          13 | CHK        |
|          13 | MM         |
|          13 | SBL        |
|          16 | BUS        |
|          16 | CHK        |
|          16 | CHK        |
|          16 | CHK        |
|          16 | CHK        |
|          16 | SAV        |
+-------------+------------+

• 升序或降序

可以通过关键字asc和desc指定是升序还是降序。默认情况是升序排序。

SELECT account_id, product_cd, open_date, avail_balance FROM account ORDER BY avail_balance DESC;
+------------+------------+------------+---------------+
| account_id | product_cd | open_date  | avail_balance |
+------------+------------+------------+---------------+
|         29 | SBL        | 2004-02-22 |      50000.00 |
|         28 | CHK        | 2003-07-30 |      38552.05 |
|         24 | CHK        | 2002-09-30 |      23575.12 |
|         15 | CD         | 2004-12-28 |      10000.00 |
|         22 | MM         | 2004-10-28 |       9345.55 |
|         27 | BUS        | 2004-03-22 |       9345.55 |
|         12 | MM         | 2004-09-30 |       5487.09 |
|         17 | CD         | 2004-01-12 |       5000.00 |
|         18 | CHK        | 2001-05-23 |       3487.19 |
|          3 | CD         | 2004-06-30 |       3000.00 |
|          4 | CHK        | 2001-03-12 |       2258.02 |
|         13 | CHK        | 2004-01-27 |       2237.97 |
|          8 | MM         | 2002-12-15 |       2212.50 |
|         23 | CD         | 2004-06-30 |       1500.00 |
|          1 | CHK        | 2000-01-15 |       1057.75 |
|          7 | CHK        | 2002-11-23 |       1057.75 |
|         11 | SAV        | 2000-01-15 |        767.77 |
|         10 | CHK        | 2003-09-12 |        534.12 |
|          2 | SAV        | 2000-01-15 |        500.00 |
|         19 | SAV        | 2001-05-23 |        387.99 |
|          5 | SAV        | 2001-03-12 |        200.00 |
|         21 | CHK        | 2003-07-30 |        125.67 |
|         14 | CHK        | 2002-08-24 |        122.37 |
|         25 | BUS        | 2002-10-01 |          0.00 |
+------------+------------+------------+---------------+

• 根据表达式排序

根据识别号码的最后(最右)3位数字进行排序。 

SELECT cust_id, cust_type_cd, city, state, fed_id FROM customer ORDER BY RIGHT(fed_id,3);
+---------+--------------+------------+-------+-------------+
| cust_id | cust_type_cd | city       | state | fed_id      |
+---------+--------------+------------+-------+-------------+
|       1 | I            | Lynnfield  | MA    | 111-11-1111 |
|      10 | B            | Salem      | NH    | 04-1111111  |
|       2 | I            | Woburn     | MA    | 222-22-2222 |
|      11 | B            | Wilmington | MA    | 04-2222222  |
|       3 | I            | Quincy     | MA    | 333-33-3333 |
|      12 | B            | Salem      | NH    | 04-3333333  |
|       4 | I            | Waltham    | MA    | 444-44-4444 |
|      13 | B            | Quincy     | MA    | 04-4444444  |
|       5 | I            | Salem      | NH    | 555-55-5555 |
|       6 | I            | Waltham    | MA    | 666-66-6666 |
|       7 | I            | Wilmington | MA    | 777-77-7777 |
|       8 | I            | Salem      | NH    | 888-88-8888 |
|       9 | I            | Newton     | MA    | 999-99-9999 |
+---------+--------------+------------+-------+-------------+

• 根据数字占位符排序

使用该列位于select子句中的位置号来替代列名。例如根据查询返回的第2列和第5列排序。

SELECT emp_id,title,start_date,fname,lname FROM employee ORDER BY 2,5;
+--------+--------------------+------------+----------+-----------+
| emp_id | title              | start_date | fname    | lname     |
+--------+--------------------+------------+----------+-----------+
|     13 | Head Teller        | 2004-05-11 | John     | Blake     |
|      6 | Head Teller        | 2008-03-17 | Helen    | Fleming   |
|     16 | Head Teller        | 2005-03-15 | Theresa  | Markham   |
|     10 | Head Teller        | 2006-07-27 | Paula    | Roberts   |
|      5 | Loan Manager       | 2007-11-14 | John     | Gooding   |
|      4 | Operations Manager | 2006-04-24 | Susan    | Hawthorne |
|      1 | President          | 2005-06-22 | Michael  | Smith     |
|     17 | Teller             | 2006-06-29 | Beth     | Fowler    |
|      9 | Teller             | 2006-05-03 | Jane     | Grossman  |
|     12 | Teller             | 2007-01-08 | Samantha | Jameson   |
|     14 | Teller             | 2006-08-09 | Cindy    | Mason     |
|      8 | Teller             | 2006-12-02 | Sarah    | Parker    |
|     15 | Teller             | 2007-04-01 | Frank    | Portman   |
|      7 | Teller             | 2008-09-15 | Chris    | Tucker    |
|     18 | Teller             | 2006-12-12 | Rick     | Tulman    |
|     11 | Teller             | 2004-10-23 | Thomas   | Ziegler   |
|      3 | Treasurer          | 2005-02-09 | Robert   | Tyler     |
|      2 | Vice President     | 2006-09-12 | Susan    | Barker    |
+--------+--------------------+------------+----------+-----------+

case表达式

CASE
    WHEN C1 THEN E1
    WHEN C2 THEN E2
    ...
    WHEN CN THEN EN
    [ELSE ED]
END

在三路外连接章节中，我们希望可以从individual表中检索出fname/lname列，从business表中检索name列。然后根据cust_type_cd列的值，决定是使用indiv_name还是business_name列的值。

SELECT c.cust_id, c.fed_id, c.cust_type_cd, CONCAT(i.fname,' ', i.lname) indiv_name, b.name business_name 
    -> FROM customer c LEFT OUTER JOIN individual i ON c.cust_id=i.cust_id 
    -> LEFT OUTER JOIN business b ON c.cust_id = b.cust_id;

+---------+-------------+--------------+-----------------+------------------------+
| cust_id | fed_id      | cust_type_cd | indiv_name      | business_name          |
+---------+-------------+--------------+-----------------+------------------------+
|       1 | 111-11-1111 | I            | James Hadley    | NULL                   |
|       2 | 222-22-2222 | I            | Susan Tingley   | NULL                   |
|       3 | 333-33-3333 | I            | Frank Tucker    | NULL                   |
|       4 | 444-44-4444 | I            | John Hayward    | NULL                   |
|       5 | 555-55-5555 | I            | Charles Frasier | NULL                   |
|       6 | 666-66-6666 | I            | John Spencer    | NULL                   |
|       7 | 777-77-7777 | I            | Margaret Young  | NULL                   |
|       8 | 888-88-8888 | I            | George Blake    | NULL                   |
|       9 | 999-99-9999 | I            | Richard Farley  | NULL                   |
|      10 | 04-1111111  | B            | NULL            | Chilton Engineering    |
|      11 | 04-2222222  | B            | NULL            | Northeast Cooling Inc. |
|      12 | 04-3333333  | B            | NULL            | Superior Auto Body     |
|      13 | 04-4444444  | B            | NULL            | AAA Insurance Inc.     |
+---------+-------------+--------------+-----------------+------------------------+

我们可以通过case表达式使用条件逻辑决定客户类型，进而返回恰当的字符串

SELECT c.cust_id, c.fed_id,
    -> CASE
    ->  WHEN c.cust_type_cd = 'I'
    ->    THEN CONCAT(i.fname,' ', i.lname)
    ->  WHEN c.cust_type_cd = 'B'
    ->    THEN b.name
    ->  ELSE 'Unknow'
    -> END name
    -> FROM customer c LEFT OUTER JOIN individual i ON c.cust_id=i.cust_id LEFT OUTER JOIN business b ON c.cust_id=b.cust_id;

+---------+-------------+------------------------+
| cust_id | fed_id      | name                   |
+---------+-------------+------------------------+
|       1 | 111-11-1111 | James Hadley           |
|       2 | 222-22-2222 | Susan Tingley          |
|       3 | 333-33-3333 | Frank Tucker           |
|       4 | 444-44-4444 | John Hayward           |
|       5 | 555-55-5555 | Charles Frasier        |
|       6 | 666-66-6666 | John Spencer           |
|       7 | 777-77-7777 | Margaret Young         |
|       8 | 888-88-8888 | George Blake           |
|       9 | 999-99-9999 | Richard Farley         |
|      10 | 04-1111111  | Chilton Engineering    |
|      11 | 04-2222222  | Northeast Cooling Inc. |
|      12 | 04-3333333  | Superior Auto Body     |
|      13 | 04-4444444  | AAA Insurance Inc.     |
+---------+-------------+------------------------+

case表达式可以返回任意类型的表达式，甚至包括子查询，下面是利用子查询代替外连接从individual和business表中检索

SELECT c.cust_id, c.fed_id,
    -> CASE
    ->  WHEN c.cust_type_cd = 'I' THEN
    ->    (SELECT CONCAT(i.fname,' ', i.lname) FROM individual i WHERE c.cust_id=i.cust_id)
    ->  WHEN c.cust_type_cd = 'B' THEN
    ->    (SELECT b.name FROM business b WHERE c.cust_id=b.cust_id)
    ->  ELSE  'Unknow'
    -> END name
    -> FROM customer c;

+---------+-------------+------------------------+
| cust_id | fed_id      | name                   |
+---------+-------------+------------------------+
|       1 | 111-11-1111 | James Hadley           |
|       2 | 222-22-2222 | Susan Tingley          |
|       3 | 333-33-3333 | Frank Tucker           |
|       4 | 444-44-4444 | John Hayward           |
|       5 | 555-55-5555 | Charles Frasier        |
|       6 | 666-66-6666 | John Spencer           |
|       7 | 777-77-7777 | Margaret Young         |
|       8 | 888-88-8888 | George Blake           |
|       9 | 999-99-9999 | Richard Farley         |
|      10 | 04-1111111  | Chilton Engineering    |
|      11 | 04-2222222  | Northeast Cooling Inc. |
|      12 | 04-3333333  | Superior Auto Body     |
|      13 | 04-4444444  | AAA Insurance Inc.     |
+---------+-------------+------------------------+