4.4: Selection Query

Last updated
Save as PDF

Page ID: 15513

\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

Suppose we want a list of paperbacks. That is, we want to list information about books where the paperback field has a value Yes. Requirements like this are placed on the criteria line of the pertinent field(s).

To develop this query we need to select the Book table and then add its’ fields to the grid. For the paperback field we also enter the value Yes on the criteria line:

Figure \(\PageIndex{1}\): Query with selection criteria.

When we run the query we get results listing paperbacks:

Table \(\PageIndex{1}\): Query results.
callNo	title	author	paperback
CB 351 M293 1983	Atlas of medieval Europe	Donald Matthew	True
PC 14 V48 1965	Medieval miscellany	Frederick Whitehead	True
QA 76.73 S67C46 1997	Joe Celko's SQL puzzles & answers	Joe Celko	True
QA 76.76 A65P76 2011	Programming Android	Zigurd R Mednieks	True
QA 76.9 D26H355 2008	Information modeling and relational databases	T A Halpin	True
QA 76.9 D26H39 1996	Data model patterns : conventions of thought	David Hay	True

When a query runs, the query processor accesses the underlying table(s), and displays results where the data meets the criteria specified. For a query accessing a single table consider that the query processor is performing these actions:

For each row in the table:

retrieve the row from the database
test the row to see if it meets the criteria specified
- if the row meets the criteria then display the fields marked for show

Save your query as paperbacksQuery. This is a selection query that selects, according to criteria, specific rows for display; this type of query produces a horizontal subset of a table.

Most queries are a combination of selection and projection. It is typically the case that queries will select a subset of fields for display and criteria will be needed to constrain the rows retrieved.