Single table queries

Physical modelThe SELECT command retrieves the data contained in a database. This topic looks at querying a single table.

 

 

Single table queries


Ch 6 pp 295-310

Two readings are provided to help you understand single table queries.

  1. The first reading from your textbook Processing Single Tables details
    some of the power of the SELECT statement.

  2. Select summary
    The additional reading SELECT summary gives a one page summary
    of the SELECT statement.

 

 

Getting your hands dirty


INVOICE databaseInvoice Database

Log on to Oracle and try these queries on the INVOICE database.

You may find it a lot quicker to design your queries with pen and paper to ensure correct syntax rather than typing them straight into your text editor.

1. Find all the customers in NSW Your query should return

CutsName
Adams Tractors
Mellow Yellow Lighting
Johnson Tyler Consultatns
Crabtree Suits

2. Find the description and price of items valued at $600 or more Your query should return

ITEMDESC ITEMPRICE
Dishwasher Freestyle 650
Dishwasher Virtuoso 750
Frosty Freeze 650
Fridge Right 670
CoolRoom 899
Family Freeze 655
Family Freeze Deluxe 899
16 cm Thin Tube 600
20 cm Thin Tube 899
National 16 cm 650
National 18 cm 690
National 20 cm 900
EarWorks Family 600
EarWorks Deluxe 780
EarWorks Super 980
SpinWash 760

3. Repeat the query from 2 but this time sort the output by the items description Your query should return

ITEMDESC ITEMPRICE
16 cm Thin Tube 600
20 cm Thin Tube 899
CoolRoom 899
Dishwasher Freestyle 650
Dishwasher Virtuoso 750
EarWorks Deluxe 780
EarWorks Family 600
EarWorks Super 980
Family Freeze 655
Family Freeze Deluxe 899
FridgeRight 670
Frosty Freeze 650
National 16 cm 650
National 18 cm 690
National 20 cm 900
SpinWash 760

4. Find the number of items Your query should return

COUNT(*)
     36

5. Find the average price of an item Your query should return

AVG(ITEMPRICE)
    536.666667

6. Find the sum of all the item prices Your query should return

SUM(ITEMPRICE)
        19320

7. Find the number of customers in each state Your query should return

STA COUNT(CUSTSTATE)
ACT 1
NSW 4
NT 2
Qld 4
SA 3
Tas 2
Vic 3
WA 2

8. Find the number of customers in each state that has three or more customers Your query should return

STA COUNT(CUSTSTATE)
NSW 4
Qld 4
SA 3
Vic 3

9. List all the items that have Screen or Tube in their name Your query should return

ITEMNO ITEMDESC ITEMPRICE
1300-2201 Big Screen 299
1300-2202 Wide Screen 455
1300-5000 10 cm ThinTube 233
1300-5001 12 cm ThinTube 322
1300-5002 14 cm Thin Tube 450
1300-5003 16 cm Thin Tube 600
1300-5004 20 cm Thin Tube 899

10. List all the items that have Screen or Tube in their name and are valued at over $400 Your query should return

ITEMNO ITEMDESC ITEMPRICE
1300-2202 Wide Screen 455
1300-5002 14 cm Thin Tube 450
1300-5003 16 cm Thin Tube 600
1300-5004 20 cm Thin Tube 899

Check (Invoice Database )


1. Find all the customers in NSW Your query should return

CUSTNAME
Adams Tractors
Mellow Yellow Lighting
Johnson Tyler Consultatns
Crabtree Suits

View the answer

SELECT CustName AS Name
FROM Customer
WHERE CustState = 'NSW';

Here, an alias was used as an alternative for the column name CustName to help with readability. This is especially good when abbreviated terms have been used in tables. Clearly answers without an alias are still correct.

2. Find the description and price of items valued at $600 or more Your query should return

ITEMDESC ITEMPRICE
Dishwasher Freestyle 650
Dishwasher Virtuoso 750
Frosty Freeze 650
Fridge Right 670
CoolRoom 899
Family Freeze 655
Family Freeze Deluxe 899
16 cm Thin Tube 600
20 cm Thin Tube 899
National 16 cm 650
National 18 cm 690
National 20 cm 900
EarWorks Family 600
EarWorks Deluxe 780
EarWorks Super 980
SpinWash 760

View the answer

