assignment 1

Database1 Lab

Home Work sheet

 

 Depending on the following database tables , Write a query to do the following:

 Team

Teamno  number (3)

PK

Tname  Varchar2(20)

N.N

Country 

varchar2(20)

N.N

Player

Palyerno

number(4)

PK

Playername

varchar2(20)

N.N

Salary

 number(7)

Birthdate 

date

Teamno

number(3)

Captainno number(4)

 

  1. List names of all teams.
  2. List all player's names and salary.
  3. Show players born in year 1985.
  4. Display team's country if team name begins with letter 'M'
  5. List player information if his salary between 25000 and 50000.
  6. List player's names whose captainno= 1.
  7. List player name, yearly salary labeled as "Annual Salary" for any player who played in team number 1.
  8. Show players sorted according to salary.
  9. Display team names all in capital letters.
  10. List name's length for any player whose salary is grater than 12000 or plays in team number 1.Sort your output according to name length.
  11. List all players who have no captain.
  12. List average salary  of players in each team. (Group func.)
  13. Display number of teams in 'FRANCE'. (Group func.)
  14. Display total salary of all players. (Group func.)
  15. List maximum and minimum salary for each team.(group func)
  16. List player names whose country is ‘FRANCE’.(join)
  17. List team number,total salary and highest salary for each team if the total exceeds 100000. (group func)
  18. Show country,team name and player name for all players.(join)

 

 

 Note:

    • Use user Scott to login to database and run your query
    • Save your answers in a script file as "hw1_id.sql"
    • Upload your file before dead line