Please help me writing 3 - 4 basic queries

by Anil
(Cambridge, UK)

Hi There,


I am new for sql and i wrote few basic queries but i need to write few more queries and im finding it difficult. I would be very helpful if someone can help me writing 3 - 4 queries.

I have three tables Customers, Orders and Products.

Question 1: Delete all customers who haven’t placed an order

Question 2
Write a script to display all customers who have orders that exceed their credit limit and provide the following
• Customer name
• Credit Limit
• Amount credit limit exceeded by

Question 3
Write a script to display all products that have no stock availability.
CustomerID | CustomerName CreditLimit
----------------------------------------------
1 | customer 1 100.00
2 | customer 2 150.00
3 | customer 3 200.00
4 | customer 4 250.00
7 | customer 7 45.00
8 | customer 8 55.00
9 | customer 9 65.00


OrderID CustomerID ProductID Quantity OrderDate OrderTotal
-------------------------------------------------------------------------------------------
1 1 1 1 2011-09-18 12:36:31.010 10.00
2 1 3 5 2011-09-18 12:36:31.010 40.00
3 1 5 5 2011-09-18 12:36:31.010 90.00
4 2 2 1 2011-09-18 12:36:31.013 15.00
5 3 3 2 2011-09-18 12:36:31.013 40.00
6 4 6 1 2011-09-18 12:36:31.013 35.00
7 3 7 1 2011-09-18 12:36:31.013 40.00
8 4 8 1 2011-09-18 12:36:31.013 45.00
9 2 9 1 2011-09-18 12:36:31.013 50.00
10 2 4 1 2011-09-18 12:36:31.013 25.00


ProductID ProductName Cost StockLevel
--------------------------------------------------
1 Product 1 10.00 100
2 Product 2 15.00 100
3 Product 3 20.00 100
4 Product 4 25.00 100
5 Product 5 30.00 100
6 Product 6 35.00 100

Kind Regards

Anil


As you're new to SQL I would recommend you start by reading the free SQL tutorials on our site. The key to writing SQL queries against multiple tables is to understand the relationships between the tables - Oracle is after all a relational database (mostly anyway). You can tell the relationship usually from the column names or form the constraints defined on the tables or failing that just by considering what would make sense in real life.

For example, the orders table has to relate to both the customers and products tables. We know this because orders has the columns CustomerId which relates to the CustomerId in the customers table and ProductId which relates to ProductId in the products table. This makes sense because in the real world an order has to be placed by a customer and has to be for a product.

Once you've established the relationship you just need to decide how to combine the data from the tables (you might need to do multiple combinations) and use relational theory (which is more or less the same as set theory) to produce those combinations in SQL. This is true for all relational databases not just Oracle.

Going back to the specific questions, question 1 asks you to delete all customers who haven't placed an order. Looking at the tables involved, order details are in the orders table and customer details are in the customers table and there is a relationship between them because both tables have CustomerId as a column. Considering the task in hand, this is really 2 tasks: first, determine which customers haven't placed an order (i.e we need to write some SQL to query the order table), second, remove that set of customers from the customer table (which is a simple delete statement in SQL). How do we combine them? Simple we use a sub query to drive the outer delete statement. However there is a slight twist - obviously if a customer exists in the order table, that customer has placed an order but we want to remove those who haven't placed an order so we need to reverse the results of the sub query - to identify customers who have NOT placed an order. Our sql, using the simplest form of sub-query, would look like this:-

DELETE customers WHERE customerid NOT IN (SELECT customerid FROM orders)

Question 2 also requires querying 2 tables: again it's customers and orders. If we break down the question to its fundamentals as before, we can see that it's really asking is, for each customer (1) what is the total value of orders for that customer and (2) is the answer from part (1) greater than the customer's credit limit? and (3) if so print the customer's details.

To achieve this in SQL we need to join customers and orders so that we can compare the customer's credit limit with the total value of orders placed by that customer, then we need to use the HAVING clause to ignore customers who haven't exceeded their credit limit.

SELECT customerid, SUM(creditlimit), SUM(creditlimit)-SUM(ordertotal)
FROM customers, orders
WHERE orders.customerid = customers.customerid GROUP BY customerid
HAVING SUM(creditlimit)-SUM(ordertotal) < 0


The HAVING clause is only used with groups and is equivalent to the WHERE clause in non group queries in the sense that it is used to filter the rows returned by the query.

The join is necessary because we want to reference data from 2 tables as if they were in just one table. The join operation combines the 2 tables into one, using, in this case, CustomerId as the link between the 2 tables.

Question 3 would follow along similar lines: for each product, compare total orders against availability

SELECT productid, SUM(stocklevel)-SUM(quantity)
FROM products, orders
WHERE orders.productid = products.productid
GROUP BY productid
HAVING SUM(stocklevel)-SUM(quantity) < 0


To learn SQL requires study and practice. We have SQL tutorials on our site and Oracle provide a SQL Reference Manual as well as the Oracle database software which you can download and use without charge (subject to license agreements). Visit the Oracle Technology Network for documentation and software.

See our Oracle training page for information about formal training either on-line or in person.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.