Skip to content
Advertisement

Tag: oracle

Oracle cascade delete

Is cascade delete on a table more efficient than individual delete statements (executed in a single plsql block) ? Answer What cascade delete does is issue individual delete statements. Examine the following test case: In the trace file, you will find a line like this: That is Oracle issuing a delete statement against CHILD for each record it’s deleting in

Superkey, candidate key & primary key

Can any kind soul clarify my doubts with a simple example below and identify the superkey, candidate key and primary key? I know there are a lot of posts and websites out there explaining the differences between them. But it looks like all are generic definitions. Example: So from the above example, I can know StudentNumber is a primary key.

Oracle Count even if nothing is there

So i have this query: It will return stuff from place and then d is another table describes. It will get the number of reviews the place has and the average rating. This works perfectly fine as long as there is something in the describes table. How can i go about getting the stuff that does not have anything in

Oracle, Make date time’s first day of its month

I have a datevariable, I would like to have convert it to first day of its monh, Eg: 10/10/2010 -> 01/10/2010 Eg: 31/07/2010 -> 01/07/2010 Answer According to http://psoug.org/reference/date_func.html, this should work a dandy…

Trigger created with compilation errors

I wrote this trigger to discount the top client in the database by 10% when a new purchase is made: However when i execute this statement i receive this message: Can someone please tell me what I am doing wrong? Thanks, Alex. UPDATE – Errors: Solution: Answer I don’t have your tables to hand so I can’t guarantee that I’ve

How to get the current effective date in Oracle?

I have a table like the following: TID TName EffectiveDate 1 A 2011-7-1 2 A 2011-8-1 3 A 2011-9-1 4 A 2011-10-1 5 B 2011-8-1 6 B 2011-9-1 7 B 2011-10-1 8 C 2011-9-1 If today is 2011-9-10, I wish the query result will be like this: TID TName EffectiveDate Status 1 A 2011-7-1 Invalid 2 A 2011-8-1 Invalid 3

SQL query to get the precision value of a column

I need a sql query that gets the precision value for certain columns.. I am mainly concerned with a decimal type column and I need the precision value for the same. I realise that it is possible to do so in certain versions and vendors of database servers. It would be nice if you could list down for a few

Delete all records except the most recent one?

I have two DB tables in a one-to-many relationship. The data looks like this: Resultset: I want to delete all applications except for the most recent one. In other words, each student must only have one application linked to it. Using the above example, the data should look like this: How would I go about constructing my DELETE statement to

sql query with a case when returning more than one row

I’m trying to do a query with a case when condition to see what list I will show but I’m having this error ORA-01427: single-row subquery returns more than one row. the query is this: is it possible to call a query with more than one row inside a case condition? Answer I would do this in multiple steps. Get

Advertisement