create table Players(number int,name varchar(50),age int,starts int,totalGoals…
- User
create table Players(number int,name varchar(50),age int,starts int,totalGoals int,totalShots int) create table injuredPlayers(number int,name varchar(50)) insert into Players values (3,'Eric Bailly',23,8,1,NULL), (17,'Daley Blind',27,3,NULL,NULL), (36,'Matteo Darmian',28,2,NULL,NULL), (4,'Phil Jones',25,17,NULL,NULL), (2,'Victor Lindelof',23,7,NULL,NULL), (35,'Demetri Mitchell',20,NULL,NULL,NULL), (5,'Marcos Rojo',28,5,NULL,NULL), (23,'Luke Shaw',22,5,NULL,NULL), (12,'Chris Smaling',28,13,1,NULL), (38,'Axel Tuanzebe',20,NULL,NULL,NULL), (25,'Antonio Valencia',32,18,2,NULL), (16,'Michael Carrick',36,NULL,NULL,NULL), (27,'Marouane Fellaini',30,3,3,NULL), (21,'Ander Herrera',28,8,NULL,NULL), (14,'Jesse Lingard',25,9,8,NULL), (8,'Juan Mata',29,15,3,NULL), (31,'Nemanja Matic',29,22,NULL,NULL), (39,'Scott McTominay',21,1,NULL,NULL), (22,'Henrikh Mkhitaryan',28,11,1,NULL), (6,'Paul Pogba',24,12,3,NULL), (18,'Ashley Young',32,16,2,NULL), (10,'Zlatan Ibrahimovic',36,1,NULL,NULL), (9,'Romelu Lukaku',24,21,10,NULL), (11,'Anthony Martial',22,11,7,NULL), (19,'Marcus Rashford',20,13,4,NULL)
insert into injuredPlayers values (16,'Michael Carrick'), (3,'Eric Bailly'), (27,'Marouane Fellaini'), (25,'Antonio Valencia'), (10,'Zlatan Ibrahimovich'), (9,'Romelu Lukaku')
SELECT * FROM injuredPlayers SELECT * FROM Players
- Assistant
Table "Players":
number name age starts totalGoals totalShots 3 Eric Bailly 23 8 1 NULL 17 Daley Blind 27 3 NULL NULL 36 Matteo Darmian 28 2 NULL NULL 4 Phil Jones 25 17 NULL NULL 2 Victor Lindelof 23 7 NULL NULL 35 Demetri Mitchell 20 NULL NULL NULL 5 Marcos Rojo 28 5 NULL NULL 23 Luke Shaw 22 5 NULL NULL 12 Chris Smaling 28 13 1 NULL 38 Axel Tuanzebe 20 NULL NULL NULL 25 Antonio Valencia 32 18 2 NULL 16 Michael Carrick 36 NULL NULL NULL 27 Marouane Fellaini 30 3 3 NULL 21 Ander Herrera 28 8 NULL NULL 14 Jesse Lingard 25 9 8 NULL 8 Juan Mata 29 15 3 NULL 31 Nemanja Matic 29 22 NULL NULL 39 Scott McTominay 21 1 NULL NULL 22 Henrikh Mkhitaryan 28 11 1 NULL 6 Paul Pogba 24 12 3 NULL 18 Ashley Young 32 16 2 NULL 10 Zlatan Ibrahimovic 36 1 NULL NULL 9 Romelu Lukaku 24 21 10 NULL 11 Anthony Martial 22 11 7 NULL 19 Marcus Rashford 20 13 4 NULL Table "injuredPlayers":
number name 16 Michael Carrick 3 Eric Bailly 27 Marouane Fellaini 25 Antonio Valencia 10 Zlatan Ibrahimovich 9 Romelu Lukaku - User
Add column called Position to Players table and type char(1)
- Assistant
You can add a new column called "Position" to the "Players" table with the data type char(1) using the following SQL statement:
ALTER TABLE Players ADD Position char(1);After executing the above statement, the "Players" table will have a new column called "Position" with the data type char(1).
- User
Change values of Position to 'F' for forward if the player ever scored goal if not then put 'D' for defenders
- Assistant
To update the values in the "Position" column of the "Players" table based on whether the player has scored a goal or not, you can use the following SQL statement:
UPDATE Players SET Position = CASE WHEN totalGoals > 0 THEN 'F' ELSE 'D' END;This query will update the "Position" column to 'F' for players who have scored goals and 'D' for players who haven't scored goals.
- User
Create stored procedure called usp_gethealthyplayers to return only non-injured players
- Assistant
To create a stored procedure called "usp_gethealthyplayers" that returns only non-injured players from the "Players" table, you can use the following SQL code:
CREATE PROCEDURE usp_gethealthyplayers AS BEGIN SELECT * FROM Players WHERE number NOT IN (SELECT number FROM injuredPlayers); END;This stored procedure selects all rows from the "Players" table where the player's number does not exist in the "injuredPlayers" table, effectively returning only non-injured players when the procedure is executed.
- User
Create stored proc to return only defenders
- Assistant
To create a stored procedure called "usp_getdefenders" that returns only the defenders from the "Players" table based on the value of the "Position" column, you can use the following SQL code:
CREATE PROCEDURE usp_getdefenders AS BEGIN SELECT * FROM Players WHERE Position = 'D'; END;This stored procedure selects all rows from the "Players" table where the value of the "Position" column is equal to 'D', effectively returning only the defenders when the procedure is executed.