SQL Assessment questions
Note:
Please be advised that these assessment inquiries are designed to accommodate interns with diverse skill levels,
ranging from novices to seasoned analysts. Should you encounter any challenging questions, you are encouraged to
seek solutions independently or reach out to us for assistance at intern@[Link]. Best wishes for success
in completing the assessment!
Tools needed :- MS SQL
Dataset – Alibaba dataset
1. Define a SQL table to store the given dataset.
2. Load the provided dataset into the SQL table.
3. Retrieve all columns from the table for the first 10 rows.
4. Display the products where the shipping city is 'New York'.
5. Retrieve the top 5 products with the highest item price.
6. Calculate the average quantity sold.
7. Group the data by category and display the total quantity sold for each category.
8. Create a new table for payment methods and join it with the main table to display product names and
their payment methods.
9. Find products where the cost price is greater than the average cost price.
10. Calculate the total special price for products in the 'Electronics' category.
11. Increase the cost price by 10% for products in the 'Clothing' category.
12. Add a new record for a product with necessary details.
13. Remove all products where the sale flag is 0.
14. Create a new column 'Discount_Type' that categorizes products based on their item price: 'High' if
above $200, 'Medium' if between $100 and $200, 'Low' if below $100.
15. Rank the products based on their special prices within each category.
16. Calculate the running total of the quantity sold for each product.
1|Page
17. Create a CTE that lists products in the 'Fashion' sub-category with their corresponding brand and color.
18. Pivot the data to show the total quantity sold for each category and sub-category.
19. Unpivot the table to transform the 'Value_CM1' and 'Value_CM2' columns into a single column named
'CM_Value'.
20. Create a stored procedure that accepts a category name as input and returns the total quantity sold for
that category.
2|Page