Question

Write an SQL query to list those properties that have never been sold.

Design Tip

When a property has sold, its information can be found in Sales table and vice versa.

List each property with the following four columns: property_id, primary_purpose, locality, and the area.

Name the area coulmn as "Area". (Since the dataset might be incomplete, return "N/A" when the area is unknown for a property.)

So the problem I got is that I got very similar output as its actual output which has 5 rows(in the picture 1).When I deleted "natural join Areas natural join Localities"(in the picture 2), It gave me same output as the actual output for the first 2 columns, but I have no idea for how to code for the last two columns.

Problem query.sql init.sql Sales.csv Properties.csv Localities.csv Districts.csv Areas. select property_id,primary_purpose, locality, area as "Area" Write an SQL query to list those properties that have never been sold. FROM Properties P natural join Areas natural join Localities Where NOT EXISTS (select* FROM Sales S where S.property_id-P.property_id) Design Tip When a pr table and vice versa opery has sold, its informa tion can be found in Sales Submissions Output #4 Not yet! Failed a test. Testing your query produces the correct data values, using the example dataset provided. Yo List each property with the following four columns: property id, primary purpose, locality, and the area. did not produce the correct output. Your program output: . Name the area coulmn as "Area". (Since the dataset might be incomplete, return"N/A" when the area is unknown for a property.) I property_id primary_purpose| locality Area 1954216409 | FARM TAYLORS FLAT| 16.19I 1954216410 | VACANT LANDTAYLORS FLAT32.38I 1954216702 VACANT LAND BOX HILL300 | property id dealing number contract date settlement date purchase price registrar id house_number unit_number street locality id district code zoning local (3 rows) council name when it was meant to output: nature of property primary purpose property_id | primary_purposelocality Area | area area Diagram of tables in the Property Sales database 1954215914 | RESIDENCE 1954215915 RESIDENCE MOUNT HUTTONN/AI TAYLORS FLAT| 16.19 | BOX HILL MOUNT HUTTON|N/AI | 19542 164Θ9 I FARM 1954216410 | VACANT LANDTAYLORS FLAT 32.38I 1954216702 | VACANT LAND 300 rows) Problem query.sql init.sql Sales.csv Properties.csv Localities.c select property_id,primary purpose Write an SQL query to list those properties that have never been sold. FROM Properties P Where NOT EXISTS (select * FROM Sales S where S.property_id-P.property_id) Design Tip When a propery has sold, its information can be found in Sales table and vice versa Submissions Output |property_id | primary_purpose | List each property with the following four columns property_id, primary purpose, locality, and the area Name the area coulmn as "Area". (Since the dataset might be incomplete, return "N/A" when the area is unknown for a property.) . 1954215914 | RESIDENCE 1954215915 RESIDENCE . I 19542164θ9 | FARM 1954216410|VACANT LAND| 1954216702 |VACANT LAND | ales ities (5 rows) sale id property_id dealing number contract date settlement date purchase price registrar id localit house number unit _number street locality id district code council name area id reas nature of property primary_purpose area area Diagram of tables in the Property Sales database

Answer 1

SELECT property_id,primary_purpose FROM properties p WHERE NOT EXISTS (SELECT * FROM sales S WHERE S.property_id = p.property_id) natural join   SELECT property_id,primary_purpose,locality,area as "Area" natural join localities natutral join Areas FROM properties p WHERE NOT EXISTS (SELECT * FROM sales S WHERE S.property_id = p.property_id)