Question

MIS Corp is a mid tier company involved in the production and sales of home products. The

company wants to build a small database to maintain data about its customers and the products

it will sell to its customers. MIS also wants to keep information about what its customers were

buying in a Sales table.

The table definitions are as follows:

Customer [ CustID, LastName, FirstName, Address, City, State, Zip, Phone, Fax, Email]

Product [ ProdID, Description, Color, Size, Pack]

Sales [ TransID, CustID, ProdID, Price, Quantity, Amount]

Using SQL Server, create a database MIS310GRPXX. Write SQL statements and answer questions for this database as follows:

1. Write SQL CREATE TABLE statements for each of the above tables.

2. Write primary key - foreign key constraints for the relationships in each of these tables.

3. Write SQL INSERT INTO statements to insert data into these tables – the sample files are provided in excel sheets
4. Write SQL statements to list all columns for all tables. There will be 1 SQL statement for each of the 3 tables.

Answer 1

Dear Student ,

As per requirement submitted above kindly find below solution.

This demonstration is using SQL Server.

--create database
create database MIS310GRPXX;

use MIS310GRPXX;--use database to create table

--1.Table Name :Customer
create table Customer(
CustID int primary key,
LastName varchar(50),
FirstName varchar(50),
Address varchar(50),
City varchar(50),
State varchar(50),
Zip varchar(10),
Phone varchar(10),
Fax varchar(50),
Email varchar(50));

--inserting records into customer table
insert into Customer values (1,'Fedrer','Roger','Swizerland','Paris','MH',
'456123','8899889988','7894561230','fedRoger@fg.com');

--selecting record
select * from customer;

Screen in SQL Server:

*************************************

--2.Table Name :Product
create table Product(
ProdID int primary key,
Description varchar(50),
Color varchar(50),
Size varchar(50),
Pack varchar(50));

--inserting records into Product table
insert into Product values (1,'Tennis Racket','White','4.5','10');

--selecting record
select * from Product;

Screen in SQL Server:

*************************************

--3.Table Name :Sales
Create table Sales(
TransID int primary key,
CustID int ,
ProdID int,
Price decimal(6,2),
Quantity int,
Amount decimal(6,2),
foreign key(CustID) references Customer (CustID),
foreign key(ProdID) references Product (ProdID)
);

--inserting records into Sales table
insert into Sales values (1,1,1,550.00,10,5500);

--selecting record
select * from Sales;

Screen in SQL Server:

*************************************

NOTE :PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.