DataBase Implant

  How can we handle optimistic locking in Mango DB? In Mango DB, we have versions associated with each record of the table. If 3 threads are trying to update our record and one third updates it Remaining 2 will have older or stale versions. As soon as they try to update the table with old version, it throws an optimistic lock exception. Using retryable, we can fetch the data of latest version And update latest data.

  What are Cursers? A curser holds multiple rows returned by SQL statement. There are two types of cursor Implicit cursor They are generated by database itself. These are created by user. Explicit cursor These are created by user. Cursor has following steps We first declare a cursor Then we open cursor Then we fetch values from the cursor Then we close the cursor Declare CID Customer.id : id%type Cname Customer.name : id%type Cursor C1 is select statement  Open C1 Beginning  Loop Open C1 Fetch C1 into cid,cname Exit when c1% not found DBMS_Output.put_line(cid||” “||cname) End Loop Close C1 End What is the difference between primary key and foreign key? Prime key uniquely identifies the records.Foreign is a field in the table that is primary key in another table. Primary key cannot accept null values, foreign key can accept multiple null values. Primary  key is a clustered Index and data in the table is physically organised Foreign key do not automatically create an i...

 We can create a customized Row_Number and Map it to a Partition in table as follows With cte as (SELECT id,studentName,streetAddress, ROW_NUMBER() OVER(PARTITION BY id,studentName,streetAddress order by id,studentName,streetAddress) AS row_num FROM springjdbc.student) select * from cte WHERE row_num>1; Delete from cte WHERE row_num>1; Where our table is as follows SELECT id, studentName, streetAddress FROM springjdbc.student; To use the above query in database abstraction layers in code we can use as follows. Delete from student where id in (Select ds.id from (SELECT id,studentName,streetAddress, ROW_NUMBER() OVER(PARTITION BY id,studentName,streetAddress order by id,studentName,streetAddress) AS row_num FROM springjdbc.student) ds where ds.row_num>1) c