SQL>
SQL> CREATE TABLE department
2 (id INT PRIMARY KEY
3 ,name VARCHAR(30)
4 );
Table created.
SQL>
SQL> CREATE TABLE employee
2 (id INT PRIMARY KEY
3 ,name VARCHAR(30)
4 ,rank VARCHAR(30)
5 ,dept INT REFERENCES department(id)
6 );
Table created.
SQL> INSERT INTO department VALUES (1,'Ladies'' Outfitting');
1 row created.
SQL> INSERT INTO department VALUES (2,'Gent'' Outfitting');
1 row created.
SQL>
SQL> INSERT INTO employee VALUES (1,'Mr. Abc','Assistant',1);
1 row created.
SQL> INSERT INTO employee VALUES (2,'Mrs. Bcd','Supervisor',1);
1 row created.
SQL> INSERT INTO employee VALUES (3,'Miss. Cee','Assistant',2);
1 row created.
SQL> INSERT INTO employee VALUES (4,'Mr. Jack','Assistant',2);
1 row created.
SQL>
SQL> SELECT department.name, COUNT(employee.id)
2 FROM department JOIN employee ON department.id=dept
3 GROUP BY department.name;
NAME COUNT(EMPLOYEE.ID)
------------------------------ ------------------
Ladies' Outfitting 2
Gent' Outfitting 2
SQL>
SQL> DROP TABLE employee CASCADE CONSTRAINT;
Table dropped.
SQL> DROP TABLE department CASCADE CONSTRAINT;
Table dropped.
SQL>