Assignment module 1&2

 create database assignment1;

use assignment1;

/*Problem Statement:

 ABC Fashion is a leading retailer with a vast customer base and a team of dedicated sales

 representatives. They have a Sales Order Processing System that helps manage customer

 orders and interactions.*/

 -- Salesman table creation

 CREATE TABLE Salesman (

 SalesmanId INT,

 Name VARCHAR(255),

 Commission DECIMAL(10, 2),

 City VARCHAR(255),

 Age INT

 );

 --Salesman table record insertion

 INSERT INTO Salesman (SalesmanId, Name, Commission, City, Age)

 VALUES

 (101, 'Joe', 50, 'California', 17),

 (102, 'Simon', 75, 'Texas', 25),

 (103, 'Jessie', 105, 'Florida', 35),

 (104, 'Danny', 100, 'Texas', 22),

 (105, 'Lia', 65, 'New Jersey', 30);

 -- Customer table creation

 CREATE TABLE Customer (

 SalesmanId INT,

 CustomerId INT,

 CustomerName VARCHAR(255),

 PurchaseAmount INT,

 );

 -- Customer table record insertion

 INSERT INTO Customer (SalesmanId, CustomerId, CustomerName, PurchaseAmount)

 VALUES

 (101, 2345, 'Andrew', 550),

 (103, 1575, 'Lucky', 4500),

 (104, 2345, 'Andrew', 4000),

 (107, 3747, 'Remona', 2700),

 (110, 4004, 'Julia', 4545);

--Orders table Creation

CREATE TABLE Orders (

OrderId int, 

CustomerId int, 

SalesmanId int, 

Orderdate Date, 

Amount money);

--Orders table record insertion

 INSERT INTO Orders Values

 (5001,2345,101,'2021-07-01',550),

 (5003,1234,105,'2022-02-15',1500);

select * from Salesman;

select * from Customer;

select * from Orders;

---------------------------------------------------------------------------------------------------------------

--Tasks to be Performed:

-- 1. Insert a new record in your Orders table.

insert into Orders(OrderId,CustomerId,SalesmanId,Orderdate,Amount)

values(5004,3747,103,'2023-04-18',1350);

-------------------------------------------------------------------------------------------------------------

/* 2.Add Primary key constraint for SalesmanId column in Salesman table. Add default

 constraint for City column in Salesman table. Add Foreign key constraint for SalesmanId

 column in Customer table. Add not null constraint in Customer_name column for the

 Customer table.*/

--making notnull constraint first

alter table salesman

alter column salesmanid int not null;

--adding primary key

alter table Salesman

add constraint pk_salesmanid primary key (salesmanid);

--add default constraint for City column in Salesman table

alter table salesman 

add constraint df_salesman_city default 'Texas' for city;

--Add Foreign key constraint for SalesmanId column in Customer table

alter table customer

add constraint fk_customer_salesmanid foreign key (salesmanid) references salesman(Salesmanid);

SELECT SalesmanId

FROM Customer

WHERE SalesmanId NOT IN (SELECT SalesmanId FROM Salesman); -- finding changes between 2 tables

UPDATE Customer

SET SalesmanId = 103

WHERE SalesmanId NOT IN (SELECT SalesmanId FROM Salesman); -- correcting the changes

--Add not null constraint in Customer_name column for the Customer table

alter table Customer

alter column CustomerName VARCHAR(255) not null;

-- 3.Fetch the data where the Customer s name is ending with  N  also get the purchase amount value greater than 500

select * from Customer

Where CustomerName like ('%n') and PurchaseAmount>500

----------------------------------------------------------------------------------------------------------------

/*4.Using SET operators, retrieve the first result with unique SalesmanId values from two tables, 

and the other result containing SalesmanId with duplicates from two tables*/

select Salesmanid from salesman

union

select salesmanid from customer;

select Salesmanid from salesman

intersect

select salesmanid from customer;

----------------------------------------------------------------------------------------------------

/* 5.Display the below columns which has the matching data.

 Orderdate, Salesman Name, Customer Name, Commission, and City which has the

 range of Purchase Amount between 500 to 1500*/

select o.Orderdate,s.Name as [salesman name],c.CustomerName,s.Commission,s.City

from Salesman s

left join Customer c on s.SalesmanId=c.SalesmanId

left join Orders o on c.CustomerId=o.CustomerId

where c.PurchaseAmount between 500 and 1500;

---------------------------------------------------------------------------------------------

-- 6. Using right join fetch all the results from Salesman and Orders table

select * from Salesman s

right join Orders o on s.SalesmanId=o.SalesmanId

---------------------------------------------------------------------------------------------------------------------------