Program to Create Implement 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å");

SQL Program to Implement Database Assignment Solution.

Instructions

Requirements and Specifications