SMMS Stored Procedure Assignment 2022 parsa shabari

.docx

School

University of British Columbia *

*We aren’t endorsed by this school

Course

395

Subject

Computer Science

Date

May 20, 2024

Type

docx

Pages

3

Uploaded by CorporalNarwhal1452

Report
BSYS 3105 | SSMS Stored Procedure Assignment Instructions Create and document the following Stored Procedures for the Northwind database. Under each question below, copy/paste the SQL code used to create the Stored Procedure. For example: Q: Display all records from the Quarterly Orders view CREATE PROCEDURE ShowQuarterlyOrders AS SELECT * FROM [Quarterly Orders] (Hint: You could use the query editor to help you write SQL statement: Query Menu Design Query in Editor and/or use the shortcut CTRL|SHIFT|Q while editing in the SQL window. Check out the separate hint notes posted as well) Q1. List Products for user defined Product Category name CREATE procedure [dbo].[FindCategoryName] @CategoryName nvarchar (15) As Begin SELECT [Products].[ProductName], [Products].[ProductID], [CategoryName] FROM Products INNER JOIN Categories ON Products.CategoryID = [Categories].[CategoryID] Where [Categories].[CategoryName] = @CategoryName End Q2. List customer Orders from user defined Country and City CREATE procedure [dbo].[FindCustomerOrders] @Country nvarchar (40), @City nvarchar (40) As Begin SELECT [orders].[CustomerID], [orders].[EmployeeID] FROM Orders INNER JOIN Customers ON [Orders].[CustomerID] = [Customers].[CustomerID] Where [Customers].[Country] = @Country and [Customers].[City] = @City End Q3. List Orders for user defined Employee last name CREATE Procedure [dbo].[OrderByEmployeeLastName] @lastname nvarchar (40) As Begin SELECT [Orders].[OrderID], [Employees].[LastName] FROM Orders INNER JOIN Employees ON [Orders].[EmployeeID] = [Employees].[EmployeeID] Where [employees].[lastname] = @lastname
End Q4. Display Total sales for user defined Product Category name CREATE PROCEDURE [dbo].[Total_by_CategoryName] @CategoryName nvarchar(15) AS SELECT Categories.CategoryName, SUM([Order Details].Quantity * Products.UnitPrice) AS Total FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID WHERE (Categories.CategoryName = @CategoryName) GROUP BY CategoryName Q5. Display the Number of sales for user defined Supplier and Year (Use YEAR function) CREATE PROCEDURE [dbo].[SalesbySupplierandYear] @CompanyName nvarchar(40), @year int AS BEGIN SELECT CompanyName, YEAR(Orders.OrderDate) as OrderYear, COUNT(Orders.OrderID) AS NumberofSales FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN Products ON [Order Details].ProductID = Products.ProductID INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID WHERE CompanyName = @CompanyName AND YEAR(Orders.OrderDate) = @year GROUP BY CompanyName, YEAR(Orders.OrderDate) END Q6. As above, but assume we know only part of Supplier name (Keyword Search – Use LIKE operator) CREATE PROCEDURE [dbo].[SalesbySupplierandYearNotKnowingName] @CompanyName nvarchar(40), @year int AS BEGIN SELECT CompanyName, YEAR(Orders.OrderDate) as OrderYear, COUNT[Orders].[OrderID] AS NumberofSales FROM Orders INNER JOIN [Order Details] ON [Orders].[OrderID] = [Order Details].[OrderID] INNER JOIN Products ON [Order Details].[ProductID] = Products.ProductID INNER JOIN Suppliers ON [Products].[SupplierID] = [Suppliers].[SupplierID] WHERE CompanyName = @CompanyName AND YEAR(Orders.OrderDate) = @year and CompanyName LIKE '__%' GROUP BY CompanyName, YEAR[Orders].[OrderDate] END Q7. Create a customer Order with one Product (INSERT statement for Orders and Order Details tables) One SP that includes two statements:
Step 1. Add a new order in the Orders table with parameters for CustomerID and EmployeeID and the current date. (Remember: NO need for OrderID in the INSERT as it is an Identity field. Use the GETDATE() function for the current date.) Step 2. Add a record in the Order Details table for the Order you just created above. Instead of OrderID enter @@IDENTITY (SQL Server stores the last created identity value in this variable). Check out the Order Details table design to see the required fields.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Recommended textbooks for you
Text book image
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
Text book image
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
COMPREHENSIVE MICROSOFT OFFICE 365 EXCE
Computer Science
ISBN:9780357392676
Author:FREUND, Steven
Publisher:CENGAGE L
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
Text book image
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
COMPREHENSIVE MICROSOFT OFFICE 365 EXCE
Computer Science
ISBN:9780357392676
Author:FREUND, Steven
Publisher:CENGAGE L
SEE MORE TEXTBOOKS