INDEX

ORDERS

ORDERID

LINENUM

CUSTID

PRODID

QUANTITY

1

1

c1

p2

1

1

2

c1

p3

2

1

3

c1

p1

1

2

1

c2

p4

2

2

2

c2

p2

2

2

3

c2

p3

1

3

1

c3

p2

1

3

2

c3

p3

1

4

1

c4

p2

2

4

2

c4

p1

1

4

3

c4

p3

1

4

4

c4

p4

3

5

1

c1

p3

4

5

2

c1

p4

8

6

1

c3

p1

3

6

2

c3

p4

2

6

3

c3

p2

5

ORDERS

LINENUM

3

ORDERS

ORDERID

LINENUM

CUSTID

PRODID

QUANTITY

PRODUCTS
PRODID	PRODNAME	PRODPRICE
p1	ritz crkrs	3.19
p2	krft mayo	2.39
p3	dawn dish	1.49
p4	bert evo	18.95

PROJECTION

EXAMPLE 1.1: List customer name and credit limit

select custname,creditlimit

from customers

ex1.1
custname	creditlimit
Able	500
Baker	1000
Crane	1000
Doe	500
Evans	500

EXAMPLE 1.2: List customer id and credit limit

select custid,creditlimit

from customers

Ex1.2
custid	creditlimit
c1	500
c2	1000
c3	1000
c4	500
c5	500

EXAMPLE 1.3: List creditlimit (lowest to highest) and customer name

SELECT creditlimit, custname

FROM customers

Rectangular Callout: asc=ascending (default)
desc==descending

NOTE: The order by clause is always the last clause in the sql statement. ORDER BY creditlimit asc;

Ex1.3
creditlimit	custname
500	Evans
500	Doe
500	Able
1000	Crane
1000	Baker

SELECTION

EXAMPLE 2.1: List customers with a credit limit greater than 500

NOTE:

> means greater than;

< means less than;

= means equal;

>= means greater than or equal;

<= means less than or equal;

<> means not equal.

select custname,creditlimit

from customers

where creditlimit > 500;

Ex2.1
custname	creditlimit
Baker	1000
Crane	1000

EXAMPLE 2.2: List customers who purchased p4

select custid

from orders

where prodid='p4';

Rectangular Callout: Single (or double) quotes surround text values.

ex2-2
custid
c2
c4
c1
c3

EXAMPLE 2.3:List customers who purchased p4 or p3

select custid

from orders

where prodid='p4' or prodid='p3';

ex2-3
custid
c1
c2
c2
c3
c4
c4
c1
c1
c3

EXAMPLE 2.4: Same as example 2.3, except without the duplicate id’s

select distinct custid

from orders

where prodid='p4' or prodid='p3';

Ex2.4
custid
c1
c2
c3
c4

BUILT-IN AGGREGATE FUNCTIONS

MIN()/MAX()

EXAMPLE 3.1.1: List the highest credit limit

select max(creditlimit)

from customers;

ex3.1.1
Expr1000
1000

EXAMPLE 3.1.2: Overriding the default heading in example 3.11

select max(creditlimit) as max_credit

from customers;

EX3.1.2
MAX_CREDIT
1000

COUNT()

EXAMPLE 3.2.1: List the number of customers.

select count(*) as NUM_OF_CUSTOMERS

from customers;

Ex3.2.1
NUM_OF_CUSTOMERS
5

EXAMPLE 3.2.2: List the number of customers with a 1000 credit limit.

select count(*) as NUM_OF_CUSTOMERS

from customers

where creditlimit=1000;

Ex3.2.2
NUM_OF_CUSTOMERS
2

EXAMPLE 3.2.3: List the number of different orders.

select count(*) as NUM_ORDERS

from orders;

Rectangular Callout: Problem: The number of different orders is 6- not 17. Seventeen is the total number of order lines!

ex3-2-3
NUM_ORDERS
17

EXAMPLE 3.2.4 modification: Attempted correction of example 3.2.3.

select count(distinct orderid) as NUM_ORDERS

from orders;

Rectangular Callout: MS Acces SQL does not support this; some other versions of SQL do support this. One work-around solution is:

select count(*) as num_distinct_orders
from (select distinct orderid. from orders)

SUM()

EXAMPLE 3.3.1: List the quantity of p2’s that has been ordered.

select sum(quantity) as QTY_ORDERED

from orders

where prodid='p2';

Ex3.3.1
QTY_ORDERED
11

AVG()

EXAMPLE 3.4.1: List the average price of all products.

select avg(prodprice) as AVG_PRICE

from products;

Ex3.4.1
AVG_PRICE
6.505

NUMERICAL EXPRESSIONSC

EXAMPLE 4.1: List the prices of the products with a 20% increase.

select prodid,prodname,prodprice as OLD_PRICE,prodprice*1.2 as NEW_PRICE

NOTE:

+ means addition;

- means subtraction;

* means multiplication;

/ means division.

from products;

Ex4.1
prodid	prodname	OLD_PRICE	NEW_PRICE
p1	ritz crkrs	3.19	3.828
p2	krft mayo	2.39	2.868
p3	dawn dish	1.49	1.788
p4	bert evo	18.95	22.74

EXAMPLE 4.2: Same as example 4.1, except round new_price to 2 decimals.

select prodid,prodname,prodprice as OLD_PRICE,round(prodprice*1.2,2) as NEW_PRICE

from products;

Ex4.2
prodid	prodname	OLD_PRICE	NEW_PRICE
p1	ritz crkrs	3.19	3.83
p2	krft mayo	2.39	2.87
p3	dawn dish	1.49	1.79
p4	bert evo	18.95	22.74

GROUPINGC

EXAMPLE 5.1: List the number of customers in each credit limit (500 and 1000) .

select creditlimit,count(*) as COUNT

from customers

Rectangular Callout: The group by divides the customer table into 2 groups – one for each different value of creditlimit:
1. a group of all the customer
records with a credit limit of 500;
2. a group of all the customer records with a credit limit of 1000.
The count() function is then applied to each group. group by creditlimit;

Ex5.1
creditlimit	COUNT
500	3
1000	2

EXAMPLE 5.2: List the average line quantity ordered per product. Order the results from highest to lowest average quantity.

select prodid,avg(quantity) as AVG_QTY

