Bright Academic Experts

Category: MySQL

  • identify all dupicate email adresses with in a user table

    the sql query

    select Email , count (Email)

    from user

    group by Email

    having count(Email) >1 ;

  • The “Trips and Users” Scenario

    Calculate the cancellation rate of requests with unbanned users (both client and driver) each day.

    Why its tricky: It requires multiple joins, specific filtering on status, and a GROUP BY on a date rangeall in one query.

  • Microsoft Report Builder needed: Display and Analyze Health-…

    Assignment Instructions

    You must have SQL Server Express and SQL Server Management Studio (SSMS) installed to complete this assignment. You must also install the Microsoft Report Builder application. The following document provides instructions on how to install Microsoft Report Builder:

    You will need to fully understand the COVID-19 database design for this assignment. The COVID-19 database design diagram and data dictionary can be accessed via the following links:

    Provide your assignment task responses in a Microsoft Word report document. Incorporate a screenshot of each completed Microsoft Report Builder report into the assignment document as proof of completed work.

    Task 1 – Generate a Column Chart Report

    You are tasked with generating a column chart report showing the five states/jurisdictions with the highest cumulative number of reported COVID-19 cases. Use the following guidance document to generate the column chart report:

    Examine the generated column chart report. What information can be drawn from this report? What assertions can be made from this report?

    Task 2 – Generate a Pie Chart Report

    You are tasked with generating a pie chart report showing the administration percentage of each COVID-19 vaccine. Use the following guidance document to generate the pie chart report:

    Examine the generated pie chart report. What information can be drawn from this report? What assertions can be made from this report?

    Task 3 – Generate a Bar Chart Report

    You are tasked with generating a bar chart report showing the monthly COVID-19 vaccine distribution for the first six months of 2021 in the United States. Use the following guidance document to generate the bar chart report:

    Examine the generated bar chart report. What information can be drawn from this report? What assertions can be made from this report?

    Task 4 – Generate a Line Chart Report

    You are tasked with generating a line chart report showing the monthly reported COVID-19 cases in the 2021 calendar year. Use the following guidance document to generate the line chart report:

    Examine the generated line chart report. What information can be drawn from this report? What assertions can be made from this report?

    Task 5 – Generate a Tabular Report

    You are tasked with generating a tabular chart report showing the monthly reported COVID-19 cases in the 2021 calendar year. Use the following guidance document to generate the tabular report:

    Examine the generated tabular report. What information can be drawn from this report? What assertions can be made from this report?

    Task 6 – Interpret Data Dashboards

    Interpret the data dashboards by writing a 50-100 word description for each graph detailing the data.

    Requirements: 8 PAGES

  • MySQL Question

    Requirements: However long it says

  • MySQL Question

    Please follow the steps and use the correct information

    Requirements: However long the assignment says

  • Executive Report on Non-Relational Database Alternatives

    Executive Report on Non-Relational Database Alternatives

Please note the GenAI results may be incomplete and/or incorrect. You may need to revise the questions posed to the GenAI applications to get an acceptable outcome. Also, the use of multiple GenAI applications for this assignment is recommended since they offer different levels of result fidelity.

Question one or more GenAI applications to gain the following:

  • Three reasons to implement a non-relational database system at Northwind (minimum one paragraph of content)
  • Summary of the high-level functionality, benefits, and limitations of the following NoSQL database types with respect to Northwind adoption (minimum one paragraph of content for each):
    • Key-Value Data Store
    • Document Data Store
    • Columnar Data Store
    • Graph Data Store
  • Summary of the high-level functionality, benefits, and limitations of the following reporting and analytical database types with respect to Northwind adoption (minimum one paragraph of content for each):
    • Big Data (e.g., Hadoop)
    • Online Analytical Processing (OLAP)
  • Three use cases involving non-relational database systems that relate to Northwind adoption (minimum one paragraph of content)

Copy the GenAI-extracted content and paste it into the report document. Organize the GenAI-produced content in the report document to enhance readability, flow, and structure. Revise the content as required to maintain proper grammar and spelling. Provide a citation for each batch of content with the GenAI source denoted in the reports reference section.

Take screenshots of the question-answer sessions with the GenAI applications. The screenshots need to be included in an appendix of the executive-level report. Each screenshot needs to show the full question(s) given to the GenAI application and a partial set of results (i.e., you do not need to show the entire GenAI output for proof of performed work).

Report Parameters/Expectations

