Java

1. Card Game (Flip Operations)

## Question & Explanation

Question: Simulate a game with N cards, which are either White ('W') or Black ('B'). You
perform an operation X times. In each operation, you remove the rightmost card. If the card you
just removed was Black, you flip all the remaining cards (W becomes B, B becomes W). Your
task is to find the number of black cards left after X operations.

Explanation: This problem requires you to simulate a card game. A naive approach would be to
actually change every card in an array each time a flip is needed. A more efficient solution is to
simply keep track of how many times a flip has occurred. If the total number of flips is even, the
cards are in their original state. If the number of flips is odd, their state is inverted. This avoids
repeatedly modifying the entire array.

## Input/Output Example

Input:

• N = 5 (Number of cards)

• X = 3 (Number of operations)

• C = {"B", "W", "W", "B", "W"} (Initial card arrangement)

Output:

• 1

Walkthrough (Efficient Method):

1. Initial: [B, W, W, B, W], flipCount = 0 (even).

2. Operation 1: Check rightmost card W. Its effective color is W (since flip count is even).
Remove it. flipCount remains 0. Cards left: 4.
3. Operation 2: Check new rightmost card B. Its effective color is B. Remove it. Increment
flipCount to 1 (odd). Cards left: 3.

4. Operation 3: Check new rightmost card W. Its effective color is now B (since flip count
is odd). Remove it. Increment flipCount to 2 (even). Cards left: 2.
5. Final Count: The remaining cards are [B, W]. Since the final flipCount is 2 (even), their
colors are what they appear to be. There is 1 black card.

PRAJES DAS
## Code in Java

Java
import [Link];

import [Link];

