14.3.1 Functional Data Manipulation Language (FDML)

14.3.1.1 Querying

select

Function

select &rest selections &key all set-operation distinct from where flatp group-by having order-by database

: Selects data from database given the constraints specified. Returns a list of lists of record values as specified by args. By default, the records are each represented as lists of attribute values. The selections argument may be either db-identifiers or literal strings.

Database identifiers used inselect are conveniently specified using thesymbolic SQL[] syntax. This syntax is enabled by calling enable-sql-reader-syntax, and is only available in packages which inherit from the SQL package.

You should therefore create a new package which includes thesql package, before using the bracket syntax.

For a description of the symbolic SQL syntax see Section 14.5 on page 133. For example, the following is a potential query and result:

(select [person_id] [person surname] :from [person])
--> ((111 "Brown") (112 "Jones") (113 "Smith"))

In this example,[person_id],[person surname] and[person] are database-identifiers and evaluate to literal SQL. The result is a list of lists of attribute values. Conversely, consider

(select [surname] :from [person] :flatp t)
--> ("Brown" "Jones" "Smith")

In this case the result is a simple list of surname values because of the use of the flatp keyword. The flatp keyword only works when there is one column of data to return.

In this final example the:where keyword is used to specify a condition for returning selected values from the database.

(select [surname] :from [person] :where [= [person_id] 112])
-> (("Jones"))

print-query

Function

print-query query-exp &key titles formats sizes stream database

Prints a tabulated version of the records resulting from query-exp.

titles: A list of strings for using as column headings --nil means no column headings are used. It isnil by default.
formats: A list of format strings used to print each attribute --t means use~A, or~VA if sizes are provided or computed. It ist by default.
sizes: A list of field sizes for printing the attributes --t means compute minimum sizes. It ist by default.
stream: An output stream --t means use *standard-output*. It ist by default.

The following call prints two even columns of names and salaries:

(print-query [select [surname] [income] :from [person]]
             :titles '("NAME" "SALARY"))

NAME   SALARY
Brown  22000
Jones  45000
Smith  35000