R
@Roopesh122
SQL Interview Q3
/*
EY SQL Interview Question
You are provided with a table named `Students` that contains information about students' scores and attendance.
The table structure is as follows:
| Column Name | Data Type |
|--------------|-----------|
| `Name` | VARCHAR |
| `Score` | INT |
SQL Interview Q2
/*
Your team at JPMorgan Chase is preparing to launch a new credit card, and to gain some insights, you're analyzing how many credit cards were issued each month.
Write a query that outputs the name of each credit card and the difference in the number of issued cards
between the month with the highest issuance cards and the lowest issuance. Arrange the results based on the largest disparity.
*/
-- Create the table
CREATE TABLE credit_card_issuance (
card_name VARCHAR(50) NOT NULL,
SQL Interview Q1
/*
You are given a table named Sample with the following columns:
ID: A unique identifier for each row.
Letter: A string containing a word where the first letter may be either lowercase or uppercase.
Your task is to write an SQL query to transform the table such that:
1. If the first letter of the word is in lowercase, it should be converted to uppercase.
2. If the first letter of the word is in uppercase, it should be converted to lowercase.
FIRST_VALUE AND LAST_VALUE Functions
/*
As a Data Analyst, your task is to identify the employees with the minimum and maximum overtime hours in the company.
This information will help the organization determine appropriate holiday allocations for each employee based on their workload.
Schema:
Table Name: Employee_Overtime
| Column Name | Data Type | Description |
|----------------|--------------|---------------------------------------------|
| employee_name | VARCHAR(50) | Name of
LEAD() & LAG() Function
/*
Analyze Percentage Change in Game Sales on the Steam Store
As a data analyst for a gaming company, your task is to analyze the sales trends for selected games on the Steam store.
Using sales data for four popular games — Valorant, Marvel Rivals, COD (Call of Duty), and PUBG PC, calculate the percentage change in monthly sales.
Schema:
Table Name: Game_Sales
| Column Name | Data Type | Description |
|------------------|-------------|----------
Ranking() Functions 2
/*
You are a data analyst at an e-commerce company. Your task is to analyze the distribution of product sales across different regions.
Each sale has a unique `sale_id`, and the dataset includes the region where the sale occurred and the total sales amount.
Schema:
Table Name:`Sales`
| Column Name | Data Type | Description |
|--------------|-------------|---------------------------------|
| sale_id | INT | Unique identifier for each sale |
| region
Ranking() Functions
/*
You are working as a data analyst for an e-commerce platform. The company is interested in analyzing product sales across different categories.
To gain insights, you have been asked to use SQL ranking functions to evaluate the performance of products based on their total sales.
Schema:
| Column Name | Data Type | Description |
|----------------|---------------|-------------------------------------------|
| category | VARCHAR(50) | The ca
SQRT() Function
/*
You are a data analyst working for a logistics company.
Your task is to analyze the delivery time efficiency of the delivery agents.
The company's database contains a table named `deliveries`, which has the following schema:
Schema:
| Column Name | Data Type | Description |
|---------------------|---------------|------------------------------------------------------|
| delivery_id | INT | Unique ID for each del
POWER() Function
/*
You are a data analyst working for a retail company. The company tracks product sales across different regions and wants to predict future revenue growth. The sales data is stored in the `sales_data` table.
Calculate the projected revenue for each product after applying a growth rate for the next 3 years.
Schema:
Table Name: `sales_data`
| Column Name | Data Type | Description |
|-------------------|-------------|---------------------------------
MOD() Function
/*
You are managing an e-commerce system where each order has a unique `order_id`.
To streamline delivery and logistics, the orders are categorized into two groups based on their `order_id`:
1. Even Group: Orders with an even `order_id`.
2. Odd Group: Orders with an odd `order_id`.
Additionally, calculate the percentage of total orders in each group (Even and Odd) to understand the distribution of orders.
Schema:
ABS() Function
/*
You are managing a financial database for a company that tracks monthly gains and losses for various departments.
The `financial_records` table stores both positive and negative amounts, representing profits and losses, respectively.
The management wants a report that calculates the absolute value of these amounts to compare the total scale of transactions without considering whether they are gains or losses.
Schema:
| Column Name | Data Type | Description
ROUND(),FLOOR() & CEIL() Functions
/*
Scenario: Salary and Tax Calculations
You are managing an employee payroll system. The salary details are stored in the `employee_salaries` table, which has the following schema:
Schema:
| Column Name | Data Type | Description |
|------------------|------------|----------------------------------------|
| employee_id | INT | Unique ID for each employee |
| employee_name | VARCHAR(50)| Name of the employee |
IF() Function
/*
Case Scenario: December Holiday Bonuses
You are managing an employee database for a company that provides bonuses to employees who have worked a certain number of hours during the month of December.
Schema:
| Column Name | Data Type | Description |
|---------------|--------------|--------------------------------------------|
| employee_id | INT | Unique ID for each employee |
| employee_name | VARCHAR(50) | Name of the
CURRENT_DATE, CURRENT_TIME And CURRENT_TIMESTAMP Functions
/*
You are managing an e-commerce platform that tracks customer orders. One of the critical tasks is monitoring the processing times for orders that are still pending.
To ensure timely delivery and customer satisfaction, you need to analyze the time elapsed since each pending order was placed
Schema:
| Column Name | Data Type | Description |
|------------------|---------------|-----------------------------------------|
| order_id | INT
DATE_ADD() & DATE_SUB() Functions
/*
A hotel chain is looking to analyze its room sales performance during the 2024 holiday season.
The goal is to gain insights into booking trends in the lead-up to Christmas and during the period between Christmas and New Year's Day.
By understanding these trends, the hotel can optimize its sales strategies during peak holiday periods.
Schema:
| Column Name | Data Type | Description |
|---------------|--------------|---------------------------
DATE_FORMAT() Function
/*
You are managing a hotel booking system. The `bookings` table stores information about customer bookings, including check-in and check-out dates. The goal is to generate a summary of bookings for a monthly report. The report should format the dates and display useful information.
Schema:
| Column Name | Data Type | Description |
|-------------------|-------------|--------------------------------------|
| booking_id | INT | Unique ID for
DATEDIFF() Function
/*
You are managing an employee leave tracking system for an organization. The `employee_leaves` table contains information about employees’ leave requests:
| Column Name | Data Type | Description |
|--------------------|---------------|-------------------------------------------------|
| leave_id | INT | Unique ID for each leave request |
| employee_id | INT | Unique ID for each employee
GROUP_CONCAT() Function
/*
You are managing a university database containing information about students and the courses they are enrolled in. The `enrollments` table has the following schema:
| Column Name | Data Type | Description |
|--------------|---------------|-------------------------------|
| student_id | INT | Unique ID for each student |
| student_name | VARCHAR(100) | Full name of the student |
| course_id | INT | Unique ID for each course
REGEXP() Function
/*
You are managing a user accounts database for a platform that enforces strict username and email validation. The `users` table has the following schema:
| Column Name | Data Type | Description |
|--------------|---------------|----------------------------------------------|
| user_id | INT | Unique ID for each user |
| username | VARCHAR(50) | The username chosen by the user |
| email
UPPER() and LOWER() Functions
/*
You manage a product catalog for an e-commerce platform. The `products` table stores product names and descriptions in mixed case. You need to ensure data consistency and extract specific insights based on standardized formats.
Task:
1. Convert all product names to uppercase using the `UPPER()` function.
2. Convert all product descriptions to lowercase using the `LOWER()` function.While keeping the first letter in caps
3. Filter and display only products where the product name conta