Sample Employee Table in SQL With Data and Queries

In This Tutorial we are Going to Create Sample Employee Table In SQL with Data of An Organization. This Tutorials Provide you the Sample Database Table of Employee Client and Project for Practice

Introduction 

The Company Maintains the Details of Their Employee Such As Employee Name, emp id, Age, Phone Number and Address. It Also Track or Stored the Client information Such as Client Id, Client Name, Address, phone Number. The Company assign One Employee to Each Client to Have Their Work Done.

Sample employee Table, Client Table and Project Table In  SQL with data Oracle

Creating Employee Table

 SQL> create table employee ( emp_id  number(2) primary key ,
    e_name varchar(10),
    age number(2),
    phone_no number(10) unique,
    address varchar(20));

Inserting Data Into Employee Table

 SQL> insert into employee values(1, 'Harry', 20, 4443256789, 'Banglore');

1 row created.

SQL> insert into employee values(2, 'Ram', 21, 9805912359, 'Bhubaneshwar');

1 row created.

SQL> insert into employee values(3, 'shyam', 24, 8102846193, 'Cuttak');

1 row created.

SQL> insert into employee values(4, 'Gaurav', 28, 8736346724, 'BBSR');

1 row created.

SQL> insert into employee values(5, 'Raj', 30, 9873562875, 'Delhi');

1 row created.

Showing Inserted Data of Employee Table

 SQL> select *from employee;

    EMP_ID E_NAME            AGE   PHONE_NO ADDRESS
---------- ---------- ---------- ---------- --------------------
         1 Harry              20 4443256789 Banglore
         2 Ram                21 9805912359 Bhubaneshwar
         3 shyam              24 8102846193 Cuttak
         4 Gaurav             28 8736346724 BBSR
         5 Raj                30 9873562875 Delhi

Creating Client Table

SQL>   CREATE TABLE CLIENT(CLTID NUMBER(2)
 NOT NULL PRIMARY KEY,
    CNAME VARCHAR2(10),
    AGE NUMBER(2),
    PH NUMBER(10),
    ADDR VARCHAR2(10),
    EMPID NUMBER(2));

Inserting Data Into Client

 SQL> insert into CLIENT values(1, 'Ravi', 33, 8746379876, 'Kolkatta',1);

1 row created.

SQL> insert into CLIENT values(2, 'Rajesh', 35, 7638656456, 'Delhi',2);

1 row created.

SQL>  insert into CLIENT values(3, 'Prakesh', 36, 7387625409, 'Mumbai',1);

1 row created.

SQL> insert into CLIENT values(4, 'Rahul', 40, 7826490267, 'Bhopal',5);

1 row created.

SQL> insert into CLIENT values(5, 'Rishi', 39, 6783562781, 'Chennai',3);

1 row created.

Showing Inserted Data Into Client Table

 SQL> select *from CLIENT;

     CLTID CNAME             AGE         PH ADDR            EMP_ID
---------- ---------- ---------- ---------- ---------- ----------
         1 Ravi               33 8746379876 Kolkatta            1
         2 Rajesh             35 7638656456 Delhi               2
         3 Prakesh            36 7387625409 Mumbai              1
         4 Rahul              40 7826490267 Bhopal              5
         5 Rishi              39 6783562781 Chennai             3

Creating Project Table

 SQL> CREATE TABLE PROJECT (P_ID NUMBER(2) PRIMARY KEY,
   EMP_ID NUMBER(2),
   CLTID NUMBER(2),
   PNAME VARCHAR2(8),
   P_DATE DATE);

Showing Inserted Data of Project Table

 SQL> insert into PROJECT values(44, 1, 1, 'pro-1', '12-FEB-09');

1 row created.

SQL> insert into PROJECT values(11, 2, 2, 'pro-2', '12-FEB-15');

1 row created.

SQL>  insert into PROJECT values(22, 1, 3, 'pro-3', '12-MAR-10');

