Answer to Question #261673 in Databases | SQL | Oracle | MS Access for Angela

Question

Create SQL statement (JOIN) to display a list with salesman name, customer name and their cities for the salesmen and customer who belongs to the same city. (Use the JOIN statement))
Question 2- 10 pts
Create a function that calculate the total commission
Question 3 -10pts
Create a store procedure that displays the names of salesman located in Paris and their commission

Answer 1

SELECT s.name,
	       c.cust_name,
	       c.city
	  FROM salesman s
	  INNER JOIN customer c
	    ON s.city = c.city;

create function total_commission 
RETURN varchar AS 
BEGIN 
DECLARE @var varchar
    SELECT @var=SUM(commission) FROM salesman 
    RETURN @var 
RETURN @var
END;

CREATE PROCEDURE procedure_salesman
AS
SELECT name, commission FROM salesman WHERE city =  'Paris'
GO;