Can you please respond with the SQL code for all of the below and also display h
ID: 3868504 • Letter: C
Question
Can you please respond with the SQL code for all of the below and also display how it will look in Microsft Access.
Create in SQL a table for Suppliers that contains fields for the following data: Supplier ID, first name, last name, address, city, state, zip.Be sure to set the correct primary key.
Create in SQL a table for Products: Product ID, Product Name.Be sure to set the correct primary key.
Tie the two tables together by using a foreign key (Supplier ID) in Product Table and cascade the foreign key.
Enter the following data into the supplier table:
Supplier ID
First Name
Last Name
Address
City
Zip
SUP_001
John
Smith
Oak Ave
Mount Olive
28365
SUP_002
Bill
Williams
Chestnut St
Mount Olive
28365
Enter the following data into the product table:
Product ID
Product Name
Pro_001
Widget_1
Pro_002
Widget_2
Create a Customer table that has 2 customers and the fields CustomerID, Customer Name, Customer Phone. Be sure to set the correct primary key.
Create a Purchases table that includes 4 sample purchases (2 from each supplier and customer). Use primary and foreign keys as necessary, also use cascading.
Supplier ID
First Name
Last Name
Address
City
Zip
SUP_001
John
Smith
Oak Ave
Mount Olive
28365
SUP_002
Bill
Williams
Chestnut St
Mount Olive
28365
Explanation / Answer
Creation of Suppliers Table:
Create Table Suppliers(SupplierID varchar(50) not null primary key,firstname varchar(50),lastname varchar(50),address varchar(50),city varchar(50),state varchar(50),zip int)
Inserting Data to Suppliers Table
Insert into Suppliers values('SUP_001','John','Smith','Oak Ave','Mount Olive',28365)
Insert Into Suppliers values('SUP_002','Bill','Williams','Chestnut St','Mount Olive',28365)
Creation of Products Table
Crate Table Products(Product ID varchar(50) not null primary key,ProductName varchar(50),SupplierID varchar(50) References Suppliers(SupplierID))
Inserting Data into Products Table
Insert Into Product Values('Pro_001','Widget_1','Sup_001')
Insert Into Product Values('Pro_002','Widget_2','Sup_002')
Creation of Customer table
Create Table Customer(CustomerID varchar(50) not null primary key,CustomerName varchar(50),CustomerPhone int)
Insertion of Data into Customer Table
Insert Into Customer Values('Cust_001','Jhon',1234556)
Insert Into Customer Values('Cust_002','Rajak',1234557)
Creation of Purchases Table:
Craete Table Purchases(PurchaseId varchar(50) not null primary key,SupplierID varchar(50) References Suppliers(SupplierID),CustomerID varchar(50) References Customer(CustomerID))
Insertion Of Data into Purchases Table:
Insert Into Purchases Values('Pur_001','SUP_001','Cust_001')
Insert Into Purchases Values('Pur_002','SUP_001','Cust_002')
Insert Into Purchases Values('Pur_003','SUP_002','Cust_001')
Insert Into Purchases Values('Pur_004','SUP_002','Cust_002')