CS403 Assignment No 03 Fall 2019 Idea Solution

Описание: write a SQL statement which creates a database,3. After creation of all tables, you are required to add the following records into the Order, Customer and Bill tables by writing the appropriate SQL Queries.

------- Subscribe my Youtube Channel for more Articles and tutorails
--- Learning School
---- Mani Siddiqui

Create Database BS140201235 -BS142123131313 ---------------- mani ke jaga ap k jis name sa database create krna us k name likhan
use BS140201235 -- 1st task complete



--table 1 Select * from Ordertable
-- jo feild miss ho agr tu ap wo feild call krwa laina its jxt given u idea ok

Create Table Ordertable (orderID Varchar(50),Orderstatus varchar(20),DeliveryDate Date,Manager varchar(50),CustomerID Varchar(50))
insert into Ordertable (orderId,Orderstatus,DeliveryDate,Manager,CustomerID )Values('ord0001','Pending','2020-01-01','mang001','Cus001')
insert into Ordertable (orderId,Orderstatus,DeliveryDate,Manager,CustomerID )Values('ord0004','Pending','2020-01-01','mang001','Cus002')
insert into Ordertable (orderId,Orderstatus,DeliveryDate,Manager,CustomerID )Values('ord0002','Complete','2020-01-10','mang002','Cus002')
insert into Ordertable (orderId,Orderstatus,DeliveryDate,Manager,CustomerID )Values('ord0003','Complete','2020-01-14','mang002','Cus003')

--table 2 Select * from Customer
Create Table Customer(customer_Id varchar(50),CusName Varchar(50),Contact Varchar(50),Email varchar(50))
insert into Customer(customer_Id ,CusName,Contact ,Email)values('Cus001','Adil Shah','0321-1234567','[email protected]')
insert into Customer(customer_Id ,CusName,Contact ,Email)values('Cus002','AmirShah','0333-1234567','[email protected]')
insert into Customer(customer_Id ,CusName,Contact ,Email)values('Cus002','AmirShah','0333-1234567','[email protected]')

--table 3 Select * from Bill


Create Table Bill (BillId Varchar(50),OrderID varchar(50),BillDate date,Amount int)
insert into bill(BillId,OrderID,BillDate,Amount )values('Bill001','ord0001','2020-01-1','50000')
insert into bill(BillId,OrderID,BillDate,Amount )values('Bill001','ord0002','2020-01-14','550000')
insert into bill(BillId,OrderID,BillDate,Amount )values('Bill001','ord0003','2020-01-14','100000')
insert into bill(BillId,OrderID,BillDate,Amount )values('Bill001','ord0003','2020-01-14','100000')


--- ap 2nd task b complete ho gya 3no table create b ho gya and data b insert ho gya
ab ap na 3no j join krwana hai us k lai 3no table ma sa jo common feild hai wo pick kra 1st table customer id or 2nd table k custmr id same same hai in k apas ma join lga ga then
3rd table order id and 1st table k order id common hai in k aps ma join krnga

lets see





----------- 3 tables join
Select ordertable.orderID OID,ordertable.Orderstatus Status,Ordertable.DeliveryDate 'Date',Customer.CusName Name,Bill.Amount from Ordertable

-- its join
inner join customer on customer.customer_Id=ordertable.CustomerID
inner join bill on bill.OrderID=Ordertable.orderId
where ordertable.Orderstatus='Pending'


--- hope its helpful for you
duao ma yad rkhna
ALLAH hafiz