Yorkville High School Computer Science

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.

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

Part

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

PartsSupplied

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