Query Questions

Find the total count of employees under each Manager?

select empname,(select count(*) from employee where reporterid=e.empid) from employee e

select e1.empid,e1.empname,count(*) from employee e1

inner join employee e2 on e1.empid=e2.reporterid group by e1.empid,e1.empname;

Comments

Post a Comment

Popular posts from this blog

Mongo DB

  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.
Read more

General DB Questions

  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...
Read more