Question

A state-wide land tax assessment database has two tables: LandParcel that stores a set of land parcels, and ZoningTypes that stores a set of zoning codes and zoning types. The LandParcel table has 5 columns: ParcelNumb as text indicating each parcel's unique number defined by the State, Zoning as an integer number indicating the numerical code of a zoning type and each land parcel belongs to one zoning type, Owner First Name and Owner Last Name as text indicating the owner name of a parcel, and AssessedValue as a double number indicating the value (in US dollars) of a land parcel. The ZoningTypes table has 2 columns: Code as an integer number indicating the code of a zoning type, and Type as text indicating the full text description of zoning codes. Zoning codes and types are regulated by State government and the same set of codes and types are used in the database. Write ONE SQL statement to find the zoning type(s) of the parcel(s) that are owned by people whose last name is Widseler. Return the parcel number, zoning type and full owner name in the result of the SQL query. Write ONE SQL statement to find the parcels that owned by Jessica Smith and the assessed value is less than 100000. Return the owner name and the assessed value in the result. Write ONE SQL statement to find the code of zoning type Agricultural. Return the code and type in the result. Write ONE SQL statement to find the land parcels that have assessed value $10000 or lower or the parcels that are not in zone type (code) 2 and 3. Return all the columns in the query result.

Answer 1

In the database you uploaded, it contains white space. In database generally there's no space between the words like Parcel and Number. So in the queries I am ignoring the space. You can either have it like OwnerFirstName or can use _ as Owner_First_Name.

You may use <> instead of != for checking not equal to condition.

The queries are as follows:

(3). Query: SELECT ParcelNumb, Type, Concat(OwnerFirstName, ' ', OwnerLastName) as OwnerName from LandParcel l, ZoningTypes z where l.Zoning=z.Code AND OwnerLastName='Widseler';

Description: In this query, we have to display columns from 2 tables so we will require a join. The join will be done on Zoning and Code columns of respected tables.

The next thing is, we need to concat the two columns content so concat() is the function needed here. This function will give the result as owner name. There's one more thing we need to check which is the last name of owner. So this will also go in where clause. Both the conditions must satisfy in order to get the results so AND clause is needed in between the 2 conditions.

(4). Query: SELECT Concat(OwnerFirstName, ' ', OwnerLastName) as OwnerName, ParcelNumb from LandParcel where OwnerFirstName='Jessica' AND OwnerLastName='Smith' AND AssessedValue<100000;

Description: Here the query will not return any rows as it does not have any matching criteria in the 4 lines. This query requires to deal with only LandParcel table so we won't have any join there. The query needs to find the owner so owner's first and the last name is checked in the query's where clause and the assessed value is also checked in it. As all the conditions are necessary, it will have AND clause everywhere.

(5). Query: SELECT Code, Type from ZoningType where Type='Agricultural';

Description: Here you need to find code and the type of the zone where the type is Agricultural. So this deals with ZoningType table and where clause will have one condition.

(6). Query: SELECT * from LandParcel where AssessedValue<=10000 OR (Zoning!=2 AND Zoning!=3);

Description: Here you need to write a query for which the Assessed value is at max 10000. This deals with LandParcel table. The other condition is having 'or' in the question itself. So, we will also use 'OR' clause here. And the next thing is, zoning column is having the value of zone code. And we need to check it is neither 2 nor 3.

So, I've used AND clause here for combine checking this. So the query is checking either for assessed value or for the combination of those conditions zoning!=2 and zoning!=3.

Do comment if there is any query. Thank you. :)