Question

Craig is working in a database that stores homes for sale for a Realtor. The database contains a table called HomeListings which contains a field called ListingDate that holds the date that a home was listed. Craig is designing a query that will identify houses on the market for less than 30 days as new listings, and houses on the market 30 days or more as old listings. This information will be included in a query column called ListingAge. Which of the following expressions will achieve these results:_________.

Answer 1

Craig can classify home listings using an SQL query with a CASE statement that leverages the DATEDIFF function to determine if the ListingDate is less than 30 days from the current date, which would categorize the listing as 'new' for the ListingAge column.

Step-by-step explanation:

Craig is tasked with classifying listings in the HomeListings table according to how long they have been on the market. The desired outcome is to have a new column called ListingAge that indicates whether a listing is 'new' or 'old' based on whether it has been on the market for less or more than 30 days. To achieve this, Craig would need to use an SQL query with a CASE statement that compares the ListingDate with the current date. An example expression for the ListingAge column could be:

SELECT ListingID,
CASE
WHEN DATEDIFF(day, ListingDate, GETDATE()) < 30 THEN 'New Listing'
ELSE 'Old Listing'
END AS ListingAge
FROM HomeListings;

This SQL expression uses the DATEDIFF function to calculate the difference in days between the ListingDate and the current date, which is retrieved using the GETDATE() function. If this difference is less than 30 days, the ListingAge is set to 'New Listing'; otherwise, it is 'Old Listing'.