Question

A new company is planning to build a new database system for holding information about customers and salesmen. ‘Customers’, ‘Salesmen’ and ‘Customers_Salesmen’ are part of the information that the new company wants to store in the new database. These tables are shown below in figure 1, figure 2 and figure 3. The new company intends to use MySQL for building the new database.

Customer_ID	Customer_Name	Customer_City	Customer_Grade
3002	Ahmad Salman	New York	100
3007	Mazen Ali	New York	200
3005	Sami Khalil	California	200
3008	Ashraf Ahmad	London	300
3004	Manal Faris	Paris	300
3009	Tahani Mahdi	Berlin	100
3003	Fawzi Jama	Moscow	200
3001	Tareq Mohsen	London	100

Figure 1: Customers table

Salesman_ID	Salesman_Name	Salesman_City	Salesman_Commission
5001	Naser Hamad	New York	0.15
5002	Rami Farhan	Paris	0.13
5006	Salem Alawi	Paris	0.14
5003	Faten Morad	San Jose	0.12
5007	Turkey Fahad	Rome	0.13
5005	Juma Khalaf	London	0.11

Figure 2: Salesmen table

Customer_ID	Salesman_ID
3002	5001
3007	5001
3005	5002
3008	5002
3004	5006
3009	5003
3003	5007
3001	5005

Figure 3: Customers_Salesmen table

Based on the above three tables, answer the following 6 questions:

1. Write an SQL statement for creating the customer table that is shown in figure 2. No need to write any SQL statement for adding any record to the customer table.  
2. Write an SQL statement for adding one record to the customer table that was created in question-1 above. The added record should be one of the records that are given in the customer table.   
3. Write an SQL statement to list the information of salesmen who work in Paris.
4. Write an SQL statement to list all customer names who work with the salesman whose id is 5001.                                                                                                                           \
5. Write an SQL statement to list all customer names who work with the salesman whose commission is 0.13.                                                                                                          ]
6. Write an SQL statement to increase the salesman commission by 5%.

Answer 1

CREATE TABLE Customers(Customer_ID integer PRIMARY KEY, Customer_Name varchar(50), Customer_City varchar(50), Customer_Grade integer);

___________________________________________________________________

INSERT INTO Customers(Customer_ID, Customer_Name, Customer_City, Customer_Grade) VALUES (3002, 'Ahmad Salman', 'New York', 100);

___________________________________________________________________

SELECT * FROM Salesmen WHERE Salesman_City = 'Paris';

___________________________________________________________________

ALTER TABLE Customers_Salesmen ADD FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID);

ALTER TABLE Customers_Salesmen ADD FOREIGN KEY (Salesman_ID) REFERENCES Salesmen(Salesman_ID);

SELECT Customers.Customer_Name FROM Customers, Customers_Salesmen WHERE Customers.Customer_ID = Customers_Salesmen.Customer_ID AND Customers_Salesmen.Salesman_ID = 5001;

Ahmad Salman
Mazen Ali

___________________________________________________________________

SELECT Customers.Customer_Name FROM Customers, Customers_Salesmen, Salesmen  WHERE Customers.Customer_ID = Customers_Salesmen.Customer_ID AND Salesmen.Salesman_ID = Customers_Salesmen.Salesman_ID AND Salesmen.Salesman_Commission = 0.13;

Sami Khalil
Ashraf Ahmad
Fawzi Jama

___________________________________________________________________

UPDATE Salesmen SET Salesman_Commission = Salesman_Commission * 1.05;

___________________________________________________________________

