1 / 26

Basketball exercise

Basketball exercise. Q1 - Show all the records in the basket ball player table. select * from BSKT_BALL_PLAYER;. Q1 - answer. Q2 - Show the last name and first name of the players. select player_lname , player_fname From BSKT_BALL_PLAYER;. Q2 with alias.

illiana-bentley
Télécharger la présentation

Basketball exercise

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Basketball exercise

  2. Q1 - Show all the records in the basket ball player table select * from BSKT_BALL_PLAYER;

  3. Q1 - answer

  4. Q2 - Show the last name and first name of the players. • select player_lname, player_fname • From BSKT_BALL_PLAYER;

  5. Q2 with alias select player_lname as "Last Name", player_fname as "First Name" from BSKT_BALL_PLAYER;

  6. Q3 -Order the above by descending lname then descending first name select player_lname, player_fname from BSKT_BALL_PLAYER • order by player_lname DESC, player_fname DESC ;

  7. Q3 - output

  8. Q4 - Add the GPA to the output and sort the output by GPA only. select player_lname, player_fname, gpa from BSKT_BALL_PLAYER orderbygpa;

  9. Q5 - Show the unique graduation years of all the players. select distinct year_graduation • from BSKT_BALL_PLAYER;

  10. What if I don’t use the keyword distinct? Some of the values are duplicated, like 2009, 2010, 2009… Number of records goes from 13 to 19

  11. Q6 - Show the player’s first and last name and college if the college is 1. Order the output by player’s last name. select player_fname, player_lname, college from BSKT_BALL_PLAYER where COLLEGE = 1 • order by player_lname;

  12. Q 6 answer

  13. But is college_id what they are looking for? select player_fname, player_lname, college_name, college_id from BSKT_BALL_PLAYER, college where COLLEGE = 1 and college = college_id • order by player_lname;

  14. Q6 answer revised

  15. Q7 - Show the player’s first and last name , college, gpa if the college is 1 and the student’s GPA is greater than 3.5. Order the output by player’s last name.

  16. No QC

  17. With QC

  18. Q 8 - Show the player’s first and last name , college, gpa if the college is 1 and the student’s GPA is greater than 3.5 OR the student’s jersey is #13 Order the output by player’s last name.

  19. Key to this one… • Make sure to add parentheses!!!!

  20. Q 9 Display the player’s first and last name , college, gpa if the college is 1, 2, 3, 4, or 5. Order the output by player’s last name.

  21. Remember the in operator • select player_fname, player_lname, college_namegpa, jersey_number, college • from BSKT_BALL_PLAYER, college • where COLLEGE in (1, 2, 3, 4, 5) and • college = college_id • order by player_lname; • OR • select player_fname, player_lname, college_name, gpa, jersey_number, college • from BSKT_BALL_PLAYER, college • where COLLEGE = 1 or college =2 or college =3 or college = 4 or college= 5 and • college = college_id • order by player_lname;

  22. Q10 Show the players’ names born between 1/1/1984 and 3/5/1986. Order by player last name.

  23. Remember the to_date function! • select player_fname, player_lname, dob • from BSKT_BALL_PLAYER • where dob between to_date ('01/01/1984', 'mm/dd/yyyy') and to_date ('03/05/1986', 'mm/dd/yyyy') • order by player_lname;

  24. Q 11 Display the player’s first and last name , college, and gpa if the college is Temple University. Order the output by player’s last name.

  25. select player_fname, player_lname, college, gpa, jersey_number from BSKT_BALL_PLAYER, college where COLLEGE_name = 'Temple University' and COLLEGE_ID= COLLEGE order by player_lname;

More Related