Basketball exercise

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!!!!

21. 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.

22. 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;

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

24. 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;

25. 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.

26. 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;