Scribd Logo
Upload

Welcome to Scribd!

  • 22 views

    BDMS Assignment 1 SHS

    Uploaded by

    Shamanta H. Shammi
    The document contains SQL queries written by Shamanta Hoque Shammi for an assignment involving querying tables, joining tables, and aggregation. The assignment includes 4 problems with multiple parts each: 1) querying a single table, 2) joining tables, 3) aggregation, and 4) modifying queries to filter or include additional results. For each problem, Shammi provides the question and SQL query to return the requested data.

    Copyright:

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

    BDMS Assignment 1 SHS

    Uploaded by

    Shamanta H. Shammi
    22 views10 pages
    The document contains SQL queries written by Shamanta Hoque Shammi for an assignment involving querying tables, joining tables, and aggregation. The assignment includes 4 problems with multiple parts each: 1) querying a single table, 2) joining tables, 3) aggregation, and 4) modifying queries to filter or include additional results. For each problem, Shammi provides the question and SQL query to return the requested data.

    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 contains SQL queries written by Shamanta Hoque Shammi for an assignment involving querying tables, joining tables, and aggregation. The assignment includes 4 problems with multiple parts each: 1) querying a single table, 2) joining tables, 3) aggregation, and 4) modifying queries to filter or include additional results. For each problem, Shammi provides the question and SQL query to return the requested data.

    Copyright:

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

    BDMS Assignment 1 SHS

    Uploaded by

    Shamanta H. Shammi
    The document contains SQL queries written by Shamanta Hoque Shammi for an assignment involving querying tables, joining tables, and aggregation. The assignment includes 4 problems with multiple parts each: 1) querying a single table, 2) joining tables, 3) aggregation, and 4) modifying queries to filter or include additional results. For each problem, Shammi provides the question and SQL query to return the requested data.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 10

    Assignment 1: SQL Queries

    Name: Shamanta Hoque Shammi


    Problem 1: Querying a Single Table
    1a: Write a Query that returns the following data from the Customers table:
    a. FirstName and Last name concatenated into a single column,
    b. The Company Name
    c. Their city
    d. Their state
    1a: Query:
    SELECT FirstName || ' ' || LastName AS FullName,
    Company AS CompanyName,
    City,
    State
    FROM Customers;
    Order the resulting data by city.
    1a: Query:
    SELECT FirstName || ' ' || LastName AS FullName,
    Company AS CompanyName,
    City,
    State
    FROM Customers
    ORDER BY City;

    1b: Modify your query to only return customers from Canada or the United States
    1b Query:
    SELECT FirstName || ' ' || LastName AS FullName,
    Company AS CompanyName,
    City,
    State
    FROM Customers
    WHERE Country IN ('Canada', 'United States')
    ORDER BY City;
    1c: Modify your query to only return customers in Canada or the United States
    whose last name starts with the letter M.
    1c Query:
    SELECT FirstName || ' ' || LastName AS FullName,
    Company AS CompanyName,
    City,
    State
    FROM Customers
    WHERE Country IN ('Canada', 'United States')
    AND LastName LIKE 'M%'
    ORDER BY City;

    Problem 2: Joining tables


    2a: Write a query white returns the following information from the Artist Albums
    and Tracks tables:
    a. Artist Name
    b. Album Title
    c. Track Names Order the results by Artists
    2a Query:
    SELECT
    artists.Name AS "Artist Name",
    albums.Title AS "Album Title",
    tracks.Name AS "Track Names"
    FROM
    artists
    JOIN
    albums ON artists.ArtistId = albums.ArtistId
    JOIN
    tracks ON albums.AlbumId = tracks.AlbumId
    ORDER BY artists.Name;
    2b: Modify the previous query so it only returns tracks that have the word
    “dancing” somewhere in the track name.
    2b Query:
    SELECT
    artists.Name AS "Artist Name",
    albums.Title AS "Album Title",
    tracks.Name AS "Track Names"
    FROM
    artists
    JOIN
    albums ON artists.ArtistId = albums.ArtistId
    JOIN
    tracks ON albums.AlbumId = tracks.AlbumId
    WHERE
    tracks.Name LIKE '%dancing%'
    ORDER BY
    artists.Name;
    2c: You have been asked to create a org chart for the company. Create a query
    that returns two columns one with the employee’s first and last name and the
    second with their managers first and last name.
    Example:
    Employee Manager
    Nancy Edwards | Andrew Adams
    2c Query:
    SELECT
    e.FirstName || ' ' || e.LastName AS Employee,
    m.FirstName || ' ' || m.LastName AS Manager
    FROM employees e
    LEFT JOIN employees m ON e.ReportsTo = m.EmployeeId
    ORDER BY Manager, Employee;
    2d: When you review the list you notice that the General Manager, Andrew
    Adams, is not included on the list. Modify your query from 2c: to include Andrew
    so the record looks as follows: “Andrew Adams reports to himself”

    2d Query:
    SELECT
    e.FirstName || ' ' || e.LastName AS Employee,
    CASE
    WHEN m.FirstName IS NULL THEN e.FirstName || ' ' || e.LastName
    ELSE m.FirstName || ' ' || m.LastName
    END AS Manager
    FROM employees e
    LEFT JOIN employees m ON e.ReportsTo = m.EmployeeId
    ORDER BY Manager, Employee;

    Problem 3: Aggregation
    3a: Create a report that that Joins the Albums to Tracks table and returns the title
    of each album and the number of tracks it contains.
    3a Query:
    SELECT
    a.Title AS AlbumTitle,
    COUNT(t.TrackId) AS NumberOfTracks
    FROM
    albums a
    JOIN
    tracks t ON a.AlbumId = t.AlbumId
    GROUP BY
    a.AlbumId, a.Title
    ORDER BY
    a.Title;
    3b: Modify your query from 3a so that the report only shows albums that have
    more than ten tracks.
    3b Query:
    SELECT
    a.Title AS AlbumTitle,
    COUNT(t.TrackId) AS NumberOfTracks
    FROM
    albums a
    JOIN
    tracks t ON a.AlbumId = t.AlbumId
    GROUP BY
    a.AlbumId, a.Title
    HAVING
    COUNT(t.TrackId) > 10
    ORDER BY a.Title;
    SQL RTF file:
    -- Shamanta Hoque Shammi | Assignment 1

    -- Problem 1: Querying a Single Table

    -- 1a: Query:
    SELECT
    FirstName || ' ' || LastName AS FullName,
    Company AS CompanyName,
    City,
    State
    FROM Customers
    ORDER BY City;

    -- 1b Query:
    SELECT
    FirstName || ' ' || LastName AS FullName,
    Company AS CompanyName,
    City,
    State
    FROM Customers
    WHERE Country IN ('Canada', 'United States')
    ORDER BY City;

    -- 1c Query:
    SELECT
    FirstName || ' ' || LastName AS FullName,
    Company AS CompanyName,
    City,
    State
    FROM Customers
    WHERE Country IN ('Canada', 'United States')
    AND LastName LIKE 'M%'
    ORDER BY City;

    -- Problem 2: Joining tables

    -- 2a Query:
    SELECT
    artists.Name AS "Artist Name",
    albums.Title AS "Album Title",
    tracks.Name AS "Track Names"
    FROM
    artists
    JOIN
    albums ON artists.ArtistId = albums.ArtistId
    JOIN
    tracks ON albums.AlbumId = tracks.AlbumId
    ORDER BY artists.Name;

    -- 2b Query:
    SELECT
    artists.Name AS "Artist Name",
    albums.Title AS "Album Title",
    tracks.Name AS "Track Names"
    FROM
    artists
    JOIN
    albums ON artists.ArtistId = albums.ArtistId
    JOIN
    tracks ON albums.AlbumId = tracks.AlbumId
    WHERE
    tracks.Name LIKE '%dancing%'
    ORDER BY
    artists.Name;

    -- 2c Query:
    SELECT
    e.FirstName || ' ' || e.LastName AS Employee,
    m.FirstName || ' ' || m.LastName AS Manager
    FROM employees e
    LEFT JOIN employees m ON e.ReportsTo = m.EmployeeId
    ORDER BY Manager, Employee;

    -- 2d Query:
    SELECT
    e.FirstName || ' ' || e.LastName AS Employee,
    CASE
    WHEN m.FirstName IS NULL THEN e.FirstName || ' ' || e.LastName
    ELSE m.FirstName || ' ' || m.LastName
    END AS Manager
    FROM employees e
    LEFT JOIN employees m ON e.ReportsTo = m.EmployeeId
    ORDER BY Manager, Employee;
    -- Problem 3: Aggregation

    -- 3a Query:
    SELECT
    a.Title AS AlbumTitle,
    COUNT(t.TrackId) AS NumberOfTracks
    FROM
    albums a
    JOIN
    tracks t ON a.AlbumId = t.AlbumId
    GROUP BY
    a.AlbumId, a.Title
    ORDER BY
    a.Title;

    -- 3b Query:
    SELECT
    a.Title AS AlbumTitle,
    COUNT(t.TrackId) AS NumberOfTracks
    FROM
    albums a
    JOIN
    tracks t ON a.AlbumId = t.AlbumId
    GROUP BY
    a.AlbumId, a.Title
    HAVING
    COUNT(t.TrackId) > 10
    ORDER BY a.Title;

    You might also like