Rectangular Callout: The group by divides the orders table into 4 groups – one for each different value of prodid:
1. a group of all the orders
records with a prodid of p1
2. a group of all the orders
records with a prodid of p2
3. a group of all the orders
records with a prodid of p3
4. a group of all the orders
records with a prodid of p4
The avg() function is then applied to each group. from orders

group by prodid

order by avg(quantity) desc;

ex5-2
prodid	AVG_QTY
p4	3.75
p2	2.2
p3	1.8
p1	1.67

EXAMPLE 5.3: Obtain the average line quantity ordered per product and list averages that are greater than 1.4.

select prodid,avg(quantity) as AVG_QTY

from orders

group by prodid

Rectangular Callout: The group by divides the orders table into 4 groups – one for each different value of prodid: The avg() function is then applied to each group. Finally, the having clause is applied to each group average and only the averages greater than 1.4 are selected.

The having clause must be preceded by a group by clause. having avg(quantity) > 1.4;

ex5-3
prodid	AVG_QTY
p1	1.67
p2	2.2
p3	1.8
p4	3.75

EXAMPLE 5.4: List the average line quantity ordered per product.

select prodid,custid,avg(quantity) as AVG_QTY

from orders

Rectangular Callout: Problem: The custid field will cause an error. The select clause associated with a group by clause cannot refer to fields that are not contained in the group by clause group by prodid

EXAMPLE 5.5: List customers that have ordered more than a total quantity of 10.

select custid,sum(quantity) as totQty

from orders

group by custid

having sum(quantity) > 4

ex5-5
custid	totQty
c1	16
c3	12

JOINSC

CARTESIAN PRODUCT

EXAMPLE 6.1.1: Join each record in the orders table with each record in the customers table

select *

from orders,customers

ex6-1-1
ORDERID	LINENUM	orders.CUSTID	PRODID	QUANTITY	customers.CUSTID	CUSTNAME	CREDITLIMIT
1	1	c1	p2	1	c1	Able	500
1	1	c1	p2	1	c2	Baker	1000
1	1	c1	p2	1	c3	Crane	1000
1	1	c1	p2	1	c4	Doe	500
1	1	c1	p2	1	c5	Evans	500
1	2	c1	p3	2	c1	Able	500
1	2	c1	p3	2	c2	Baker	1000
1	2	c1	p3	2	c3	Crane	1000
1	2	c1	p3	2	c4	Doe	500
1	2	c1	p3	2	c5	Evans	500
1	3	c1	p1	1	c1	Able	500
1	3	c1	p1	1	c2	Baker	1000
1	3	c1	p1	1	c3	Crane	1000
1	3	c1	p1	1	c4	Doe	500
1	3	c1	p1	1	c5	Evans	500
2	1	c2	p4	2	c1	Able	500
2	1	c2	p4	2	c2	Baker	1000
2	1	c2	p4	2	c3	Crane	1000
2	1	c2	p4	2	c4	Doe	500
2	1	c2	p4	2	c5	Evans	500
2	2	c2	p2	2	c1	Able	500
2	2	c2	p2	2	c2	Baker	1000
2	2	c2	p2	2	c3	Crane	1000
2	2	c2	p2	2	c4	Doe	500
2	2	c2	p2	2	c5	Evans	500
2	3	c2	p3	1	c1	Able	500
2	3	c2	p3	1	c2	Baker	1000
2	3	c2	p3	1	c3	Crane	1000
2	3	c2	p3	1	c4	Doe	500
2	3	c2	p3	1	c5	Evans	500
3	1	c3	p2	1	c1	Able	500
3	1	c3	p2	1	c2	Baker	1000
3	1	c3	p2	1	c3	Crane	1000
3	1	c3	p2	1	c4	Doe	500
3	1	c3	p2	1	c5	Evans	500
3	2	c3	p3	1	c1	Able	500
3	2	c3	p3	1	c2	Baker	1000
3	2	c3	p3	1	c3	Crane	1000
3	2	c3	p3	1	c4	Doe	500
3	2	c3	p3	1	c5	Evans	500
4	1	c4	p2	2	c1	Able	500
4	1	c4	p2	2	c2	Baker	1000
4	1	c4	p2	2	c3	Crane	1000
4	1	c4	p2	2	c4	Doe	500
4	1	c4	p2	2	c5	Evans	500
4	2	c4	p1	1	c1	Able	500
4	2	c4	p1	1	c2	Baker	1000
4	2	c4	p1	1	c3	Crane	1000
4	2	c4	p1	1	c4	Doe	500
4	2	c4	p1	1	c5	Evans	500
4	3	c4	p3	1	c1	Able	500
4	3	c4	p3	1	c2	Baker	1000
4	3	c4	p3	1	c3	Crane	1000
4	3	c4	p3	1	c4	Doe	500
4	3	c4	p3	1	c5	Evans	500
4	4	c4	p4	3	c1	Able	500
4	4	c4	p4	3	c2	Baker	1000
4	4	c4	p4	3	c3	Crane	1000
4	4	c4	p4	3	c4	Doe	500
4	4	c4	p4	3	c5	Evans	500
5	1	c1	p3	4	c1	Able	500
5	1	c1	p3	4	c2	Baker	1000
5	1	c1	p3	4	c3	Crane	1000
5	1	c1	p3	4	c4	Doe	500
5	1	c1	p3	4	c5	Evans	500
5	2	c1	p4	8	c1	Able	500
5	2	c1	p4	8	c2	Baker	1000
5	2	c1	p4	8	c3	Crane	1000
5	2	c1	p4	8	c4	Doe	500
5	2	c1	p4	8	c5	Evans	500
6	1	c3	p1	3	c1	Able	500
6	1	c3	p1	3	c2	Baker	1000
6	1	c3	p1	3	c3	Crane	1000
6	1	c3	p1	3	c4	Doe	500
6	1	c3	p1	3	c5	Evans	500
6	2	c3	p4	2	c1	Able	500
6	2	c3	p4	2	c2	Baker	1000
6	2	c3	p4	2	c3	Crane	1000
6	2	c3	p4	2	c4	Doe	500
6	2	c3	p4	2	c5	Evans	500
6	3	c3	p2	5	c1	Able	500
6	3	c3	p2	5	c2	Baker	1000
6	3	c3	p2	5	c3	Crane	1000
6	3	c3	p2	5	c4	Doe	500
6	3	c3	p2	5	c5	Evans	500

INNER JOIN

EXAMPLE 6.2.1: Join each record in the orders table with each record in the customers table where the custid in the orders table equals the custid in the customers table

