Example SQL queries

The following are examples of some queries that you can issue directly against the Catasto data set. We cannot give you detailed help with SQL (Structured Query Language) beyond providing you with the names of some good currently available manuals. If you need further assistance with SQL, ask someone at your computer center. Sybase will not permit you to recode the values of variables from the data base, but you can select different groups of cases and variables and do some simple calculations. You can also download cases and variables for use in a spread sheet or statistical analysis program of your own.

Martin Gruber, Understanding SQL (SYBEX: San Francisco, 1990--ISBN 0895886448)

C.J. Date and Hugh Darwen, A Guide to the SQL Standard (Addison Wesley: Boston, 1993--ISBN 020155822X)

Martin Gruber, SQL Instant Reference (SYBEX: San Francisco, 1993--ISBN 0782111483)

Example 1

Show all variables for household heads in 1427-29 whose family name was “MACHIAVELL” (i.e. Machiavelli).

select * from catasto where family_name = “MACHIAVELL”

[For this operation, and those that follow, choose formatted records as the type of reception. The long number “stg-id” is an identification number used by the Sybex server that has no other significance.]

Example 2

Show the first name, patronymic, family name, sex , total assets, and bocche for household heads in 1427-29 whose family name was “MACHIAVELL” (i.e. Machiavelli), sort the list by patronymic.

select name, patronymic, family_name, sex, total, bocche from catasto where family_name = “MACHIAVELL” order by patronymic

Example 3

Count households listed in the catasto in 1427

select count(*) from catasto where series = 1

Example 4

Count households listed in the catasto, distinguishing between households headed by women and households headed by men

select count(*), sex from catasto where series = 1 group by sex

Example 5

Show the average wealth of households in 1427, distinguishing between households headed by women and households headed by men, and excluding households with 0 total wealth, or where the sex of the household head was uncertain.

select avg(total), sex from catasto where series = 1 and sex <> 0 and total <> 0 group by sex

Example 6

Count the number of household heads listed in various trades in 1427, distinguishing between men and women, and excluding households where the sex of the household head was uncertain

select count(*), trade, sex from catasto where series = 1 and sex <> 0 group by trade, sex

[Note that for this and the following operation you would need to increase the default maximum number of lines of output from 50 to 250]

Example 7

Show the average wealth-per-head in 1427, distinguishing among different trades, excluding households with 0 total wealth indicated, and female-headed or sex-unknown households, and rounding the average to two decimal places

select (round(avg(total/bocche), 2)), trade from catasto where series = 1 and total <> 0 and sex = 1 group by trade

Example 8

Download information on the name, patronymic, family_name, volume, page, trade, sex, age, marital_status, bocche, and total wealth of all German household heads living in Florence and listed in 1427

select name, patronymic, family_name, volume, page, trade, sex, age, marital_status, bocche, total from catasto where series = 1 and migration = 4

[For this operation increase the default number of records. You can use the 'download to spreadsheet' option to do a further analysis of the results on your own computer.]

The following is a schematic representation of the general logic and syntax of SQL commands; to read it correctly you must have a tables-capable browser.

select from catasto where order by [var. name] [or] group by[var. name]

[all variables]

or

[variable list of all variables to be used in output, separated by commas]

and/or

aggregate functions:

(count(var. name))
(max(var.name))
(min(var.name))
(sum(var.name))
(avg(var.name))

and/or

a calculation:

(total/bocche) [the total wealth divided by the number of bocche in the household]
(round(total/bocche, 2)) [total wealth/bocche rounded to two decimal points].

[conditions such as]

series = 1 sex = 2 family_name = "STROZZI";

[an alphanumeric value has to be enclosed in ""]

[lists each value]:

order by patronymic

[groups aggregated variables by another variable, or by more than one variable separated by commas]:

group by sex group by trade, sex

You can use the symbols:
+, -, *, /, = ,<> [not equals]
< [less than] <= [less than or equal to]
> [greater than] >= [greater than or equal to]

Return to the direct search form.