Hello Everyone This is Gautam, in this post i m going to solve some basic SQL query Practice Set 3 this is continuous of set 1 and set 2. if you don't know about set 1 & 2 then refer set 1 & 2: link is given below.
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 List - 3
1. Produce the order no, amount and date of all orders.
2. Give all the information about all the customers with salesman number 1001.
3. Display the following information in the order of city, sname,snum and commission.
4. List of rating followed by the name of each customer in Surat.
5. List of all orders for more than Rs. 1000.
6. List all customers whose names begins with letter 'A' to 'G'.
7. List of names and cities of all salesmen in London with commission above 10%.
8. List all customers excluding those with rating <= 100 unless they are located in Rome.
9. List all orders for more than Rs.1000 except the orders of snum<1006 of 10/03/97.
10. List all orders with zero or NULL amount.
-->Practical List - 3 Solution
--1. Produce the order no, amount and date of all orders.
SELECT ONUM,AMOUNT,ODATE FROM ORDERS;
--2. Give all the information about all the customers with salesman number 1001.
SELECT * FROM CUSTOMERS WHERE SNUM=1001;
--3. Display the following information in the order of city, sname,snum and commission.
SELECT CITY,SNAME,SNUM,COMMISION FROM SALESMAN;
--4. List of rating followed by the name of each customer in Surat.
SELECT CNAME,RATING FROM CUSTOMERS WHERE CITY='SURAT';
--5. List of all orders for more than Rs. 1000.
SELECT * FROM ORDERS WHERE AMOUNT>1000;
--6. List all customers whose names begins with letter 'A' to 'G'.
SELECT * FROM CUSTOMERS WHERE CNAME BETWEEN 'A%' AND 'G%';
--7. List of names and cities of all salesmen in London with commission above 10%.
select sname,city from salesman where city='london' and commision>'10%'; --IF COMMISION DATATYPE VARCHAR
--OR-----
SELECT SNAME,CITY FROM SALESMAN WHERE CITY='London' AND COMMISION>10; --IF COMMISION DATATYPE INT
--8. List all customers excluding those with rating <= 100 unless they are located in Rome.
SELECT * FROM CUSTOMERS WHERE RATING<=100 OR CITY='Rome'; --RATING>100
--9. List all orders for more than Rs.1000 except the orders of snum<1006 of 10/03/97.
SELECT * FROM ORDERS WHERE AMOUNT>1000 AND ODATE='10-MAR-97' AND SNUM<1006;
--10. List all orders with zero or NULL amount.
SELECT * FROM ORDERS WHERE AMOUNT='0' OR AMOUNT='';
Note :- Please Follow me, Here i will update all the java lab programs, i m doing hardwork for you people guys 🙏. Follow me and share this page.
THANKS FOR VISITING THIS BLOG PAGE 💖.
0 Comments