Scribd Logo
Upload

Welcome to Scribd!

  • 6 views

    20bcs7635 - EXP 9

    Uploaded by

    sameer
    The document describes an experiment on performing data analysis using SQL. It involves connecting to a database, writing queries to retrieve, filter, aggregate and analyze data, joining tables, sorting data, and generating insights.

    Copyright:

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

    20bcs7635 - EXP 9

    Uploaded by

    sameer
    6 views4 pages
    The document describes an experiment on performing data analysis using SQL. It involves connecting to a database, writing queries to retrieve, filter, aggregate and analyze data, joining tables, sorting data, and generating insights.

    Original Description:

    kmk

    Original Title

    20bcs7635- EXP 9

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes an experiment on performing data analysis using SQL. It involves connecting to a database, writing queries to retrieve, filter, aggregate and analyze data, joining tables, sorting data, and generating insights.

    Copyright:

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

    20bcs7635 - EXP 9

    Uploaded by

    sameer
    The document describes an experiment on performing data analysis using SQL. It involves connecting to a database, writing queries to retrieve, filter, aggregate and analyze data, joining tables, sorting data, and generating insights.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 4

    Course Name: Business Intelligence Lab Course Code: CSP-421

    Experiment: 3.2

    Aim: Perform data analysis using SQL.


    Software Required:
     SQL Compiler

    Description: The experiment involves using SQL to perform data analysis tasks in the context of
    business intelligence. Participants will learn how to write SQL queries to retrieve, filter, aggregate, and
    analyze data stored in a database, enabling them to extract actionable insights from large datasets.
    Pseudo code/Algorithms/Flowchart/Steps:
    1. Connect to the Database:
    a. Launch a suitable SQL client or environment (e.g., MySQL Workbench, SQL Server Management
    Studio).
    b. Connect to the database containing the relevant data for analysis.

    2. Retrieve and Filter Data:


    a. Write SQL queries to retrieve specific data from the database tables.
    b. Apply filtering conditions using the WHERE clause to extract relevant subsets of data.

    3. Perform Aggregation and Grouping:


    a. Use SQL aggregate functions (e.g., SUM, COUNT, AVG) to perform calculations on numerical data.
    b. Employ the GROUP BY clause to group data based on specific columns.

    4. Join Tables:
    a. Write SQL queries to join multiple tables based on common columns.
    b. Combine related data from different tables to perform comprehensive analysis.

    5. Sort and Order Data:


    a. Utilize the ORDER BY clause to sort data based on specified columns.
    b. Ascertain the presentation order of data for better analysis and reporting.

    6. Analyze Data and Generate Insights:


    a. Use SQL functions and expressions to perform calculations and transformations on data.
    b. Employ SQL clauses and operators (e.g., HAVING, IN, BETWEEN) for advanced analysis.
    c. Generate meaningful insights by extracting patterns, trends, and summaries from the data.

    Implementation:
    INSERT INTO CUSTOMERS VALUES (7,'Brij','Mala', 24,'INDIA');
    SELECT * FROM CUSTOMERS;
    DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = 7;

    Name: Shreyansh Kumar UID: 20BCS7635


    Course Name: Business Intelligence Lab Course Code: CSP-421
    SELECT FIRST_NAME , AGE FROM CUSTOMERS WHERE COUNTRY = 'USA';
    SELECT SUM(AGE) FROM CUSTOMERS ;
    SELECT AVG(AGE) FROM CUSTOMERS ;
    SELECT MIN(AGE) FROM CUSTOMERS ;
    SELECT MAX(AGE) FROM CUSTOMERS ;
    SELECT COUNT(AGE) FROM CUSTOMERS WHERE AGE>22 ;
    SELECT CUSTOMERS.customer_id, FIRST_NAME, ORDER_ID FROM CUSTOMERS
    INNER JOIN ORDERS ON CUSTOMERS.customer_id = ORDERS.customer_id;
    SELECT * FROM orders ORDER BY amount;
    SELECT customer_id, first_name , Country FROM Customers GROUP BY Country HAVING
    last_name ='Doe';
    SELECT customer_id, first_name , age, Country FROM Customers WHERE age BETWEEN 20 and 28;
    Output:
    INSERT INTO CUSTOMERS VALUES (7,'Brij','Mala', 24,'INDIA');
    SELECT * FROM CUSTOMERS;

    DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = 7;

    Name: Shreyansh Kumar UID: 20BCS7635


    Course Name: Business Intelligence Lab Course Code: CSP-421
    SELECT FIRST_NAME , AGE FROM CUSTOMERS WHERE COUNTRY = 'USA';

    SELECT SUM(AGE) FROM CUSTOMERS ; SELECT AVG(AGE) FROM CUSTOMERS ;


    SELECT MIN(AGE) FROM CUSTOMERS ; SELECT MAX(AGE) FROM CUSTOMERS ;
    SELECT COUNT(AGE) FROM CUSTOMERS WHERE AGE>22 ;

    SELECT CUSTOMERS.customer_id, FIRST_NAME, ORDER_ID FROM CUSTOMERS


    INNER JOIN ORDERS ON CUSTOMERS.customer_id = ORDERS.customer_id;

    SELECT * FROM orders ORDER BY amount;

    Name: Shreyansh Kumar UID: 20BCS7635


    Course Name: Business Intelligence Lab Course Code: CSP-421

    SELECT customer_id, first_name , Country FROM Customers GROUP BY Country HAVING


    last_name ='Doe';

    SELECT customer_id, first_name , age, Country FROM Customers WHERE age BETWEEN 20 and 28;

    LEARNING OUTCOME:
    1. Develop proficiency in using SQL for data analysis in the context of business intelligence.
    2. Understand how to connect to databases and retrieve data using SQL queries.
    3. Learn to apply filters, aggregations, and grouping to analyze data effectively.
    4. Gain knowledge of joining tables to combine related data for comprehensive analysis.
    5. Acquire skills in sorting and ordering data for improved analysis and reporting.

    Name: Shreyansh Kumar UID: 20BCS7635

    You might also like