## Database Programming :: Lessons :: SQL SELECT

### SQL Data Retrieval

SQL is a non-procedural langauge, which means it tells the DBMS what to get, but not how to get it. A SELECT statement is used to retrieve data from a database. THe general structure of a SELECT statement is as follows:

SELECT [DISTINCT | ALL] [ * | [list-of-attributes] FROM list-of-tables [WHERE condition] [GROUP BY column-list HAVING condition] [ORDER BY column-list] ;

We will use the following tables for the examples in this lesson.

SupplierNum | SupplierName | Status | City |
---|---|---|---|

S1 | Miller | 20 | Naperville |

S2 | Peters | 10 | Yorkville |

S3 | Hettel | 30 | Yorkville |

S4 | Rizio | 20 | Naperville |

S5 | Rossler | 30 | Oswego |

PartNum | PartName | Color | Weight |
---|---|---|---|

P1 | Nut | Red | 12 |

P2 | Bolt | Green | 17 |

P3 | Screw | Blue | 17 |

P4 | Screw | Red | 14 |

P5 | Cam | Blue | 12 |

P6 | Cog | Red | 19 |

SupplierNum | PartNum | Quantity |
---|---|---|

S1 | P1 | 300 |

S1 | P2 | 200 |

S1 | P3 | 400 |

S1 | P4 | 200 |

S1 | P5 | 100 |

S1 | P6 | 300 |

S2 | P1 | 300 |

S2 | P2 | 400 |

S3 | P2 | 200 |

S4 | P2 | 200 |

S4 | P4 | 300 |

S4 | P5 | 400 |

#### Example 1:

Get supplier name and status for suppliers in Yorkville. The FROM clause specifies which table(s) to select from and the WHERE clause specifies the conditions of the selection.

SELECT SupplierName, Status FROM Supplier WHERE CITY = 'Yorkville';

SupplierName | Status |
---|---|

Peters | 10 |

Hettel | 30 |

#### Example 2:

Get the part number for all parts supplied.

SELECT PartNum FROM PartsSupplied

PartNum |
---|

P1 |

P2 |

P3 |

P4 |

P5 |

P6 |

P1 |

P2 |

P2 |

P2 |

P4 |

P5 |

#### Example 3:

Get part numbers for all parts supplied with no duplicates. The DISTINCT clause limits the results to not include duplicates.

SELECT DISTINCT PartNum FROM PartsSupplied;

PartNum |
---|

P1 |

P2 |

P3 |

P4 |

P5 |

P6 |

#### Eaxmple 4:

Show the full details of all suppliers. The wildcard character (*) means all fields of the table(s) should be selected.

SELECT * FROM Supplier;

SupplierNum | SupplierName | Status | City |
---|---|---|---|

S1 | Miller | 20 | Naperville |

S2 | Peters | 10 | Yorkville |

S3 | Hettel | 30 | Yorkville |

S4 | Rizio | 20 | Naperville |

S5 | Rossler | 30 | Oswego |

#### Example 5:

List the supplier info for all suppliers in Yorkville with status greater than 20. You can use the <, >, <=, >=, =, or != for not equals.

SELECT * FROM Supplier WHERE City = 'Yorkville' AND Status > 20;

SupplierNum | SupplierName | Status | City |
---|---|---|---|

S3 | Hettel | 30 | Yorkville |

#### Example 6:

List the supplier number and status of all suppliers in Yorkville in descending order of status. The ORDER BY clause can be used to sort results using the ASC or DESC operator to sort in ascending or descending order.

SELECT SupplierNum, Status FROM Supplier WHERE City = 'Yorkville' ORDER BY Status DESC;

SupplierNum | Status |
---|---|

S3 | 30 |

S2 | 10 |

#### Example 7:

For each part supplied, get the part number and names of all the cities supplying that part. Note that using DISTINCT does not give us the correct results we are looking for as only the part number is distinct, not the pair. Be careful using DISTINCT in situations like this.

SELECT DISTINCT PartNum, City FROM PartsSupplied, Supplier WHERE PartsSupplied.SupplierNum = Supplier.SupplierNum;

PartNum | City |
---|---|

P1 | Naperville |

P2 | Yorkville |

P3 | Oswego |

P4 | Naperville |

P5 | Yorkville |

P6 | Naperville |

#### Example 8:

List the supplier numbers for all pairs of suppliers that are in the same city. You can assign aliases to table names so you don't have to use the full name again. We use less than instead of not equal to in the query since the pairs would show up twice if we used not equal to.

SELECT T1.SupplierNum, T2.SupplierNum FROM Supplier T1, Supplier T2 WHERE T1.City = T2.City AND T1.SupplierNum < T2.SupplierNum;

T1.SupplierNum | T2.SupplierNum |
---|---|

S1 | S4 |

S2 | S3 |

#### Example 9:

List the supplier name for suppliers who supply part P2:

SELECT DISTINCT SupplierName FROM Supplier, PartsSupplied WHERE Supplier.SupplierNum = PartsSupplied.SupplierNum AND PartsSupplied.PartNum = 'P2';

SupplierName |
---|

Miller |

Peters |

Hettel |

Rizio |

You could also use multiple-row subqueries to get the same results. The nested subquery is evaluated first.

SELECT DISTINCT SupplierName FROM Supplier WHERE SupplierNum IN (SELECT SupplierNum FROM PartsSupplied WHERE PartNum = 'P2');

You could also use the EXISTS operator that determines whether a condition is present in a subquery:

SELECT SupplierName FROM Supplier WHERE EXISTS (SELECT * FROM PartsSupplied WHERE PartsSupplied.SupplierNum = Supplier.SupplierNum AND PartNum = 'P2');

#### Example 10:

List the supplier name for supplies who supply at least one red part:

SELECT SupplierName FROM Supplier, PartsSupplied, Part WHERE Supplier.SupplierNum = PartsSupplied.SupplierNum AND PartsSupplied.PartNum = Part.PartNum AND Part.Color = 'Red';

SupplierName |
---|

Miller |

Peters |

Rizio |

The multiple-row subquery version:

SELECT SupplierName FROM Supplier WHERE SupplierNum IN (SELECT SupplierNum FROM PartsSupplied WHERE PartNum IN (SELECT PartNum FROM Part WHERE Color = 'Red'));

#### Example 11:

List the supplier numbers for suppliers who supply at least one part also supplied by supplier S2:

SELECT DISTINCT SupplierNum FROM PartsSupplied WHERE PartNum IN (SELECT PartNum FROM PartsSupplied WHERE SupplierNum = 'S2');

SupplierNum |
---|

S1 |

S3 |

S4 |

### Group Functions

Group functions perform certain operations on multiple rows:

SUM ( [DISTINCT | ALL ] numeric_column) AVG ( [DISTINCT | ALL ] numeric_column) COUNT ( * | [DISTINCT | ALL ] column) MAX ( [DISTINCT | ALL ] column) MIN ( [DISTINCT | ALL ] column)

The GROUP BY clause allows you to apply group functions to a subgroup of tuples in a relation. GROUP BY allows you to select a subgroup of tuples that have the same value for the grouping attribute(s). The grouping attributes must appear in the SELECT clause.

#### Group Example 1:

For each part, get the part number and total number of suppliers for the part:

SELECT PartNum, COUNT(*) FROM PartsSupplied GROUP BY PartNum;

PartNum | COUNT(*) |
---|---|

P1 | 2 |

P2 | 4 |

P3 | 1 |

P4 | 2 |

P5 | 2 |

P6 | 1 |

#### Group Example 2:

For each supplied part, get the part number and total quantity:

SELECT PartNum, SUM(Quantity) FROM PartsSupplied GROUP BY PartNum;

PartNum | SUM(Quantity) |
---|---|

P1 | 600 |

P2 | 100 |

P3 | 400 |

P4 | 500 |

P5 | 500 |

P6 | 100 |

#### Group Example 3:

Get the total number of suppliers:

SELECT COUNT(*) FROM Supplier;

COUNT |
---|

5 |

#### Group Example 4:

List the part number for all parts supplied by more than one supplier. The HAVING clause can be used to specify a condition on groups.

SELECT PartNum FROM PartsSupplied GROUP BY PartNum HAVING COUNT(*) > 1;

PartNum |
---|

P1 |

P2 |

P4 |

P6 |

#### Group Example 5:

Get the total number of suppliers currently supplying parts:

SELECT DISTINCT COUNT(SupplierNum) FROM PartsSupplied;

COUNT |
---|

4 |

#### Group Example 6:

Get the number of shipments for part P2:

SELECT COUNT(*) FROM PartsSupplied WHERE PartNum = 'P2';

COUNT |
---|

4 |

#### Group Example 7:

Get the total amount of part P2 being supplied:

SELECT SUM(Quantity) FROM PartsSupplied WHERE PartNum = 'P2';

COUNT |
---|

1000 |

### Single-Row Subqueries

A single-row subquery is used when the results of the outer query are based on a single, unknown value. A single-row subquery should return a result with only one row and one column.

#### Single-Row Subquery Example 1:

List the supplier number for all suppliers who are located in the same city as supplier S1. We need to use a single-row subquery since we don't know the city for supplier S1.

SELECT SupplierNum FROM Supplier WHERE City = (SELECT City FROM Supplier WHERE SupplierNum = 'S1');

SupplierNum |
---|

S1 |

S4 |

#### Single-Row Subquery Example 2:

Get the supplier number for suppliers whose status is less than the current maximum status:

SELECT SupplierNum FROM Supplier WHERE Status < (SELECT MAX(Status) FROM Supplier);

SupplierNum |
---|

S1 |

S2 |

S4 |

### String Matching

The LIKE operator allows you to match substrings of strings. A LIKE condition takes the following form:

column LIKE string-literal

The column must be a string column. The underscore (_) used in a LIKE condition stands for any single character while the percent sign (%) stands for any string of 0 or more characters.

#### String Matching Example 1:

Find the supplier number for all suppliers in cities without "ville" at the end of the name:

SELECT SupplierNum FROM Supplier WHERE City NOT LIKE '%ville';

SupplierNum |
---|

S5 |

#### String Matching Example 2:

Find the part number for part names that are 5 characters long or more and the fourth to last character is a "c:"

SELECT PartNum FROM Part WHERE PartName LIKE '%c____';

PartNum |
---|

P3 |

P4 |