Question

1. Create the following tables and establish relationships.

All fields are Varchar except parts price and date.

Invoice
Invoice_ID	Customer_ID	Part_Number	Date

Customer
Customer_ID	Customer_Name	Customer_Address	Customer_Telephone

Parts
Part_Number	Part_Description	Parts_Price	Supplier_ID

Supplier
Supplier_ID	Supplier_Name	Supplier_Address	Sales_Rep

10 points

2. Populate data (you make it up) use at least one insert into query for each table the rest you can enter in grid view.

10 points (queries)

3. Update one of the supplier’s address.

4. Add a field named supplier tax ID to the supplier table.

5. What is the telephone number of a particular customer?

6. What is the name of every customer who has purchased a certain part?

7. What part has the lowest price?

8. Which customer names purchased something within the last three 3 days?

9. What are the invoice IDs of parts purchased from a particular sales rep?

SQL

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using SQL Server 2014.Database and Tables are created using SQL Server Management Studio (SSMS)

Database :

create database ERPDB;

/*use database to create tables*/
use ERPDB;

Tables :

1.Table Name :Customer

SQL Query :

/*1.Table Name :Customer*/
create table Customer(
Customer_ID varchar(10) primary key,
Customer_Name varchar(20) not null,
Customer_Address varchar(100) not null,
Customer_Telephone varchar(10) not null
);

/*inserting records*/
insert into Customer values ('C01','Virat Kohli','Chandni Chowk Mumbai','8888881245');

/*selecting records*/
select * from Customer;

Screen in SSMS :

1 ERPDB.sql - NAGESH-PC.ERPDB (NAGESH-PCNagesh (54) Microsoft SQL Server Managemen... File Edit View Query Project Debug Tools Window Help ERPDB ERPDB.sql NAGES... H-PCNagesh (54) X Ecreate database ERPDB /*use database to create tables* use ERPDB *1.Table Name :Customer* Ecreate table Customer( Customer_ID varchar (10) primary key, Customer Name varchar (20) not null Customer_Address varchar (100) not null, Customer_Telephone varchar (10) not null /*inserting records insert into Customer values ('Ce1', 'Virat Kohli', 'Chandni Chowk Mumbai', 888888124 /*selecting records select *from Customer 100 % < Results Fa Messages Customer_ID Customer_Name Customer_Address CO1 Customer_Telephone Virat Kohli Chandni Chowk Mumba 8888881245

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

2.Table Name :Supplier

SQL Query :

/*2.Table Name :Supplier*/
create table Supplier(
Supplier_ID varchar(10) primary key,
Supplier_Name varchar(20) not null,
Supplier_Address varchar(100) not null,
Sales_Rep varchar(10) not null
);

/*inserting records*/
insert into Supplier values ('S01','ABC Corp','Tilak Road Pune','Mr.Sam');

/*selecting records*/
select * from Supplier;

Screen in SSMS :

ERPDB.sql - NAGESH-PC.ERPDB (NAGESH-PCNagesh (54)) - Microsoft SQL Server Managem... File Edit View Query Project Debug Tools Window Help ERPDB.sql NAGES... H-PCNagesh (54))* X /*2.Table Name :Supplier*/ Ecreate table Supplier( Supplier_ID varchar (10) primary key, Supplier Name varchar (20) not null Supplier_Address varchar (100) not null, Sales_Rep varchar (10) not null /*inserting records*/ insert into Supplier values ('se1', 'ABC Corp', 'Tilak Road Pune', 'Mr.Sam') /*selecting records*/ select from Supplier;l 100 % ▼ < Results Fa Messages Supplier ID Supplier _Name Supplier_Address Sales Rep 1S01 ABC Corp Tilak Road Pune Mr.Sam

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

3.Table Name :Parts

SQL Query :

/*3.Table Name :Parts*/
create table Parts(
Part_Number varchar(10) primary key,
Part_Description varchar(100) not null,
Parts_Price decimal(6,2) not null,
Supplier_ID varchar(10) not null,
foreign key (Supplier_ID) references Supplier(Supplier_ID)
);

/*inserting records*/
insert into Parts values ('P01','Hammer',100.25,'S01');

/*selecting records*/
select * from Parts;

Screen in SSMS :

ERPDB.sql - NAGESH-PC.ERPDB (NAGESH-PCNagesh (54)) - Microsoft SQL Server Managem... File Edit View Query Project Debug Tools Window Help ERPDB.sql NAGES... H-PCNagesh (54))* X *3.Table Name Parts* create table Parts ( Part_Number varchar (10) primary key, Part Description varchar (100) not null, Parts_Price decimal (6,2) not null, Supplier_ID varchar (10) not null, foreign key Supplier_ID) references Supplier(Supplier_ID) /*inserting records*/ insert into Parts values ('Pe1', 'Hammer',100.25, 'se1'); /*selecting records*/ select from Parts 100 % < Results Fa Messages Part Number Part Description Parts_Price Supplier ID 1PO1 100.25 S01

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

4.Table Name :Invoice

SQL Query :