SELECT *

FROM orders, customers

WHERE orders.custid=customers.custid;

Oval: Matching custid’s

Note: Whenever different tables have the same field name, they are prefixed with the table name

ex6-2-1
ORDERID	LINENUM	orders.CUSTID	PRODID	QUANTITY	customers.CUSTID	CUSTNAME	CREDITLIMIT
1	1	c1	p2	1	c1	Able	500
1	2	c1	p3	2	c1	Able	500
1	3	c1	p1	1	c1	Able	500
5	1	c1	p3	4	c1	Able	500
5	2	c1	p4	8	c1	Able	500
2	1	c2	p4	2	c2	Baker	1000
2	2	c2	p2	2	c2	Baker	1000
2	3	c2	p3	1	c2	Baker	1000
3	1	c3	p2	1	c3	Crane	1000
3	2	c3	p3	1	c3	Crane	1000
6	1	c3	p1	3	c3	Crane	1000
6	2	c3	p4	2	c3	Crane	1000
6	3	c3	p2	5	c3	Crane	1000
4	1	c4	p2	2	c4	Doe	500
4	2	c4	p1	1	c4	Doe	500
4	3	c4	p3	1	c4	Doe	500
4	4	c4	p4	3	c4	Doe	500

Oval Callout: Orders records Oval Callout: customers records +

EXAMPLE 6.2.2: An alternative syntax to example 6.2.1.

SELECT *

from orders inner join customers on orders.custid=customers.custid

EXAMPLE 6.2.3: List the custid,orderid, linenum, prodprice, quantity, and cost (quantity times prodprice) for each order line .

select custid,orderid,linenum,quantity,prodprice,quantity*prodprice as COST

Rectangular Callout: Join the orders and the products tables on the common field prodid. The join is required to acquire the prodprice field from the products table. from orders,products

where orders.prodid=products.prodid

order by orderid,linenum

ex6-2-3
custid	orderid	linenum	quantity	prodprice	COST
c1	1	1	1	2.39	2.39
c1	1	2	2	1.49	2.98
c1	1	3	1	3.19	3.19
c2	2	1	2	18.95	37.9
c2	2	2	2	2.39	4.78
c2	2	3	1	1.49	1.49
c3	3	1	1	2.39	2.39
c3	3	2	1	1.49	1.49
c4	4	1	2	2.39	4.78
c4	4	2	1	3.19	3.19
c4	4	3	1	1.49	1.49
c4	4	4	3	18.95	56.85
c1	5	1	4	1.49	5.96
c1	5	2	8	18.95	151.6
c3	6	1	3	3.19	9.57
c3	6	2	2	18.95	37.9
c3	6	3	5	2.39	11.95

EXAMPLE 6.2.4: An alternative syntax to example 6.2.3.

select custid,orderid,linenum,quantity,prodprice,quantity*prodprice as COST

from orders inner join products on orders.prodid=products.prodid

order by orderid,linenum

EXAMPLE 6.2.5: Joining three tables.

select *

from customers,orders,products

where customers.custid=orders.custid and orders.prodid=products.prodid;

EXAMPLE 6.2.6: An alternative syntax to example 6.2.5.

select *

from (customers inner join orders on customers.custid=orders.custid) inner join products on orders.prodid=products.prodid

order by orderid, linenum;

ALIAS

EXAMPLE 6.3.1: Join customers and orders using aliases.

Oval Callout: Alias o stands for orders; alias c stands for customers. select c.custid,orderid

from customers c, orders o

where c.custid=o.custid

EXAMPLE 6.3.2: Join a table to itself.

Oval Callout: P2 acts as a temporary copy of products. select *

from products p1,products p2

where p1.prodid=p2.prodid;

Ex6.3.2
p1.PRODID	p1.PRODNAME	p1.PRODPRICE	p2.PRODID	p2.PRODNAME	p2.PRODPRICE
p1	ritz crkrs	3.19	p1	ritz crkrs	3.19
p2	krft mayo	2.39	p2	krft mayo	2.39
p3	dawn dish	1.49	p3	dawn dish	1.49
p4	bert evo	18.95	p4	bert evo	18.95

LEFT JOIN

EXAMPLE 6.4.1: Left join customers and orders.

select c.custid,orderid

from customers c left join orders o on c.custid=o.custid

ex6-4-1
custid	orderid
c1	1
c1	1
c1	1
c1	5
c1	5
c2	2
c2	2
c2	2
c3	3
c3	3
c3	6
c3	6
c3	6
c4	4
c4	4
c4	4
c4	4
c5

EXAMPLE 6.4.2: Same as example 6.4.1 except an inner join is used.

SELECT c.custid, orderid

FROM customers AS c inner join orders AS o ON c.custid=o.custid;

ex6-4-2
custid	orderid
c1	1
c1	1
c1	1
c1	5
c1	5
c2	2
c2	2
c2	2
c3	3
c3	3
c3	6
c3	6
c3	6
c4	4
c4	4
c4	4
c4	4

JOIN WITH GROUP BY

EXAMPLE 6.5.1: List the dollar total of all orders per customer.

SELECT o.custid, sum(quantity*prodprice) AS TOT_ORDER

FROM orders AS o, products AS p

WHERE o.prodid=p.prodid

GROUP BY o.custid;

Ex6-5-1
custid	TOT_ORDER
c1	166.12
c2	44.17
c3	3.88
c4	66.31

EXAMPLE 6.5.2: List the dollar total for each order per customer.

SELECT o.custid, o.orderid, sum(quantity*prodprice) AS TOT_ORDER

FROM orders AS o, products AS p

WHERE o.prodid=p.prodid

GROUP BY o.custid, o.orderid;

Ex6-5-2
custid	orderid	TOT_ORDER
c1	1	8.56
c1	5	157.56
c2	2	44.17
c3	3	3.88
c4	4	66.31

EMBEDDED SELECT STATEMENTS (Nested Subqueries)

NON-CORRELATED SUBQUERY

EXAMPLE 7.1.1:List the name of the highest price product – syntax error.

Rectangular Callout: An aggregate function cannot appear in a where clause select prodname

from products

where prod price=max(price);

Rectangular Callout: The programmer is doing the work of determining the highest price in advance, whereas the computer should do the work.

Imagine a products table with 100,000 records or more – it would not be practical for the programmer to manually determine the highest price.

