sql question set 1

✅ SQL Question #1: Repeating Rows Based on Value

Input Table:

A single-column table with values:

Value
-----
1
2
3

Expected Output:

Each value should be repeated as many times as its value:



Value
-----
1
2
2
3
3
3

Question:

Write an SQL query to return each number repeated as many times as its value.
For example, 1 should appear once, 2 should appear twice, 3 thrice, and so on.

✅ SQL Question #2: Get the Start and End Cities per Journey

Input Table (TravelRoutes):

IdFromTo
1HyderabadBangalore
1BangaloreChennai
1ChennaiKochi
2BangaloreHyderabad
2HyderabadDelhi
3DelhiChennai

Expected Output:

IdFromTo
1HyderabadKochi
2BangaloreDelhi
3DelhiChennai

Question:

Write an SQL query to find the starting and ending cities of each journey (Id).

Assume the path is connected and ordered — you need to find the first From city and the last To city for each Id. 

Comments

Post a Comment

Popular posts from this blog

collect_list( opposite of explode)

 from pyspark.sql.functions import collect_list from pyspark.sql import SparkSession data1 = [     (1,"apple"),     (1,"banana"),     (1,"cherry"),     (2,"orange"),     (3,"cherry") ] spark = SparkSession.builder.appName("StreamingExample").getOrCreate() df_exploded = spark.createDataFrame(data1, ["id", "fruit"]) df_exploded.show() df_exploded.groupBy("id").agg(collect_list("fruit").alias("fruits")).show()
Read more

SCD Type 2 in MySQL/ coforge

 To implement SCD Type 2 in MySQL , you typically use SQL scripts to: Detect changes between source and target Expire old rows in the dimension table Insert new rows with updated values Assume two tables: customer_src (source/staging table) customer_dim (dimension table with SCD Type 2 tracking) CREATE TABLE customer_dim (     customer_sk INT AUTO_INCREMENT PRIMARY KEY,     customer_id INT,     name VARCHAR(100),     address VARCHAR(255),     start_date DATE,     end_date DATE,     is_current CHAR(1) -- 'Y' or 'N' ); Step 1: Identify Changes (New or Modified Data) -- Find records in source that are new or have changed CREATE TEMPORARY TABLE scd2_changes AS SELECT s.* FROM customer_src s LEFT JOIN customer_dim d   ON s.customer_id = d.customer_id AND d.is_current = 'Y' WHERE d.customer_id IS NULL    OR s.name <> d.name    OR s.address <> d.address; Step 2: Expi...
Read more