SQL exercises
INTRODUCTION:
In the following sessions you will make use of a database that is already prepared for you to further practice SQL queries. Take a look at the E-R model and data dictionary (see following pages) that are provided and see the skeleton tables for correct spelling of attribute names in your queries. Try to formulate SQL statements for the following questions using your provided database.
I．Queries on a single table using key words such as: AND, OR, IN, BETWEEN, LIKE, NOT
1. Give a list of owners names (o＿name) and year of birth (birth) of owners who were born in 1945. 2. Who are the owners living in the street named ‘LINETYPE’. List their names (o＿name). 3. Give a list of owners names (o＿name) and year of
birth (birth) who were born later than 1940 but before 1950.
4. List the parcel number, area and tax＿class of parcels that belong to taxclass U1 or A1. 5. List the parcel number, area, block number and tax class of all (taxable & non taxable) urban parcels.
6. List the name, civic number, year of birth of all owners whose name includes (begins, ends or contains) the character’S’.
EXPRESSIONS AND FUNCTIONS (appendices):
An SQL command can contain arithmetic expressions. An arithmetic expression is made up of column names and constant numeric values connected by arithmetic operators. You can use any of the following arithmetic operators (+ = add,- = subtract, * = multiply, / = divide). You can use an arithmetic expression in a SELECT clause to retrieve
calculated results.
II．Queries on a single table using key words such as: DISTINCT, ORDER BY, GROUP BY, SUM(), AVG(), COUNT(), MIN()
7. make a list of street names in such a way that all different street names appear only once. 8. List the name, civic number and year of birth of all owners and display the result in descending sequence ordered by their year of birth. 9. Make a list of the tax rate, the tax rate minus 0.5 and the tax class of all taxable parcels. 10. Try to formulate a query that counts the number of parcels and also displays the average area of parcels, the largest and the smallest parcel and the total parcel area.
11. Now formulate a query that displays the number of parcels, the average parcel area and the total