The next example will replace 18.95 with a select statement that will determine the highest price (max price). EXAMPLE 7.1.2:List the name of the highest priced product – improper method.

select prodname

from products

where prodprice=18.95);

EXAMPLE 7.1.3:List the name of the highest priced product –using a non-correlated subquery..

SELECT prodname

Rectangular Callout: This subquery determines and returns the highest price. FROM products

WHERE prodprice=

(select max(prodprice)

from products);

NOTE: Values that may appear in a SQL statement can be replaced by SQL statements that generate those values – see examples 7.1.3 and 7.1.4

ex7.1.3

prodname

bert evo

EXAMPLE 7.1.4:List the line items that have a quantity exceeding the average line quantity..

select *

from orders

where quantity >

(select avg(quantity)

from orders);

ex7-1-4
ORDERID	LINENUM	CUSTID	PRODID	QUANTITY
4	4	c4	p4	3
5	1	c1	p3	4
5	2	c1	p4	8
6	1	c3	p1	3
6	3	c3	p2	5

EXAMPLE 7.1.5: List the customers with the lowest credit limit..

select *

from customers

where creditlimit=

(select min(creditlimit)

from customers);

Ex7.1.5
CUSTID	CUSTNAME	CREDITLIMIT
c1	Able	500
c4	Doe	500
c5	Evans	500

Rectangular Callout: This query displays the following values:
c1 Able
c4 Doe EXAMPLE 7.1.6: List the custid and custname of those customers who have ordered ritz crkrs. ..

select custid,custname

from customers

Rectangular Callout: This subquery returns the following values:
c1
c4 where custid in

(select custid

from orders

where prodid in

Rectangular Callout: This subquery returns the following value:
p1 (select prodid

from products

where prodname='ritz crkrs'))

NOTE: A non-correlated subquery is executed from the bottom up – beginning with the bottom subquery and working up to the first subquery.

Regarding the in operator:

where custid in (‘c1’,’c4’)

is equivalent to

where custid =’c1’ or custid=’c4’

Remember, the values (‘c1’,’c4’) can be replaced with a select statement that generates those values.

ex7-1-6

custid

custname

Able

Crane

Doe

EXAMPLE 7.1.7: .Same as example 7.1.6, except using a join instead of a non-correlated subquery.

select customers.custid,custname

from (customers inner join orders on customers.custid=orders.custid) inner join products on orders.prodid=products.prodid

where prodname='ritz crkrs'

CORRELATED SUBQUERY

EXAMPLE 7.2.1: List the line items, for a each customer, whose quantity exceeds the average line quantity for that customer.

select custid,linenum,quantity

NOTE: The action between the subquery and the outer query is interactive as follows:

1.      The outer query reads the first line item from the orders table;

2.      The subquery uses the custid from the line item read by the outer query and (using a copy of the orders table) computes the average line quantity for that customer;

3.      The where clause of the outer query can then be completed;

4.      The outer query continues by reading the next line item,. and steps 2 – 4 are repeated

The interactive process continues until the outer query reads all the line items from the orders table. The correlated subquery resembles a nested loop(s) in a programming language such as C++.

from orders o1

where o1.quantity >

(select avg(quantity)

from orders o2

where o1.custid=o2.custid)

ex7-2-1
custid	linenum	quantity
c2	1	2
c2	2	2
c4	1	2
c4	4	3
c1	1	4
c1	2	8
c3	1	3
c3	3	5

SELECT STATEMENT IN SELECT CLAUSE

EXAMPLE 7.3.1:.List the total quantity ordered by each customer as a percentage of the grand total quantity of orders.

select custid,sum(quantity), sum(quantity)/(select sum(quantity) from orders)*100 as percent_of_total

from orders

group by custid

ex7-3-1
custid	Expr1001	percent_of_total
c1	16	40
c2	5	12.5
c3	12	30
c4	7	17.5

EXAMPLE 7.3.2:.List the average line quantity per order without using a GROUP/BY.

Oval Callout: Select statement in select clause. select distinct orderid,

(select avg(quantity)

from orders o2

where o1.orderid=o2.orderid) as avgLineQty

from orders o1

NOTE: The same as ex7-3-2, except using a group/by

select orderid,avg(quantity)

from orders

group by orderid

Ex7-3-2
orderid	avgLineQty
1	1.33333333333333
2	1.66666666666667
3	1
4	1.75
5	6
6	3.33333333333333

SELECT STATEMENT IN FROM CLAUSE

EXAMPLE 7.4.1:.List each customer order whose quantity exceeds the average quantity per order for that customer.

select o.custid,o.quantity,custAvg

from orders o,(select custid,avg(quantity) as custAvg from orders group by custid) s where o.custid=s.custid and o.quantity > custAvg

Oval Callout: This Select statement could be replaced by a Virtual Table.

Ex7-4-1

custid

quantity

custAvg

1.66666666666667

NOTE: The same can be accomplished by a correlated subquery, except for the display of the customer average in each customer row.

SELECT custid, quantity

FROM orders AS o1

WHERE quantity >

   (select avg(quantity)

    from orders o2

    where o1.custid=o2.custid);

1.66666666666667

1.75

3.2

2.4

ADDITIONAL OPERATORS

EXIST

EXAMPLE 8.1.1: List the customer names of the customers who have not ordered product p1.

NOTE: Exists is true if the subquery returns any row; not exists is true if the subquery does not return any rows.

The same can be accomplished using the not operator.

               select custname

               from customers

               where custid not in

                              (select custid

                              from orders

                              where prodid='p1')

select custname

from customers c

where not exists

(select *

from orders o

where c.custid=o.custid and prodid='p1');

ex8-1-1
custname
Baker
Evans

EXAMPLE 8.1.2: List the prodid’s of all the products not ordered by customer c2

select prodid

from products p

where not exists

(select *

from orders o

where p.prodid=o.prodid and custid='c2')

ex8-1-2
prodid
p1

EXAMPLE 8.1.3: List the name(s) and id(s) of any customer who has ordered every product.

NOTE: It helps to reword the query as follows: List the customer name and

id for those customers for whom

there is NO product they have NOT

ordered.

select custname,custid

from customers c

where not exists

(select *

from products p

where not exists

(select *

from orders o

where o.custid=c.custid and o.prodid=p.prodid ))

ex8-1-3
custname	custid
Able	c1
Crane	c3
Doe	c4

EXAMPLE 8.2.1: List the customer id of customers ordering product p1 or p3.

