Blog: Database Management System (CS403) Assignment No: 4 Solution

QUESTION NO 1:

Insert into sale (SaleID, CustomerID, InvoiceDate, InvoiceNumber, Discount, Description) values (3, 3,27-01-2008, 45, 2%, ‘saving’);

Insert into Purchase (PurchaseID, Invoice, Discount Daaate , Price, VendorID) values (22, 3500, 2%, 28-09-2008, 3400, 102);

Inset into Customer(CustomerID, Name, Address PhoneNumber, Fax, Email, CustomerType) values (502, ‘Ali’, ‘Lahore’, 03008765234, 032-65467482, ‘Ali@yahoo.com’, ‘regulaar’);

Insert into PurchaseDetail(ProductID, Unitprice, UnitDiscount, PurchaseID) Values (6122, 2500, 3%, 22);

Insert into Accounts(AccoountHolderID, AccountHolderName, Description) values (51111, ‘Asad’, -------);

Insert into AccountsLedger(LedgerID, Description, Date, AccountsHolerID) Values (11111, -------, 01-01-2008, 51111);

Insert into Vendor(VendorID, CompanyName, Address, PhoneNumber, Email, VendorLedgerID) values (0011, ‘Ali Traders’, ‘chichawatni’, 03345678901, ‘zab@yahoo.com’, 110);

Insert into VendorLedger(VendorLedgerID, VendorID, Description, Date) values (110, 1231, ------, 01-07-2010);

Insert into SaleDetail(SaleDetailSR, ProductID, SaleUnit, UnitPrice, SaleID) values (34, 222, 3, 125, 3);

Insert into Product(ProductID, Name, VendorID, Quality) values (222, ‘shampoo’, 0011, ‘Ten Bottles');

Insert into Stock(StockNumber, Date, InvoiceNumber, AccountHolderID, ProductID) values (1, 15-03-2010, 53, 51111, 222);

QUESTION NO2:

Alter Table Sale Add Constraint sal_FK Foreign Key(CustomerID) References Customer(CustomerID);

Alter Table SaleDetail Add Constraint salD_FK2 Foreign Key(SaleID) References Sale (SaleID);

Alter Table Purchase  Add Constraint Pur_FK Foreign Key(VendorID) References Vendor(VendorID);

Alter Table PurchaseDetail Add Constraint PurD_FK2 Foreign Key(PurchaseID) References Purchase (PurchaseID);

Alter Table PurchaseDetail Add Constraint PurD_FK1 Foreign Key(ProductID) References Product(ProductID);

Alter Table SaleDetail Add Constraint salD_FK1 Foreign Key(ProductID) References Product(ProductID);

Alter Table Stock Add Constraint sto_FK2 Foreign Key(ProductID) References Product(ProductID);

Alter Table Stock Add Constraint sto_FK1 Foreign Key(AccountHolderID) References Accounts(AccountsHolderID);

                                                                                                                                                                                          

Alter Table AccountsLedger  Add Constraint  Acc_FK1 Foreign Key(AccountHolderID) References Accounts (AccountHolderID);

Alter Table Vendor Add Constraint  Ven_FK Foreign Key(VendorLedgerID) References VendorLedger(VendorLedgerID);

QUESTION NO 3:

SELECT * FROM SALE ORDERED BY CUSTOMERID, INVOICENUMBER;

QUESTION NO 4:

SELECT COUNT(*) FROM PURCHASE WHERE PURCHASE.DISCOUNT>10;

Note:  main nay first question mian  1 record insert kia ha baqi record bi isi tarha insert krny hain insert krty huy foreign key ka khayl rakhna ha.

Ap ny last assignment jo send ke the us main jo attributes k name thy ap wohi use krna otherwise ap ka wrong ho jay ga.

Ap is ko sql main run krain aur screen short le kr assign bna lain