SQL OR DBMS SQL QUERIES 1

Table Structure

Sales_Header

(orderno, orderdate, customerID, salesmanid, paymentStatus, TransactionType, paymentdate)

Sales_detail

(Orderno, ProductID, Qty, Rate, Amt)

i) Display the order, which were issued in first Quarter of Current year

Select orderno from sales_header where to_char(orderDate,’mm’)<4 and to_char(orderDate,’yy’) = to_char(sysdate,’yy’)

ii) Display the order number, order date, customer id and order amount for orders having value of 500 Rs.

select orderno,orderdate,cutomerid, amt from sales_header as h, sales_detail as d

where h.orderno = d.orderno and amt>=500

iii) Display the order detail where RIN001 soap is sold for Min of 50 Rs

select h.* from sales_header as h, sales_detail as d

where h.orderno = d.orderno and productid=’RIN001’ and amt>=50

iv) Display the order collected by Executive no ‘S120’

select orderno, amt from sales_header as h, sales_detail as d

where h.orderno = d.orderno and h.salesmanid = ‘S120’

v) Display the unpaid order in ascending order of Order no

select orderno, orderdate from sales_header

where paymentstatus = ‘unpaid’ order by orderno

vi) Assign the privileges to see the data from both tables to ‘Raj’

Grant select on sales_header, sales_details to ‘Raj’

vii) Display the various types of product sold to customers.

Select distinct productid from sales_header as h, sales_details as d where h.orderno = d.orderno

viii) Display all credit transaction, with their payment status done in Dec. 2003

select * from sales_header where transactiontype=’credit’ and paymentstatus = ‘paid’ and to_char(paymentdate,’mon-yyyy’) = ‘dec-2003’

xi) Display the details of total cash transaction done by each sales executives.

Select * , sum(amt) from sales_header as h, sales_detail as d where h.orderno = d.orderno and transactiontype = ‘cash’ and paymentstatus = ‘paid’ group by salesmanid