I can’t get the correct output when I use / ORDER BY from my SQL example?

I was playing around SQL sample that I wrote and I’m quite surprised that I couldn’t get the correct output when I use less than, greater than or ORDER BY.

for example, select player_name, jersey_number from Players order by 'jersey_number' desc; it should give players name and jersey number sort descending order from their jersey number. However, this is what I’m getting from the above query:

+--------------------+---------------+
| player_name        | jersey_number |
+--------------------+---------------+
| Karim Benzema      |             9 |
| Kevin De Bruyne    |            17 |
| Kylian Mbappe      |             7 |
| Mohamed Salah      |            11 |
| Christaino Ronaldo |             7 |
| Leonel Messi       |            30 |
| NGolo Kante        |             7 |
| Dusan Vlahovic     |             7 |
| Gabriel Jesus      |             9 |
| Harry Kane         |            10 |
| Lautaro Martinez   |            10 |
| Robert Lewandowski |             9 |
| Hakan Calhanoglu   |            20 |
+--------------------+---------------+

Similarly, when I request to show me only 'player_age' < 30 from this query: select player_name, player_age from Players where 'player_age' < 30;the output is incorrect based on this screenshot:

+--------------------+------------+
| player_name        | player_age |
+--------------------+------------+
| Karim Benzema      |         34 |
| Kevin De Bruyne    |         31 |
| Kylian Mbappe      |         23 |
| Mohamed Salah      |         30 |
| Christaino Ronaldo |         37 |
| Leonel Messi       |         34 |
| NGolo Kante        |         31 |
| Dusan Vlahovic     |         22 |
| Gabriel Jesus      |         25 |
| Harry Kane         |         28 |
| Lautaro Martinez   |         24 |
| Robert Lewandowski |         33 |
| Hakan Calhanoglu   |         28 |
+--------------------+------------+

This is also from phpMyAdmin page:

I’m using MariaBD via Mac terminal and I imported my .sql file through phpMyAdmin website.

Here’s Players table and value inersion:

-- Disable Commits & Foreign key Checks
SET FOREIGN_KEY_CHECKS=0;
SET AUTOCOMMIT = 0;

DROP TABLE IF EXISTS Players;

-- Players Table

CREATE TABLE Players (
    player_id int NOT NULL AUTO_INCREMENT,
    player_name varchar(50) NOT NULL,
    player_age int NOT NULL,
    position varchar(50) NOT NULL,
    salary int NOT NULL,
    jersey_number int NOT NULL,
    team_id int NOT NULL,
    PRIMARY KEY (player_id),
    FOREIGN KEY (team_id) REFERENCES Teams(team_id)
)ENGINE = InnoDB;

--
-- Sample Data for `Players` -- Note: Players salary euros per year
--

INSERT INTO Players (player_name, player_age, position, salary, jersey_number, team_id)
VALUES ('Karim Benzema', 34, 'Centre Forward', 24000000, 9, 3),
('Kevin De Bruyne', 31, 'Midfielder', 20800000, 17, 2),
('Kylian Mbappe', 23, 'Center Forward', 90910000, 7, 4),
('Mohamed Salah', 30, 'Right Winger', 18200000, 11, 1),
('Christaino Ronaldo', 37, 'Center Forward', 26800000, 7, 8),
('Leonel Messi', 34, 'Center Forward', 63640000, 30, 14),
('NGolo Kante', 31, 'Midfielder', 15050000, 7, 5),
('Dusan Vlahovic', 22, 'Center Forward', 12960000, 7, 10),
('Gabriel Jesus', 25, 'Center Forward', 13780000, 9, 12),
('Harry Kane', 28, 'Center Forward', 10400000, 10, 6),
('Lautaro Martinez', 24, 'Center Forward', 11110000, 10, 13),
('Robert Lewandowski', 33, 'Center Forward', 23000000, 9, 11),
('Hakan Calhanoglu', 28, 'Center Forward', 9260000, 20, 7);


-- Enable Commits & Foreign Key Checks 
SET FOREIGN_KEY_CHECKS=1;
COMMIT;

NOTE: I have deleted a couple tables include: Teams, Owners, Matches, Team_ownerships, Trophies..etc for simplicity. I need help with why order by and <> can’t output the correct answer. Am I doing something wrong here?

Thank you for the help in advance,

Answer

select player_name, jersey_number from Players order by jersey_number desc;

select player_name, player_age from Players where player_age < 30;

–DONT USE QUOTES IN ORDERBY–

Advertisement

Answer