SELECT ItemDesc AS Product, ItemPrice AS Price
FROM Item
WHERE ItemPrice >= 600;

Here >= rather than > is used to include 600.

3. Repeat the query from 2 but this time sort the output by the item's description Your query should return

ITEMDESC ITEMPRICE
16 cm Thin Tube 600
20 cm Thin Tube 899
CoolRoom 899
Dishwasher Freestyle 650
Dishwasher Virtuoso 750
EarWorks Deluxe 780
EarWorks Family 600
EarWorks Super 980
Family Freeze 655
Family Freeze Deluxe 899
FridgeRight 670
Frosty Freeze 650
National 16 cm 650
National 18 cm 690
National 20 cm 900
SpinWash 760

View the answer

SELECT ItemDesc AS Product, ItemPrice AS Price
FROM Item
WHERE ItemPrice >= 600
ORDER BY ItemDesc;

4. Find the number of items Your query should return

COUNT(*)
     36

View the answer

SELECT COUNT(*) FROM ITEM;

5. Find the average price of an item Your query should return

AVG(ITEMPRICE)
    536.666667

View the answer

SELECT AVG(ItemPrice) FROM ITEM;

6. Find the sum of all the item prices Your query should return

SUM(ITEMPRICE)
        19320

View the answer

SELECT SUM(ItemPrice) FROM ITEM;

7. Find the number of customers in each state Your query should return

STA COUNT(CUSTSTATE)
ACT 1
NSW 4
NT 2
Qld 4
SA 3
Tas 2
Vic 3
WA 2

View the answer

SELECT CustState AS State, COUNT(CustState)
FROM CUSTOMER
GROUP BY CustState;

8. Find the number of customers in each state that has three or more customers Your query should return

STA COUNT(CUSTSTATE)
NSW 4
Qld 4
SA 3
Vic 3

View the answer

SELECT CustState AS State, COUNT(CustState)
FROM CUSTOMER
GROUP BY CustState
HAVING COUNT(CustState) >= 3;

9. List all the items that have Screen or Tube in their name Your query should return

ITEMNO ITEMDESC ITEMPRICE
1300-2201 Big Screen 299
1300-2202 Wide Screen 455
1300-5000 10 cm ThinTube 233
1300-5001 12 cm ThinTube 322
1300-5002 14 cm Thin Tube 450
1300-5003 16 cm Thin Tube 600
1300-5004 20 cm Thin Tube 899

View the answer

SELECT * FROM ITEM
WHERE (ItemDesc LIKE '%Screen%'
OR ItemDesc LIKE '%Tube%');

10. List all the items that have Screen or Tube in their name and are valued at over $400 Your query should return

ITEMNO ITEMDESC ITEMPRICE
1300-2202 Wide Screen 455
1300-5002 14 cm Thin Tube 450
1300-5003 16 cm Thin Tube 600
1300-5004 20 cm Thin Tube 899

View the answer

SELECT * FROM ITEM
WHERE (ItemDesc LIKE '%Screen%'
OR ItemDesc LIKE '%Tube%')
AND ItemPrice > 400;

Download the questions and answers

Your Turn

Now try the following queries on the INVOICE database.

These activities relate to the PowerPoint Presentation “Multi-table queries” refer to “Your turn”

1. The number of different items on each invoice (each item has a new line)Your query should return

INVOICENO COUNT(*)
3 3
4 2
5 7
6 6
7 3
8 3
9 4
10 5
11 3
12 3
13 6
14 6
15 1
16 4
17 4
18 5
19 1
20 2
21 10
22 3

 

2. The maximum number sold for each item Your query should return

ITEMNO MAX(QUANTITY)
1100-2301 5
1100-2302 5
1100-2303 3
1200-2234 5
1200-2236 5
1200-2250 4
1200-2251 4
1200-2252 10
1200-2255 3
1200-2256 3
1300-2200 4
1300-2201 5
1300-2202 7
1300-2205 5
1300-5000 7
1300-5001 7
1300-5002 5
1300-5003 5
1300-5004 3
1300-6001 5
1300-6003 5
1300-6004 5
1300-6006 3
1400-2100 7
1400-2101 10
1400-2102 5
1400-2104 5
1400-3101 2
1400-3102 2
1400-3103 2
1500-2100 5
1500-2200 4
1500-2201 5
33 rows selected