select distinct custid, prodid

from orders

where prodid in (‘p1’,’p3’);

ex8-2-1
custid	prodid
c1	p1
c1	p3
c2	p3
c3	p1
c3	p3
c4	p1
c4	p3

NOT

EXAMPLE 8.3.1: List the products not ordered by customer “c2”..

select prodid

NOTE: The non-correlated subquery returns the the id’s of the products that were ordered by “c2”. The outer query, selects all the product id’s that are not in the set of id’s ordered by “c2”.

In effect, the id’s selected in the subquery are SUBTRACTED from all the id’s in the products table.

prodid=’p3’

from products

where prodid not in

(select prodid

from orders

where custid='c2')

Query1
prodid
p1

LIKE

EXAMPLE 8.4.1: List the customers whose names end in “e”.”..

SELECT *

FROM customers

WHERE custname like "*e";

ex8-4-1
CUSTID	CUSTNAME	CREDITLIMIT
c1	Able	500
c3	Crane	1000
c4	Doe	500

EXAMPLE 8.4.2: List the customers whose names have an “a”.

SELECT *

FROM customers

WHERE custname like "*a*";

ex8-4-2
CUSTID	CUSTNAME	CREDITLIMIT
c1	Able	500
c2	Baker	1000
c3	Crane	1000
c5	Evans	500

EXAMPLE 8.4.3: List the customers whose names have an “e” as their 4th letter.

SELECT *

FROM customers

WHERE custname like "???e*";

ex8-4-3
CUSTID	CUSTNAME	CREDITLIMIT
c1	Able	500
c2	Baker	1000

BETWEEN

EXAMPLE 8.5.1: List the orders with quantities >=4 and <=8.

SELECT *

FROM orders

WHERE quantity between 4 and 8;

ex8-5-1
ORDERID	LINENUM	CUSTID	PRODID	QUANTITY
5	1	c1	p3	4
5	2	c1	p4	8
6	3	c3	p2	5

ALL

EXAMPLE 8.6.1: List the creditlimit with the most customers.

select creditlimit,count(*) as count

from customers c

group by creditlimit

having count(*) >=all

(select count(*)

from customers c

group by creditlimit)

ex8-6-1
creditlimit	count
500	3

ANY

EXAMPLE 8.7.1: List the products with quantities greater than any quantities of product “p4”.

NOTE: The same can be accomplished without the any operator.

SELECT prodid, quantity

FROM orders

WHERE prodid <>'p4' and quantity >

   (select min(quantity)

    from orders

    where prodid='p4');

select prodid,quantity

from orders

where prodid <>'p4' and quantity >any

(select quantity

from orders

where prodid='p4')

ex8-7-1
prodid	quantity
p3	4
p1	3
p2	5

EXAMPLE 8.7.2: List the products with average quantities greater than either of the average quantities of products “p2” or “p3”.

SELECT prodid, avg(quantity) as avg_qty

FROM orders

WHERE prodid <>'p3' and prodid <> 'p2'

group by prodid

having avg(quantity) > any

(select avg(quantity)

from orders

where prodid='p3' or prodid='p2'

group by prodid)

ex8-7-2
prodid	avg_qty
p4	3.75

UNION

EXAMPLE 8.8.1: List all the products followed by all the orders.

select prodid,quantity as QuantityOrProdname

from orders

NOTE: A union occurs when the rows created by the second select statement are added (not joined) to the rows created by the first select statement. The second select statement must have a column corresponding to each column in the first select statement. In some SQL engines the corresponding columns must be the same data type, but that is not the case with MS Access SQL – in this example the prodname column is text while the corresponding quantity column is number. The column names in the output are determined by the first select statement. Only one order statement is permitted and appears at the end.

UNION

select prodid,prodname

from products

ORDER BY QuantityOrProdname DESC;

ex8-8-1
prodid	QuantityOrProdname
p1	ritz crkrs
p2	krft mayo
p3	dawn dish
p4	bert evo
p4	8
p2	5
p3	4
p1	3
p4	3
p2	2
p3	2
p4	2
p1	1
p2	1
p3	1

EXAMPLE 8.8.2: List the total quantity of each product ordered by each customer. Include a final row stating the grand total quantity ordered.

select custid,prodid,sum(quantity) as totqty

Rectangular Callout: NOTE : The “xx” prefix is used to sort this row to the end of the output. This assumes that there is no custid that begins with y or z. from orders

group by custid,prodid

UNION

select 'xxGrandTotal',' ',sum(quantity)

from orders

order by custid;

ex8-8-2
custid	prodid	totqty
c1	p1	1
c1	p2	1
c1	p3	6
c1	p4	8
c2	p2	2
c2	p3	1
c2	p4	2
c3	p1	3
c3	p2	6
c3	p3	1
c3	p4	2
c4	p1	1
c4	p2	2
c4	p3	1
c4	p4	3
xxGrandTotal		40

ISNULL

EXAMPLE 8.9.1: List the products with a null value for price.

select *

from products

where isnull(prodprice)

NOTE: A value of zero is not a null value.

ex8-9-1
PRODID	PRODNAME	PRODPRICE
p5	nath franks

TOP

EXAMPLE 8.10.1: List the top 2 highest priced products..

SELECT TOP 2 prodname, prodprice

FROM products

ORDER BY prodprice DESC;

ex8-10-1
prodname	prodprice
bert evo	18.95
ritz crkrs	3.19

EXAMPLE 8.10.2: List the top 50 percent highest priced products..

SELECT TOP 50 percent prodname, prodprice

FROM products

ORDER BY prodprice DESC;

ex8-10-2
prodname	prodprice
bert evo	18.95
ritz crkrs	3.19
krft mayo	2.39

EXAMPLE 8.10.3: List the top 3 orders with highest total quantity ordered.

select top 3 orderid,sum(quantity) as tot_qty

from orders

group by orderid

order by sum(quantity) desc

ex8-10-3
orderid	tot_qty
5	12
6	10
4	7

PARAMETERS

EXAMPLE 10.1: List an individual customer name and credit limit using a parameter to determine the customer name.

select custname,creditlimit

from customers

where custname=[enter cust name]

Oval Callout: Operator enters any customer name.

ex10-1
custname	creditlimit
Crane	1000

VIEWS/VIRTUAL TABLES

EXAMPLE11.1: List the average line quantity per customer.

select custid,avg(quantity) as AVG_QTY