class Solution {

public int cardFlipGame(int n, int x, String[] c) {

List<Character> cards = new ArrayList<>();

for (String s : c) {

[Link]([Link](0));
}

int flipCount = 0;

for (int i = 0; i < x; i++) {

if ([Link]()) break;

char physicalCard = [Link]([Link]() - 1);

char effectiveCard = physicalCard;

// Determine the card's actual color based on the number of flips

if (flipCount % 2 != 0) {

effectiveCard = (physicalCard == 'B') ? 'W' : 'B';

if (effectiveCard == 'B') {
flipCount++;

PRAJES DAS
 }

int blackCardCount = 0;
for (char card : cards) {

char effectiveCard = card;

if (flipCount % 2 != 0) {

effectiveCard = (card == 'B') ? 'W' : 'B';

if (effectiveCard == 'B') {

blackCardCount++;

}
}

return blackCardCount;

<br>

2. Sum of Primes at Even Indices (1-based)

## Question & Explanation

Question: Given an array of integers, find the sum of all the numbers that are prime and are
located at even indices. The problem specifies 1-based indexing, meaning the first element is at
index 1, the second at 2, etc. 1 is not a prime number.
Explanation: This problem requires you to iterate through an array and check two conditions for
each element: its position and its value. You'll loop through the array from i = 0 to N-1. For each
element, you first check if its 1-based position (i + 1) is an even number. If it is, you then use a
helper function to check if the number itself (A[i]) is prime. If both are true, you add the number
to a running sum.

PRAJES DAS
## Input/Output Example

Input:

• N=4
• A = {2, 3, 4, 1}

Output:

• 3

Walkthrough:

1. The array is [2, 3, 4, 1].

2. The even positions (1-based) are 2 and 4.

3. The number at position 2 is 3. Is 3 prime? Yes.

4. The number at position 4 is 1. Is 1 prime? No.

5. The only number that satisfies both conditions is 3. The total sum is 3.

## Code in Java

Java

class Solution {

private boolean isPrime(int n) {

if (n <= 1) return false;

for (int i = 2; i * i <= n; i++) {

if (n % i == 0) return false;

return true;

public int sumOfPrimesAtEvenIndices(int n, int[] a) {

int sum = 0;
for (int i = 0; i < n; i++) {

PRAJES DAS
 // Convert 0-based array index to 1-based position

int position = i + 1;

if (position % 2 == 0) { // Check if the position is even

if (isPrime(a[i])) { // Check if the number is prime

sum += a[i];

return sum;

<br>

3. Watering Trees (Total Water Calculation)

## Question & Explanation

Question: You have N trees, K units of available water, and an initial water requirement of M for
each tree. For each tree i, array A gives the number of times it needs to be watered, and array B
gives the amount of water needed per watering. Calculate the total water required. If it's less than
or equal to K, return the remaining water. Otherwise, return 0.

Explanation: This is a straightforward calculation problem. You need to determine the total
water demand and compare it to the supply. The total demand is the sum of the water needed for
each tree. For each of the N trees, the water needed is M (the initial amount) plus A[i] * B[i] (the
daily amount). Loop through all trees, sum up their individual requirements, and finally, compare
this total sum with K.

## Input/Output Example

Input:
• N = 3 (trees)
• K = 150 (available water)

PRAJES DAS
 • M = 5 (initial water per tree)

• A = {2, 3, 1} (watering frequencies)

• B = {10, 20, 15} (water per watering)

Output:
• 40

Walkthrough:

1. Water for Tree 1: 5 + (2 * 10) = 25

2. Water for Tree 2: 5 + (3 * 20) = 65

3. Water for Tree 3: 5 + (1 * 15) = 20

4. Total water needed: 25 + 65 + 20 = 110

5. Since 110 <= 150, the remaining water is 150 - 110 = 40.

## Code in Java

Java

class Solution {

public int calculateRemainingWater(int n, int k, int m, int[] a, int[] b) {

long totalWaterRequired = 0;

for (int i = 0; i < n; i++) {

totalWaterRequired += m; // Add initial water

totalWaterRequired += (long) a[i] * b[i]; // Add daily water

if (totalWaterRequired > k) {

return 0;
} else {
return (int) (k - totalWaterRequired);

PRAJES DAS
 }

<br>

4. Galaxy with Maximum Stars

## Question & Explanation

Question: You are given a string containing galaxies enclosed in curly braces {}. Inside, stars
are '*' and planets are 'P'. Your task is to find the index (or indices) of the galaxy with the most
stars. If there's a tie, return all winning indices separated by a space.

Explanation: This problem requires parsing a string and keeping track of a maximum value. The
first step is to split the input string into individual galaxy strings (e.g., by splitting on } {). Then,
you iterate through each galaxy, counting the '*' characters. As you go, you'll maintain a variable
for the highest star count seen so far and a list of indices where this count was found. If you find
a new galaxy with more stars, you reset the max count and clear your list of indices. If you find
one with the same max count, you simply add its index to the list.

## Input/Output Example

Input:
• input1 = "{***P**P***} {*****} {**P******}"

Output:

• "0 2"

Walkthrough:

1. Galaxy at index 0 ({***P**P***}) has 8 stars. maxStars = 8, indices = [0].

2. Galaxy at index 1 ({*****}) has 5 stars. This is less than 8, so nothing changes.

3. Galaxy at index 2 ({**P******}) has 8 stars. This is equal to maxStars, so we add 2 to

the list. indices = [0, 2].

4. The final list of indices is [0, 2], which is returned as the string "0 2".

PRAJES DAS
## Code in Java

Java

import [Link];
import [Link];

import [Link];

class Solution {

public String findGalaxyWithMaxStars(String input1) {

// Clean and split the string into an array of galaxies

String cleanedInput = [Link](1, [Link]() - 1);

String[] galaxies = [Link]("\\} \\{");

int maxStars = -1;

List<Integer> maxIndices = new ArrayList<>();

for (int i = 0; i < [Link]; i++) {

int currentStars = 0;

// Count stars in the current galaxy

for (char c : galaxies[i].toCharArray()) {

if (c == '*') {

currentStars++;

// Compare with the max found so far

if (currentStars > maxStars) {
maxStars = currentStars;

PRAJES DAS
 [Link](); // Found a new max, so clear previous indices

[Link](i);

} else if (currentStars == maxStars) {

[Link](i); // Found a tie, so add this index

}

// Format the output string

return [Link]()

.map(String::valueOf)

.collect([Link](" "));

}
}

<br>

5. Unique Airport Codes

## Question & Explanation

Question: You are given a string S of length N, which is a multiple of 3. The string consists of
concatenated 3-character airport codes. Two codes are considered identical if they are anagrams
of each other (e.g., "ABC" is the same as "BAC"). Count the number of unique codes.

Explanation: The key to solving this problem is to "normalize" each 3-character code into a
standard form. The easiest way to do this is by sorting the characters of each code alphabetically.
For example, "ABC", "BAC", and "CAB" all become "ABC" when sorted. You can then add
these normalized codes to a Set, which automatically handles duplicates. The final answer is
simply the size of the set.

## Input/Output Example

Input:
• N=9

PRAJES DAS
 • S = "LHRJFKHRL"

Output:

• 2

Walkthrough:
1. Split S into 3-character chunks: "LHR", "JFK", "HRL".

2. Normalize each chunk by sorting its characters:

o "LHR" -> "HLR"

o "JFK" -> "FJK"

o "HRL" -> "HLR"

3. Add these normalized codes to a Set: {"HLR", "FJK"}. The second "HLR" is a duplicate
and is not added.

4. The size of the set is 2.

## Code in Java

Java

import [Link];

import [Link];

import [Link];

class Solution {
public int countUniqueAirportCodes(int n, String s) {

Set<String> uniqueNormalizedCodes = new HashSet<>();

for (int i = 0; i < n; i += 3) {

String code = [Link](i, i + 3);

char[] chars = [Link]();

[Link](chars);
String normalizedCode = new String(chars);

PRAJES DAS
 [Link](normalizedCode);

return [Link]();

}
}

<br>

6. Light Bulb Toggling

## Question & Explanation

Question: You are given a string of light bulbs ('O' for on, 'X' for off) and a series of commands.
The commands are either "toggle i", which flips the state of the bulb at index i, or "invert", which
flips the state of all bulbs. Find the final state of the bulbs after all commands are executed.

Explanation: A naive solution would be to flip every bulb for each "invert" command, which
can be slow. A much more efficient method is to use a boolean flag (e.g., isInverted) to track the
global inversion state. When an "invert" command appears, you just flip this flag. When a
"toggle i" command appears, you toggle the bulb at index i. After all commands are processed,
you apply the final inversion state to the entire array in a single pass if the flag is true.

## Input/Output Example

Input:

• S = "OXXO"
• M=2

• commands = {"toggle 1", "invert"}

Output:
• "XOOX"

Walkthrough:

1. Initial state: [O, X, X, O], isInverted = false.

2. Command "toggle 1": The bulb at index 1 ('X') is flipped to 'O'. State becomes [O, O, X,
O].

PRAJES DAS
 3. Command "invert": The isInverted flag is flipped to true.

4. All commands done. Since isInverted is true, we apply a final flip to [O, O, X, O],
resulting in [X, X, O, X]. (Correction: the toggle should be applied based on the inversion
state. A more careful simulation is needed).

o Correct Walkthrough: Initial: [O,X,X,O].

o "toggle 1": [O,O,X,O].

o "invert": The isInverted flag is now true.

o Final state: Apply the inversion to the final array [O,O,X,O], which gives
[X,X,O,X]. Hmm, the logic is subtle.

o Let's rethink: The code should directly modify the array. After all commands, the
result is final.

o Initial [O,X,X,O]. Command "toggle 1" flips index 1 to 'O', array is [O,O,X,O].
Command "invert" flips all, array is [X,X,O,X]. The efficient approach is tricky to
reason about. Let's provide the direct simulation code which is easier to
understand and correct.

o Final Correct Walkthrough:

1. Initial: [O, X, X, O]

2. toggle 1: [O, O, X, O]

3. invert: [X, X, O, X]

4. Final state is X, X, O, X.

## Code in Java

Java

class Solution {

public String lightBulbToggling(String s, int m, String[] commands) {

char[] bulbs = [Link]();

for (String command : commands) {

if ([Link]("toggle")) {

PRAJES DAS
 int index = [Link]([Link](" ")[1]);

if (index >= 0 && index < [Link]) {

bulbs[index] = (bulbs[index] == 'O') ? 'X' : 'O';

}
} else if ([Link]("invert")) {

for (int i = 0; i < [Link]; i++) {

bulbs[i] = (bulbs[i] == 'O') ? 'X' : 'O';

return new String(bulbs);

}
}

<br>

7. Find Max Height and Weight Index

## Question & Explanation

Question: You are given an array where heights and weights of N people are interleaved: [h1,
w1, h2, w2, ...]. Find the 1-based index of the person with the maximum height and the 1-based
index of the person with the maximum weight. In case of a tie, the person who appears first in
the array wins.

Explanation: The problem requires you to iterate through the combined array while keeping
track of two separate maximums: one for height and one for weight. You can loop from i = 0 to
N-1. In each iteration, you are looking at person i+1. The height for this person is at array index
i*2 and the weight is at i*2 + 1. You can compare these values with the running maximums for
height and weight and update them along with their corresponding person indices if a new
maximum is found.

## Input/Output Example

PRAJES DAS
Input:

• N=3

• array = {180, 70, 190, 85, 175, 85}

Output:
• "2 2"

Walkthrough:

1. Heights: 180 (person 1), 190 (person 2), 175 (person 3). The maximum is 190, belonging
to person 2.

2. Weights: 70 (person 1), 85 (person 2), 85 (person 3). The maximum is 85. Since person 2
and person 3 are tied, we take the one that appeared first, which is person 2.

3. The result is "maxHeightIndex maxWeightIndex" -> "2 2".

## Code in Java

Java

class Solution {
public String findMaxHeightAndWeight(int n, int[] data) {

int maxHeight = -1;

int maxWeight = -1;

int maxHeightIndex = -1;

int maxWeightIndex = -1;

for (int i = 0; i < n; i++) {

int personIndex = i + 1;
int height = data[i * 2];

int weight = data[i * 2 + 1];

if (height > maxHeight) {

maxHeight = height;

PRAJES DAS
 maxHeightIndex = personIndex;

if (weight > maxWeight) {

maxWeight = weight;

maxWeightIndex = personIndex;

return maxHeightIndex + " " + maxWeightIndex;

<br>

8. Cost of Missing Letters

## Question & Explanation

Question: James has a string S containing some English letters. You are given an array P of 26
prices, where P[0] is the cost of 'A', P[1] is the cost of 'B', and so on. Find the minimum cost to
buy the letters he is missing so that he has at least one of every letter from 'A' to 'Z'. If he isn't
missing any letters, the cost is 0.

Explanation: This problem is about identifying a set difference. First, you need to determine
which letters of the alphabet are missing from the input string S. A simple way to do this is to
create a boolean array of size 26 to represent the alphabet. Iterate through the string S and mark
each letter you see as "found". Then, iterate through your boolean array from 'A' to 'Z'. For every
letter that was not found, add its corresponding price from the P array to a total cost.

## Input/Output Example

Input:
• S = "ACE"
• P = {10, 20, 30, 40, 50, ...} (prices for A, B, C...)

PRAJES DAS
Output:

• 60

Walkthrough:

1. The letters James has are A, C, E.

2. The letters he is missing (among the first 5) are B and D.

3. The price for B is P[1] = 20.

4. The price for D is P[3] = 40.

5. Assuming he has all other letters, the minimum cost to buy the missing B and D is 20 +
40 = 60.

## Code in Java

Java

class Solution {
public int costOfMissingLetters(String s, int[] p) {

boolean[] hasLetter = new boolean[26];

for (char c : [Link]().toCharArray()) {

if (c >= 'A' && c <= 'Z') {

hasLetter[c - 'A'] = true;

int totalCost = 0;

for (int i = 0; i < 26; i++) {

if (!hasLetter[i]) {

totalCost += p[i];

}
}

PRAJES DAS
 return totalCost;

<br>

9. Balanced Index Finder

## Question & Explanation

Question: Given an array, find the smallest index i (where 1 <= i < N-1) such that the element to
its immediate left is equal to the element to its immediate right (arr[i-1] == arr[i+1]). Return the
first such index i found. If no such index exists, return -1.

Explanation: The problem description seems confusing, but the core task simplifies to finding a
"pivot" element arr[i] whose immediate neighbors are identical. You need to loop through the
array, but be careful with the bounds. The loop should start at index 1 and end at index N-2 to
ensure that arr[i-1] and arr[i+1] are always valid indices. Inside the loop, you simply check the
condition, and if it's true, you return the current index i immediately.

## Input/Output Example

Input:

• N=5

• A = {1, 8, 2, 8, 5}

Output:
• 2

Walkthrough:

1. Start loop at i = 1. Check A[0] vs A[2] (1 vs 2). No match.

2. Next, i = 2. Check A[1] vs A[3] (8 vs 8). Match!

3. Return the current index, which is 2.

## Code in Java

Java

PRAJES DAS
class Solution {

public int findBalancedIndex(int n, int[] a) {

// We can only check for indices from 1 to n-2

for (int i = 1; i < n - 1; i++) {

if (a[i - 1] == a[i + 1]) {

return i; // Return the first one found

return -1; // No such index was found

<br>

10. Triangular Substring Counter

## Question & Explanation

Question: Count the number of 5-character "triangular substrings" in a given string S. A

substring is triangular if its characters c1, c2, c3, c4, c5 satisfy the pattern: c1 < c2 < c3 > c4 >
c5.

Explanation: This problem can be solved by iterating through the string with a "sliding
window" of size 5. Your loop will go from the start of the string up to the last possible position
where a 5-character substring can begin. In each step, you'll grab the five characters in the
current window and check if they satisfy all four required conditions (c1 < c2, c2 < c3, c4 < c3,
and c5 < c4). If they do, you increment a counter.

## Input/Output Example

Input:

• S = "abceca"

Output:
• 1

PRAJES DAS
Walkthrough:

1. Window 1: "abcec". Fails because e is not less than c.

2. Window 2: "bceca". Check the pattern:

o b < c (true)
o c < e (true)

o c < e (true)

o a < c (true)

o All conditions pass. Count is 1.

## Code in Java

Java
class Solution {

public int countTriangularSubstrings(String s) {

if (s == null || [Link]() < 5) {

return 0;

int count = 0;

for (int i = 0; i <= [Link]() - 5; i++) {

char c1 = [Link](i);

char c2 = [Link](i + 1);

char c3 = [Link](i + 2);

char c4 = [Link](i + 3);

char c5 = [Link](i + 4);

if (c1 < c2 && c2 < c3 && c4 < c3 && c5 < c4) {

count++;
}

PRAJES DAS
 }

return count;

<br>

11. Find Character with Mirror Occurrence

## Question & Explanation

Question: Given a string S and an integer K, find the first character in the string that has the
same character appearing exactly K positions to its left and K positions to its right.

Explanation: The most likely interpretation of this ambiguous phrasing is to find the first
character S[i] where the character K spots to its left (S[i-K]) is identical to the character K spots
to its right (S[i+K]). The character S[i] itself does not need to match. You must iterate through
the string, being careful with array bounds. The loop for the central index i can only run from K
to [Link]() - 1 - K.

## Input/Output Example

Input:

• K=2

• S = "abacaba"

Output:

• "a"
Walkthrough:

1. Loop starts at i = K = 2.

2. i = 2 (char 'c'): Check s[0] ('a') vs s[4] ('b'). No match.

3. i = 3 (char 'a'): Check s[1] ('b') vs s[5] ('b'). Match!

4. The condition is met for the character at index 3, which is 'a'. Return "a".

PRAJES DAS
## Code in Java

Java

class Solution {
public String findMirrorCharacter(int k, String s) {

if (s == null || 2 * k >= [Link]()) {

return ""; // Not possible to find such a character

// Loop through the possible middle indices

for (int i = k; i < [Link]() - k; i++) {

if ([Link](i - k) == [Link](i + k)) {

return [Link]([Link](i)); // Return the character at the middle index

return ""; // No such character found

<br>

12. Contained Sentence Check

## Question & Explanation

Question: You are given two sentences, S and T. Check if all the words of the shorter sentence
appear as words in the longer sentence. Return "TRUE" if they do, and "FALSE" otherwise.

Explanation: This problem requires you to compare the set of words in two sentences. The most
efficient way is to split both sentences into arrays of words. Then, add all the words of the longer
sentence into a HashSet for very fast lookups. Finally, iterate through each word of the shorter
sentence and check if it exists in the set. If you find any word that doesn't exist in the set, you can

PRAJES DAS
immediately return "FALSE". If the loop completes, it means all words were found, and you can
return "TRUE".

## Input/Output Example

Input:

• S = "hello world"
• T = "world is big and hello there"

Output:

• "TRUE"

Walkthrough:

1. S is shorter than T. The words to check are "hello" and "world".

2. The words in T are {"world", "is", "big", "and", "hello", "there"}.

3. Is "hello" in the set of T's words? Yes.

4. Is "world" in the set of T's words? Yes.

5. All words were found. Return "TRUE".

## Code in Java

Java

import [Link];
import [Link];

import [Link];

class Solution {

public String checkContainedSentence(String s, String t) {

String[] wordsS = [Link](" ");

String[] wordsT = [Link](" ");

PRAJES DAS
 String[] shorter, longer;

if ([Link] < [Link]) {

shorter = wordsS;

longer = wordsT;
} else {

shorter = wordsT;

longer = wordsS;

Set<String> longerWordsSet = new HashSet<>([Link](longer));

for (String word : shorter) {

if () {

return "FALSE";

return "TRUE";

}
}

MySQL
1. High Balance Accounts

PRAJES DAS
 • Problem: Retrieve the Account_ID and Customer_ID for all accounts with a balance
greater than 6000.

• Expected Tables: Accounts (with columns like Account_ID, Customer_ID, Balance)

• Output Columns: Account_ID, Customer_ID

• Sample Solution:

sql

SELECT Account_ID, Customer_ID

FROM Accounts

WHERE Balance > 6000;

2. Patient Admission List

• Problem: Display the full name (a combination of first and last name), phone number,
and admission date of all patients. Sort the results by admission date in descending order.

• Expected Tables: Patients (with columns

like First_Name, Last_Name, Phone_Number, Admission_Date)

• Output Columns: PatientFullName, ContactNumber, DateOfAdmission

• Sample Solution:

sql

SELECT

CONCAT(First_Name, ' ', Last_Name) AS PatientFullName,

Phone_Number AS ContactNumber,

Admission_Date AS DateOfAdmission

FROM Patients

ORDER BY Admission_Date DESC;

3. Users Without Followers

• Problem: Find the user_id, first_name, and last_name of users who have no followers.

• Expected Tables: users, follows (with columns like follower_id, following_id)

• Output Columns: USER_ID, FIRST_NAME, LAST_NAME
• Sample Solution (Using LEFT JOIN):

PRAJES DAS
sql

SELECT u.user_id, u.first_name, u.last_name

FROM users u

LEFT JOIN follows f ON u.user_id = f.following_id

WHERE f.following_id IS NULL;

4. Hotel Revenue & Occupancy (Post March 2024)

• Problem: For each hotel, find its total revenue and the number of rooms booked for all
bookings where the checkout date is after March 1, 2024. Sort the results by total revenue
in descending order.
• Expected Tables: Hotels, Rooms, Bookings (with columns
like checkout_date, total_amount)

• Output Columns: HOTELNAME, TOTALREVENUE, ROOMSBOOKED

• Sample Solution:

sql

SELECT

[Link] AS HOTELNAME,

SUM(b.total_amount) AS TOTALREVENUE,

COUNT(b.booking_id) AS ROOMSBOOKED

FROM Hotels h
JOIN Rooms r ON h.hotel_id = r.hotel_id

JOIN Bookings b ON r.room_id = b.room_id

WHERE b.checkout_date > '2024-03-01'

GROUP BY h.hotel_id, [Link]

ORDER BY TOTALREVENUE DESC;

5. Departments Starting with 'O'

• Problem: List the name, location, and phone number of all departments whose names
start with the letter 'O'.
• Expected Tables: Departments (with columns
like DepartmentName, Location, PhoneNumber)

PRAJES DAS
 • Output Columns: DEPARTMENTNAME, LOCATION, PHONENUMBER

• Sample Solution:

sql

SELECT
DepartmentName AS DEPARTMENTNAME,

Location AS LOCATION,

PhoneNumber AS PHONENUMBER

FROM Departments

WHERE DepartmentName LIKE 'O%';

6. Flight Passenger Summary

• Problem: Show flight details (ID, departure city, arrival city) along with the total number
of passengers for each flight. Order the results by Flight ID in ascending order.

• Expected Tables: Flights, BoardingPass (or a similar junction table)

• Output
Columns: FLIGHTID, DEPARTURECITY, ARRIVALCITY, TOTALPASSENGERS

• Sample Solution:
sql

SELECT

f.flight_id AS FLIGHTID,

f.departure_city AS DEPARTURECITY,
f.arrival_city AS ARRIVALCITY,

COUNT(bp.passenger_id) AS TOTALPASSENGERS

FROM Flights f

JOIN BoardingPass bp ON f.flight_id = bp.flight_id

GROUP BY f.flight_id, f.departure_city, f.arrival_city

ORDER BY f.flight_id ASC;

7. Currently Occupied Rooms Details

PRAJES DAS
 • Problem: List the room number, hotel name, and checkout date for all rooms that are
currently marked as 'Occupied'. Order the results by checkout date in ascending order.

• Expected Tables: Hotels, Rooms, Bookings (The status might be in Rooms or Bookings)

• Output Columns: ROOMNUMBER, HOTELNAME, CHECKOUTDATE

• Sample Solution (if status is in Rooms):

sql

SELECT
r.room_number AS ROOMNUMBER,

[Link] AS HOTELNAME,
b.checkout_date AS CHECKOUTDATE

FROM Rooms r

JOIN Hotels h ON r.hotel_id = h.hotel_id

JOIN Bookings b ON r.room_id = b.room_id

WHERE [Link] = 'Occupied'

ORDER BY b.checkout_date ASC;

8. Users by Phone Number Pattern

• Problem: Retrieve the username and email of users whose phone number ends with the
digits '29'.

• Expected Tables: users (with columns like username, email, phone_number)

• Output Columns: USERNAME, EMAILID

• Sample Solution:

sql

SELECT

username AS USERNAME,
email AS EMAILID

FROM users

WHERE phone_number LIKE '%29';

9. Seat Categories by Price Range

PRAJES DAS
 • Problem: Find all seat categories whose price is between 3000 and 9000 (inclusive).

• Expected Tables: SeatCategories (with columns like category_name, price)

• Output Columns: CATEGORY_NAME, PRICE

• Sample Solution:
sql

SELECT

category_name AS CATEGORY_NAME,

price AS PRICE

FROM SeatCategories

WHERE price BETWEEN 3000 AND 9000;

10. Seats in a Specific Stadium

• Problem: List the seat number and its category for all seats in the stadium named 'Eden
Gardens'. Order the output by seat number in descending order.

• Expected Tables: Stadiums, Seats, SeatCategories

• Output Columns: SEATNO, CATEGORYNAME

• Sample Solution:

sql

SELECT

s.seat_number AS SEATNO,

sc.category_name AS CATEGORYNAME

FROM Seats s
JOIN Stadiums st ON s.stadium_id = st.stadium_id

JOIN SeatCategories sc ON s.category_id = sc.category_id

WHERE [Link] = 'Eden Gardens'

ORDER BY s.seat_number DESC;

11. expensive Seats with Stadium Name

• Problem: List the seat number, stadium name, and price for all seats priced more than
4000. Display the result in ascending order of price.

PRAJES DAS
 • Expected Tables: Stadiums, Seats (or SeatCategories linked to Seats)

• Output Columns: SEATNO, STADIUM_NAME, PRICE

• Sample Solution:

sql
SELECT

s.seat_number AS SEATNO,

[Link] AS STADIUM_NAME,

[Link] AS PRICE

FROM Seats s

JOIN Stadiums st ON s.stadium_id = st.stadium_id

WHERE [Link] > 4000

ORDER BY [Link] ASC;

PRAJES DAS