This is a sample table and sample data only. I have three tables:
CREATE TABLE EMP_TBL
(
EMP_ID INT GENERATED BY DEFAULT AS IDENTITY,
EMP_NAME VARCHAR2(50) NOT NULL,
PRIMARY KEY (EMP_ID)
);
CREATE TABLE BRANCH_TBL
(
BRANCH_ID INT GENERATED BY DEFAULT AS IDENTITY,
BRANCH_NAME VARCHAR2(50) NOT NULL,
PRIMARY KEY (BRANCH_ID)
);
CREATE TABLE EMP_BRANCH_TBL
(
EMP_BRANCH_ID INT GENERATED BY DEFAULT AS IDENTITY,
EMP_ID INT NOT NULL,
BRANCH_ID INT NOT NULL,
PRIMARY KEY (EMP_BRANCH_ID),
FOREIGN KEY (EMP_ID) REFERENCES EMP_TBL(EMP_ID),
FOREIGN KEY (BRANCH_ID) REFERENCES BRANCH_TBL(BRANCH_ID)
)
INSERT INTO EMP_TBL (EMP_NAME) VALUES ('MNOP');
INSERT INTO EMP_TBL (EMP_NAME) VALUES ('ABCD');
INSERT INTO BRANCH_TBL (BRANCH_NAME) VALUES ('SALES');
INSERT INTO BRANCH_TBL (BRANCH_NAME) VALUES ('LOAN');
INSERT INTO BRANCH_TBL (BRANCH_NAME) VALUES ('HR');
INSERT INTO EMP_BRANCH_TBL (EMP_ID, BRANCH_ID) VALUES (1, 2);
INSERT INTO EMP_BRANCH_TBL (EMP_ID, BRANCH_ID) VALUES (2, 3);
I want count of employee in different branch. How can I do that?
branch_name count
SALES 0
LOAN 1
HR 1