Dear SQLTeam,
I'm new to this forum and I was wondering if I could get some help. Next week I have an exam and I was working some past papers , however I can't check if the queries are worked out correctly.
Is there anyone kind enough that can give me the correct answer for the queries because I don't know if I'm working them incorrectly.
Regards.
If you post both the question and your answer then folk here would be glad to help - both to tell you if your answer is correct and to guide you to the correct answer if not 
If you put any SQL inside:
```sql
(your code here)
```
then it will format nicely on this forum.
I will write my answers as a softcopy as I only have them as a hardcopy so it will probably take a couple of minutes.
Thanks for the first reply.
Scan to Image and upload that will probably do (so long as the image is big enough to be readable)
However, if you can upload as TEXT its easier for us - we can Cut & Paste bits into our answers to provide examples etc.
This is the database.
Write a SQL construct for the following query: Print measure number, tree number and type of measure from the measure table. Order output by measure data.
SELECT Me_numb,Me_trnumb,Me_type
FROM Measure
ORDER BY Me_data ASC;
Write a SQL construct for the following query: Print a unique list of measure types.
SELECT DISTINCT Me_type
FROM Measure;
Write a SQL construct for the following query: Print the minimum and maximum values of measure whose type is 'ACID'.
SELECT MIN(Me_result),MAX(Me_result)
FROM Measure
WHERE Me_type =’ACID’ ;
Write a SQL construct for the following query: List the forest name,company name and location for any forest whose name must start with 'GREAT', and has a size greater than 1000000 or its location is 'SOUTH'. In the output ensure that company name and location are concatenated as 'ABC Co Ltd- SOUTH'.
SELECT Fo_name,Fo_comp,Fo_Loc
FROM Forest
WHERE ((Fo_name LIKE ‘GREAT%’ AND Fo_size > 1000000) OR (Fo_location =’SOUTH’));
Write a SQL construct for the following query: List species name, species wood type, forest name and forest location for all trees. Ensure that output doesn't contain repetitions.
SELECT Sp_name ,Sp_woodtype
FROM Species
UNION
SELECT Fo_name,Fo_location
FROM Forest;
Write a SQL construct for the following query: In the measure table one can find rows for 'HEIGHT' reading for many trees. List those trees that have a measured height (ie: me_type='HEIGHT') that is actually greater than the expected height of the tree's species max height (ie:sp_maxht).
SELECT Sp_maxht
FROM Species
UNION
SELECT Me_trnumb,Me_result
FROM Measure
WHERE Me_result >Sp_maxht
Write a SQL construct for the following query: List trees that are co-located (ie: tr_loc values are identical) and where one tree is the 'granddaughter' of the other. The output should include the number of each tree, their common location and forest name.
SELECT Tr_numb,Tr_loc,Tr_parent
FROM Tree
WHERE ( I have no idea how to continue this questions...)
Write a SQL construct for the following query: For each species with the same wood type (ie: sp_woodtype) compare their maximum height ( ie:sp_maxht). The output should include the species wood type, species name of the larger height, species of the smaller height.
SELECT Sp_woodtype,Sp_maxht
FROM Species
WHERE Sp_woodtype = Sp_woodtype
Write a SQL construct for the following query: For each species wood type, forest location and measure type output the average of measure result and count of aggregates. Exclude measure on species whose wood type is 'SOFT' and whose aggregate count is less than 10.
Write a SQL construct for the following query: Which trees have all measure types?
In the last 4 questions can you give me a couple of hints so I can try to worth them out alone ?
Thanks a lot.
The resultset won't have any column names. That's not an error, and may not be something you have learnt, but often it is desirable to have column names / labels in the resultset :
SELECT MIN(Me_result) AS MinResult, MAX(Me_result) AS MaxResult
Well that's ambiguous!! I will assume that the punctuation is intended to clarify the question, in which case you have that wrong ... but I would argue that your answer is right, and the question is ambiguous!
Change
WHERE ((Fo_name LIKE ‘GREAT%’ AND Fo_size > 1000000) OR (Fo_location =’SOUTH’));
to
WHERE Fo_name LIKE ‘GREAT%’
AND (Fo_size > 1000000 OR Fo_location =’SOUTH’);
also, you missed this bit:
"In the output ensure that company name and location are concatenated as 'ABC Co Ltd- SOUTH'."
I don't reckon that your answer is what they want. You have made two, separate, lists (Species Name and Wood Type, and then Forest Name and Location) and combined them (by using UNION) into a single 2-column report. My guess is that used UNION because the question said "Ensure that output doesn't contain repetitions". If you have two separate queries, and you combine them with UNION, then that will, indeed, remove any duplicates. But to just remove duplicates from a "regular query" use SELECT DISTINCT (as you did in the first question)
I think what they want is
SELECT DISTINCT Sp_name, Sp_woodtype, Fo_name, Fo_location
FROM Tree
JOIN Species
ON Sp_name = Tr_species
JOIN Forest
ON Fo_name = Tr_forest
In the question " List those trees that have a measured height (ie: me_type='HEIGHT') that is actually greater than the expected height of the tree's species max height (ie:sp_maxht)." it looks like you are misunderstanding what UNION is useful for, so you may want to read up on that. At the least you have to have the same number of columns in each SELECT that forms the UNION, and each column has to have a matching data type (or a datatype that can be implicitly converted to match the equivalent column in the other SELECT statements in the Union).
I'll give you some further feedback tomorrow (unless someone else has jumped in in the meantime!)
Thanks a lot Kristen. I will look up on Unions and Joins as I thought union was used to use 2 separate tables.
I was wondering if anybody else can help me.
Regards.