The tables that will participate in the demonstration
- tblPlayers
- tblStudents
- tblDept
- tblEmployee
Script for tblPlayers
CREATE TABLE tblPlayers
(
PlayerID INT NOT NULL,
PlayerFirstName VARCHAR(10),
PlayerLastName VARCHAR(10),
BelongsTo VARCHAR(10),
DOB varchar(20),
FeePerMatch NUMERIC(7,2)
);
Insert into tblPlayers (PlayerID,PlayerFirstName,PlayerLastName,BelongsTo,DOB,FeePerMatch) Values(1,'A','Raman','India','20/12/1984',2000);
Insert into tblPlayers (PlayerID,PlayerFirstName,PlayerLastName,BelongsTo,DOB,FeePerMatch) Values(2,'B','Kadir','India','01/01/1980',1000);
Insert into tblPlayers (PlayerID,PlayerFirstName,PlayerLastName,BelongsTo,DOB,FeePerMatch) Values(3,'C','Nadir','India','21/04/2000',3000);
Insert into tblPlayers (PlayerID,PlayerFirstName,PlayerLastName,BelongsTo,DOB,FeePerMatch) Values(4,'D','Das','India','11/11/1980',500);
Insert into tblPlayers (PlayerID,PlayerFirstName,PlayerLastName,BelongsTo,DOB,FeePerMatch) Values(5,'E','Punchkar','India','14/02/1980',12000);
Insert into tblPlayers (PlayerID,PlayerFirstName,PlayerLastName,BelongsTo,DOB,FeePerMatch) Values(6,'F','Fateman','USA','20/12/1971',22000);
Insert into tblPlayers (PlayerID,PlayerFirstName,PlayerLastName,BelongsTo,DOB,FeePerMatch) Values(7,'G','Gajani','USA','01/01/1990',999);
Insert into tblPlayers (PlayerID,PlayerFirstName,PlayerLastName,BelongsTo,DOB,FeePerMatch) Values(8,'H','Hariharan','USA','21/04/1999',7000);
Insert into tblPlayers (PlayerID,PlayerFirstName,PlayerLastName,BelongsTo,DOB,FeePerMatch) Values(9,'I','Ikat','USA','11/11/1988',5999);
Insert into tblPlayers (PlayerID,PlayerFirstName,PlayerLastName,BelongsTo,DOB,FeePerMatch) Values(10,'J','Jwar','USA','14/02/1998',7900);
Select * from tblplayers;
/*
PLAYERID PLAYERFIRS PLAYERLAST BELONGSTO DOB FEEPERMATCH
---------- ---------- ---------- ---------- --------- -----------
1 A Raman India 20-DEC-84 2000
2 B Kadir India 01-JAN-80 1000
3 C Nadir India 21-APR-00 3000
4 D Das India 11-NOV-80 500
5 E Punchkar India 14-FEB-80 12000
6 F Fateman USA 20-DEC-71 22000
7 G Gajani USA 01-JAN-90 999
8 H Hariharan USA 21-APR-99 7000
9 I Ikat USA 11-NOV-88 5999
10 J Jwar USA 14-FEB-98 7900
*/
Script for tblStudents
CREATE TABLE tblStudents
(
StudentID INT NOT NULL,
StudentName VARCHAR(10),
Marks1 INT,
Marks2 INT,
Marks3 INT,
Marks4 INT
);
Insert into tblStudents (StudentID,StudentName,Marks1,Marks2,Marks3,Marks4) Values(1,'A.Raman',45,50,30,88);
Insert into tblStudents (StudentID,StudentName,Marks1,Marks2,Marks3,Marks4) Values(2,'B.Sekhar',56,84,99,100);
Insert into tblStudents (StudentID,StudentName,Marks1,Marks2,Marks3,Marks4) Values(3,'A.Raman',32,22,78,34);
Insert into tblStudents (StudentID,StudentName,Marks1,Marks2,Marks3,Marks4) Values(4,'A.Raman',58,67,11,55);
Insert into tblStudents (StudentID,StudentName,Marks1,Marks2,Marks3,Marks4) Values(5,'A.Raman',99,92,69,89);
Select * from tblStudents;
STUDENTID STUDENTNAM MARKS1 MARKS2 MARKS3 MARKS4
---------- ---------- ---------- ---------- ---------- ----------
1 A.Raman 45 50 30 88
2 B.Sekhar 56 84 99 100
3 A.Raman 32 22 78 34
4 A.Raman 58 67 11 55
5 A.Raman 99 92 69 89
Script for tblDept
Create Table tblDept
(DeptID Int
,DeptName Varchar(50)
,Constraint PK_DeptID Primary Key(DeptID));
Insert Into tblDept(DeptID,DeptName) Values(1,'IT');
Insert Into tblDept(DeptID,DeptName) Values(2,'Accounts');
Insert Into tblDept(DeptID,DeptName) Values(3,'Finance');
Insert Into tblDept(DeptID,DeptName) Values(4,'Research and Development');
Insert Into tblDept(DeptID,DeptName) Values(5,'Sales');
Select * from tblDept
DEPTID DEPTNAME
---------- --------------------------
1 IT
2 Accounts
3 Finance
4 Research and Development
5 Sales
Script for tblEmployee
Create Table tblEmployee
(EmpId Int Primary Key
,EmpName Varchar(50)
,Salary Decimal(7,2)
,DeptID Int References tblDept(DeptID)
);
Insert Into tblEmployee(EmpId,EmpName,Salary,DeptID) Values(1,'Deepak Kumar Goyal', 6000,1);
Insert Into tblEmployee(EmpId,EmpName,Salary,DeptID) Values(2,'Shashi Dayal', 5000,2);
Insert Into tblEmployee(EmpId,EmpName,Salary,DeptID) Values(3,'Amitav Mallik', 16000,1);
Insert Into tblEmployee(EmpId,EmpName,Salary,DeptID) Values(4,'Amit Ojha', 650,5);
Insert Into tblEmployee(EmpId,EmpName,Salary,DeptID) Values(5,'Sumanta Manik', 6900,3);
Select * From tblEmployee;
EMPID EMPNAME SALARY DEPTID
---------- ----------------- ---------- ----------
1 Deepak Kumar Goyal 6000 1
2 Shashi Dayal 5000 2
3 Amitav Mallik 16000 1
4 Amit Ojha 650 5
5 Sumanta Manik 6900 3