Scribd
Upload
27 views5 pages

SQL Player Table Management

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
27 views5 pages

SQL Player Table Management

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

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_____%’;

Common questions

Powered by AI

Making 'FirstName' unique is questionable in a player database because it does not account for common first names that multiple individuals might share. This restriction can cause unnecessary data entry errors and complications in real-world scenarios where players may have identical first names. A better approach would be to ensure uniqueness through combining first and last names, or using unique identifiers .

Not enforcing a NOT NULL constraint on 'LastName' could result in incomplete data entries where some players might have missing surnames, making it difficult to accurately identify players by name. This could impair data retrieval and integrity when searching for specific individuals or generating reports that rely on full names .

Changing 'Rohit Sharma's' age using an update query demonstrates the database's ability to handle dynamic data by allowing modifications to existing records. This capacity is crucial for maintaining the accuracy and relevance of the information as players age or as new information becomes available .

Deleting 'Shahid Afridi's' record from the database indicates a permanent loss of data related to that player, emphasizing the need for careful management of deletions. It highlights data permanence issues, where once data is deleted, retrieval is impossible unless backups exist. This underscores the importance of adopting strategies for data recovery and maintaining audit trails for significant deletions .

The player table's flexibility in capturing diverse nationalities is supported by the optional 'Country' field during insertion, which can be specified or left as a default value ('India'). The ability to include various nationalities during insertions and selectively query by 'Country' showcases the table's adaptability and scope for accurately reflecting the international nature of sports data .

Setting a default value for 'Country' as 'India' could result in inaccurate data if not explicitly specified otherwise, as records might default to 'India' unintentionally. This can affect data integrity as it may not truly represent the player's actual country. It could also complicate querying as users may need to filter out default values to get accurate analytics .

The table structure includes a 'MatchPlayed' column with a CHECK constraint ensuring it is greater than or equal to 10. This allows for efficient querying of players with a significant number of matches by using simple WHERE conditions, important for performance metrics and eligibility criteria in sports analysis .

The 'check' constraint on 'MatchPlayed' ensures that only players who have played at least 10 matches are stored in the database. This contributes to data quality by filtering out insignificant records of players with minimal or no professional experience, thus maintaining relevance and reliability in the data for meaningful analysis and reporting .

The primary key in the player table is 'Pcode' which is a char(5). It is significant for the integrity of the database as it uniquely identifies each player record, ensuring that no two records have the same Pcode, thus maintaining data uniqueness and enabling efficient data retrieval .

The database table ensures realistic age constraints for players by using a CHECK constraint on the 'Age' column. This constraint stipulates that a player's age must be greater than or equal to 15 and less than or equal to 50, thus preventing unrealistic age entries .

You might also like