INTRO_TO_SQL_7

USE TelerikAcademy

SELECT *FROM Employees

SELECT FirstName + '.' + LastName + '@telerik.com' AS [Full Email Adress] FROM Employees

SELECT DISTINCT Salary FROM Employees

SELECT FirstName, JobTitle FROM Employees
WHERE JobTitle = 'Sales Representative'

SELECT FirstName FROM Employees
WHERE FirstName LIKE 'SA%'

SELECT LastName FROM Employees
WHERE LastName LIKE '%SA%'

SELECT FirstName, Salary FROM Employees
WHERE Salary BETWEEN 20000 AND 30000
ORDER BY Salary DESC;

SELECT FirstName, Salary FROM Employees
WHERE Salary IN(25000,14000,12500,23600)
ORDER BY Salary DESC;

SELECT FirstName, ManagerID FROM Employees
WHERE ManagerID IS NULL;

SELECT FirstName, Salary FROM Employees
WHERE Salary > 50000
ORDER BY Salary DESC;

SELECT TOP 5 EmployeeID, FirstName, Salary FROM Employees
ORDER BY Salary DESC

SELECT * FROM Employees e JOIN Addresses a ON e.AddressID = a.AddressID

SELECT e.FirstName, a.AddressText FROM Employees e JOIN Addresses a ON e.AddressID = a.AddressID

SELECT FirstName, ManagerID From Employees

SELECT e.FirstName, e1.FirstName AS [Manager Name] FROM Employees e JOIN Employees e1 ON e.ManagerID = e1.EmployeeID
ORDER BY e.EmployeeID;
GO

/*21. Write a SQL query to find all employees, along with their manager and their address. 
Join the 3 tables: Employees e, Employees m and Addresses a*/
SELECT e.FirstName, m.FirstName, ea.AddressText AS [Employee Adress], ma.AddressText [Manager Adress]
FROM Employees e JOIN Employees m ON e.ManagerID = m.EmployeeID
JOIN Addresses ea ON e.AddressID = ea.AddressID
JOIN Addresses ma ON m.AddressID = ma.AddressID

--22.
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 AS [Employee], m.FirstName AS [Manager] FROM
Employees e LEFT 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 e.FirstName, d.Name, e.HireDate FROM 
Employees e JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.HireDate BETWEEN '1995-01-01' AND '2005-01-01' AND
d.Name IN('Sales','Finance')
ORDER BY e.HireDate;