How does an inner join operation work under the hood in SQL databases, and what are the performance implications?
Please sign-in to view the solution
How can Java collections be effectively integrated with SQL databases to enhance backend systems' performance and scalability?
Please sign-in to view the solution
You are given two tables in a relational database: Orders and Customers. The Orders table includes columns for order_id
, customer_id
, order_date
, and order_amount
. The Customers table includes columns for customer_id
, customer_name
, and customer_region
. Write an SQL query to find the total order amount for each customer in the "West" region, but only include customers who have made orders totaling more than $5000. Your query should use both JOIN and HAVING clauses.
Please sign-in to view the solution
Write an SQL query to identify customers who have placed orders in multiple years. The orders
table has columns order_id
, customer_id
, and order_date
.
Please sign-in to view the solution
Consecutive Days Logins: Given a table
logins
with columnsuser_id
andlogin_date
, find users who logged in on two consecutive days.Users with All Activities: Given a table
activities
with columnsuser_id
andactivity
, find users who have performed all three activities: 'login', 'purchase', and 'logout'.Ranking Products by Price: Given a table
products
with columnsproduct_id
andprice
, rank the products by price without using theRANK()
function.Second Highest Salary: Given a table
employees
with columnsemployee_id
andsalary
, find the second highest salary.Duplicate Email Addresses: Given a table
users
with anemail
column, find all email addresses that appear more than once.
Please sign-in to view the solution
SQL Query for Top N Records: Write an SQL query to fetch the top 5 employees with the highest salaries from an
employees
table with columnsemployee_id
,name
, andsalary
.SQL Query for Aggregate Function: Write an SQL query to find the average salary of employees in each department from a table
employees
with columnsdepartment_id
,employee_id
, andsalary
.Python Function for Factorial: Write a Python function to compute the factorial of a given number.
Python List Comprehension: Write a Python one-liner using list comprehension to create a list of squares of the first 10 natural numbers.
Python String Manipulation: Write a Python function to reverse a given string.
Please sign-in to view the solution
You are given two tables: gift_cards
and transactions
. The gift_cards
table has columns card_id
, issued_date
, and initial_balance
. The transactions
table has columns transaction_id
, card_id
, transaction_date
, and amount
. Write an SQL query to calculate the remaining balance on each gift card as of the current date.
Please sign-in to view the solution
As part of a hypothetical gift card program, you are asked to analyze the performance of the program. Key metrics to calculate include:
- Total number of gift cards issued
- Total value of gift cards issued
- Total value of transactions
- Average remaining balance per gift card Provide SQL queries for each metric.
Please sign-in to view the solution
Given a table events
with columns event_id
, user_id
, event_type
, and event_time
, write an SQL query to find the time difference between two specific types of events for each user. For example, calculate the time difference between 'login' and 'logout' events for each user.
Please sign-in to view the solution
Discuss the differences between SQL and NoSQL databases and when to use each.
Please sign-in to view the solution
Given a set of data points, write SQL queries to perform various transformations. Suppose you have a table sales_data
with columns sale_id
, product_id
, sale_date
, quantity
, and price
. Write SQL queries to:
- Calculate the total sales amount for each product.
- Find the average quantity sold per product per day.
- Identify the top 3 best-selling products by total sales amount.
- Transform the sale_date to include only the month and year.
Please sign-in to view the solution
Write a SQL query to retrieve data from multiple tables using joins. Suppose you have three tables: customers
, orders
, and products
. The customers
table has columns customer_id
, name
, and email
. The orders
table has columns order_id
, customer_id
, order_date
, and total_amount
. The products
table has columns product_id
, order_id
, product_name
, and price
. Write an SQL query to retrieve the customer's name, email, order date, product name, and price for all orders.
Please sign-in to view the solution
Given the following database schema for an e-commerce platform:
Customers Table:
customer_id
(Primary Key)first_name
last_name
email
Orders Table:
order_id
(Primary Key)customer_id
(Foreign Key)order_date
total_amount
OrderDetails Table:
order_detail_id
(Primary Key)order_id
(Foreign Key)product_id
(Foreign Key)quantity
price
Products Table:
product_id
(Primary Key)product_name
category
Write a complex SQL query to retrieve the top 5 customers who have spent the most on products in the 'Electronics' category over the past year. The query should also return the total amount spent by these customers and the number of orders they placed in the past year.
Please sign-in to view the solution
Write an SQL query to retrieve the top 5 highest salaries from the employees
table. The table has the following columns: employee_id
, name
, and salary
.
Please sign-in to view the solution
Write an SQL query to find all email addresses that appear more than once in a users
table with an email
column.
Please sign-in to view the solution
Design a database schema for a blogging platform. The schema should include tables for users, posts, comments, and tags. Describe the relationships between the tables and any constraints you would apply.
Please sign-in to view the solution
Write an SQL query to find the average salary of employees in each department from a table employees
with columns department_id
, employee_id
, and salary
.
Please sign-in to view the solution
Design a database schema for an online bookstore. The schema should include tables for books, authors, customers, and orders. Describe the relationships between the tables and any constraints you would apply.
Please sign-in to view the solution
Design a database from scratch to store and summarize sales data. Include tables for products, customers, orders, and order details. Explain how you would design the schema, summarize sales data, and predict future sales.
Please sign-in to view the solution
Given the following database schema for a retail platform:
Customers Table:
customer_id
(Primary Key)first_name
last_name
email
Orders Table:
order_id
(Primary Key)customer_id
(Foreign Key)order_date
total_amount
OrderDetails Table:
order_detail_id
(Primary Key)order_id
(Foreign Key)product_id
(Foreign Key)quantity
price
Products Table:
product_id
(Primary Key)product_name
category
Write a complex SQL query that performs the following tasks:
- Use a
JOIN
to retrieve the customer's name and email along with the total amount they spent on each order. - Use a
UNION
to combine the result with a similar query that retrieves the total number of products ordered by each customer. - Use a
WINDOW FUNCTION
to rank customers based on their total spending. - Use a
SUBQUERY
to find customers who have placed more than 5 orders.
The final result should include the customer's name, email, total amount spent, total products ordered, and their spending rank.
Please sign-in to view the solution
How can you optimize database queries on SQL Server to reduce load times in a web application hosted on IIS, considering networking constraints within your DevOps environment?
Please sign-in to view the solution
Using the tables Employees, Salaries, and Departments, can you write an SQL query to find the names of employees who earn more than the average salary in their department? The Employees table contains columns employee_id, employee_name, and department_id. The Salaries table has columns employee_id and salary. The Departments table includes department_id and department_name.
Please sign-in to view the solution
Given three tables: Employees
, Salaries
, and Departments
, write an SQL query to find the salary of each employee along with their corresponding department name. The Employees
table contains columns employee_id
, employee_name
, and department_id
. The Salaries
table has columns employee_id
and salary
. The Departments
table includes department_id
and department_name
. Provide a query to list employee_name
, salary
, and department_name
.
Please sign-in to view the solution
Can you write an SQL query to check if a number is a perfect square without using the square root function? Assume the number is stored in a table named Numbers
with a column named num
.
Please sign-in to view the solution
Can you explain what MapReduce is, and describe its purpose and how it works?
Please sign-in to view the solution
Given a table named Sales
with columns id
, employee_id
, and amount
, and another table named Employees
with columns id
and name
, write a SQL query to rank employees based on their total sales amount. The result should include the employee name and their total sales amount, along with their rank.
Input
Sales
table:
id | employee_id | amount |
---|---|---|
1 | 1 | 100 |
2 | 2 | 200 |
3 | 1 | 150 |
Employees
table:
id | name |
---|---|
1 | John |
2 | Jane |
Please sign-in to view the solution
Explain the different types of SQL joins, the order of SQL execution, and how you would perform SQL operations on a table to achieve a specific output. Provide examples.
Please sign-in to view the solution
Describe how you would handle several SQL query questions and explain how you would analyze a histogram and pie chart to present data insights.
Please sign-in to view the solution
Explain how you would use advanced SQL techniques to identify consecutive numbers and segregate absent and present dates.
Please sign-in to view the solution
Two coding questions based on SQL. The first question involves using joins, and the second question involves ranking.
Please sign-in to view the solution
Explain the differences between linear regression and logistic regression. What are R-squared and P-value? Provide an example SQL query.
Please sign-in to view the solution
Create a products
table and a sales
table with the following schemas:
products table schema:
product_id
(INT)product_name
(VARCHAR)category
(VARCHAR)
sales table schema:
sale_id
(INT)product_id
(INT)sale_date
(DATE)quantity
(INT)amount
(DECIMAL)
Write SQL statements to:
- Create the
products
andsales
tables. - Write a query to get the total sales amount for each product, grouped by category and sorted by total sales in descending order.
- Write a query to rank products by their total sales amount within each category.
Please sign-in to view the solution
Assume we have the following data in sales_fact
and product_dimension
tables:
sales_fact
table:
| sale_id | product_id | sale_date | quantity | amount |
|---------|------------|------------|----------|--------|
| 1 | 101 | 2023-10-01 | 2 | 150.00 |
| 2 | 102 | 2023-10-02 | 1 | 200.00 |
| 3 | 103 | 2023-10-03 | 3 | 300.00 |
| 4 | 101 | 2023-10-04 | 1 | 75.00 |
| 5 | 102 | 2023-10-05 | 2 | 400.00 |
product_dimension
Table:
| product_id | product_name | product_group |
|------------|--------------|---------------|
| 101 | Product A | Group 1 |
| 102 | Product B | Group 1 |
| 103 | Product C | Group 2 |
Expected Output: The output should list the product with the highest revenue within each product group:
| product_group | product_name | total_revenue |
|---------------|--------------|---------------|
| Group 1 | Product B | 600.00 |
| Group 2 | Product C | 300.00 |
Please sign-in to view the solution
How would you identify and address potentially under-performing predictive models in a production environment?
Please sign-in to view the solution
Given three tables named Products
, Sales
, and Customers
, write an SQL query to find the top 5 products with the highest total sales from the last month. The query should involve joins between these tables and should account for any discounts applied. Also, handle any potential NULL values in the total sales calculation.
Input:
- `Products` table:
| id | name | price |
|----|------------|-------|
| 1 | Product A | 10 |
| 2 | Product B | 20 |
| ...| ... | ... |
- `Sales` table:
| id | product_id | customer_id | quantity | discount | sale_date |
|----|------------|-------------|----------|----------|-----------|
| 1 | 1 | 1 | 5 | 0.1 | 2023-09-15|
| 2 | 2 | 2 | 3 | 0.0 | 2023-09-20|
| ...| ... | ... | ... | ... | ... |
- `Customers` table:
| id | name |
|----|------------|
| 1 | John |
| 2 | Jane |
| ...| ... |
Output: The query should return the product name and the total sales amount in descending order of sales, limited to the top 5 products.
Please sign-in to view the solution
Write a SQL query to find the top 5 customers based on total expenditure over the last year.
Additionally, create a Python script to plot a bar chart showing total monthly orders over the same period.
Sample Input Data
orders
Table:
| order_id | customer_id | order_date | total_amount |
|----------|-------------|------------|--------------|
| 1 | 1 | 2022-11-20 | 150.75 |
| 2 | 2 | 2022-12-05 | 200.50 |
| 3 | 1 | 2022-12-20 | 75.00 |
| 4 | 3 | 2023-01-05 | 300.00 |
| 5 | 2 | 2023-01-15 | 180.75 |
| 6 | 1 | 2023-02-10 | 120.00 |
| 7 | 4 | 2023-02-20 | 250.00 |
| 8 | 3 | 2023-03-01 | 90.00 |
customers
Table:
| customer_id | customer_name | signup_date |
|-------------|---------------|-------------|
| 1 | Alice | 2021-05-01 |
| 2 | Bob | 2022-03-15 |
| 3 | Carol | 2022-07-22 |
| 4 | Dave | 2022-09-19 |
Please sign-in to view the solution
Given the following Sales
table in a SQL database, write queries to: (1) Calculate the cumulative sales amount for each salesperson, ordered by SaleDate
; and (2) Find the average sales amount over a rolling window of the last 3 sales for each salesperson.
Sales Table:
| SaleID | SalespersonID | SaleAmount | SaleDate |
|--------|---------------|------------|-----------|
| 1 | 101 | 100 | 2023-01-01|
| 2 | 102 | 200 | 2023-01-02|
| 3 | 101 | 150 | 2023-01-03|
| 4 | 102 | 250 | 2023-01-04|
| 5 | 101 | 200 | 2023-01-05|
| 6 | 101 | 300 | 2023-01-06|
Please sign-in to view the solution
Given the following Orders
and Customers
tables, write a SQL query to calculate the total amount spent by each customer. The result should include the CustomerID
, CustomerName
, and TotalAmountSpent
.
Orders Table:
| OrderID | CustomerID | OrderAmount | OrderDate |
|---------|------------|-------------|------------|
| 1 | 101 | 250 | 2023-01-01 |
| 2 | 102 | 150 | 2023-01-03 |
| 3 | 101 | 100 | 2023-01-04 |
| 4 | 103 | 200 | 2023-01-06 |
| 5 | 102 | 50 | 2023-01-07 |
Customers Table:
| CustomerID | CustomerName |
|------------|--------------|
| 101 | John Doe |
| 102 | Jane Smith |
| 103 | Bob Johnson |
Please sign-in to view the solution
Given the following two SQL queries, recommend improvements to each query and determine which query would run faster.
Sample Queries:
Query A:
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderAmount, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Orders.OrderDate >= '2023-01-01';
Query B:
SELECT o.OrderID, c.CustomerName, o.OrderAmount, o.OrderDate FROM Orders o INNER JOIN Customers c ON o.CustomerID = c.CustomerID WHERE o.OrderID IN (SELECT OrderID FROM Orders WHERE OrderDate >= '2023-01-01');
Please sign-in to view the solution
You mentioned your proficiency with SQL and data forecasting. Can you describe a past experience where you used SQL to analyze data and generate a forecast? What challenges did you face, and how did you address them?
Please sign-in to view the solution
What type of SQL joins do you know?
Please sign-in to view the solution
Write an SQL query to assign a RANK
and DENSE_RANK
to each employee within their respective department based on their total sales. Additionally, demonstrate the use of PARTITION BY
to calculate the total sales for each department.
employee_sales
table:
| employee_id | employee_name | department | sales_amount | sales_date |
|-------------|---------------|------------|--------------|------------|
| 1 | Alice | Sales | 500.00 | 2023-01-01 |
| 2 | Bob | Sales | 300.00 | 2023-01-02 |
| 3 | Carol | Sales | 700.00 | 2023-01-03 |
| 4 | Dave | Marketing | 400.00 | 2023-01-04 |
| 5 | Eve | Marketing | 450.00 | 2023-01-05 |
| 6 | Frank | Sales | 300.00 | 2023-01-06 |
Please sign-in to view the solution
Can you explain Data Mining, Data Pipeline, and Data Warehouse? Highlight the purpose and key differences between these three concepts.
Please sign-in to view the solution
Using the tables Employees, Salaries, and Departments, can you write an SQL query to find the names of employees who earn more than the average salary in their department? The Employees table contains columns employee_id, employee_name, and department_id. The Salaries table has columns employee_id and salary. The Departments table includes department_id and department_name.
Please sign-in to view the solution
Given two tables in a database, how would you fetch a field from the second table based on a related field in the first table? Provide an example using SQL JOIN.
Please sign-in to view the solution
Given a table named Salaries
with columns id
, employee_id
, and salary
, and another table named Employees
with columns id
and name
, write a SQL query to find the employee with the highest salary. The result should contain the employee's name and their salary.
Input
Salaries
table:
id | employee_id | salary |
---|---|---|
1 | 1 | 50000 |
2 | 2 | 70000 |
3 | 1 | 60000 |
Employees
table:
id | name |
---|---|
1 | John |
2 | Jane |
Please sign-in to view the solution
How would you put a cap on the number of items a seller is allowed to have on the Amazon marketplace? What would be the criteria for selecting which items stay on the marketplace if this limit was imposed and sellers were over the limit?
Please sign-in to view the solution
Write a SQL query to find the average salary for each department. The average should be computed using the most recent salary for each employee.
Input Data:
employees
Table:
| employee_id | department_id | employee_name |
|-------------|---------------|---------------|
| 1 | 101 | John Doe |
| 2 | 102 | Jane Smith |
| 3 | 101 | Alice Brown |
| 4 | 103 | Bob White |
salaries
Table:
| employee_id | salary | effective_date |
|-------------|--------|----------------|
| 1 | 50000 | 2023-01-01 |
| 1 | 55000 | 2023-07-01 |
| 2 | 60000 | 2023-03-01 |
| 3 | 45000 | 2023-02-01 |
| 3 | 48000 | 2023-08-01 |
| 4 | 70000 | 2023-05-01 |
| 4 | 75000 | 2023-10-01 |
Expected Output
| department_id | average_salary |
|---------------|----------------|
| 101 | 51500 |
| 102 | 60000 |
| 103 | 75000 |
Please sign-in to view the solution
Write a SQL query to find the names of all employees along with the names of their respective managers.
Input:
employees
table:
| employee_id | employee_name | manager_id |
|-------------|---------------|------------|
| 1 | John Doe | NULL |
| 2 | Jane Smith | 1 |
| 3 | Alice Brown | 1 |
| 4 | Bob White | 2 |
Expected Output
| employee_name | manager_name |
|---------------|--------------|
| John Doe | NULL |
| Jane Smith | John Doe |
| Alice Brown | John Doe |
| Bob White | Jane Smith |
Please sign-in to view the solution
How would you diagnose and resolve issues when a business intelligence report breaks in a production environment?
Please sign-in to view the solution
Here is sample data for the transactions
table:
| customer_id | month | amount_spent |
|-------------|------------|--------------|
| 1 | 2022-10-01 | 100.50 |
| 1 | 2022-11-01 | 150.75 |
| 1 | 2022-12-01 | 120.00 |
| 2 | 2022-10-01 | 200.00 |
| 2 | 2022-11-01 | 180.25 |
| 2 | 2022-12-01 | 210.50 |
| 3 | 2022-10-01 | 50.25 |
| 3 | 2022-11-01 | 75.00 |
| 3 | 2022-12-01 | 90.00 |
Additionally, assume the dataset also provides churn information with the following columns:
churn_info
table:
| customer_id | has_churned |
|-------------|-------------|
| 1 | 0 |
| 2 | 1 |
| 3 | 0 |
Write a SQL query to:
- Calculate the average monthly spend per customer for the past year.
Write a Python script that uses the dataset to:
2. Calculate the probability of customer churn using logistic regression. Assume you have columns for customer_id
, month
, amount_spent
, and a target column has_churned
.
Please sign-in to view the solution
Write a SQL query to find the top 3 products based on total sales value.
Additionally, create a Python script to plot a line graph showing daily total sales over the past month.
Lastly, explain how you would use Excel to calculate and visualize the sales trend.
Input:
transactions
Table:
| transaction_id | date | product_id | quantity | price |
|----------------|------------|------------|----------|--------|
| 1 | 2023-09-01 | 101 | 2 | 10.00 |
| 2 | 2023-09-01 | 102 | 1 | 20.00 |
| 3 | 2023-09-02 | 101 | 1 | 10.00 |
| 4 | 2023-09-02 | 103 | 3 | 15.00 |
| 5 | 2023-09-03 | 102 | 2 | 20.00 |
| 6 | 2023-09-03 | 101 | 2 | 10.00 |
Please sign-in to view the solution
In the context of database systems, explain the different kinds of schemas that could be used. Provide examples of each schema type to illustrate their use.
Please sign-in to view the solution
How do you write a query to calculate the Root Mean Square Error (RMSE) in SQL? Assume you have two columns, Actual
and Predicted
, in a table called Predictions
.
Please sign-in to view the solution
Given the following database schema, write SQL queries to answer the specific questions.
Schema:
**Employees Table**:
| EmployeeID | EmployeeName | DepartmentID |
|------------|--------------|--------------|
| 1 | John Doe | 10 |
| 2 | Jane Smith | 20 |
| 3 | Bob Johnson | 10 |
| 4 | Alice Brown | 30 |
**Departments Table**:
| DepartmentID | DepartmentName |
|--------------|----------------|
| 10 | Sales |
| 20 | Marketing |
| 30 | Support |
**Sales Table**:
| SaleID | EmployeeID | SaleAmount | SaleDate |
|--------|------------|------------|-----------|
| 1 | 1 | 500 | 2023-01-01|
| 2 | 2 | 300 | 2023-01-02|
| 3 | 1 | 700 | 2023-01-04|
| 4 | 3 | 400 | 2023-01-06|
| 5 | 4 | 600 | 2023-01-07|
Specific SQL Queries:
- Query 1: Find the total sales amount for each department.
- Query 2: Identify the top-performing employee in terms of sales within each department.
- Query 3: List all employees along with their department names and total sales.
Please sign-in to view the solution
What is OLAP and what are its differences compared to other database types?
Please sign-in to view the solution
Write an SQL query to calculate the probability that a user who logged in also made a purchase. Then, demonstrate how you would visualize this result using Excel.
Input:
user_events
Table:
| user_id | event_type | event_date |
|---------|------------|-------------|
| 1 | login | 2023-01-01 |
| 1 | click | 2023-01-01 |
| 1 | purchase | 2023-01-01 |
| 2 | login | 2023-01-02 |
| 2 | click | 2023-01-02 |
| 3 | login | 2023-01-03 |
| 3 | click | 2023-01-03 |
| 4 | click | 2023-01-04 |
| 4 | purchase | 2023-01-04 |
| 5 | login | 2023-01-05 |
Please sign-in to view the solution
Write an SQL nested query to find the top-performing product (the product with the highest total sales amount) and display its product_id
and product_name
.
sales
Table:
| product_id | product_name | sales_amount | sales_date |
|------------|--------------|--------------|------------|
| 1 | Product A | 200.00 | 2023-01-01 |
| 2 | Product B | 500.00 | 2023-01-02 |
| 1 | Product A | 300.00 | 2023-01-03 |
| 3 | Product C | 400.00 | 2023-01-04 |
| 2 | Product B | 100.00 | 2023-01-05 |
Please sign-in to view the solution
Can you explain what a LEFT JOIN is in SQL and give an example of when you might use it?
Please sign-in to view the solution
Can you write an SQL query to check if a given string is a palindrome? A palindrome is a string that reads the same forward and backward. Assume the input string is stored in a table named Strings with a column named input_string.
Please sign-in to view the solution
Can you explain the difference between the WHERE and HAVING clauses in SQL?
Please sign-in to view the solution
Can you write SQL syntax to connect two tables horizontally and vertically? Please provide examples of both methods.
Please sign-in to view the solution
Given the following R output from a linear regression analysis, interpret the results and explain the key components of the output.
Please sign-in to view the solution
Given a database with a table named 'Sales' containing columns 'SaleID', 'ProductID', 'Quantity', 'SaleDate', and 'Price', write an SQL query to find the total sales amount for each product in the year 2023. Additionally, rank these products based on the total sales amount in descending order and only display the top 5 products.
Please sign-in to view the solution
Given a table named 'Employees' with columns 'EmployeeID', 'ManagerID', 'Name', and 'Salary', write an SQL query to find each employee's salary as well as their manager's salary. Additionally, use a window function to rank employees based on their salaries within their respective departments.
Please sign-in to view the solution
- Calculate the average performance score for each department.
- Identify the top performer in each department based on the latest performance reviews using window functions.
- Rank employees within their departments based on their average performance scores.
Input: Assume the tables have the following data:
employees
Table:
| employee_id | employee_name | department_id |
|-------------|---------------|---------------|
| 1 | Alice | 10 |
| 2 | Bob | 10 |
| 3 | Carol | 20 |
| 4 | Dave | 20 |
| 5 | Eve | 30 |
performance
Table:
| employee_id | review_date | performance_score |
|-------------|--------------|-------------------|
| 1 | 2023-10-01 | 85 |
| 2 | 2023-10-01 | 90 |
| 1 | 2023-11-01 | 88 |
| 2 | 2023-11-01 | 92 |
| 3 | 2023-10-01 | 78 |
| 3 | 2023-11-01 | 80 |
| 4 | 2023-10-01 | 85 |
| 4 | 2023-11-01 | 87 |
| 5 | 2023-10-01 | 95 |
Please sign-in to view the solution
If you have two SQL database tables that are not joined together, how would you create another table to join them.
Please sign-in to view the solution
What factors should be used in generating a forecast for an item, and what should be the process of generation?
Please sign-in to view the solution
Write an SQL query to retrieve the names of customers who have purchased more than 5 products in total within the last 30 days. The query should use joins to connect the Customers
, Orders
, and OrderDetails
tables.
Input:
- `Customers` table:
| id | name |
|----|------------|
| 1 | John |
| 2 | Jane |
| ...| ... |
- `Orders` table:
| id | customer_id | order_date |
|----|-------------|------------|
| 1 | 1 | 2023-09-25 |
| 2 | 2 | 2023-09-20 |
| ...| ... | ... |
- `OrderDetails` table:
| id | order_id | product_id | quantity |
|----|----------|------------|----------|
| 1 | 1 | 1 | 3 |
| 2 | 1 | 2 | 4 |
| 3 | 2 | 1 | 6 |
| ...| ... | ... | ... |
Output: The query should return the names of customers who have purchased more than 5 products in total within the last 30 days.
Please sign-in to view the solution
You are given a database containing customer orders. The database currently has redundant and repetitive data, leading to inconsistency and excess storage usage. Explain how you would apply normalization techniques to this database. What specific steps would you take to achieve Third Normal Form (3NF)? Provide example tables before and after normalization.
Please sign-in to view the solution
Write a SQL query that combines the use of JOINs and analytical functions. Assume we have two tables: Sales
and Employees
. The query should calculate the total sales for each employee, and also rank the employees based on their total sales amount within each department. The result should include EmployeeID
, EmployeeName
, Department
, TotalSales
, and SalesRank
.
Tables:
**Sales Table**:
| SaleID | EmployeeID | SaleAmount | SaleDate |
|--------|------------|------------|-----------|
| 1 | 101 | 500 | 2023-01-01|
| 2 | 102 | 300 | 2023-01-02|
| 3 | 101 | 700 | 2023-01-04|
| 4 | 103 | 400 | 2023-01-06|
| 5 | 104 | 600 | 2023-01-07|
**Employees Table**:
| EmployeeID | EmployeeName | Department |
|------------|--------------|------------|
| 101 | John Doe | Sales |
| 102 | Jane Smith | Sales |
| 103 | Bob Johnson | Support |
| 104 | Alice Brown | Sales |
Please sign-in to view the solution
Write an SQL query to assign a rank to each customer based on their total spending using both RANK()
and DENSE_RANK()
functions.
Then, using Python, categorize each customer's total spending into High, Medium, and Low using pandas and numpy, and visualize the distribution.
Input:
sales_data
table:
| customer_id | total_spent | purchase_date |
|-------------|-------------|---------------|
| 1 | 100.00 | 2023-01-01 |
| 2 | 200.00 | 2023-01-02 |
| 3 | 150.00 | 2023-01-03 |
| 4 | 300.00 | 2023-01-04 |
| 5 | 200.00 | 2023-01-05 |
Please sign-in to view the solution
Write an SQL query to merge these tables, sort the result by department_name
and employee_name
, and rank employees within their departments.
Additionally, write a small Python program using list comprehensions to filter and sort a list of dictionaries representing employees.
employees
Table:
| employee_id | employee_name | department_id |
|-------------|---------------|---------------|
| 1 | Alice | 10 |
| 2 | Bob | 10 |
| 3 | Carol | 20 |
| 4 | Dave | 20 |
| 5 | Eve | 30 |
departments
Table:
| department_id | department_name |
|---------------|-----------------|
| 10 | Sales |
| 20 | Marketing |
| 30 | Finance |
Please sign-in to view the solution
Can you explain what the Software Development Life Cycle (SDLC) is, and describe its different phases?
Please sign-in to view the solution
Describe your understanding of SQL basic concepts such as Primary/Foreign Key, Joins, Union/Union All. Write SQL queries involving JOINs across 2-3 tables, Aggregation, Subqueries, Window functions, and CTEs (Leetcode- Easy and Medium). Explain statistical concepts and business metrics you have used in previous projects, KPIs you have built for analyzing data, strategies and tools to compare products, and how you would approach situation-based questions to improve efficiency and reduce query run time.
Please sign-in to view the solution
How do you join tables in SQL? Explain the types of joins and provide examples.
Please sign-in to view the solution
Given two tables in an SQL database: Orders
and Customers
. The Orders
table has columns OrderID
, CustomerID
, OrderDate
, and Amount
. The Customers
table has columns CustomerID
and CustomerName
. Write an SQL query to fetch the total Amount
spent by each CustomerName
in the current year. Then describe how you would visualize this data using Tableau.
Input:
Orders
table:
| OrderID | CustomerID | OrderDate | Amount |
|---------|------------|-------------|--------|
| 1 | 101 | 2023-01-10 | 100 |
| 2 | 102 | 2023-03-15 | 200 |
| 3 | 101 | 2023-07-22 | 150 |
| 4 | 103 | 2022-12-01 | 300 |
Customers
table:
| CustomerID | CustomerName |
|------------|--------------|
| 101 | John Doe |
| 102 | Jane Smith |
| 103 | Alice Brown |
Please sign-in to view the solution
You have a table called customer_purchases
:
| customer_id | purchase_date | amount |
|-------------|---------------|--------|
| 1 | 2022-10-10 | 100.50 |
| 1 | 2022-11-15 | 150.75 |
| 2 | 2022-10-25 | 200.00 |
| 2 | 2022-12-14 | 250.00 |
| 1 | 2023-01-10 | 300.00 |
Write a SQL query to find out the average monthly expenditure of each customer over the past year. Use window functions to achieve this.
Please sign-in to view the solution
Write a SQL query to calculate the total revenue generated per product per month over the last year. Explain how you would visualize this data in Tableau, mentioning the types of charts you would use and why.
Input Data:
sales
table:
| sale_id | product_id | sale_date | quantity | price |
|---------|------------|------------|----------|--------|
| 1 | 101 | 2023-01-10 | 10 | 15.00 |
| 2 | 102 | 2023-02-15 | 5 | 20.00 |
| 3 | 101 | 2023-01-20 | 7 | 15.00 |
| 4 | 103 | 2022-12-10 | 3 | 30.00 |
| 5 | 101 | 2022-11-10 | 12 | 15.00 |
Output: The query should return the total revenue generated per product per month over the last year.
Please sign-in to view the solution
- What is a primary key in a database, and why is it important?
- Explain the difference between a clustered index and a non-clustered index. Provide a use case for each.
Please sign-in to view the solution
Write a SQL query to find customers who logged in for at least 3 consecutive days.
Input:
logins
table:
| customer_id | login_date |
|-------------|------------|
| 1 | 2023-10-01 |
| 1 | 2023-10-02 |
| 1 | 2023-10-03 |
| 2 | 2023-10-01 |
| 2 | 2023-10-03 |
| 2 | 2023-10-04 |
| 3 | 2023-10-05 |
| 3 | 2023-10-06 |
| 3 | 2023-10-07 |
| 4 | 2023-10-08 |
| 4 | 2023-10-09 |
| 4 | 2023-10-11 |
Expected Output
| customer_id |
|-------------|
| 1 |
| 3 |
Please sign-in to view the solution
You have a sales
table with the following schema:
sale_id
(INT)category
(VARCHAR)sale_date
(DATETIME)amount
(DECIMAL)
Write a SQL query to fetch the total sales amount category-wise, grouped by month and by hour of the day.
Please sign-in to view the solution
Given two tables named Tickets
and Employees
, where Tickets
contains columns id
, employee_id
, and status
, and Employees
contains columns id
and name
, write a SQL query to find the employee who is solving the most tickets. The result should include the employee's name, the number of tickets they have solved, and their rank based on the number of tickets solved.
Input:
`Tickets` table:
| id | employee_id | status |
|----|-------------|---------|
| 1 | 1 | solved |
| 2 | 2 | pending |
| 3 | 1 | solved |
| 4 | 2 | solved |
| 5 | 1 | solved |
`Employees` table:
| id | name |
|----|--------------|
| 1 | John |
| 2 | Jane |
Output: The query should return the employee name, the number of tickets they have solved, and their rank.
Please sign-in to view the solution
How do you perform transpose operations in SQL to convert rows into columns?
Input:
Consider a table named `Sales` with columns `month`, `employee_id`, and `amount`:
| month | employee_id | amount |
|-------|--------------|--------|
| Jan | 1 | 100 |
| Jan | 2 | 150 |
| Feb | 1 | 200 |
| Feb | 2 | 250 |
Output:
Transpose the table to show each month as a separate column with amounts for each employee:
| employee_id | Jan | Feb |
|-------------|-----|-----|
| 1 | 100 | 200 |
| 2 | 150 | 250 |
Please sign-in to view the solution
What is the difference between 'WHERE' and 'HAVING' filters in SQL? Provide an example to illustrate your point.
Please sign-in to view the solution
What is SQL? Explain the differences between the DELETE, TRUNCATE, and DROP commands in SQL.
Please sign-in to view the solution
How will you build a patent system?
Please sign-in to view the solution
Write a SQL query to retrieve the list of employees along with their department names, and assign a rank to each employee based on their salary within their department.
employees
Table:
| employee_id | employee_name | department_id | salary |
|-------------|---------------|---------------|--------|
| 1 | Alice | 10 | 60000 |
| 2 | Bob | 10 | 50000 |
| 3 | Carol | 20 | 70000 |
| 4 | Dave | 20 | 85000 |
| 5 | Eve | 30 | 90000 |
departments
Table:
| department_id | department_name |
|---------------|-----------------|
| 10 | Sales |
| 20 | Marketing |
| 30 | Finance |
Please sign-in to view the solution
You are given two datasets: employee_details
with columns employee_id
, employee_name
, hire_date
, and department_id
; and sales_performance
with columns employee_id
, sales_amount
, and sales_date
. During a panel interview, you are asked to analyze the performance of employees by joining these datasets and calculating the total sales for each department, as well as identifying top and bottom performers in each department.
Please sign-in to view the solution
You have a dataset driver_trips
with columns trip_date
, fare
, driver_id
, and status
. Write an SQL query to find details of drivers who have had earnings in consecutive months. Only consider trips with status
as 'completed'.
Please sign-in to view the solution
You are given a dataset orders
with columns order_id
, product_name
, quantity
, price
, and order_date
. Provide SQL solutions to illustrate the following:
- The difference between
WHERE
andHAVING
. - How to filter out
NULL
values in theprice
column. - The correct order of execution for a complex SQL query.
- Using
CASE WHEN
statements to categorize orders based onquantity
.
Please sign-in to view the solution
You are analyzing sales data and notice that there are anomalies in the data for several key regions over the past quarter. Specifically, the sales figures for certain products are either too high or too low compared to historical data. Describe a step-by-step approach to identify and address these anomalies using SQL and Python.
Please sign-in to view the solution
You are tasked with testing the login page of an e-commerce website. The login page requires two fields: Email and Password. Describe the test cases you would write to ensure the login functionality works correctly. Consider both functional and non-functional aspects.
Please sign-in to view the solution
Explain the difference between database testing and backend testing in the case of the Amazon login page.
Please sign-in to view the solution
Imagine you are a Data Analyst at Amazon working on improving the product recommendation algorithm. You are given a dataset with user_id, product_id, rating, and timestamp. Your task is to identify the top 5 most popular products (most purchased) in the last 30 days. What steps would you take to solve this problem using Python and SQL?
Please sign-in to view the solution
As a QA Engineer, you encounter a situation where an issue could be either on the server-side or the client-side of an application. Describe the steps you would take to determine and prove that the issue is on the client side and not on the server side.
Please sign-in to view the solution
Explain the use of the HAVING clause and Aggregate Functions in SQL. Provide examples of SQL queries that demonstrate their usage with a detailed explanation for each.
Please sign-in to view the solution
How is requirement gathering & analysis different in the Software Development Life Cycle (SDLC) and the Software Testing Life Cycle (STLC)?
Please sign-in to view the solution
You are analyzing sales data and notice that there are anomalies in the data for several key regions over the past quarter. Specifically, the sales figures for certain products are either too high or too low compared to historical data. Describe a step-by-step approach to identify and address these anomalies using SQL and Python.
Please sign-in to view the solution
Given a database table Sales
with columns Date
, Product_ID
, and Sales_Amount
, write an SQL query to calculate the monthly sales total for each product. Ensure the result is sorted by Product_ID
and Month
.
Please sign-in to view the solution
As a QA Engineer, you are tasked with writing test cases for the search button functionality on an e-commerce website. The search feature allows users to search for products using keywords. List and describe at least five test cases you would execute to ensure the search button works correctly.
Please sign-in to view the solution
As a QA Engineer, you are tasked with testing the 'Add Pay Card' page of an application. Describe the key test cases you would write to ensure that this functionality works correctly.
Please sign-in to view the solution
As a software engineer at Oracle, explain the concept of XPath axes and functions. Provide an example of how you would use two different XPath axes and two XPath functions to query XML data. Include a brief explanation of each axis and function you use.
Please sign-in to view the solution
You're provided with a database containing customer interaction data collected from a web application. Using SQL, extract the list of customers who have logged in more than 5 times in the past 30 days but have not made any purchases. Explain the steps and SQL query you would use to achieve this. Assume the database has the following tables:
logins
(fields:customer_id
,login_date
)purchases
(fields:customer_id
,purchase_date
,amount
)
Please sign-in to view the solution
In the context of a business analysis for a web application, you are given two data tables: logins
and purchases
. You are asked to find out the engagement level of customers who have interacted with the application. Write a SQL query to determine the list of customers who have logged in more than 5 times in the last 30 days and have made at least one purchase. Provide the steps and the final SQL query. The table structures are:
logins
(fields:customer_id
,login_date
)purchases
(fields:customer_id
,purchase_date
,amount
)
Please sign-in to view the solution
You need to write a SQL query to reverse the digits of a given integer. Assume the integer is stored in a table called numbers
with a single column value
. Provide an example integer and show how your query processes it to produce the reversed number.
Please sign-in to view the solution
In a multi-threaded environment, you have a log table thread_logs
with the columns thread_id
, start_time
, end_time
, and operation
. You need to analyze the performance of each thread by calculating the total time spent on operations by each thread. Write an SQL query to summarize the total processing time for each thread. Provide the steps and the final SQL query.
Please sign-in to view the solution
You are given a table employees
with columns employee_id
, name
, and department_id
. You also have a table departments
with columns department_id
and department_name
. Describe how you would leverage a HashMap (or equivalent data structure) approach to optimize querying the department name for each employee in an SQL environment. Provide the steps and the final SQL query.
Please sign-in to view the solution
You need to write an SQL query that identifies whether a given integer in a table is a prime number. Assume the table numbers
has a single column value
containing positive integers. Write a query to determine if each number is prime and return the value
along with a is_prime
flag. Provide the steps and the final SQL query.
Please sign-in to view the solution
Describe at least three different types of authentication methods commonly used in RESTful APIs. Provide brief explanations and examples of each.
Please sign-in to view the solution
As a Data Analyst at Oracle, you're asked to optimize a SQL database for better performance. Describe the strategies you would implement to optimize the database and provide an example SQL query to identify slow-running queries along with an explanation.
Please sign-in to view the solution
You are given a table orders
with columns order_id
, customer_id
, and order_date
. Additionally, you have a table customers
with columns customer_id
, customer_name
, and customer_email
. Explain how you can use principles similar to a Hash Table to efficiently retrieve customer details for each order in an SQL database. Provide the steps and the final SQL query.
Please sign-in to view the solution
Suppose you are working with a SQL database, and you have a table sales_data
with columns product_id
, region
, and sales_amount
. You have noticed that there are some missing values in the sales_amount
column for certain region
s.
Write a SQL query to identify all regions where any sales_amount
value is missing.
Please sign-in to view the solution
Explain the key steps and considerations in designing a database. Include how you would handle requirements gathering, schema design, normalization, indexing, and ensuring data integrity.
Please sign-in to view the solution
Question: You are given a table named Sales
with the following structure:
Sales
:sale_id
(INTEGER)product_id
(INTEGER)sale_amount
(FLOAT)sale_date
(DATE)customer_id
(INTEGER)
Write an SQL query to find the total sales amount for each product for the month of January 2022. The result should include product_id
along with the total sales amount and should be sorted by product_id
.
Please sign-in to view the solution
You are tasked with analyzing sales data for a retail company using SQL and Power BI. The dataset includes tables for sales transactions, products, and customer information. The goal is to create a comprehensive report that provides insights into sales trends, customer segmentation, and product performance.
Data Extraction and Preparation: Outline the SQL queries you would use to extract relevant data from the sales database. Include how you would join tables to gather information on sales transactions, product details, and customer demographics.
Data Analysis in Power BI: Describe the steps you would take in Power BI to visualize and analyze the extracted data. Include the types of visualizations you would create to showcase sales trends over time, customer purchasing behavior, and regional sales performance.
Insights and Recommendations: Based on your analysis, identify key insights such as top-selling products, customer segments with high purchase frequency, and regions with potential growth opportunities. Provide recommendations on strategies to improve sales and customer engagement based on your findings.
Please sign-in to view the solution
As a QA Engineer, you need to test a chat bot option integrated into a web page. Describe the key test cases you would write to ensure that the chat bot functions correctly and provides a good user experience.
Please sign-in to view the solution
Imagine Amazon wanted a file uploader project from you. What would the detailed test plan for this project?
Please sign-in to view the solution
Write an SQL query to find the third highest salary from an Employee table. Additionally, write a query to find the count of employees in each department from the Employee and Department tables.
Assume the Employee table (Emp
) has columns: EmpID
, EmpName
, DeptID
, Salary
, and the Department table (Dept
) has columns: DeptID
, DeptName
.
Please sign-in to view the solution
As a Business Analyst at Oracle, you need to analyze a large volume of trace logs to identify and extract error patterns for system diagnostics. Describe the process, tools, and technologies you would utilize. Additionally, provide an example Python script to extract error patterns from a log file.
Please sign-in to view the solution
You are given a table articles
containing a column content
which holds textual data. Write an SQL query to count the frequency of each word in the content
column across all rows. Explain the steps and the final SQL query to achieve this. Assume your SQL environment supports common table expressions (CTEs) and string functions.
Please sign-in to view the solution
As a QA Engineer, you need to write integration test cases for the WhatsApp application. What key scenarios would you include to ensure the application works correctly across different components?
Please sign-in to view the solution