Skip to content

Tag: oracle11g

How to simplify nested select in where clause?

I have 4 tables EMPLOYEE, COMPANY, WORKS and MANAGES. The tables are defined as follows- I need to find all the employees who live in the same city as the company for which they work. So far I have done this. It’s working fine. But I want to know is there any simpler way to do this query? Answer You

Find out available days and slots for doctor appointment

I have three tables: TimeSlotToken is the number of patients a doctor will see in his TimeSlot. TimeSlot 1, 2, and 3 are Morning, Afternoon and Evening respectively. I need to find the days where the TimeSlot is not full, i.e TimeSlot token is less than total bookings on that particular day and that slot. Since each doctor may have

How to automate source control with Oracle database

I work in an Oracle instance that has hundreds of schemas and multiple developers. We have a development instance where developers can integrate their work before test or production. We want to have source control for all the DDL run in this integrated development database. Currently this is done through a product Red Gate which we run manually after we

Query with rownum got slow

I have got a table table with 21 millions records of which 20 millions meet the criterion col1= ‘text’. I then started iteratively to set the value of col2 to a value unequal to NULL. After I have mutated 10 million records, the following query got slow, that was fast in the beginning: I noticed that as soon as I

SQL CASE Statement for no data

I have a table that has process engines 1,2,3,4,5,6 with a running status. When one of the engines is down the record gets deleted from the table. Using a case statement I can display the first engine that is down but how do I go about displaying the engines if 2 or more engines are down. For e.g. how do