Scribd
Upload
34 views5 pages

Document

The document creates a database table called "player" to store information about cricket players. It defines the structure of the table with various columns like player code, name, age, matches played, runs scored, wickets taken etc along with relevant data types and constraints. It then inserts record of 17 players into the table with details like name, age, country etc. Finally, it performs various select, update and delete operations on the player table to retrieve, modify and delete records based on different conditions.

Uploaded by

Raman Singh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
34 views5 pages

Document

The document creates a database table called "player" to store information about cricket players. It defines the structure of the table with various columns like player code, name, age, matches played, runs scored, wickets taken etc along with relevant data types and constraints. It then inserts record of 17 players into the table with details like name, age, country etc. Finally, it performs various select, update and delete operations on the player table to retrieve, modify and delete records based on different conditions.

Uploaded by

Raman Singh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

Create table player(

PCode char(5),

FirstName varchar(30) not null unique,

LastName varchar(30) not null,

Age int check (Age >= 15 and Age <= 50),

MatchPlayed int check (MatchPlayed >= 10),

RunScored int,

WicketTaken int,

Country varchar(20) default ‘India’,

Primary key(Pcode)

);

Describe player;

Insert into
player(Pcode,FirstName,LastName,Age,MatchPlayed,RunScored,WicketTaken)values(‘A001’,’Sourav’,’Ga
nguly’,49,424,18575,132);

Insert into
player(Pcode,FirstName,LastName,Age,MatchPlayed,RunScored,WicketTaken)values(‘A002’,’Rohit’,’Sha
rma’,31,270,12252,11);

Insert into
player(Pcode,FirstName,LastName,Age,MatchPlayed,RunScored,Country)values(‘A003’,’Shahid’,’Afridi’,4
4,425,9780,’Pakistan’);

Insert into
player(Pcode,FirstName,LastName,Age,MatchPlayed,RunScored,WicketTaken,Country)values(‘A004’,’Mi
tchell’,’Johnson’,39,226,3016,552,’Australia’);

Insert into
player(Pcode,FirstName,LastName,Age,MatchPlayed,RunScored,WicketTaken,Country)values(‘A005’,’Jo
e’,’Root’,30,350,5000,250,’England’);

Insert into
player(Pcode,FirstName,LastName,Age,MatchPlayed,RunScored,WicketTaken,Country)values(‘A006’,’JP’
,’Duminy’,26,450,10000,210,’South Africa’);
Insert into
player(Pcode,FirstName,LastName,Age,MatchPlayed,RunScored,WicketTaken)values(‘A007’,’Yuvraj’,’Sin
gh’,39,400,10000,140);

Insert into
player(Pcode,FirstName,LastName,Age,MatchPlayed,RunScored,WicketTaken,Country)values(‘A008’,’Ro
ss’,’Taylor’,37,380,16500,3,’New Zealand’);

Insert into
player(Pcode,FirstName,LastName,Age,MatchPlayed,RunScored,WicketTaken)values(‘A009’,’Priyam’,’Ga
rg’,19,14,133,8);

Insert into
player(Pcode,FirstName,LastName,Age,MatchPlayed,RunScored,WicketTaken,Country)values(‘A01’,’Coo
per’,’Connolly’,18,12,114,6,’Australia’);

Insert into
player(Pcode,FirstName,LastName,Age,MatchPlayed,RunScored,WicketTaken,Country)values(‘A02’,’And
rew’,’Symonds’,46,224,6550,157,’Australia’);

Insert into
player(Pcode,FirstName,LastName,Age,RunScored,WicketTaken)values(‘A03’,’VVS’,’Laxman’,46,11119,2
);

Insert into
player(Pcode,FirstName,LastName,Age,MatchPlayed,RunScored,Country)values(‘A04’,’Adrian’,’Barath’,3
1,29,1011,’West Indies’);

Insert into
player(Pcode,FirstName,LastName,Age,MatchPlayed,RunScored,WicketTaken,Country)values(‘A05’,’Bar
ney’,’Rogers’,39,19,568,6,’Zimbabwe’);

Insert into
player(Pcode,FirstName,LastName,Age,MatchPlayed,RunScored,WicketTaken,Country)values(‘A06’,’Jac
ob’,’Oram’,43,193,4214,233,’New Zealand’);

Insert into
player(Pcode,FirstName,LastName,Age,MatchPlayed,RunScored,WicketTaken,Country)values(‘A07’,’AB’,
’deVillers’,34,850,25000,200,’South Africa’);

Select * from player;

Select FirstName, LastName , Country


From player

Where Country = ‘India’;

Select *

From player

Where Age <= 19;

Select FirstName, LastName , Age

From player

Where Age > 30;

Select FirstName, LastName , Country , MatchPlayed

From player

Where MatchPlayed >= 150;

Select *

From player

Where RunScored >= 5000;

Select FirstName , LastName ,Country

From player

Where Country != ‘Australia’;

Update player

Set Age = 32

Where FirstName = ‘Rohit’ and LastName = ‘Sharma’;


Select * from player

Where FirstName = ‘Rohit’ and LastName = ‘Sharma’;

Delete from player

Where FirstName = ‘Shahid’ and LastName = ‘Afridi’;

Select * from player;

Select count(Pcode)

From player

Where Country = ‘Australia’;

Select *

From player

Where FirstName Like ‘S%’ and FirstName Like ‘%v’;

Select *

From player

Where FirstName like ‘______’;

Select *

From player

Where FirstName like ‘_a%’;

Select *

From player

Where FirstName like ‘%a_’;


Select *

From player

Where FirstName like ‘a%’ and length(FirstName) >= 6;

Select *

From player

Where FirstName like ‘a_____%’;

You might also like