Editing SQL 2

=== SQL Exercise 3 ===

Create the tables and insert data from the following files [[https://github.com/DaStanzel/Unterlagen-Datenbanken-CB/blob/main/%C3%9Cbungen/SQLUebung03/parts.sql Parts insert and create tables]] [[https://github.com/DaStanzel/Unterlagen-Datenbanken-CB/blob/main/%C3%9Cbungen/SQLUebung03/tennis-tables.sql Tennis Create Tables]] [[https://github.com/DaStanzel/Unterlagen-Datenbanken-CB/blob/main/%C3%9Cbungen/SQLUebung03/tennis-insert.sql Tennis Insert Data]]

==== 1-8 Tennis queries ====
1. output of PLAYERNO, NAME of players born after 1960.

2. output of PLAYERNO, NAME and TOWN of all female players who do not reside in Strat- ford.

3. output of player numbers of players who joined the club between 1970 and 1980.

4. output of PlayerId, Name, Year of Birth of players born in a leap year.

5. output of the penalty numbers of the penalties between 50,- and 100,-.

6. output of PlayerId, name of players who do not live in Stratford or Douglas.

7. output of playerId and name of players whose name contains 'is'.

8. output of all hobby players.

==== 9 - 21 EmpDept queries. ====
9. output of those employees who receive more commission than salary.

10. output of all employees from department 30 whose salary is greater than or equal to 1500.

11. output of all managers who do not belong to department 30.

12. output of all employees from department 10 who are neither managers nor clerical workers (CLERK).

13. output of all employees who earn between 1200,- and 1300,-.

14. output all employees whose name is 5 characters long and begins with ALL.

15. output the total salary (salary + commission) for each employee.

16. output all employees, whose commission is over 25% of the salary.

17. searched is the average salary of all office employees.

18. searched is the number of employees who have received a commission.

19. wanted is the number of different jobs in department 30.

20. wanted is the number of employees in department 30.

21. output of employees hired between 4/1/81 and 15/4/81.

=== SQL Exercise 4 ===


==== 1-6 Tennis query ==== 
1. output TEAMNO of the teams in which the player with the number 27 is not captain
2. output of PLAYERNO, NAME and INITIALS of the players who have won at least one match
3. output of playerNo and name of the players who have received at least one penalty
4. output of playerNo and name of the players, who have received at least one penalty over 50.
5. output of PlayerNo and name of players born in the same year as R. Parmenter
6. output of playerNo and name of the oldest player from Stratford

==== 7-12 EmpDept query ====
7. search all departments, which have no employees
8. search all employees who have the same job as JONES
9. show all employees who make more than any employee from department 30
10. show all employees who earn more than any employee from department 30
11. display all employees from department 10 whose job is not held by any employee from department 30
12. search for the employee data (EMPNO, ENAME, JOB, SAL) of the employee with the highest salary.

=== SQL Exercise 5 ===

==== 1-11 Tennis query ====
1. number of new players per year
2. number and average amount of penalties per player
3. number of penalties for the years before 1983
4. in which cities live more than 4 players
5. PLAYERNO of those players whose penalty total is over 150
6. NAME and INITIALS of those players who received more than one penalty
7. in which years there were exactly 2 penalties
8. NAME and INITIALS of the players who received 2 or more penalties over $40
9. NAME and INITIALS of the player with the highest penalty amount
10. in which year there were the most penalties and how many were there
11. PLAYERNO, TEAMNO, WON - LOST sorted by the the lost.

==== 12-19 EmpDept query ====
12. output of all employees from department 30 sorted by their salary starting with the highest salary.
13. output of all employees sorted by job and within the job by their salary
14. output of all employees sorted by their year of employment in descending order and within the year by their name
15. output of all salesmen in descending order regarding the ratio commission to salary
16. output the average salary to each department number
17. calculate the average annual salaries of those jobs that are performed by more than 2 employees
18. output all department numbers with at least 2 office workers
19. find the average value for salary and commission of all employees from department 30

=== SQL Exercise 6 ===

==== 1-5 Tennis query ====
1. NAME, INITIALS and number of sets won for each player
2. NAME, PEN_DATE and AMOUNT sorted in descending order by AMOUNT
3. TEAMNO, NAME (of the captain) per team
4. NAME (player name), WON, LOST of all won matches
5. PLAYERNO, NAME and penalty amount for each team player. If a player has not yet received a penalty, it should still be issued. Sorting should be done in ascending order of penalty amount

==== 6-9 EmptDept query ==== 
6. in which city does the employee Allen work?
7. search for all employees who earn more than their supervisor
8. output the number of hires in each year
9. output all employees who have a job like an employee from CHICAGO.

=== SQL Exercise 7 ===

==== 1-4 Tennis query ====
1. output of players' names who played for both team 1 and team 2.
2. output the NAME and INITIALS of the players who did not receive a penalty in 1980
3. output of players who received at least one penalty over $80
4. output of players who had any penalty over $80.

==== 5-8 EmpDept query ====
5. find all employees whose salary is higher than the average salary of their department
6. identify all departments that have at least one employee
7. output of all departments that have at least one employee earning over $1000
8. output of all departments in which each employee earns at least 1000,-.


=== SQL Exercise 8 ===

# creating the PARTS table([[https://github.com/DaStanzel/Unterlagen-Datenbanken-CB/blob/main/%C3%9Cbungen/SQLUebung03/parts.sql Parts insert and create tables]])
# display the whole hierarchy of those parts that make up P3 and P9
# at which hierarchy level is P12 used in P1
# how many parts to P1 cost more than $20 to EMP-DEPT
# output of all direct and indirect employees belonging to JONES (without JONES itself, with corresponding indentation per hierarchy)
# output of all direct and indirect superiors of SMITH (including SMITH itself)
# output of the average salary for each hierarchy level

=== SQL Exercise 6 using Joins ===

Solve exercise 6 using Joins.

=== SQL Exercise 7 using Joins ===

Solve exercise 7 using Joins.

=== SQL Exercise 8 using Joins ===

Solve exercise 8 using Joins.

=== Competence Check ===

Solve the following excercises using MySQL and upload an .sql File with the solutions

Tables
Create the following tables like shown in the [[https://github.com/DaStanzel/Unterlagen-Datenbanken-CB/blob/main/Übungen/Joins/codersbay_ue6.jpg Class Diagram]]

Class Diagram

Table description:

REGIONS contains rows that represent a region (for example, North, South America, Asia). COUNTRIES contains rows for countries. Each row is linked to a region. LOCATIONS contains the addresses of specific offices, warehouses and/or production sites of a company in a specific country. DEPARTMENTS displays department details where employees work. Each department can have a relationship that represents the head of department in the EMPLOYEES table. EMPLOYEES contains details of each employee working for a department. Not all employees must be assigned to a department. JOBS contains the types of positions that each employee can hold. JOB_HISTORY contains the individual positions that the employee has held so far. If an employee changes department or position within a department, a new row is added to this table with information about the employee's old position. JOB_GRADES identifies a salary range per pay grade level. The salary ranges do not overlap.

Insert
For inserting use the insert script file which is in the repository

==== Select ====
For excercising the select statement do the following excercises

the HR department wants a query to display the last name, job ID (JOB_ID), hiring date and employee number for each employee, with the employee number as the first value Specify the alias STARTDATE for the HIRE_DATE column. (2 points)

the human resources department needs a query to display all unique job identifiers (JOB_ID) from the EMPLOYEES table Duplicates should be avoided. (2 points)

For budget purposes, the Human Resources Department requires a report that shows last name and salary for employees earning more than $12,000. Run the query. (2 points)

create a report to display the last name and department number for the employee with employee number 176 (3 points)

create a report to show the last name, job ID (JOB_ID) and hiring date of all employees Sort the query in ascending order by hiring date. (3 points)

display last names and department numbers of all employees in department 20, sorted alphabetically by last name in ascending order (3 points)

create a query that shows last names, salaries and commissions of all employees whose commission is 20%. Give the columns Employee, Monthly Salary, and Commission headings. (3 points)

==== Joins ====
For excercising joins solve the following excercises

The management would like a list of the different salaries per job. The output should contain the job_id as well as the sum of the salaries per job_id. In addition, the output should be sorted in descending order according to the sum of the salaries.

The personnel department wants to have information about the average salary of the employees at the current time.

The personnel department would like a list of all employees (first name, last name), on which the department name (department_name) is also displayed.

For the new stationery, the secretary's office needs a list of all departments (department_name) as well as their address consisting of the postal code, the city, the province, and the street and house number

The secretariat thanks for the list, but would like to have the name of the country in addition.

The secretariat thanks for the updated list. Embarrassed, the first and last name as "Manager" of the respective manager of the department is now requested in addition.

The personnel department needs a list of the employees with the following contents:

1.) First and last name as "Name

2.) job_title as "job"

3.) The salary

4.) The department name

The new General Manager asks you to find out which subordinates each employee has.
You could now collect the data manually, but something stirs inside you when you feel the challenge of generating the result via MySQL. Accept it!