3. The number of each item that has been sold Your query should return

ITEMNO SUM(QUANTITY)
1100-2301 12
1100-2302 18
1100-2303 11
1200-2234 8
1200-2236 15
1200-2250 7
1200-2251 9
1200-2252 29
1200-2255 3
1200-2256 6
1300-2200 4
1300-2201 11
1300-2202 14
1300-2205 8
1300-5000 7
1300-5001 7
1300-5002 5
1300-5003 5
1300-5004 5
1300-6001 5
1300-6003 5
1300-6004 5
1300-6006 3
1400-2100 7
1400-2101 13
1400-2102 8
1400-2104 10
1400-3101 2
1400-3102 2
1400-3103 2
1500-2100 27
1500-2200 14
1500-2201 21
33 rows selected

4. The maximum number of any item sold on a single invoice Your query should return

ITEMNO MAX(QUANTITY)
3 5
4 3
5 5
6 4
7 10
8 3
9 5
10 7
11 2
12 5
13 3
14 5
15 10
16 5
17 7
18 3
19 7
20 3
21 5
22 4
20 rows selected

 

Check (your turn)

The Answers to these will be available in Week Tasks multiple table queries.
The specific day is mentioned in announcements.

 

Student Database

STUDENT databaseMastering SQL needs practice. If you want to test your understanding further try these examples using a student database. This is an optional activity.

View the Student Tables

Simple SELECT examples

1. Display the student information Your query should return

Student_no Name Total_Units Coord_No
12345678 Donald Duck 230 23
98765432 Mickey Mouse 120 45
56321478 Mother Goose 40 23

2. Display all student numbers and names Your query should return

Student_no Name
12345678 Donald Duck
98765432 Mickey Mouse
56321478 Mother Goose

3. What is the name, total number of units completed and coordinator number of student no 12345678? Your query should return

Name completed Coord_No
Donald Duck 230 23

4. What is the name and number of units student no 12345678 needs to graduate? Assume that a student needs 240 credit points. Your query should return

Name Left
Donald Duck 10

Using Conditional Operators and patterns

5. Which student numbers in HIT232 received HDs? Your query should return

Student_No Course_No Grade
12345678 HIT232 HD
98765432 HIT232 HD

6. Which students have completed less than 100 credit points or have coordinator no 45? Your query should return

Student_No Name Total_Units Coord_No
98765432 Mickey Mouse 120 45
56321478 Mother Goose 40 23

7. Find all coordinators whose employee number starts with AS1 Your query should return

Coord_No Name Employee_No
23 Fred Flinstone AS1234

Distinct and ALL

8. Display all coord numbers from the student table Your query should return

Coord_No
23
45
23

9. Display all unique coord numbers from the student table Your query should return

Coord_No
23
45

Order by

10. Which students have completed between 120 and 250 units. Order by the total units Your query should return

Student_No Name Total_Units Coord_No
98765432 Mickey Mouse 120 45
12345678 Donald Duck 230 23

Aggregate functions and grouping

11. Find the average no of units students with Coord_No 23 have completed Your query should return

AVG(Total_Units)
135

12. How many students does Coord_No 23 look after? Your query should return

COUNT(*)
2

13. How many coordinators are there that are currently advising students? Your query should return

COUNT(DISTINCT Coord_No)
2

14. Find the number of students for each coord no. Your query should return

Coord_No COUNT(*)
23 2
45 1

STUDENT databaseCheck (student database)


Click on each of the topics below to expand the relevant content.

Simple SELECT examples from one table

1. Display the student information Your query should return

Student_no Name Total_Units Coord_No
12345678 Donald Duck 230 23
98765432 Mickey Mouse 120 45
56321478 Mother Goose 40 23

View the answer

SELECT *
FROM Student;

Here * stands for all attributes. The following examples show how to get just some attributes.

2. Display all student numbers and names Your query should return

Student_no Name
12345678 Donald Duck
98765432 Mickey Mouse
56321478 Mother Goose

View the answer

SELECT student_no, name
FROM Student;

3. What is the name, total number of units completed and coordinator number of student no 12345678? Your query should return

Name completed Coord_No
Donald Duck 230 23

View the answer

SELECT name, total_units as completed, coord_no
FROM Student
WHERE student_no=12345678;

