+1 (315) 557-6473 

SQL Program to Implement Database Assignment Solution.


Instructions

Objective
Write a program to implement database in SQL.

Requirements and Specifications

program to implement database in SQL
program to implement database in SQL 1
program to implement database in SQL 2
program to implement database in SQL 3
program to implement database in SQL 4
program to implement database in SQL 5

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å");