Oracle Interview question How To Get unique records without using distinct in oracle
HTML-код
- Опубликовано: 16 сен 2024
- Oracle Interview question How To Get unique records without using distinct in oracle?
easy-learning-t...
List of all the SQL and PLSQL interview questions
@ easy-learning-...
Super sir you're the life saver.
For my work i need to filter the column data. But tried with all possible options like distinct unique etc..but row_number partition worked like charm ❤️
Great 👍👍
Helps to refresh and nice examples. Thank you
Welcome 🙏👍💐
Start
0:09 - How to Get Records without using Unique Keyword
1:19 - Some queries Examples
1:43 - What do you mean by communicating a Database
2:29 - There are Types of Sql
3:24 - DDL
4:40 - Truncate
4:59 - DML
5:36 - DCL
6:16 - TCL
6:49 - DRL
7:53 - Some More Languages
1:19 - Some queries Examples
2:44 - Using Distinct Keyword
2:54 - With Unique Keyword
3:11 - Using Group by
4:11 - Using Union
5:19 - Union with Nulls
6:13 - Using Intersect Operator
6:35 - Using Minus Operator
7:11 - Using Row_Number ()
7:55 - Using Rank or DenseRank
8:31 - Two more ways
very helpful information hatsoff sir
We can do it using ROWID too:-
SELECT * FROM TABLE_NAME WHERE ROWID IN (SELECT MAX(ROWID) FROM TABLE_NAME GROUP BY COLUMN_NAME)
OR
SELECT * FROM TABLE_NAME WHERE ROWID IN (SELECT MIN(ROWID) FROM TABLE_NAME GROUP BY COLUMN_NAME)
Thanks for posting the query, it may help someone whoever is reading the comments :-)
Hi siva,
Thanks for the video, i wanted to know the difference between distinct and unique function used here.
Regards
Start
1:19 - Some queries Examples
2:44 - Using Distinct Keyword
2:54 - With Unique Keyword
3:11 - Using Group by
4:11 - Using Union
5:19 - Union with Nulls
6:13 - Using Intersect Operator
6:35 - Using Minus Operator
7:11 - Using Row_Number ()
7:55 - Using Rank or DenseRank
8:31 - Two more ways - One is Using RowID - Using Subquery
8:53 - Using RowID with Corelated Subquery
9:11 - Recap all these Queries
Siva can you please clarify one doubt as a general, when we use 1=2 in the where clause it means it’s false and hence create or select will not be performed but in below query why is table being created but with no data? Create table emp1 as select * from emp where 1=2;
Create will be performed but select will not return any data as this condition is false for each row in table emp.. So table emp1 will be created without any data. We do this when we want to copy only table structure without any data.
But on interview if some one asks this question reply them "there are many ways to get distinct record but using Oracle key words boost the performance so using distinct keyword is the right choice.
👍
Can you please make a video on regular expression and type of regular expression with small example in easy way to get it understand.
It will be very thankful, if you make video on this.
Thanks in advance cheers.
sure bro, please stay tuned, i will add into my upcoming list of videos
For the correlated query , wouldn't the count be equal to 2 when 3 rows are repeated for a particular record? Can someone explain explain please?
Bro you are Great 👍👍
Which way have better performance when working with millions of records??
can you recheck the correlated one, its not giving the proper output when checked in db
Ur videos helping alot
Thank you
Glad to hear that
Very helpful. Can we get some videos on AWR report interpretation and the performance tuning tips and Partitions in Oracle
@Venkat, Sure bro, will cover the explain plan as part of performance tuning series soon.
great video
Thank you bro
Siva sir ,
in one of the interview i got a question like "you need to delete duplicates without using rowid or rownum.only one record should be present.duplicates should be deleted.11' how can we do this ?
You. Can use group with having and in sub query use max() on serial number of the table
If it is full row duplication then without rowid deleting duplicate is impossible.
Happy learning 😘
Nice work.
Excellent video sir, thank you
Thank you
The way in which you used to filter the unique records
Select null,null from dual where 1=2,if we have 4 columns in the first select list,will that work ?
The number of columns should match between select statement., otherwise query will throw error
Please post some video on oracle partionining
@rahul, sure, will post , please stay tuned.
@@SivaAcademy Hi Siva, Have you posted anything on db partitioning?
Hi shiv, in mysql workbench by using union keyword it is not fetching uniqe records is there any other way in mysql.
Hi Siva,
How to find missing values of table.
@Vilas, Can you please elaborate your question with a sample data, so that its bit easy for me explain. Thanks, Siva
Very informative Thanks lot
Welcome bro
Siva, at 6.09 how it works select rollno, name from emp union select null, null from dual 1=2...null is not comparable with anyvalue pls explain
the second query "select null, null from dual 1=2" wont return any rows, because of condition 1=2, hence the null values also not returned from the select statement, just because, the union is used, oracle tend to remove the duplicate value and sort the result.
which way is best performance wise
How developers has been categorized as beginner, intermediate, and expert is it based on years of experience we have in the domain. If it is based on years, pls give the range for each level. I have 6 years will I come under intermediate or expert
There is no strict category, however generally 0 to 4, 4 to 10, 10 + and 15+, you tipically fit into intermediate level of experience
Out of 9 ways. Which one has more performance sir ?
I need to test with large volume of data, i will post a video after my analysis with numbers
Hi Siva
Can you pls do post videos on sql nd performance tuning?
Thanks in advance
@Sreekanth, Sure, Will be posting soon, Please stay tuned.
Thanks for your comments :-)
Siva
Informative
Thank you
UNIQUE video.
Thank you
Thanks siva.
I have 15k records in table .
Can we fetch at a time using select query. I have only read access.
Please suggest.
Thanks.
yes you can do.........if you want to limit the number of records, then you can use LIMIT clause as part of cursor fetch operation
Can we use join? @siva academy
Hi siva, when I got this question from an interview panel, I wrote using row number analytical function, it is correct. But the panel asked why you are making life complex.... Then he told me that you would have written using rowid.. So pls suggest me when they expect me a single query from me what should I say
May be the same functionality can be implemented with out using row_number function. If that's the case try writing the same query with out using analytical functions
Can you send me the exact query they asked for, so that I can explain with more details
Hi Sir,
How can we call URL in procedure
Hi what is the difference between SELECT UNIQUE and SELECT DISTINCT?
No difference at all... Same command in oracle
Great video bro.
Thank you
I have one table T , having 10 records with out using count function ,how we can count the records??
Rank
Max of rownum
By using case on any column is not null then 1 and Sum on then you will get.
Happy learning😃
Sir how to used out parameter in procedure and call these out parameter in the same procedure when we create a out mode. Sir please can you explain how this can be done
@Suyog, Can you please explain with an example, is your requirement to call the procedure with an out parameter within the same procedure?
Ya sure
Create or replace procedure ps (e_id in number, sal out number)
Is
Begin
Select salary+1000 into sal from emplyees where emp_id=e_id;
Dbms (sal);
End;
/
@filename.sql
Exec ps (100,sal);
When i do that it will give error for out parameter. We can not pass out mode value in procedure execution?
@SuyogYou have to decalare a local variable to get the out value,
a sample execution method is given below for your reference, reply back to me if you need more information
------------------------------------------------------------------------
SQL> set serveroutput on
SQL>
SQL> variable lv_num number;
SQL> exec ps(1,:lv_num);
PL/SQL procedure successfully completed.
SQL> print lv_num;
LV_NUM
----------
100
SQL>
------------------------------------------------------------------------
@@SivaAcademy thank you sir it working
@@SivaAcademy sir where i get the get the detail information about that
Perfect!!
Thank you
Sir Please create How to delete duplicate record from table please create an video
@Vicky, Sure, I will create on duplicate record deletion using different sqls, pls stay tuned. Thanks, Siva
@@SivaAcademy thank you sir
How to delete parent records without deleting the child records?
Please help with..
I think it is not possible
Disable foreign key constraint in child table and then delete parent record
While creating the table use Delete On Cascade command....
👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍
Tq sir
Welcome :-)
👏🏻👏🏻👏🏻
Thank you
Anyone have duplicate record query?
How to delete Duplicate Records without using ROWID?
Sure, will do as soon as possible.
Will post a video on this question as soon as possible. Stay tuned.
DELETE FROM TABLE_NAME WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM TABLE_NAME GROUP BY COLUMN_NAME)
OR
DELETE FROM TABLE_NAME WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM TABLE_NAME GROUP BY COLUMN_NAME)
@@4ashutosh As per the question, You should not use ROWID
Vidio is not clear