Notice how the column is renamed, i.e. the total_units column will be displayed as completed

4. What is the name and number of units student no 12345678 needs to graduate? Assume that a student needs 240 credit points. Your query should return

Name Left
Donald Duck 10

View the answer

SELECT name, 240 - Total_Units as Left
FROM Student
WHERE student_no=12345678;

Using Conditional Operators and patterns

5. Which student numbers in HIT232 received HDs? Your query should return

Student_No Course_No Grade
12345678 HIT232 HD
98765432 HIT232 HD

View the answer

SELECT *
FROM Grade
WHERE course_no='HIT232' AND grade='HD';

6. Which students have completed less than 100 credit points or have coordinator no 45? Your query should return

Student_No Name Total_Units Coord_No
98765432 Mickey Mouse 120 45
56321478 Mother Goose 40 23

View the answer

SELECT *
FROM Student
WHERE coord_no=45 OR total_units <100;

7. Find all coordinators whose employee number starts with AS1? Your query should return

Coord_No Name Employee_No
23 Fred Flinstone AS1234

View the answer

SELECT *
FROM Coordinator
WHERE Employee_No LIKE 'AS1%';

Where clauses can have conditions in which a string is compared with a pattern to see if it matches. The phrase used is:

     column LIKE pattern
or
     column NOT LIKE pattern

In the pattern % is used to match any string while _ matches any character

DISTINCT and ALL

8. Display all coord numbers form the student table Your query should return

Coord_No
23
45
23

Here the number 23 appears twice

View the answer

SELECT Coord_no
FROM Student;

Tables and results of SQL queries allow duplicates. The only exception is the set operations introduced in the third module.

SQL does not automatically eliminate duplicates as it is an expensive operation to implement (consider how it might be implemented). Further in some instances duplicate results may be required by the user or other SLQ statements (e.g. aggregate functions such as COUNT).

There is a keyword ALL that specifies duplicates should not be removed. As this is the default ALL is not usually used on the SELECT line. The following example is equivalent to above.

SELECT ALL Coord_no
FROM student;

9. Display all unique coord numbers from the student table Your query should return

Coord_No
23
45

View the answer

To eliminate duplicates use the word DISTINCT.

SELECT DISTINCT Coord_no
FROM Student;

ORDER BY

10. Which students have completed between 120 and 250 units? Order by the total units Your query should return

Student_No Name Total_Units Coord_No
98765432 Mickey Mouse 120 45
12345678 Donald Duck 230 23

View the answer

SELECT *
FROM Student
WHERE total_units BETWEEN 120 AND 250
ORDER BY total_units ASC;

The ASC was not necessary. To order in descending order the word DESC is used.

Data can be organised by one of the ORDER BY clause. The default if in ascending order and can be specified on multiple columns where the first column listed is the primary sequence, the second column is secondary etc.

Aggregate functions and grouping

SQL provides the SUM, AVG, COUNT, MIN and MAX functions that can be applied to a column in a SELECT clause to produce that aggregation on the column. For example:

11. Find the average no of units students with Coord_No 23 have completed Your query should return

AVG(Total_Units)
135

View the answer

SELECT AVG(Total_Units)
FROM Student
WHERE Coord_no = 23;

12. How many students does Coord_No 23 look after? Your query should return

COUNT(*)
2

View the answer

SELECT COUNT(*)
FROM Student
WHERE Coord_No=23;

SQL default behaviour in not removing duplicates works well in these examples. If duplicates need to be removed the DISTINCT keyword can be used to count the number of distinct values in a column.

13. How many coordinators are there that are currently advising students? Your query should return

COUNT(DISTINCT Coord_No)
2

View the answer

SELECT COUNT(DISTINCT Coord_No)
FROM Students;

This will return the number of coordinators that appear in the Student table. Note this is different to the query asking how many coordinators there are - for this query you could simply COUNT the number of rows in the Coordinator table.

14. Find the number of students for each coord no. Your query should return

Coord_No COUNT(*)
23 2
45 1

View the answer

SELECT coord_no, COUNT(*)
FROM Student
GROUP BY coord_no;

The aggregation functions can also be applied to subgroups of rows using the GROUP BY clause. The table that results from the SELECT-FROM-WHERE is grouped according to the values of the attributes in the GROUP BY clause and any aggregation is applied only within each group.

Download the questions and answers