Yorkville High School Computer Science Department
Yorkville High School Computer Science Department on Facebook  Yorkville High School Computer Science Department Twitter Feed  Yorkville High School Computer Science Department on Instagram

Yorkville High School Computer Science

ASSIGNMENTS: Compiler Part 2 - December 3, 2018 :: Challenges 7 - December 7, 2018 :: Network App - December 14, 2018 >>

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
Yorkville High School Computer Science Department on Facebook Yorkville High School Computer Science Department Twitter Feed Yorkville High School Computer Science Department on Instagram