SQL Introduction

--4. Write a SQL query to find all information about all departments (use "SoftUni" database).
SELECT * FROM Departments

--5. Write a SQL query to find all department names.
SELECT Name FROM Departments

--6. Write a SQL query to find the salary of each employee.
SELECT Salary FROM Employees

--7. Write a SQL to find the full name of each employee.
SELECT FirstName + ' ' + LastName AS [FULL Name] FROM Employees

--8. Write a SQL query to find the email addresses of each employee.
SELECT FirstName + '.' + LastName + '@softuni.bg' AS [FULL Email Adresses] FROM Employees

--9. Write a SQL query to find all different employee salaries.
SELECT DISTINCT Salary FROM Employees

--10. Write a SQL query to find all information about the employees whose job title is “Sales Representative“.
SELECT * FROM Employees
WHERE JobTitle = 'Sales Representative'

--11. Write a SQL query to find the names of all employees whose first name starts with "SA".
SELECT FirstName FROM Employees
WHERE FirstName LIKE 'sa%'

--12. Write a SQL query to find the names of all employees whose last name contains "ei".
SELECT FirstName, LastName FROM Employees
WHERE LastName LIKE '%ei%'

--13. Write a SQL query to find the salary of all employees whose salary is in the range [20000…30000].
SELECT Salary FROM Employees
WHERE Salary BETWEEN 20000 AND 30000

--14. Write a SQL query to find the names of all employees whose salary is 25000, 14000, 12500 or 23600.
SELECT FirstName, LastName, Salary FROM Employees
WHERE Salary IN(25000, 14000, 12500, 23600)

--15. Write a SQL query to find all employees that do not have manager.
SELECT * FROM Employees
WHERE ManagerID IS NULL

--16. Write a SQL query to find all employees that have salary more than 50000.
--Order them in decreasing order by salary.
SELECT * FROM Employees
WHERE Salary > 50000
ORDER BY Salary DESC

--17. Write a SQL query to find the top 5 best paid employees.
SELECT TOP 5 * FROM EMPLOYEES
ORDER BY Salary DESC

--18. Write a SQL query to find all employees along with their address.
SELECT FirstName, LastName, a.AddressText AS [Address], t.Name FROM Employees e
    JOIN Addresses a ON e.AddressID = a.AddressID
    JOIN Towns t ON a.TownID = t.TownID

--19. Write a SQL query to find all employees and their address.
SELECT FirstName, LastName, a.AddressText, t.Name
FROM Employees e, Addresses a, Towns t
WHERE e.AddressID = a.AddressID AND a.TownID = t.TownID

--20. Write a SQL query to find all employees along with their manager.
SELECT e.FirstName, e.LastName,
    m.FirstName + ' ' + m.LastName AS Manager
FROM Employees e
    JOIN Employees m ON e.ManagerID = m.EmployeeID

--21. Write a SQL query to find all employees, along with their manager and their address.
SELECT e.FirstName, e.LastName,
    m.FirstName + ' ' + m.LastName AS Manager,
    a.AddressText AS [Address],
    t.Name AS Town
FROM Employees e
    JOIN Employees m ON e.ManagerID = m.EmployeeID
    JOIN Addresses a ON e.AddressID = a.AddressID
    JOIN Towns t ON a.TownID = t.TownID

--22. Write a SQL query to find all departments and all town names as a single list.
SELECT Name FROM Departments
UNION
SELECT Name FROM Towns

--23. Write a SQL query to find all the employees and the manager for each of them along with the employees that do not have manager.
SELECT e.FirstName, e.LastName,
    m.FirstName + ' ' + m.LastName AS Manager
FROM Employees e
    LEFT OUTER JOIN Employees m ON e.ManagerID = m.EmployeeID

--24. Write a SQL query to find the names of all employees from the departments "Sales" and "Finance"
--whose hire year is between 1995 and 2005.
SELECT FirstName, LastName, d.Name, HireDate FROM Employees e
    JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.Name = 'Sales' OR d.Name = 'Finance' AND
e.HireDate BETWEEN '1995/01/01' AND '2005/12/31'