Need Help with Oracle SQL?

Question

Hey guys, I need help with an assignment i was given at school, here is the description. "Develop a query that will identify and mark for deletion those customers that have bills overdue by more than 14 days (you may need a series of SQL statements to accomplish this, but it usually can be done using a sub-query). Remember, you are only marking a customer as inactive, not actually deleting the customer record from the system."
I am still very new to SQL so I'm not sure how to start this off.

Any help is greatly appreciated.

Answer 1

Assumption here is that you have a column type boolean called markForDeletion which defaults to FALSE and you have a customer and accounts receivable tables related by customer ID.



UPDATE Customer

SET markForDeletion = TRUE

WHERE markForDeletion = FALSE AND customerID IN

(SELECT DISTINCT customerID FROM AccountsReceivable

WHERE DueDate < NOW() - 14 DAYS)



(Depending on the DBMS, instead of NOW, you might use CURRENT_DATE or some other function/system variable)

Answer 2

Before you write a query, you have to have a table structure defined to query against. Define the structure of your CUSTOMER and BILL tables. The CUSTOMER table will have a name and other identifying information, along with an "ACTIVE" column, which is true by default. The BILL table will have a synthetic primary key value (a value from a sequence), along with an amount due, a DUE_DATE, and other information, and also a foreign key reference to the CUSTOMER table.



Your query will look for CUSTOMER rows where the PK of the CUSTOMER is found in BILL rows where the DUE_DATE < TODAY + 14. You'll have to look at the DATE manipulation functions available in Oracle. You'll see some good examples of that in the second source link.

Answer 3

Try something like (adjust the table and field names for your database):



Update customers set active = false where customerID = (select distinct customerID from accounts where (billdate + interval '14' day) < today)