Step-by-Step Guide to Implementing a Database Assignment Solution

Instructions

Objective

Write a SQL assignment program to implement a database in SQL. This assignment could involve creating tables, defining relationships, and performing various queries to manipulate and retrieve data. SQL assignments are a common way for students to practice their database management skills and gain hands-on experience with structuring and interacting with databases.

Requirements and Specifications

Source Code

TABLE CREATION

-- Exported from QuickDBD: https://www.quickdatabasediagrams.com/ -- NOTE! If you have used non-SQL datatypes in your design, you will have to change these here. SET XACT_ABORT ON BEGIN TRANSACTION QUICKDBD CREATE TABLE [Persons] ( [PersonID] int NOT NULL , [LastName] VARCHAR(255) NOT NULL , [FirstName] VARCHAR(255) NOT NULL , [Address] VARCHAR(255) NOT NULL , CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED ( [PersonID] ASC ) ) CREATE TABLE [Customers] ( [CustomerID] int NOT NULL , [CustomerName] VARCHAR(255) NOT NULL , [ContactName] VARCHAR(255) NOT NULL , [Address] VARCHAR(255) NOT NULL , [City] VARCHAR(255) NOT NULL , [PostalCode] VARCHAR(255) NOT NULL , [Country] VARCHAR(255) NOT NULL , CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ( [CustomerID] ASC ) ) CREATE TABLE [Categories] ( [CategoryID] int NOT NULL , [CategoryName] VARCHAR(255) NOT NULL , [Description] VARCHAR(255) NOT NULL , CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED ( [CategoryID] ASC ) ) CREATE TABLE [Employees] ( [EmployeeID] int NOT NULL , [LastName] VARCHAR(255) NOT NULL , [FirstName] VARCHAR(255) NOT NULL , [Photo] BLOB NOT NULL , [Notes] VARCHAR(500) NOT NULL , CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ( [EmployeeID] ASC ) ) CREATE TABLE [Orders] ( [OrderID] int NOT NULL , [CustomerID] int NOT NULL , [EmployeeID] int NOT NULL , [OrderDate] DATETIME NOT NULL , [ShipperID] int NOT NULL , CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [OrderID] ASC ) ) CREATE TABLE [OrderDetails] ( [OrderDetailID] int NOT NULL , [OrderID] int NOT NULL , [ProductID] int NOT NULL , [Quantity] int NOT NULL , CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED ( [OrderDetailID] ASC ) ) CREATE TABLE [Products] ( [ProductID] int NOT NULL , [ProductName] VARCHAR(255) NOT NULL , [SupplierID] int NOT NULL , [CategoryID] int NOT NULL , [Unit] VARCHAR(255) NOT NULL , [Price] DECIMAL NOT NULL , CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ( [ProductID] ASC ) ) CREATE TABLE [Shippers] ( [ShipperID] int NOT NULL , [ShipperName] VARCHAR(255) NOT NULL , [Phone] VARCHAR(255) NOT NULL , CONSTRAINT [PK_Shippers] PRIMARY KEY CLUSTERED ( [ShipperID] ASC ) ) CREATE TABLE [Suppliers] ( [SupplierID] int NOT NULL , [SupplierName] VARCHAR(255) NOT NULL , [ContactName] VARCHAR(255) NOT NULL , [Address] VARCHAR(255) NOT NULL , [City] VARCHAR(255) NOT NULL , [PostalCode] VARCHAR(255) NOT NULL , [Country] VARCHAR(255) NOT NULL , [Phone] VARCHAR(255) NOT NULL , CONSTRAINT [PK_Suppliers] PRIMARY KEY CLUSTERED ( [SupplierID] ASC ) ) ALTER TABLE [Customers] WITH CHECK ADD CONSTRAINT [FK_Customers_CustomerID] FOREIGN KEY([CustomerID]) REFERENCES [Persons] ([PersonID]) ALTER TABLE [Customers] CHECK CONSTRAINT [FK_Customers_CustomerID] ALTER TABLE [Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_EmployeeID] FOREIGN KEY([EmployeeID]) REFERENCES [Persons] ([PersonID]) ALTER TABLE [Employees] CHECK CONSTRAINT [FK_Employees_EmployeeID] ALTER TABLE [Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_CustomerID] FOREIGN KEY([CustomerID]) REFERENCES [Customers] ([CustomerID]) ALTER TABLE [Orders] CHECK CONSTRAINT [FK_Orders_CustomerID] ALTER TABLE [Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_EmployeeID] FOREIGN KEY([EmployeeID]) REFERENCES [Employees] ([EmployeeID]) ALTER TABLE [Orders] CHECK CONSTRAINT [FK_Orders_EmployeeID] ALTER TABLE [Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_ShipperID] FOREIGN KEY([ShipperID]) REFERENCES [Shippers] ([ShipperID]) ALTER TABLE [Orders] CHECK CONSTRAINT [FK_Orders_ShipperID] ALTER TABLE [OrderDetails] WITH CHECK ADD CONSTRAINT [FK_OrderDetails_OrderID] FOREIGN KEY([OrderID]) REFERENCES [Orders] ([OrderID]) ALTER TABLE [OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_OrderID] ALTER TABLE [OrderDetails] WITH CHECK ADD CONSTRAINT [FK_OrderDetails_ProductID] FOREIGN KEY([ProductID]) REFERENCES [Products] ([ProductID]) ALTER TABLE [OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_ProductID] ALTER TABLE [Products] WITH CHECK ADD CONSTRAINT [FK_Products_SupplierID] FOREIGN KEY([SupplierID]) REFERENCES [Suppliers] ([SupplierID]) ALTER TABLE [Products] CHECK CONSTRAINT [FK_Products_SupplierID] ALTER TABLE [Products] WITH CHECK ADD CONSTRAINT [FK_Products_CategoryID] FOREIGN KEY([CategoryID]) REFERENCES [Categories] ([CategoryID]) ALTER TABLE [Products] CHECK CONSTRAINT [FK_Products_CategoryID] ALTER TABLE [Shippers] WITH CHECK ADD CONSTRAINT [FK_Shippers_ShipperID] FOREIGN KEY([ShipperID]) REFERENCES [Persons] ([PersonID]) ALTER TABLE [Shippers] CHECK CONSTRAINT [FK_Shippers_ShipperID] COMMIT TRANSACTION QUICKDBD ROW INSERT INSERT INTO Persons (PersonID, LastName, FirstName, Address, City) VALUES (1, "Anders", "Maria", "Obere Str. 57", "Berlin"); INSERT INTO Persons (PersonID, LastName, FirstName, Address, City) VALUES (2, "Trujillo", "Ana", "Avda. de la Constitución 2222", "México D.F."); INSERT INTO Persons (PersonID, LastName, FirstName, Address, City) VALUES (3, "Moreno", "Antonio", "Mataderos 2312", "México D.F."); INSERT INTO Persons (PersonID, LastName, FirstName, Address, City) VALUES (4, "Hardy", "Thomas", "120 Hanover Sq.", "London"); INSERT INTO Persons (PersonID, LastName, FirstName, Address, City) VALUES (5, "Berglund", "Christina", "Berguvsvägen 8", "Luleå");