 Hello, welcome to SSUnitech. So, see this site and this is continuation of SQL Server interview questions and answers. So, today we are going to discuss one more interview question. So, how we can calculate the shortest and longest length city? So, let's move to next slide to understand about the question. So, here as we can see we have input table that would have five columns. First is ID, city, state, Latin and long w. Query the two cities in the station table with shortest and longest city names as well their respective lengths. If there are more than one smallest or largest cities choose one which come first order by alphabetically in the city. So, as we can see here we are having Pune and Kanpur in the output. Pune is having length four and Kanpur is having length six. So, in case of Pune we can see in the input table we are having Rome as well. Rome is also having four characters but if we will sort alphabetically then Pune come first. Then again go for Kanpur and Nagpur. So, Kanpur is having six lengths and Nagpur is also having six lengths. But if we will sort then we will get Kanpur first that's why Kanpur is coming in the output. So, how we can write the simplest query for getting the output? So, let's move to SQL Server Management Studio where we will write query for getting this output. So, as I am using SQL 2017 you may have another version of SQL Server. So, let me execute this create table script to create the table. So, as we can see command completed successfully. So, table is created. Now, let me insert data on this table. So, we can execute this insert statement and here we can see six rows vector data have been inserted successfully on this table. Now, let me execute the selected statement to check the data. So, here we can see we are having the same metadata as we seen in the slide. So, how we can write the query? So, first of all, I would like to get the city name and length. So, we can write city comma length of this city. Now, we can use order by clause and in order by clause we can use the length of this city. After that, we also need to sort on city. Now, let me execute this query. So, here as we can see this is increasing order. So, we just want only one city if we have multiple cities. In that case, we will get only one which come first in city. So, as we can see we have put city in the order by clause. So, city comes first that is Pune. So, we want only one. So, here we can use top one. Now, let me execute the query. So, here we are getting Pune. Now, let me use as length of city. Next, we also need to write query for the largest city. So, for that we can write select from our table that is station. Then here we need to get the city comma length of that city. So, we can use length of that city and let me call this as length city. Then in order by clause we have to use the same as we did for the minimum length and here we can use this as decreasing order comma city. Now, let me execute this query and here we are getting Kanpur first then Nagpur because we have used city in the order by clause. Then we can use top one. Now, let me execute and we are getting the Kanpur. So, if we will combine this result set then that would be our output as we are expecting. But if we will use union all then it will reflect an error because order by clause cannot be used in the first query. So, here we can see it is going to return as error because this order by is not allowed over here. So, how we can write the query? So, we can use the city. So, here this is the first city. So, we can call as city one and put this value into that city. Then again we can use city two as and let me put this into city two. Now, we can write select a stick from city one. Then we can use union select a stick from city two. Now, we can execute this. So, this is output as we are expecting. So, I hope you have understand how we can get the minimum length and maximum length cities. So, thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of all newly uploaded videos. If you need this script it will be available on the description of this video. So, you can copy from there. Thank you so much for watching. See you in next video.