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:

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

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

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

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

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

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

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

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

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

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

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

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:

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:

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.

WRITE MY PAPER