NOTE: The query is saved using the name ex11_1. A query becomes a view that can be used as a virtual table. With some exceptions, a virtual table can be used as if it were an actual table.

from orders

group by custid

Ex11_1
custid	AVG_QTY
c1	3.2
c2	1.67
c3	2.4
c4	1.75

EXAMPLE 11.2: List the average line quantity per customer using a virtual table..

Oval Callout: Using a virtual table select *

From ex11_1

Ex11-2
custid	AVG_QTY
c1	3.2
c2	1.67
c3	2.4
c4	1.75

EXAMPLE 11.3: Same as example 7.2.1, except using a virtual table - List the line items, for each customer, whose quantity exceeds the average line quantity for that customer -

Oval Callout: Joining an actual table to a virtual table. This approach may be more efficient than a correlated subquery.

select custid,linenum,quantity

from orders o, ex11_1 e

where o.custid=e.custid and o.quantity > e.avg_qty

Ex11-3

custid

linenum

quantity

NOTE: The effect of the join is to place the average line quantity per customer next to each customer’s line item.

2

*Orders* joined with ex11_1
ORDERID	LINENUM	o.custid	PRODID	QUANTITY	e.custid	AVG_QTY
1	1	c1	p2	1	c1	1.33
1	2	c1	p3	2	c1	1.33
1	3	c1	p1	1	c1	1.33
2	1	c2	p4	2	c2	1.67
2	2	c2	p2	2	c2	1.67
2	3	c2	p3	1	c2	1.67
3	1	c3	p2	1	c3	1
3	2	c3	p3	1	c3	1
4	1	c4	p2	2	c4	1.75
4	2	c4	p1	1	c4	1.75
4	3	c4	p3	1	c4	1.75
4	4	c4	p4	3	c4	1.75

SQL ACTION STATEMENT EXAMPLES

The following database is used for the examples below.

VETERINARY.MDB

animal
animal_id	name	animal_type	age
a1	tom	t1	2
a2	lassie	t2	5
a3	tweetie	t3	1
a4	rex	t2	8
a5	fluffy	t1	4
a6	darius	t1	2
a7	pauly	t3	10

animalTypes
animal_type	description
t1	cat
t2	dog
t3	bird

illnesses
animal_id	date	illness_type
a1	5/12/1999	moderate
a3	5/18/1999	severe
a4	5/12/1999	severe
a5	5/10/1999	mild
a5	5/22/1999	severe
a6	5/15/1999	moderate
a7	5/20/1999	severe

UPDATE

Oval Callout: Note:Updating multiple fields EXAMPLE 1.1: Update a specific record.

update illnesses

set illness_type = 'severe', date_visit = '5/24/99'

where animal_id='a6';

EXAMPLE 1.2: Update every record of a table.

update illnesses

set illness_type = 'moderate';

Oval Callout: Field names

INSERT

EXAMPLE 2.1:Insert a single record.

insert into illnesses (animal_id, date_visit, illness_type)

Oval Callout: Values correspond to field names
Note:date() is a function that returns the current date. values('a1',date(),'severe');

EXAMPLE 2.2: Insert a set of records from another table

insert into illnesses

select *

from temp;

DELETE

EXAMPLE 3.1: Delete all records.

delete *

from illnesses;

EXAMPLE 3.2: Delete a specific record.

delete

from illnesses

where animal_id in ('a5','a6','a7');

SELECT INTO

EXAMPLE 4.1: Create a new physical table.

select * into illnesses_bup

from illnesses;

PARAMETERS

WEB APPLICATION

(Hosted Sql Statements)

EXAMPLE 1: Display SQL generated data.

<%@ Page Language="C#" Debug="true" %>

NOTE: This is an ASP.NET (.aspx) document containing a web program called a script that is written in C#. The document is processed by a Web Server such as Microsoft’s IIS.

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.OleDb" %>

<%@ OutputCache Duration=1 VaryByParam="none" %>

<script language="c#" runat="server" debug="true">

void Page_Load(object sender, EventArgs e)

{

OleDbConnection conn;

string strconn,SQLcmd;

strconn="Provider=Microsoft.Jet.OLEDB.4.0;";

strconn+="data Source=c:\\inetpub\\wwwroot\\student2.mdb";

conn=new OleDbConnection(strconn);

conn.Open();

SQLcmd="select * from classes";

OleDbCommand cmd=new OleDbCommand(SQLcmd,conn);

dgd1.DataSource=cmd.ExecuteReader();

dgd1.DataBind();

conn.Close();

Oval Callout: Data from SQL command is displayed in a grid. }

</script>

<html>

<head>

<title> EXAMPLE OF WEB SCRIPT WITH SQL</title>

</head>

<body>

<asp:DataGrid id="dgd1" runat="server" backcolor="green" forecolor="yellow"/><br>

</body>

</html>

EXAMPLE 2: Look up records using SQL.

<%@ Page Language="C#" Debug="true" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.OleDb" %>

<%@ OutputCache Duration=1 VaryByParam="none" %>

<script language="c#" runat="server" debug="true">

NOTE: This script looks up student records based on a student id supplied by the user via a text box.

void Page_Load(object sender, EventArgs e)

{

}

void FindId(object sender, EventArgs e)

{

OleDbConnection conn;

string strconn,SQLcmd;

strconn="Provider=Microsoft.Jet.OLEDB.4.0;";

strconn+="data Source=c:\\inetpub\\wwwroot\\student2.mdb";

conn=new OleDbConnection(strconn);

conn.Open();

SQLcmd="select * from student where sid= " + "'"+txtid.Text +"'";

OleDbCommand cmd=new OleDbCommand(SQLcmd,conn);

dgd1.DataSource=cmd.ExecuteReader();

dgd1.DataBind();

Student id as a value of a text box which is named txtid.

conn.Close();

}

</script>

<html>

<head>

<title> EXAMPLE OF WEB SCRIPT WITH SQL</title>

</head>

<body>

<form id="form1" runat="server">

<br>

ENTER STUDENT ID

<BR>

<asp:textbox text="" runat="server" id="txtid" size=10/><br>

<asp:button text="Find id" runat="server" id="b1" onclick=FindId /><br>

<br>

<asp:DataGrid id="dgd1" runat="server" backcolor="green" forecolor="yellow"/><br>

</form>

</body>

</html>

EXAMPLE 3: Executing queries as Stored Procedures.

<%@ Page Language="C#" Debug="true" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.OleDb" %>