CREATE TABLE Customers(Customer_ID integer PRIMARY KEY, Customer_Name varchar(50), Customer_City varchar(50), Customer_Grade real);
INSERT INTO Customers(Customer_ID, Customer_Name, Customer_City, Customer_Grade) VALUES (3002, 'Ahmad Salman', 'New York', 100);
INSERT INTO Customers(Customer_ID, Customer_Name, Customer_City, Customer_Grade) VALUES (3007, 'Mazen Ali', 'New York', 200);
INSERT INTO Customers(Customer_ID, Customer_Name, Customer_City, Customer_Grade) VALUES (3005, 'Sami Khalil', 'California', 200);
INSERT INTO Customers(Customer_ID, Customer_Name, Customer_City, Customer_Grade) VALUES (3008, 'Ashraf Ahmad', 'London', 300);
INSERT INTO Customers(Customer_ID, Customer_Name, Customer_City, Customer_Grade) VALUES (3004, 'Manal Faris', 'Paris', 300);
INSERT INTO Customers(Customer_ID, Customer_Name, Customer_City, Customer_Grade) VALUES (3009, 'Tahani Mahdi', 'Berlin', 100);
INSERT INTO Customers(Customer_ID, Customer_Name, Customer_City, Customer_Grade) VALUES (3003, 'Fawzi Jama', 'Moscow', 200);
INSERT INTO Customers(Customer_ID, Customer_Name, Customer_City, Customer_Grade) VALUES (3001, 'Tareq Mohsen', 'London', 100);
SELECT * FROM Customers;



CREATE TABLE Salesmen(Salesman_ID integer PRIMARY KEY, Salesman_Name varchar(50), Salesman_City varchar(50), Salesman_Commission real);
INSERT INTO Salesmen(Salesman_ID, Salesman_Name, Salesman_City, Salesman_Commission) VALUES (5001, 'Naser Hamad', 'New York', 0.15);
INSERT INTO Salesmen(Salesman_ID, Salesman_Name, Salesman_City, Salesman_Commission) VALUES (5002, 'Rami Farhan', 'Paris',0.13);
INSERT INTO Salesmen(Salesman_ID, Salesman_Name, Salesman_City, Salesman_Commission) VALUES (5006,'Salem Alawi','Paris',0.14);
INSERT INTO Salesmen(Salesman_ID, Salesman_Name, Salesman_City, Salesman_Commission) VALUES (5003,'Faten Morad','San,Jose',0.12);
INSERT INTO Salesmen(Salesman_ID, Salesman_Name, Salesman_City, Salesman_Commission) VALUES (5007,'Turkey Fahad','Rome',0.13);
INSERT INTO Salesmen(Salesman_ID, Salesman_Name, Salesman_City, Salesman_Commission) VALUES (5005,'Juma Khalaf','London',0.11);
SELECT * FROM Salesmen;


 
CREATE TABLE Customers_Salesmen(Customer_ID integer, Salesman_ID integer);
INSERT INTO Customers_Salesmen(Customer_ID, Salesman_ID) VALUES (3002,5001);
INSERT INTO Customers_Salesmen(Customer_ID, Salesman_ID) VALUES (3007,5001);
INSERT INTO Customers_Salesmen(Customer_ID, Salesman_ID) VALUES (3005,5002);
INSERT INTO Customers_Salesmen(Customer_ID, Salesman_ID) VALUES (3008,5002);
INSERT INTO Customers_Salesmen(Customer_ID, Salesman_ID) VALUES (3004,5006);
INSERT INTO Customers_Salesmen(Customer_ID, Salesman_ID) VALUES (3009,5003);
INSERT INTO Customers_Salesmen(Customer_ID, Salesman_ID) VALUES (3003,5007);
INSERT INTO Customers_Salesmen(Customer_ID, Salesman_ID) VALUES (3001,5005);
SELECT * FROM Customers_Salesmen;

ALTER TABLE Customers_Salesmen ADD FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID);
ALTER TABLE Customers_Salesmen ADD FOREIGN KEY (Salesman_ID) REFERENCES Salesmen(Salesman_ID);

SELECT Customers.Customer_Name FROM Customers, Customers_Salesmen WHERE Customers.Customer_ID = Customers_Salesmen.Customer_ID AND Customers_Salesmen.Salesman_ID = 5001;

SELECT Customers.Customer_Name FROM Customers, Customers_Salesmen, Salesmen  WHERE Customers.Customer_ID = Customers_Salesmen.Customer_ID AND Salesmen.Salesman_ID = Customers_Salesmen.Salesman_ID AND Salesmen.Salesman_Commission = 0.13;

UPDATE Salesmen SET Salesman_Commission = Salesman_Commission * 1.05;
Select * from Salesmen;

___________________________________________________________________

Note: If you have queries or confusion regarding this question, please leave a comment. I would be happy to help you. If you find it to be useful, please upvote.