0% found this document useful (0 votes)
5 views5 pages

HW SQL Constraints

Uploaded by

ksp8nqg7th
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
0% found this document useful (0 votes)
5 views5 pages

HW SQL Constraints

Uploaded by

ksp8nqg7th
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

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

You might also like