1 row created.

SQL> insert into PROJECT values(33, 5, 4, 'pro-4', '12-MAR-11');

1 row created.

SQL> insert into PROJECT values(55, 3, 5, 'pro-5', '12-DEC-17');

1 row created.

Showing Inserted Data of Project Table

 SQL> select *from PROJECT;

      P_ID     EMP_ID      CLTID PNAME    P_DATE
---------- ---------- ---------- -------- ---------
        44          1          1 pro-1    12-FEB-09
        11          2          2 pro-2    12-FEB-15
        22          1          3 pro-3    12-MAR-10
        33          5          4 pro-4    12-MAR-11
        55          3          5 pro-5    12-DEC-17

Altering Table to ADD Foreign Key Reference Key

 SQL> ALTER TABLE CLIENT ADD CONSTRAINT XAA FOREIGN KEY(EMP_ID) REFERENCES 
    employee(EMP_ID);

Table altered.

SQL> ALTER TABLE PROJECT ADD CONSTRAINT XAS
FOREIGN KEY(EMP_ID) REFERENCES EMPLOYEE25(EMP_ID);

Table altered.

SQL> ALTER TABLE PROJECT ADD CONSTRAINT XAZ 
FOREIGN KEY(CLTID) REFERENCES CLIENT(CLTID);

Table altered.

Now We Will Preform Some Queries on this Sample Database Table  we Have Created in SQL Oracle

 SQL> select employee.emp_id,
employee.e_name,
employee.age,
employee.phone_no,
employee.address 
from employee 
join PROJECT on 
employee.emp_id = PROJECT.emp_id;

    EMP_ID E_NAME            AGE   PHONE_NO ADDRESS
---------- ---------- ---------- ---------- --------------------
         1 Harry              20 4443256789 Banglore
         2 Ram                21 9805912359 Bhubaneshwar
         1 Harry              20 4443256789 Banglore
         5 Raj                30 9873562875 Delhi
         3 shyam              24 8102846193 Cuttak

 SQL> select *from employee where emp_id=5 or emp_id=3 or emp_id=1;

    EMP_ID E_NAME            AGE   PHONE_NO ADDRESS
---------- ---------- ---------- ---------- --------------------
         1 Harry              20 4443256789 Banglore
         3 shyam              24 8102846193 Cuttak
         5 Raj                30 9873562875 Delhi

 SQL> select *from employee where e_name like'R%';

    EMP_ID E_NAME            AGE   PHONE_NO ADDRESS
---------- ---------- ---------- ---------- --------------------
         2 Ram                21 9805912359 Bhubaneshwar
         5 Raj                30 9873562875 Delhi

 SQL> select cltid,cname,ph from CLIENT,
employee where employee.emp_id=CLIENT.emp_id;

     CLTID CNAME              PH
---------- ---------- ----------
         1 Ravi       8746379876
         2 Rajesh     7638656456
         3 Prakesh    7387625409
         4 Rahul      7826490267
         5 Rishi      6783562781

 SQL> select p_id,pname,p_date from PROJECT,
CLIENT,employee where PROJECT.emp_id=PROJECT.cltid;

      P_ID PNAME    P_DATE
---------- -------- ---------
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09

      P_ID PNAME    P_DATE
---------- -------- ---------
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09

      P_ID PNAME    P_DATE
---------- -------- ---------
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        44 pro-1    12-FEB-09
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15

      P_ID PNAME    P_DATE
---------- -------- ---------
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15

      P_ID PNAME    P_DATE
---------- -------- ---------
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15
        11 pro-2    12-FEB-15

50 rows selected.

In the Tutorial we have shown You Oracle Sample Database Table with Data in SQL. Now You Should be Ready to make Any Kind of sample database tables with data in SQL. 

I Have also Provide You the SQL sample database download Tables Link Below of the Above Tutorial.

 Click Here