Scribd Logo
Upload

Welcome to Scribd!

  • 20 views

    SQL Joint

    Uploaded by

    ayodele.matthew.oluremi
    The document provides steps to split a column containing full names into separate first name and last name columns in SQLite. It shows how to: 1. Add new columns for first and last name 2. Populate the new columns by extracting names from the original column 3. Drop the original column once the data is migrated It also describes how to reverse the process and "undo" the changes by recreating and repopulating the original column if needed.

    Copyright:

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

    SQL Joint

    Uploaded by

    ayodele.matthew.oluremi
    20 views5 pages
    The document provides steps to split a column containing full names into separate first name and last name columns in SQLite. It shows how to: 1. Add new columns for first and last name 2. Populate the new columns by extracting names from the original column 3. Drop the original column once the data is migrated It also describes how to reverse the process and "undo" the changes by recreating and repopulating the original column if needed.

    Original Description:

    SQL Joint

    Original Title

    SQL JOINT

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document provides steps to split a column containing full names into separate first name and last name columns in SQLite. It shows how to: 1. Add new columns for first and last name 2. Populate the new columns by extracting names from the original column 3. Drop the original column once the data is migrated It also describes how to reverse the process and "undo" the changes by recreating and repopulating the original column if needed.

    Copyright:

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

    SQL Joint

    Uploaded by

    ayodele.matthew.oluremi
    The document provides steps to split a column containing full names into separate first name and last name columns in SQLite. It shows how to: 1. Add new columns for first and last name 2. Populate the new columns by extracting names from the original column 3. Drop the original column once the data is migrated It also describes how to reverse the process and "undo" the changes by recreating and repopulating the original column if needed.

    Copyright:

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

    Simple explanation on SQL JOINT

    To drop the ContactName column and keep only the First_name and Last_name columns in
    SQLite, you can use the ALTER TABLE statement. Here's an example:

    sql
    %%sql
    -- Add new columns for First_name and Last_name
    ALTER TABLE customers
    ADD COLUMN First_name TEXT;

    ALTER TABLE customers


    ADD COLUMN Last_name TEXT;

    -- Update the new columns with values from ContactName


    UPDATE customers
    SET First_name = SUBSTR(ContactName, 1, INSTR(ContactName, ' ') - 1),
    Last_name = SUBSTR(ContactName, INSTR(ContactName, ' ') + 1);

    -- Drop the ContactName column


    ALTER TABLE customers
    DROP COLUMN ContactName;

    Explanation:

    1. Add new columns: Use ALTER TABLE to add new columns for First_name and
    Last_name to the customers table.
    2. Update new columns: Use the UPDATE statement to populate the new columns with
    values derived from the ContactName column.
    3. Drop the old column: Finally, use ALTER TABLE to drop the ContactName column.

    Note: Dropping a column can lead to data loss, so make sure to back up your data or test
    this on a subset of your data first to ensure it works as expected. Also, this operation might
    not be supported in all SQLite versions. If you encounter any issues, check the SQLite
    documentation for your specific version.

    How to undo the above query

    Dropping a column is a significant operation and cannot be directly undone. However, if


    you have a backup of your database before running the queries or if you can recreate the
    dropped column and repopulate it with the original data, you can effectively "undo" the
    operation.

    Here are the steps to recreate the dropped ContactName column and update it with the
    original values:
    sql
    -- Recreate the dropped column
    ALTER TABLE customers
    ADD COLUMN ContactName TEXT;

    -- Update the ContactName column with the combination of First_name and


    Last_name
    UPDATE customers
    SET ContactName = First_name || ' ' || Last_name;

    -- Drop the added First_name and Last_name columns


    ALTER TABLE customers
    DROP COLUMN First_name;

    ALTER TABLE customers


    DROP COLUMN Last_name;

    Explanation:

    1. Recreate the dropped column: Use ALTER TABLE to add the ContactName column
    back to the customers table.
    2. Update the ContactName column: Use the UPDATE statement to repopulate the
    ContactName column with the combination of First_name and Last_name.
    3. Drop the added First_name and Last_name columns: Once the ContactName
    column is updated, you can drop the First_name and Last_name columns if they are
    no longer needed.

    Remember to have a backup before making significant changes to your database structure.
    If you don't have a backup, you may need to manually recreate the dropped column and
    populate it with the original data if possible.

    Split on postgre
    SELECT
    ContactName,
    SPLIT_PART(ContactName, ' ', 1) AS First_name,
    SPLIT_PART(ContactName, ' ', 2) AS Last_name
    FROM customers;
    Intermediate Queries
    1 SELECT
    order_id,
    total_amount
    FROM
    orders
    WHERE
    total_amount > 100;

    2
    SELECT
    c.customer_id,
    c.customer_name
    FROM
    customers c
    JOIN
    (
    SELECT
    customer_id,
    COUNT(order_id) AS order_count
    FROM
    orders
    GROUP BY
    customer_id
    HAVING
    COUNT(order_id) > 5
    ) o ON c.customer_id = o.customer_id;

    3
    SELECT
    shipment_id,
    shipment_details,
    delivery_date
    FROM
    shipments
    WHERE
    delivery_date IS NULL;
    Advancy quary

    1 %%sql

    select C.CustomerID,C.ContactName,OrderDetailID,SUM(Od.UnitPrice *
    Od.Quantity) AS Total_Spent
    FROM
    Customers C
    JOIN
    Orders O ON C.CustomerID = O.CustomerID
    JOIN
    Order_details Od ON O.orderID = Od.OrderID
    GROUP BY
    C.CustomerID, C.ContactName
    ORDER BY
    total_spent DESC

    2 SELECT
    p.ProductID,
    p.ProductName
    FROM
    zap.Products p
    LEFT JOIN
    zap.orderdetails od ON p.productID = od.productID
    WHERE
    od.orderID IS NULL;

    3
    SELECT
    DATE_TRUNC('month', o.orderDate) AS month,
    SUM(od.unitPrice * od.quantity) AS monthly_sales_total
    FROM
    zap.orders o
    JOIN
    orderdetails od ON o.orderID = od.orderID
    GROUP BY
    DATE_TRUNC('month', o.orderDate)
    ORDER BY
    month;

    postr

    SELECT
    DATE_TRUNC('month', O.OrderDate) AS Month,
    SUM(Od.UnitPrice * Od.Quantity) AS Monthly_Sales_Total
    from
    Orders O
    join
    Order_Details Od ON O.OrderID = Od.OrderID
    group by
    DATE_TRUNC('Month', O.OrderDate)
    order by
    month;
    INTERMIDEATE Select all customers that have placed more than 5 orders

    SELECT
    c.customerid,
    c.contactname,
    COUNT(DISTINCT o.orderid) AS total_orders_placed
    FROM
    zap.customers c
    JOIN
    zap.orders o ON c.customerid = o.customerid
    JOIN
    zap.orderdetails od ON o.orderid = od.orderid
    GROUP BY
    c.customerid, c.contactname
    HAVING
    COUNT(DISTINCT o.orderid) > 5;

    You might also like