Archive for January 2016
SQL ORACLE QUERIES
Structured Query Language (SQL) is the set of statements with which all programs and users access data in an Oracle database. Application programs and Oracle tools often allow users access to the database without using SQL directly, but these applications in turn must use SQL when executing the user's request. This chapter provides background information on SQL as used by most database systems.
Oracle SQL Developer provides a SQL Worksheet that you can use to query data, by writing simple or complex SQL statements. In this How-To, we look at the most basic of these, select all the data in a table, and restricting this query by reducing the columns or rows you retrieve.
TASK - 1:
1. Create the following table customer
2. Create the following table product
3. Create the following table cust_prod
TASK - 2:
1. Find out the names of all customers
13. Find out the customers who stay in ‘Chennai‘andwhose name starts with ‘S’
1. Display the number of products in each order
2. Display the order number with number ofproducts more than 2
TASK-6
1. Find out the product which has been sold to particular customer.
2. Find out the product and their quantities that have to be delivered.
3. Find out the product number and description of ordered products
4. Find out the names of clients who have purchased “CD DRIVE”
5. List the product number and sales order number of customers having quantity ordered less than 5 from the order details table for the product “1.44 floppy”.
6. Find the products and their quantities for the orders placed by particular sales man.
7. Find the products and their quantities for the orders placed by client number “C00001” and “C00002”.
16. Count the total number of products
17. Calculate the average price of the products
18. List the product which has minimum price
19. List the product that has maximum price
20. Count the number of products having the unit price greater than 2000.
17. Calculate the average price of the products
18. List the product which has minimum price
19. List the product that has maximum price
20. Count the number of products having the unit price greater than 2000.
TASK - 3:
1. Create Table Salesman
2. Create Table order
3. Create Table sales_order_details
TASK-4:
1. How many days are taken to deliver the product from ordered date?
2. Display one month later than the order date.
3. Print the month of delivery date of all the products.
1. Create Table Salesman
2. Create Table order
3. Create Table sales_order_details
TASK-4:
1. How many days are taken to deliver the product from ordered date?
2. Display one month later than the order date.
3. Print the month of delivery date of all the products.
4. Display the customer name in upper case.
5. Print the product description with right padded symbols.
6. Print the first three characters of customer names.
7. Print the length of longest customer name.
8. Print the delivery date in the format 22nd January 2016.
5. Print the product description with right padded symbols.
6. Print the first three characters of customer names.
7. Print the length of longest customer name.
8. Print the delivery date in the format 22nd January 2016.
TASK-5
1. Display the number of products in each order
2. Display the order number with number ofproducts more than 2
3. List the Salesman number who had sold maximum products
4. List the minimum, maximum, average and total price of product purchased by each customer (use cust_prod table).
5. List the total price of each customer whose product purchased is more than 5.
6. Display the salesman number who have anorder and whose name starts with ‘A’
4. List the minimum, maximum, average and total price of product purchased by each customer (use cust_prod table).
5. List the total price of each customer whose product purchased is more than 5.
6. Display the salesman number who have anorder and whose name starts with ‘A’
9. Find the total value of each productsold.
10. Display the product number and number ofproducts ordered for each product.
10. Display the product number and number ofproducts ordered for each product.
TASK-6
1. Find out the product which has been sold to particular customer.
2. Find out the product and their quantities that have to be delivered.
3. Find out the product number and description of ordered products
4. Find out the names of clients who have purchased “CD DRIVE”
5. List the product number and sales order number of customers having quantity ordered less than 5 from the order details table for the product “1.44 floppy”.
6. Find the products and their quantities for the orders placed by particular sales man.
7. Find the products and their quantities for the orders placed by client number “C00001” and “C00002”.
TASK-7
2. Find the customer name, address, city and pincode for the client who has placed order number “XXX”.
3. Find the clients who have placed order before the month of May 2016.
4. Find out if product “xxx” is ordered by only client and print the client number, name to whom it was sold.
4. Find out if product “xxx” is ordered by only client and print the client number, name to whom it was sold.
