Database HomeWork
(10 points )
Question1: Examine the following statements. Mention the type of constraint
violation (if any) and propose a solution to correct each constraint (if any)
1) insert the record ( 37235, Khaled, Mohammad, Bahraini, 22, 5) into PLAYER
insert into playervalues 37235 Khaled Mohammad Bahia
2) update the age of player 93247 to 16. 22 s
update player
16 thewhere Player'D 193247
set Age
3) Delete record (4, Tadamon, Farwaniya) from CLUBS
Delete from CLUBS Where Club D 4
Question 2 : Write SQL statements using the tables PLAYER AND CLUB
1. Use DDL to Define the Player table.
Field Type Null key Default Note
PlayerID int No PRIMARY
Key
FirstName varchar(30) No
LastName varchar(30) No
Nationality varchar(30) UNKNOWN
Age int Check Constraints:
players >= 18
ClubID int Foreign on update set to
Key NULL, on delete set
to cascade
CREATE TABLE PLAYER
Player D INT not null
FirstName VARCHAR 307NOTNULL
LastName VARCHAR 307NOTNULL
NationalityVARCHAR 307 Default UNKNOWN
Age INTCHECK Age 187
ClubID INT
Club ID REFRENCESCLUBS CLUBID
ON UPDATE SET NULL ON DELETECASCADE
1g
2. Retrieve the name and location of all the clubs and sort them by name in
descending order
3. Retrieve the number of players in each CLUB.
4. Retrieve the names and nationality of the player who their names start with ‘a’
5. Add a new club named Al-Araby and location Mansoriya and id=6
6. Move the player Ahmad Ali from Qadsiya to Nasser
7. Delete all players who are over 35
8. Write the SQL that can result in producing the following table
Fname Lname Club
Ahmad Ali Qadsiya
Salim Saad Qadsiya
Yousif Mohammad Tadamon
Karim Ahmad Nasser
Select Name Location
FROM CLUBS
Name DESC
ORDER By Club
Select Club ID count
FROM PLAYER
group by CLUD ID
Select Frame name Nationality
From PLAYER
when Fname Like at
INSERT INTO CLUBS
G AL Araby Mansoriga
Update Player
Set ClubID select
CLUBID from Clubs where
name Nasser
Where frame Ahmad
and name Ali
Delete FROM PLAYER
Where Age 739
Create table Player
Frame
Lname
Club
INSERT INTO Player
Values
Ahmad Ali Qadsia