<%@ OutputCache Duration=1 VaryByParam="none" %>

<script language="c#" runat="server" debug="true">

void Page_Load(object sender, EventArgs e)

{

}

void ExecStoredProc(object sender, EventArgs e)

{

string strconn;

OleDbConnection conn;

strconn="Provider=Microsoft.Jet.OLEDB.4.0;";

strconn+="data Source=c:\\inetpub\\wwwroot\\student2.mdb";

Oval Callout: A query stored in the database that acts as a stored procedure. The query is:
select *
from student conn=new OleDbConnection(strconn);

OleDbCommand cmd=new OleDbCommand("queryDisplayStudents",conn);

cmd.CommandType = CommandType.StoredProcedure;

conn.Open();

dgd1.DataSource=cmd.ExecuteReader();

dgd1.DataBind();

conn.Close();

}

</script>

<html>

<head>

<title> EXAMPLE OF WEB SCRIPT WITH STORED PROCEDURE</title>

</head>

<body>

<form id="form1" runat="server">

<br>

<BR>

<asp:button text="Execute Query (Stored Procedure)" runat="server" id="b2" onclick=ExecStoredProc /><br>

<br>

<asp:DataGrid id="dgd1" runat="server" backcolor="green" forecolor="yellow"/><br>

</form>

</body>

</html>

EXERCISES

The exercises below are based on the following database.

IPD.MDB

CUSTOMER
CUSTID	CUSTNAME	ADDRESS	COUNTRY	BEGBAL	CURRBAL
100	Watabe Bros	Box 241, Tokyo	Japan	45551	52113
101	Matzl	Salzburg	Austria	75314	77200
105	Jefferson	B 918, Chicago	USA	49333	57811
110	Gomez	Santiago	Chile	27400	35414

MANUFACTURER
MANUFID	MANUFNAME	ADDRESS	COUNTRY
210	Kiwi Clothes	Auckland	New Zealand
253	Brass Works	Lagos	Nigeria
317	Llama Llamps	Lima	Peru

PRODUCT
PRODID	PRODDESC	MANUFID	COST	PRICE
1035	Sweater	210	1.25	2
2241	Table Lamp	317	2.25	3.25
2249	Table Lamp	317	3.55	4.8
2518	Brass Sculpture	253	0.6	1.2

SALE
DATE	CUSTID	SALID	PRODID	QTY
2/1/2003	101	23	2249	75
2/2/2003	101	23	1035	200
2/4/2003	101	23	2241	250
2/5/2003	105	10	2241	100
2/12/2003	101	23	2518	300
2/14/2003	105	10	2249	50
2/15/2003	101	23	1035	150
2/19/2003	100	39	2518	200
2/22/2003	110	37	2518	150
2/28/2003	100	10	2241	200

SALESPERSON
SALID	SALNAME	MGRID	OFFICE	COMM
10	Rodney Jones	27	Chicago	10
12	Buster Sanchez	27	B. A.	10
14	Masaji Matsu	44	Tokyo	11
23	Francois Moire	35	Brussels	9
27	Terry Gordon		Chicago	15
35	Brigit Bovary	27	Brussels	11
37	Elena Hermana	12	B. A.	13
39	Goro Azuma	44	Tokyo	10
44	Albert Ige	27	Tokyo	12

SIMPLE QUERIES

Exercise 1.0

List the customer id and difference between beginning balance and cuurrent balance for each customer.

JOINS

Exercise 2.0

List each customer’s sales, displaying the customer name instead of customer id.

Exercise 2.1

List the customer’s name and the product description of each sale.

Exercise 2.2

List the customer’s name and the manufacturer’s name of each product per customer sale.

Exercise 2.3

List the customer country of each customer who purchased products manufactured by Brass Works.

Note: Do it 2 ways:

1. Use a join;

2. Use a non-correlated subquery.

Exercise 2.4

List the name of each salesperson’s manager. Display the salesperson’s name and the

manager’s name.

Exercise 2.5

Same as exercise 2.4, except use a left join.

AGGREGATE FUNCTIONS

Exercise 3.0

List the total number of sales transactions.

Exercise 3.1

List the total quantity of products sold.

Exercise 3.2

List the total sales amount (dollars) of all sales.

Exercise 3.3

Display the total gross profit (total sales minus total cost).

Exercise 3.4

Display the percentage of total cost to total sales.

GROUP

Exercise 4.0

Display the number of sales per customer id.

Exercise 4.1

Display the number of products per manufacturer name.

Exercise 4.2

Display the total sales dollars per customer id.

Exercise 4.3

Display the total quantity sold per product id – order from highest quantity to lowest quantity.

Exercise 4.4

Display the total quantity sold per customer id per product id.

Exercise 4.5

Display the total sales dollars per product id.

Exercise 4.6

Display the total sales dollars per salesperson – display both the name and id of the salesperson...

Exercise 4.7

Display the total sales dollars per customer per salesperson – display the salesperson id

and customer id.

Exercise 4.8

List customers with more than 2 sales. Display the customer id and count.

Exercise 4.9

CORRELATED SUB QUERY

Exercise 5.0

For each product, select the customer who has purchased the most quantity. Display the customer id, product id and quantity.

Exercise 5.1

List each customer and the largest quantity they have purchased. Display the customer id, product id, and quantity.

Exercise 5.2

Select the salespersons that make the lowest commission in each office. Display the salesperson name, the office, and the commission.

SUBTRACTION

Exercise 6.0

List the customers who have not purchased product 2241.

Exercise 6.1

List all the products that customer 105 has NOT purchased. Display the product id. and the product description.

“ALL” OPERATOR

Exercise 7.0

Display the sales id of the salesperson that has sold the most in terms of quantity.

Exercise 7.1

Which manager manages the most salespersons? Display the manager id and count.

EXERCISE ANSWERS

SIMPLE QUERIES

Exercise 1.0

List the customer id and difference between beginning balance and cuurrent balance for each customer.

SELECT custid, begbal-currbal as DIFFERENCE

FROM customer;

JOINS

Exercise 2.0

List each customer’s sales, displaying the customer name instead of customer id.

SELECT custname, date, salid, prodid, qty

FROM customer AS c, sale AS s

WHERE c.custid=s.custid;

Exercise 2.1

List the customer’s name and the product description of each sale.

SELECT custname, proddesc

FROM customer AS c, sale AS s, product AS p

WHERE c.custid=s.custid and s.prodid=p.prodid;

