Selecting columns from result of another query in SQL

[rajanikant-ghate]

Let’s say there’s a table A regarding real estate properties of a broker, where the columns are pertaining to various choices one makes regarding a house, and some measurable parameters such as sq.ft area and proximity from the market. Say both of these variables are in bins of categories for e.g. (<200 , 200-300, 300-500, … >5000 in sq. ft.). The only missing column is price.

There’s another table B, that has a chart, where there’s a column named “Proximity” that has values corresponding to proximity to the market in kms, another column as “Price” and rest all columns named as categories of sq. ft area of table A. This table is actually a price chart.

The problem statement of course would be to write a query and get price of a particular real estate property.

An interviewer when asking this sort of questions, will expect you to come up with dynamic SQL queries and proper join condition (if required), that take input of results for a user from table A, and pass it to a query on table B, to get the price. An interviewer will not directly ask if you know dynamic SQL or if you know joins.

How to prepare then for such scenarios?
Try to get as many real projects as possible. That will force you to overcome real challenges and build concepts. One word of caution here, building real projects take time, so make sure you have a mentor who prevents you from picking irrelevant case studies.