ATG Support Analysis Database Queries

Hi Guys,

Just wanted to share with you all the database Queries that can be used to analyse various things from database perspective.
Every fault that occurs in ATG has some link with the Database and we also start analysing things starting from Database standpoint.

Here i have used simple sql Queries but yet they will give perfect results (Iam not DB expert to give more optimised Queries. Any suggestions for new and optimised Queries are welcome).

• Orders Placed on Particular date in Particular state
  
  select o.* from dcspp_order o
  inner join dcspp_pay_group p

  on o.order_id = p.order_ref

  join DCSPP_BILL_ADDR b on p.PAYMENT_GROUP_ID = b.PAYMENT_GROUP_ID

  where substr(o.creation_date,0,9) = '31-JUL-17'

  and o.state not in('SUBMITTED','REMOVED'); 

• Orders with Specific ShippingGroup(for example hardgoodShippingGroup)
  
  select * from atgcore.dcspp_order o
  inner join atgcore.dcspp_ship_group s on o.order_id=S.ORDER_REF
  where s.state  not in ('INITIAL') and s.shipping_method='hardgoodShippingGroup'
  and o.state in ('PROCESSING','SUBMITTED');
  Change the states of orders as wished for.

• Inventory of a specific Product
  select * from atgcore.dcs_inventory where inventory_id in (select sku_id from atgcata.dcs_prd_chldsku where product_id='<product_id>' );
  
• Orders Placed in Last one Hour
  select * from dcspp_order where submitted_date > SYSDATE-1/24;

Request for specific Queries in comments and i will upload the same here.