Compile the obtained information into an executive-level report written in Microsoft Word (or equivalent). Your paper must contain the following:

  • Title page
  • Minimum of three full pages containing researched content (not including title, reference, and appendix pages)
  • Reference page(s) denoting the utilized GenAI application(s) and any scholarly resources (minimum of one reference)
  • An appendix containing the GenAI interaction screenshots

The report must adhere to the most current APA guidelines and contain proper grammar and spelling since it is being presented to Northwind Trader executives. The report does not need to have an abstract section or table of contents. Also, review the university policy on plagiarism. If you have any questions, please contact your professor. For assistance with APA requirements, please go to Academic Writer. You will find the link in the Academic Tools section of the course.

Requirements: long answer

  • MySQL Question

    Requirements:

  • MySQL Question

    I would like this homework assignment to be completed using the instructions for each question/exercise in the assignment. Once the assignment is complete and returned to me, I will save/screenshot and key in the answers into the database. I just need the SELECT statements/answers to key into the Editor screen of MySQL.

    Requirements:

  • Accounting Project 1 – Prepare Financial Statements

    Instructions and Excel file attached below.

    Requirements: however long it needs to be

  • Retrieving Data Using Advanced Queries & Transactions an…

    Task 1: Retrieving Data Using Advanced Queries

    You will use generative artificial intelligence (GenAI) applications to establish SQL statements retrieving data from the Northwind database for the prompts presented below. You will need to upload a copy of the Northwind physical database design diagram (, , ) to the GenAI application for each prompt. Take a screenshot of the GenAI output showing the SQL statement and paste it into your report document. Copy and paste the GenAI-created SQL statement into an SSMS or ADS query window. Modify, if necessary, the SQL statement to correct for syntax or compatibility errors. Run the query statement in SSMS or ADS. Take screenshots showing each SQL query and the execution results and paste them into the Microsoft Word report document.

    Here is a list of available free public GenAI applications that can be utilized:

    You must scrutinize the output from GenAI applications. The results may be incomplete and/or incorrect. You may need to revise the question posed to the GenAI applications to get an acceptable outcome.

    Here is an example of using Anthropic Claude to generate a query statement:

    A screenshot showing the interface of a generative AI tool. The top of the screen displays a text box where a user has entered a prompt: "Generate a query presenting the list of suppliers with 5

    Here is an example execution of the GenAI-provided SQL statement in Microsoft SSMS:

    SQL query selecting Supplier ID, Company Name, and product count from suppliers and products tables. The query groups by Supplier ID and Company Name, having at least 5 products, and orders by product

    Problem 1: Create a report of seafood and produce products, showing ProductID, ProductName, and CategoryName. Incorporate an inner join condition for this query.

    Expected Output

    alt="The expected query output showing the ProductID, ProductName, and Category Name of 'seafood' and 'produce' products."

    Problem 2: List the last name, first name, title, and salary of company employees with salaries above the company average. Use a non-correlated subquery in the SQL statement.

    Expected Output

    The expected query output showing the last name, first name, title, and salary of company employees with salaries above the company average.

    Problem 3: List the average salaries for employees in Seattle and London. The averages need to be calculated on a per city basis. Use a union operation to generate the results. (Hint: Use one SQL statement to calculate the average salary for one city and another almost identical SQL statement to calculate the average salary for the other city.)

    Expected Output

    The expected query output showing the average salaries of Seattle and London employees.

    Problem 4: Show the product names for products that have been ordered in quantities equal to or exceeding 120. Use a non-correlated subquery in the SQL statement.

    Expected Output

    The expected query output listing the products that have been ordered in quantities equal to or exceeding 120.

    Problem 5: List the supplier names and cities for suppliers that reside in the same cities as Northwind employees. Use a non-correlated subquery in the SQL statement.

    Expected Output

    The expected query output listing the supplier names and cities for suppliers residing in the same cities as Northwind employees.

    Problem 6: Display the names of Northwind employees that manage territories located in the Western region. Use inner joins in the SQL statement linking the Employees, EmployeeTerritories, Territories, and Region tables. Do not show duplicate employee names in the result set.

    Expected Output

    The expected query output listing the names of Northwind employees that manage territories in the Western region.

    Problem 7: Display customer names, cities, and order IDs for customers residing in Madrid or Paris. Show all customers regardless of whether they have placed orders or not. Use an outer join in the SQL statement.

    Expected Output

    The expected query output listing the customer names, cities, and order IDs for customers residing in Madrid or Paris.

    Problem 8: Display a combined list of supplier and shipper names along with their phone numbers. Use a union operation in the SQL statement. Present the results in alphabetical order based on CompanyName.

    Expected Output

    The expected query output displaying a combined list of supplier and shipper names along with their phone numbers.

    Problem 9: Show the employee names, salaries, and countries for employees that have salaries above the average salary within their respective countries. Use a correlated subquery in the SQL statement.

    Expected Output

    The expected query output showing the employee names, salaries, and countries for employees that have salaries above the average salary within their respective countries.

    Problem 10: Display the names of products supplied by vendors in the USA and Norway. Show the product country in the result set. Present the results in alphabetical order by product name. Use an inner join in the SQL statement.

    Expected Output

    The expected query output displaying the names of products supplied by vendors in the USA and Norway.

    To complete this assignment, Microsoft SQL Server Express and either SQL Server Management Studio (SSMS) or Azure Data Studio (ADS) must be installed. The Northwind database must be instantiated within the environment.

    Compose your assessment in a Microsoft Word report document, and be sure to identify yourself, your class, and the module assessment at the top of your paper. Embed the screenshots of your GenAI outputs, SQL statements, and confirmatory output (e.g., table structure definitions, query results, etc.) into the Microsoft Word report document.

    Task 2: Transactions and Security Implementations

    Complete the following problems in either Microsoft SQL Server Management Studio (SSMS) or Azure Data Studio (ADS). Take screenshots of the SQL statements and execution/confirmatory results and place them in a Microsoft Word report document.

    Use the Northwind physical database design diagram (, , ) as a guide for this assignment.

    Problem 1: Create a view called EmployeeDirectory that displays the first name, last name, title, and phone extension of all company employees. Use the CREATE VIEW command to accomplish this task. Then execute a query statement that selects all employees from the EmployeeDirectory view.

    Problem 2: Create a stored procedure that increases an employees salary by a specified raise percentage. The skeleton of the stored procedure is provided below.

    CREATE PROCEDURE GiveEmployeeRaise
    @EmployeeID INT, @RaisePercentage DECIMAL
    AS
    <REPLACE WITH YOUR SQL CODE>

    The equation for computing a new salary is as follows:

    New Salary = Old Salary * (1 + Raise Percentage/100)

    If an employee with EmployeeID = 9 gets a 5% raise, the stored procedure call would be as follows:

    EXEC GiveEmployeeRaise @EmployeeID = 9, @RaisePercentage = 5;

    Problem 3: Create and execute a transaction block that contains two DML statements. The first statement updates the title for all employees to President. The second statement inserts a new region record with a RegionID = 10 and a RegionDescription = Antarctica. Incorporate these statements within the SQL block specified below:

    BEGIN TRANSACTION

    <REPLACE WITH INSERT/UPDATE STATEMENTS>

    SELECT * FROM Employees;
    SELECT * FROM Region;

    ROLLBACK TRANSACTION

    SELECT * FROM Employees;
    SELECT * FROM Region;

    Execute the completed SQL block in a Microsoft SSMS query window.

    Briefly explain what happened with the execution of this transaction. Provide screenshots of the data before and after the ROLLBACK TRANSACTION statement. Please note the query results will appear in 4 separate sections in the Results area of Microsoft SSMS following the execution of the above SQL block.

    Problem 4: You are asked to add three new products to an existing order with OrderID = 11061. The additional records need to be added to the OrderDetails table with the following information:

    Record 1

    OrderID = 11061

    ProductID = 62

    UnitPrice = 45

    Quantity = 10

    Discount = 0

    Record 2

    OrderID = 11061

    ProductID = 70

    UnitPrice = 14

    Quantity = 25

    Discount = 0

    Record 3

    OrderID = 11061

    ProductID = 1000

    UnitPrice = 100

    Quantity = 5

    Discount = 0

    Incorporate the SQL insert statements for the new records into the transaction block specified below and execute in a Microsoft SSMS query window:

    BEGIN TRANSACTION NewOrderDetails

    BEGIN TRY

    <REPLACE WITH INSERT STATEMENTS>

    COMMIT TRANSACTION NewOrderDetails;

    END TRY

    BEGIN CATCH

    ROLLBACK TRANSACTION NewOrderDetails

    END CATCH

    SELECT * FROM OrderDetails
    WHERE OrderID = 11061;

    Briefly explain what happened with the execution of this transaction. Do the new records get inserted into the OrderDetails table? If not, why?

    Problem 5: Create two new roles in the Northwind database:

    • SalesPerson
    • SalesManager

    Use the CREATE ROLE command to complete this task. Execute the following SQL statement to confirm the establishment of the new database roles:

    SELECT name AS “Database Role”
    FROM sys.database_principals
    WHERE type = ‘R’ AND tenant_id IS NOT NULL;

    Problem 6: Use Data Control Language (DCL) statements that manage database user permissions.

    • Grant select, insert, and update permissions for Sales-related tables (Orders & OrderDetails) to the SalesPerson role.
    • Grant select and delete permissions for Sales-related tables (Orders & OrderDetails) to the SalesManager role.
    • Grant select permissions for the EmployeeDirectory view to the SalesManager role.

    Use the GRANT command to complete this task. Execute the following SQL statements to confirm the role permissions:

    EXEC sp_helprotect ‘Orders’;

    EXEC sp_helprotect ‘OrderDetails’;

    EXEC sp_helprotect ‘EmployeeDirectory’;

    Problem 7: Create two new users named Jill and Jack. For this exercise, each established Northwind database user account will not be associated with an SQL Server login account. Use the CREATE USER command to accomplish this task.

    CREATE USER <username> WITHOUT LOGIN;

    Execute the following SQL statement to confirm the addition of the new database users:

    SELECT name AS “Database User”
    FROM sys.database_principals
    WHERE type = ‘S’ AND tenant_id IS NOT NULL;

    Problem 8: Grant the roles specified below to Jane, Joan, Joe, and James.

    • Give Jack the role of SalesPerson.
    • Give Jill the role of SalesManager

    Use the ALTER ROLE command to accomplish the role-granting work.

    ALTER ROLE <role_name> ADD MEMBER <user_name>;

    Execute the following SQL statement to confirm the role memberships:

    SELECT DP1.name AS DatabaseRoleName,
     DP2.name AS DatabaseUserName
    FROM sys.database_role_members AS DRM
     RIGHT OUTER JOIN sys.database_principals AS DP1
     ON DRM.role_principal_id = DP1.principal_id
     LEFT OUTER JOIN sys.database_principals AS DP2
     ON DRM.member_principal_id = DP2.principal_id
    WHERE DP2.name IS NOT NULL
     AND DP2.type = ‘S’
     AND DP1.type = ‘R’
    ORDER BY DP1.name;

    Problem 9: Generate SQL statements as the user Jack in a query window to do the following:

    1. Update the order quantity to 50 for OrderID = 10249 and ProductID = 51.
    2. Delete the record in OrderDetails with OrderID = 10251 and ProductID = 65.
    3. Insert a new order item into the OrderDetails table with the following specifics:

      OrderID = 10251
      ProductID = 48
      UnitPrice = 12.24
      Quantity = 10
      Discount = 0.04

    4. Display all of the records from the EmployeeDirectory view established in Problem 1

    Use the following code block to execute each SQL command as Jack:

    EXECUTE AS USER = ‘Jack’;
    <SQL STATEMENT(S)>
    REVERT;
    GO

    Replace the <SQL Statement(S)> tag with the SQL statement(s) addressing the applicable problem prompt.

    For example, the following query looking for order products with quantities of 120 or more is executed as the user Jack:

    The results of an SQL query executed by the user "Jack." The results grid below the query displays columns for order ID, product name, and quantity. The data includes several rows with order IDs, pro

    Provide screenshots of the SQL statement outputs. Give a brief explanation for any statement that failed to execute

    Problem 10: Generate SQL statements as the user Jill in a query window to do the following:

    1. Update the order quantity to 60 for OrderID = 10249 and ProductID = 51.
    2. Delete the record in OrderDetails with OrderID = 10251 and ProductID = 65.
    3. Insert a new order item into the OrderDetails table with the following specifics:

      OrderID = 11077
      ProductID = 48
      UnitPrice = 12.24
      Quantity = 20
      Discount = 0.04

    4. Display all of the records from the EmployeeDirectory view established in Problem 1

    Use the following code block to execute each SQL command as Jack:

    EXECUTE AS USER = ‘Jill’;
    <SQL STATEMENT(S)>
    REVERT;
    GO

    Replace the <SQL Statement(S)> tag with the SQL statement(s) addressing the applicable problem prompt.

    For example, the following query looking for the count of order products with a 25% discount is executed as the user Jill:

    The results of an SQL query executed by the user "Jill." The results grid below the query displays a single row with a column labeled "25% Discount Items," showing the total number of order products t

    Provide screenshots of the SQL statement outputs. Provide a brief explanation for any statement that failed to execute.

    Compose your assessment in a Microsoft Word report document, and be sure to identify yourself, your class, and the module assessment at the top of your paper. Embed the screenshots of your GenAI outputs, SQL statements, and confirmatory output (e.g., table structure definitions, query results, etc.) into the Microsoft Word report document.

    Requirements: goog answer all the stepts