SQL help using Oracle Database - NOT MYSQL OR MICROSOFT SQL SERVER! Syntax matte

Question

SQL help using Oracle Database - NOT MYSQL OR MICROSOFT SQL SERVER! Syntax matters

Can someone please write the proper queries for this question? There are two parts to it.

Link to question if you can't see the image below: http://i.imgur.com/9gEqg2I.png

Table and database information below but I'm not sure how to include all of the data - if there is a command to show all the data fields for particular tables feel free to let me know.
Link to database information if you can't see the image below: http://i.imgur.com/VwpcPmo.png

Thank you.

Explanation / Answer

Profit-less customers are those customers that have not placed any order, which means all those customers whose cust_id does not appear in orders table. This can be easily done using IN clause or standard join but since the requirement is using NOT EXISTS I will use NOT EXISTS.

a. First of all we have to display the CUST_ID and CNAME from CUSTOMERS table so use select to get these fields and then check if the cust_id exists in Orders table as below

select CUST_ID, CNAME from CUSTOMERS where NOT EXISTS (select CUST_ID from ORDERS)

b. Next query is to delete these customers, so instead of using select we will use delete as shown below

delete from CUSTOMERS where NOT EXISTS (select CUST_ID from ORDERS)

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.