/*4.Table Name :Invoice*/
create table Invoice(
Invoice_ID varchar(10) primary key,
Customer_ID varchar(10) not null,
Part_Number varchar(10) not null,
Date date not null,
foreign key (Customer_ID) references Customer(Customer_ID),
foreign key (Part_Number) references Parts(Part_Number)
);

/*inserting records*/
insert into Invoice values ('I01','C01','P01','02/05/2018');

/*selecting records*/
select * from Invoice;

Screen in SSMS :

ERPDB.sql NAGESH-PC.ERPDB (NAGESH-PCNagesh (54)- Microsoft SQL Server Managem... File Edit View Query Project Debug Tools Window Help ERPDB.sql NAGES...H-PCNagesh (54))* X *4.Table Name Invoice* create table Invoice Invoice_ID varchar (10) primary key, Customer_ID varchar (10) not null, Part_Number varchar(10) not null, Date date not null, foreign key (Customer_ID) references Customer (Customer_ID), foreign key (Part_Number) references Parts (Part_Number) /*inserting records insert into Invoice values I01', 'ce1', 'Pe1, '02/05/2018); /*selecting records select from Invoice; 100 % ▼ < Results Fa Messages Invoice ID Customer ID Part Number Date 1 101 C01 2018-02-05 PO1

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

Question 3 :

SQL Query :

/*Query 3*/
update supplier set Supplier_Address='Delhi' where supplier_id='S01';

/*selecting record*/
select * from supplier;

Screen in SSMS:

ERPDB.sql NAGESH-PC.ERPDB (NAGESH-PCNagesh (54)- Microsoft SOL Server Manageme. File Edit View Query Project Debug Tools Window Help ERPDB.sql - NAGES...H-PCNagesh (54)* X /*Query 3 update supplier supplier-id-'S01'; Supplier Address-Delhi. where set /selecting record*/ select from supplieril 100 % ▼ < Results Fa Messages Supplier_ID Supplier_Name Supplier_Address Sales_Rep 1S01 ABC Corp Mr.Sam Delhi

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

Question 4 :

SQL Query :'

/*Query 4*/
alter table supplier add Supplier_Tax_ID varchar(10);

/*selecting record*/
select * from supplier;

Screen in SSMS :

ERPDB.sql - NAGESH-PC.ERPDB (NAGESH-PCNagesh (54) - Microsoft SQL Server Manageme...- File Edit View Query Project Debug Tools Window Help ERPDB.sql NAGES...H-PCNagesh (54))* X /Query 4*/ alter table supplier add Supplier_Tax_ID varchar (10); /*selecting record/ selectfrom supplier 100% ▼< Results Fa Messages Supplier ID Supplier NameSupplier_AddressSales_Rep Supplier_Tax_ID 1S01 ABC Corp Delhi Mr.Sam NULL

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

Question 5:

SQL Query :

/*Query 5*/
select Customer_Telephone from customer where Customer_ID='C01';

Screen in SSMS :

땟 ERPDB.sql-NAGESH-PC.ERPDB (NAGESH-PaNagesh (54))"-Microsoft SQL Server Manageme.. File Edit View Query Project Debug Tools Window Help ERPDB.sql - NAGES...H-PCNagesh (54))* X "Query 5*7 select Customer_Telephone from customer where Customer_ID-'Ce1'; 100 % < Resultsa Messages Customer Telephone - 18888881245

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

Question 6 :

SQL Query :

select Customer_Name from Customer , invoice where
Customer.Customer_ID=Invoice.Customer_ID and
Part_Number='P01';

Screen in SSMS :

安ERPDB.sql-NAGESH-PC.ERPDB (NAGESH-PCNagesh (54))"-Microsoft SQL Server Manageme...- File Edit View Query Project Debug Tools Window Help ロ × ERPDB.sql - NAGES...H-PC1Nagesh (54))* >X Eselect Customer_Name from Customer, invoice where Customer.Customer ID-Invoice.Customer ID and Part Number-' POI ' ; 100 % ▼ Results Messages Customer Name 1 Virat Kohli

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

Question 7:

SQL Query :

select min(parts_price) from parts;

Screen in SSMS :

ERPDB.sql - NAGESH-PC.ERPDB (NAGESH-PCNagesh (54))* - Microsoft SOL Server Manageme. File Edit View Query Project Debug Tools Window Help ERPDB.sql - NAGES... H-PCNagesh (54)* X select min(parts_price) from parts; 100 % < Results Fa Messages (No column name) 1 100.25

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

Question 9:

SQL Query :

select invoice_id from invoice, parts,supplier
where Invoice.Part_Number=Parts.Part_Number and
Parts.Supplier_ID=Supplier.supplier_ID and
Sales_Rep='Mr.Sam';

Screen in SSMS :

ERPDB.sql - NAGESH-PC.ERPDB (NAGESH-PCNagesh (54))* - Microsoft SOL Server Manageme. File Edit View Query Project Debug Tools Window Help Execute ERPDB.sql - NAGES... H-PCNagesh (54)* X invoice-id 曰select from invoice, parts, supplier where Invoice.Part_Number-Parts.Part_Number and Parts.Supplier_ID-Supplier.supplier_ID and Sales_Rep- 'Mr.Sam' 100 % Results Fa Messages invoice _id 1 101

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