Table
Emp
Salgrade
Dept
1) Find out the name of all employees.
2) Display all information of employee.
3) Display all information of employee whose job type is “SALESMAN”.
4) Display all information of employee whose Dept No is 20.
5) Display employee information whose hiredate is in month of DEC.
6) Display employee information whose salary is greater than 3000.
7) Find the names of all employees having ‘A’ as the second letter in their names.
8) Display department information.
9) Display Salary Grade Information.
10) Display Name, Job of employee whose manager name is “BLAKE”.
11) Display Name, Salary of employee whose commission is null.
12) Display Name, Salary, Hiredate of employee whose Hiredate is in the year of 81.
13) Update salary by 5% whose department no is 10.
14) Update commission comm=sal*5% whose job type is “MANAGER”.
15) Display employee name whose name start with ‘A’.
16) Display employee details that have maximum salary.
17) Display employee details that have minimum salary.
18) Display average salary department wise.
19) Display employee details whose salary is between 1000 to 2000.
20) Display employee information department wise.
21) Display your name in upper, lower and title case.
22) Display all Employee names and their length after trimming them
23) Extract first 3 characters from your name and display the same.
24) Extract last 4 characters from your name and display the same.
25) Extract only 3 to 7 characters of your full name.
26) Find out the absolute values of the following:
-20, 30, 25, -400, -13, -11, 34, -99.
27) Update the employee table so each employee name will contain XXXXX at the end of the same.
28) Now display the same such that they won't display XXXXX at the end of each name.
29) Round the following numbers by 5, 2, 1, 3, 4 decimal places:
4.091978,232.837733, 822.2325443.
Table 1
Product
Customer
1. To display PID,PNAME and corresponding cust of those item, whose price between 1000 and 1200.
2. To display PID, PRICE, CNAME AND PNAME of the item, which has PNAME as ‘MANGO’.
3. To increase the Price of all item by 15%.
Table 2
Member
Division
1. To display MID, NAME and corresponding DIVNAME of those ITEM whose pay between 1000 and 26000.
2. To display MID, PAY, NAME and DIVNAME of the ITEM, which has DIVNAME as MANAGER.
3. To increase the Price of all item by 25%.
4. To record in CUST table MID = 101.
Table 3
Product
Division
1. To display PID, PNAME AND CNAME OF ALL THE CUSTOMER WHO BY ‘LCD’.
2. To display PNAME, PID, CNAME AND PRICE OF ALL THE PRODUCT WHOSE PRICE IN MORE THAN 50000.
3. To DISPLAY CUSTOMER NAME AND NAME OF ALL ITEM BUY BY CUSTOMER WHOSE COSE IS ‘C01’.
Table 4
Product
Company
1. To display NO, PNAME and corresponding COMPANY of those ITEM, whose price is between 2000 AND 50000.
2. To display NO, PRICE AND CNAME of the item which has PNAME AS ‘MOBILE’.
3. To increase the price of all the item by 15%.
Table 5
Emp
Dept
1. To display EMPNO, ENAME and corresponding job is SALESMAN whose SAL between 1000 and 2000.
2. To display EMPNO, ENAME and location is NEW YORK.
3. To ENAME, EMPNO, DNAME whose HIREDATE is year 1981.
4. To increase SAL of all item by 25%.
5. DELETE record in EMP table EMP 7934.
6. To UPDATE all ENAME = ‘AVANI’ whose job is CLERK.
7. To ADD column INCOME TAX in EMP table.
8. To update all record INCOME TAX and INCOME TAX = sal * 10%.
9. DELETE column of INCOME TAX.
10. To DELETE all record of EMP.
Tables
Client Master
Product master
Sales Master
Sales Order
Sales Order Detail
Callan Header
Challan Details
1. Find out the names of all the clients.
2. Print the entire client_master table.
3. Retrieve the list of names and the cities of all the clients.
4. List the various products available from the product_master table.
5. Find the names of all clients having ‘a’ as the second letter in their names.
6. Find out the clients who stay in a city whose second letter is ‘a’.
7. Find the list of all clients who stay in city ‘Bombay’ or city ‘Delhi’ or city ‘Madras’.
8. List all the clients who are located in Bombay.
9. Print the list of clients whose bal_due are greater than value 10000.
10. Print the information from sales_order table of orders placed in the month of January
11. Display the order information for client_no ‘C00001’ and ‘C00002’.
12. Find the products with description as ‘1.44 Drive and ‘1.22 Drive.’
13. Find the products whose selling price is greater than 2000 and less than or equal to 5000.
14. Find the products whose selling price is more than 1500 and also find the new selling price as original selling price*15.
15. Rename the new column in the above query as new_price.
16. Find the products whose cost_price is less than 1500.
17. List the products in sorted order of their description.
18. Calculate the square root of the price of each product.
19. Divide the cost of product ‘540 HDD’ by difference between its price and 100.
20. List the names, city and state of clients not in the state of ‘Maharashtra’.
21. List the product_no, description, sell_price of products whose description begin with letter ‘M’.
22. List all the orders that were cancelled in the month of March.
23. Display all the Client names in Capitals and State in Lowercase.
24. Price must be displayed in 8,2 format. Put '*' before the amount. e.g. ***23.90.
25. Display Description and Qty_on_hand for each product.
26. Display sales transactions (qty_ordered and qty_disp) from Sales_order_details.
Set Functions and Concatenation :
27. Count the total number of orders.
28. Calculate the average price of all the products.
29. Calculate the minimum price of products.
30. Determine the maximum and minimum product prices. Rename the title as max_price and min_price respectively.
31. Count the number of products having price greater than or equal to 1500.
32. Find all the products whose qty_on_hand is less than reorder level.
33. Print the information of client_master,product_master,sales_order table in the following format for all the records :-
{cust_name} has placed order {order_no} on {s_order_date}
Having And Group By :
34. Print the description and total qty sold for each product.
35. Find the value of each product sold.
36. Calculate the average qty sold for each client that has a maximum order value of 15000.00
37. Find out the total sales amount receivable for the month of jan.It will be the sum total of all the billed orders for the month.
38. Print the information of product_master,order_detail table in the following format for all the records :- {description} worth Rs. {total sales for the product} was sold.
39. Print the information of product_master,order_detail table in the following format for all the records :-
{description} worth Rs. {total sales for the product} was ordered in the month of {s_order_date in month format}
40. Find out the total Qty_Disp and Qty_ordered of each item from Sales_order_Details.
41. Find out the no. of sales transactions of each day from Sales_order_details.
42. Compute average, minimum and maximum Qty_ordered for each product.