Scribd Logo
Upload

Welcome to Scribd!

  • 494 views

    Extended Project FastKart SQLite MYSQL 1 1 PDF

    Uploaded by

    Amey Udapure

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Extended Project FastKart SQLite MYSQL 1 1 PDF

    Uploaded by

    Amey Udapure
    494 views5 pages

    Original Title

    Extended_Project_FastKart__SQLite___MYSQL___1___1_.pdf

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    494 views5 pages

    Extended Project FastKart SQLite MYSQL 1 1 PDF

    Uploaded by

    Amey Udapure

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 5
    At a glance
    Powered by AI
    The document discusses using SQLite and MySQL databases to analyze sales data for an online retail store called Fastkart. A series of queries are provided to help the company make data-driven decisions.

    The document asks a series of 10 queries split between SQLite and MySQL. The SQLite queries are related to products, purchases, categories and users. The MySQL queries focus on pricing adjustments, user demographics, payment details and high value items.

    The document uses both SQLite (queries 1-6) and MySQL (queries 7-10) databases. SQLite is used to analyze the Fastkart.db database file while MySQL is used to analyze the Fastkart.sql script.

    How to save files

    Part-1-SQLite( .sqbpro): After executing all the commands/answers, click on


    the Save project tab on the top and save the file name as yourname.sqbpro
    As shown below:

    Part-2-MySQL( .SQL): After executing all the commands/answers, click on the


    file menu on the top left and save as Yourname.sql.
    As shown below:

    Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited 1

    This study source was downloaded by 100000843940989 from CourseHero.com on 11-30-2022 05:42:00 GMT -06:00

    https://www.coursehero.com/file/147100607/Extended-Project-FastKart-SQLite-MYSQL-1-1pdf/
    Extended Project- (SQLite & MYSQL)

    You are hired by a chain of online retail stores “Fastkart”. They have provided you with “Fastkart”
    database and seek answers to the following queries as the results from these queries will help the
    company in making data driven decisions that will impact the overall growth of the online retail
    stores.

    1st part- Q1-Q6 comes under SQLite and queries should be executed in DB Browser. (Database

    - Fastkart.db)

    2nd part- Q7-Q10 comes under MYSQL and the queries should be executed in MYSQL. (SQL Script
    - Fastkart.sql)

    All Questions carry 8 marks. Total Marks (8 x 10) = 80

    Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited 2

    This study source was downloaded by 100000843940989 from CourseHero.com on 11-30-2022 05:42:00 GMT -06:00

    https://www.coursehero.com/file/147100607/Extended-Project-FastKart-SQLite-MYSQL-1-1pdf/
    ER Diagram

    Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited 3

    This study source was downloaded by 100000843940989 from CourseHero.com on 11-30-2022 05:42:00 GMT -06:00

    https://www.coursehero.com/file/147100607/Extended-Project-FastKart-SQLite-MYSQL-1-1pdf/
    Part-1(SQLite)

    1. List Top 3 products based on QuantityAvailable. (productid, productname, QuantityAvailable ).

    (3 Rows) [Note: Products]

    2. Display EmailId of those customers who have done more than ten purchases. (EmailId,
    Total_Transactions).

    (5 Rows) [Note: Purchasedetails, products]

    3. List the Total QuantityAvailable category wise in descending order. (Name of the category,
    QuantityAvailable)

    (7 Rows) [Note: products, categories]

    4. Display ProductId, ProductName, CategoryName, Total_Purchased_Quantity for the product


    which has been sold maximum in terms of quantity?

    (1 Row) [Note: purchasedetails, products, categories]

    5. Display the number of male and female customers in fastkart.

    (2 Rows) [Note: roles, users]

    6. Display ProductId, ProductName, Price and Item_Classes of all the products where
    Item_Classes are as follows:

    If the price of an item is less than 2,000 then “Affordable”,

    If the price of an item is in between 2,000 and 50,000 then “High End Stuff”,

    If the price of an item is more than 50,000 then “Luxury”.

    (57 Rows)

    Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited 4

    This study source was downloaded by 100000843940989 from CourseHero.com on 11-30-2022 05:42:00 GMT -06:00

    https://www.coursehero.com/file/147100607/Extended-Project-FastKart-SQLite-MYSQL-1-1pdf/
    Part-2(MYSQL)

    7. Write a query to display ProductId, ProductName, CategoryName, Old_Price(price) and

    New_Price as per the following criteria

    a. If the category is “Motors”, decrease the price by 3000

    b. If the category is “Electronics”, increase the price by 50

    c. If the category is “Fashion”, increase the price by 150

    For the rest of the categories price remains same.

    Hint: Use case statement, there should be no permanent change done in table/DB.

    (57 Rows)

    [Note: products, categories]

    8. Display the percentage of females present among all Users. (Round up to 2 decimal places)

    Add “%” sign while displaying the percentage.


    (1 Row)

    [Note: users]

    9. Display the average balance for both card types for those records only where CVVNumber >
    333 and NameOnCard ends with the alphabet “e”.

    (2 Rows) [Note: carddetails]

    10. What is the 2nd most valuable item available which does not belong to the “Motor” category.

    Value of an item = Price * QuantityAvailable. Display ProductName, CategoryName, value.

    (1 Row)

    [Note: products, categories]

    Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited 5

    This study source was downloaded by 100000843940989 from CourseHero.com on 11-30-2022 05:42:00 GMT -06:00

    https://www.coursehero.com/file/147100607/Extended-Project-FastKart-SQLite-MYSQL-1-1pdf/
    Powered by TCPDF (www.tcpdf.org)

    You might also like