Help me to solve my Programming SQL Test TBL_PARODUCT_MASTER?

Please study the following database table structures TBL_PARODUCT_MASTER

 

Product_Code Product_Description

 

TBL_TRANSACTIONS

Tnx_Id Product_Code Batch Qty TnxDate

 

Please write SQL Queries for the following-

  1. Get a list of product codes and product description for which there has been a transaction in the last seven days
  2. Get a list of all the product codes and product descriptions that have occurred without a batch in the transaction table
  3. Get all the product codes and product descriptions that occur in the transaction table but do not exist in the Product Master table
  4. Get total quantity For every Productcode and Batch
1 Answers
theqryadmin Staff answered 1 year ago

1] Answer-

  SELECT TBL_PARODUCT_MASTER.*   FROM TBL_PARODUCT_MASTER, TBL_TRANSACTIONS
  WHERE WHERE TBL_TRANSACTIONS.TnxDate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND     TBL_PARODUCT_MASTER.Product_Codes=TBL_TRANSACTIONS.Product_Codes;

2] Answer-

This question I use Join on two tables
  SELECT TBL_PARODUCT_MASTER.*
  FROM TBL_PARODUCT_MASTER, TBL_TRANSACTIONS
  WHERE  TBL_TRANSACTIONS.batch=”NULL”;

3] Answer-

I think this question is the wrong, correct question below
Get all the product codes and product descriptions that occur in the Product Master table but do not exist in the transaction table
 
  SELECT TBL_PARODUCT_MASTER.*
  FROM TBL_PARODUCT_MASTER, TBL_TRANSACTIONS
  WHERE TBL_PARODUCT_MASTER.product_codes != TBL_TRANSACTIONS.product_codes;

4] Answer-
    SELECT Product_Codes, Batch
        SUM(Qty) AS Qty,
   FROM TBL_TRANSACTIONS
  GROUP BY Product_Codes, Batch;

 

Question 1 AlterNet answer
I personally give the simple solution without using any Join, Because in the first question it needs to join two tables.

So my solution is first to get product_codes to form single SQL query and pass the result to the second query to extract product_description

First SQL query-
    SELECT product_codes
    FROM TBL_TRANSACTIONS
    WHERE TnxDate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)

 

Second SQL Query-
    SELECT *
    FROM TBL_PARODUCT_MASTER
    WHERE product_codes=precious_result_product_code