RDBMS PRACTICAL SET 4, BCA III SEM SQL PRACTICAL SET 4

Hello Everyone This is Gautam, in this post i m going to solve some  SQL query Practice Set 4 this is continuous of set 1 & set 2 and set 3. if you don't know about set 1 & 2 and 3 then refer set 1 & 2 and 3: link is given below.

Set 1 Click Here

Set 2 Click here

Set 3 Click Here

Note:- All the Given Query Solution is Tested in Microsoft SQL Server Management Studio 18 if  you get any wrong query solution then let me know by comments section.

----------------------------------------PRACTICAL SET 4-------------------------------------

-------------------------------------------------------------------------------------------------

Practical List - 4

1) Display cnum, cname, city from customer details table.

2) Display all snum without duplicates from all orders.

3) Display names and commissions of all salespeople in London.

4) All customers who were either located in Rome or had a rating above 200.

5) All customers with NULL values in city column.

6) All orders taken on Oct 3Rd and Oct 4th 1997.

7) Largest order taken by each salesperson with order value more than $3000.

8) Select each customer’s smallest order.

9) Count the number of salespeople currently listing orders in the order table.

10) All customers serviced by Piyush or Miti.

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------

------------------------->Practical List - 4 Solution<---------------------------------------- 

1) Display cnum, cname, city from customer details table.

   SELECT CNUM,CNAME,CITY FROM CUSTOMERS;

2) Display all snum without duplicates from all orders.

   SELECT DISTINCT SNUM FROM ORDERS;

3) Display names and commissions of all salespeople in London.

   SELECT SNAME,COMMISION FROM SALESMAN WHERE CITY='London';

4) All customers who were either located in Rome or had a rating above 200.

   SELECT * FROM CUSTOMERS WHERE CITY='Rome' OR RATING>200;

5) All customers with NULL values in city column.

   SELECT * FROM CUSTOMERS WHERE CITY IS NULL;  -->OR USE CAN USE CITY='';

6) All orders taken on Oct 3Rd and Oct 4th 1997.

   SELECT * FROM ORDERS WHERE ODATE='3-OCT-97' OR ODATE='4-OCT-97'; 

  

7) Largest order taken by each salesperson with order value more than $3000.

   SELECT SNAME,MAX(AMOUNT) FROM ORDERS O, SALESMAN S WHERE AMOUNT>3000 AND S.SNUM=O.SNUM GROUP BY SNAME;

8) Select each customer’s smallest order.

   SELECT CNUM, MIN(AMOUNT) FROM ORDERS GROUP BY CNUM;

----------------------------------------OR------------------------------------------------------

   SELECT CNAME,MIN(AMOUNT) FROM ORDERS O, CUSTOMERS C WHERE       C.CNUM=O.CNUM GROUP BY CNAME;

9) Count the number of salespeople currently listing orders in the order table.

   SELECT SNUM,COUNT(SNUM) FROM ORDERS GROUP BY SNUM;

10) All customers serviced by Piyush or Miti.

    SELECT DISTINCT CNAME, SNAME FROM CUSTOMERS C,SALESMAN S,ORDERS O WHERE S.SNUM=O.SNUM AND SNAME IN ('Piyush','Miti');

Note :- Please Follow me, Here i will upload java lab programs also , i m doing hardwork for you people. guys please  🙏 Follow me and share this page with your friends. 

Social media link:

Follow me on Instagram:-  gautam_kumar83

THANKS FOR VISITING THIS BLOG PAGE 💖.