Exercise 2.2

List the customer’s name and the manufacturer’s name of each product per customer sale.

SELECT custname, manufname

FROM customer AS c, sale AS s, product AS p, manufacturer AS m

WHERE c.custid=s.custid and s.prodid=p.prodid and p.manufid=m.manufid;

Exercise 2.3

List the customer country of each customer who purchased products manufactured by Brass Works.

Note: Do it 2 ways:

1. Use a join;

2. Use a non-correlated subquery.

SELECT c.country

FROM customer AS c, sale AS s, product AS p, manufacturer AS m

WHERE c.custid=s.custid and s.prodid=p.prodid and p.manufid=m.manufid

and manufname='BRASS WORKS'

ORDER BY c.country;

SELECT country

FROM customer

WHERE custid in

(select custid

from sale

where prodid in

(select prodid

from product

where manufid in

(select manufid

from manufacturer

where manufname='brass works')))

ORDER BY country;

Exercise 2.4

List the name of each salesperson’s manager. Display the salesperson’s name and the

manager’s name.

SELECT s.salname, m.salname

FROM salesperson AS s, salesperson AS m

WHERE s.mgrid=m.salid;

Exercise 2.5

Same as exercise 2.4, except use a left join.

SELECT s.salname, m.salname

FROM salesperson AS s LEFT JOIN salesperson AS m ON s.mgrid=m.salid;

AGGREGATE FUNCTIONS

Exercise 3.0

List the total number of sales transactions.

SELECT count(*)

FROM sale;

Exercise 3.1

List the total quantity of products sold.

SELECT sum(qty) AS totQtySold

FROM sale;

Exercise 3.2

List the total sales amount (dollars) of all sales.

SELECT sum(qty*price) AS totSales

FROM sale AS s, product AS p

WHERE s.prodid=p.prodid;

Exercise 3.3

Display the total gross profit (total sales minus total cost).

SELECT (sum(qty*price) - sum(qty*cost) ) AS [TOTAL GROSS PROFIT]

FROM sale AS s, product AS p

WHERE s.prodid=p.prodid;

Exercise 3.4

Display the percentage of total cost to total sales.

SELECT round(sum(qty*cost)/sum(qty*price)* 100,0 ) AS [% COST TO SALES]

FROM sale AS s, product AS p

WHERE s.prodid=p.prodid;

GROUP

Exercise 4.0

Display the number of sales per customer id.

SELECT custid, count(*) AS numSales

FROM sale

GROUP BY custid;

Exercise 4.1

Display the number of products per manufacturer name.

SELECT manufname, count(*) AS numProducts

FROM manufacturer AS m, product AS p

WHERE m.manufid=p.manufid

GROUP BY manufname;

Exercise 4.2

Display the total sales dollars per customer id.

SELECT custid, sum(qty*price) AS totSalesAmt

FROM sale AS s, product AS p

WHERE s.prodid=p.prodid

GROUP BY custid;

Exercise 4.3

Display the total quantity sold per product id – order from highest quantity to lowest quantity.

SELECT prodid, sum(qty) AS totQty

FROM sale

GROUP BY prodid

ORDER BY sum(qty) DESC;

Exercise 4.4

Display the total quantity sold of customer id within product id.

SELECT prodid, custid, sum(qty) AS totQty

FROM sale

GROUP BY prodid, custid

ORDER BY prodid;

Exercise 4.5

Display the total sales dollars per product id.

SELECT s.prodid, sum(qty*price) AS totSalesAmt

FROM sale AS s, product AS p

WHERE s.prodid=p.prodid

GROUP BY s.prodid

ORDER BY sum(qty) DESC;

Exercise 4.6

Display the total sales dollars per salesperson – display both the name and id of the salesperson...

SELECT s.salid, salname, sum(qty*price) AS totSalesAmt

FROM salesperson AS sp, sale AS s, product AS p

WHERE sp.salid=s.salid and s.prodid=p.prodid

GROUP BY s.salid, salname;

Exercise 4.7

Display the total sales dollars of customer within salesperson – display the salesperson id,

customer id, and total.

SELECT s.salid, custid, sum(qty*price) AS totSalesAmt

FROM salesperson AS sp, sale AS s, product AS p

WHERE sp.salid=s.salid and s.prodid=p.prodid

GROUP BY s.salid, custid;

Oval Callout: Group by/having

Exercise 4.8

List customers with more than 2 sales. Display the customer id and count.

SELECT custid, count(*)

FROM sale

GROUP BY custid

HAVING count(*) >2;

CORRELATED SUB QUERY

Exercise 5.0

For each product, select the customer who has purchased the most quantity. Display the customer id, product id and quantity.

SELECT custid, prodid, qty

FROM sale AS s1

WHERE qty=

(select max(qty)

from sale s2

where s1.prodid=s2.prodid);

Exercise 5.1

List each customer and the largest quantity they have purchased. Display the customer id, product id, and quantity.

SELECT custid, prodid, qty

FROM sale AS s1

WHERE qty=

(select max(qty)

from sale s2

where s1.custid=s2.custid);

Exercise 5.2

Select the salespersons that make the lowest commission in each office. Display the salesperson name, the office, and the commission.

SELECT salname, office, comm

FROM salesperson AS s1

WHERE comm=

(select min(comm)

from salesperson s2

where s1.office=s2.office);

SUBTRACTION

Exercise 6.0

List the customers who have not purchased product 2241.

SELECT custname

FROM customer

WHERE custid not in

(select custid

from sale

where prodid='2241');

Exercise 6.1

List all the products that customer 105 has NOT purchased. Display the product id. and the product description.

SELECT prodid, proddesc

FROM product

WHERE prodid not in

(select prodid

from sale

where custid='105');

“ALL” OPERATOR

Exercise 7.0

Display the sales id of the salesperson that has sold the most in terms of quantity.

SELECT salid, sum(qty) AS totQty

FROM sale

GROUP BY salid

HAVING sum(qty) >= all

(select sum(qty)

from sale

group by salid);

Exercise 7.1

Which manager manages the most salespersons? Display the manager id and count.

SELECT s.mgrid, count(*)

FROM salesperson AS s, salesperson AS m

WHERE s.mgrid=m.salid

GROUP BY s.mgrid

HAVING count(*) >=all

(select count(*)

FROM salesperson AS s, salesperson AS m

WHERE s.mgrid=m.salid

group by s.mgrid);