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
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));
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.
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 SQL> CREATE TABLE PROJECT (P_ID NUMBER(2) PRIMARY KEY,
EMP_ID NUMBER(2),
CLTID NUMBER(2),
PNAME VARCHAR2(8),
P_DATE DATE);
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-17Altering 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.
Tags:
DBMS