SQL Introduction HW

-- Problem 3.   Start SQL Management Studio and connect to the database SoftUni.
-- Examine the major tables in the "SoftUni" database.
-- Your task is to examine the tables in the “SoftUni” database.
-- You do not have to submit anything in your homework for this problem.

SELECT TABLE_NAME AS "Table name"
FROM SoftUni.INFORMATION_SCHEMA.TABLES;

-----------------------------------------------------------------------------------------------------------
-- Problem 4.   Write a SQL query to find all information about all departments (use "SoftUni" database).
-- You should submit a SQL file as a part of your homework.

SELECT * FROM Departments;

-----------------------------------------------------------------------------------------------------------
-- Problem 5.   Write a SQL query to find all department names.
-- You should submit a SQL file as a part of your homework.

SELECT Name FROM Departments;

-----------------------------------------------------------------------------------------------------------
-- Problem 6.   Write a SQL query to find the salary of each employee.
-- You should submit a SQL file as a part of your homework.

SELECT FirstName + ' ' + LastName AS Name, Salary
FROM Employees;

-----------------------------------------------------------------------------------------------------------
-- Problem 7.   Write a SQL to find the full name of each employee.
-- You should submit a SQL file as a part of your homework.

SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name]
FROM Employees;

-----------------------------------------------------------------------------------------------------------
-- Problem 8.   Write a SQL query to find the email addresses of each employee.
-- Write a SQL query to find the email addresses of each employee. (by his first and last name).
-- Consider that the mail domain is softuni.bg. Emails should look like “John.Doe@softuni.bg".
-- The produced column should be named "Full Email Addresses".
-- You should submit a SQL file as a part of your homework.

SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name],
       FirstName + '.' + LastName + '@softuni.bg' AS [FULL Email Addresses]
FROM Employees;

-----------------------------------------------------------------------------------------------------------
-- Problem 9.   Write a SQL query to find all different employee salaries.
-- You should submit a SQL file as a part of your homework.

SELECT DISTINCT Salary
FROM Employees;

-----------------------------------------------------------------------------------------------------------
-- Problem 10.  Write a SQL query to find all information about the employees whose job
-- title is “Sales Representative“.
-- You should submit a SQL file as a part of your homework.

SELECT * FROM Employees
WHERE JobTitle = 'Sales Representative';

-----------------------------------------------------------------------------------------------------------
-- Problem 11.  Write a SQL query to find the names of all employees whose first name starts with "SA".
-- You should submit a SQL file as a part of your homework.

SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name]
FROM Employees
WHERE FirstName LIKE 'SA%';

-----------------------------------------------------------------------------------------------------------
-- Problem 12.  Write a SQL query to find the names of all employees whose last name contains "ei".
-- You should submit a SQL file as a part of your homework.

SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name]
FROM Employees
WHERE LastName LIKE '%ei%';

-----------------------------------------------------------------------------------------------------------
-- Problem 13.  Write a SQL query to find the salary of all employees whose salary is
-- in the range [20000…30000].
-- You should submit a SQL file as a part of your homework.

SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name],
       Salary
FROM Employees
WHERE Salary BETWEEN 20000 AND 30000;

-----------------------------------------------------------------------------------------------------------
-- Problem 14.  Write a SQL query to find the names of all employees whose salary
-- is 25000, 14000, 12500 or 23600.
-- You should submit a SQL file as a part of your homework.

SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name],
       Salary
FROM Employees
WHERE Salary IN(25000, 14000, 12500, 23600);

-----------------------------------------------------------------------------------------------------------
-- Problem 15.  Write a SQL query to find all employees that do not have manager.
-- You should submit a SQL file as a part of your homework.

SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name]
FROM Employees
WHERE ManagerID IS NULL;

-----------------------------------------------------------------------------------------------------------
-- Problem 16.  Write a SQL query to find all employees that have salary more than 50000.
-- Order them in decreasing order by salary.
-- You should submit a SQL file as a part of your homework.

SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name],
       Salary
FROM Employees
WHERE Salary > 50000
ORDER BY Salary DESC;

-----------------------------------------------------------------------------------------------------------
-- Problem 17.  Write a SQL query to find the top 5 best paid employees.
-- You should submit a SQL file as a part of your homework.

SELECT TOP(5) FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name], Salary
FROM Employees
ORDER BY Salary DESC;

-----------------------------------------------------------------------------------------------------------
-- Problem 18.  Write a SQL query to find all employees along with their address.
-- Use inner join with ON clause.
-- You should submit a SQL file as a part of your homework.

SELECT e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [FULL Name],
       t.Name AS Town,
       a.AddressText AS [Address]
FROM Employees e
  JOIN Addresses a
    ON e.AddressID = a.AddressID
  JOIN Towns t
    ON a.TownID = t.TownID;

-----------------------------------------------------------------------------------------------------------
-- Problem 19.  Write a SQL query to find all employees and their address.
-- Use equijoins (conditions in the WHERE clause).
-- You should submit a SQL file as a part of your homework.

SELECT e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [FULL Name],
       t.Name AS Town,
       a.AddressText AS [Address]
FROM Employees e, Addresses a, Towns t
WHERE e.AddressID = a.AddressID AND a.TownID = t.TownID;

-----------------------------------------------------------------------------------------------------------
-- Problem 20.  Write a SQL query to find all employees along with their manager.
-- You should submit a SQL file as a part of your homework.

SELECT e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [Employee FULL Name],
       m.FirstName + ' ' + ISNULL(m.MiddleName + ' ', '') + m.LastName AS [Manager FULL Name]
FROM Employees e
  JOIN Employees m
    ON e.ManagerID = m.EmployeeID;

-----------------------------------------------------------------------------------------------------------
-- Problem 21.  Write a SQL query to find all employees, along with their manager and their address.
-- You should join the 3 tables: Employees e, Employees m and Addresses a.
-- You should submit a SQL file as a part of your homework.

SELECT e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [Employee FULL Name],
       m.FirstName + ' ' + ISNULL(m.MiddleName + ' ', '') + m.LastName AS [Manager FULL Name],
       t.Name AS Town,
       a.AddressText AS [Address]
FROM Employees e
  JOIN Addresses a
    ON e.AddressID = a.AddressID
  JOIN Towns t
    ON a.TownID = t.TownID
  JOIN Employees m
    ON e.ManagerID = m.EmployeeID;

-----------------------------------------------------------------------------------------------------------
-- Problem 22.  Write a SQL query to find all departments and all town names as a single list.
-- Use UNION.
-- You should submit a SQL file as a part of your homework.

SELECT Name AS [Towns AND Departments List]
FROM Departments
UNION
SELECT Name AS [Towns AND Departments List]
FROM Towns;

-----------------------------------------------------------------------------------------------------------
-- Problem 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.
-- Use right outer join. Rewrite the query to use left outer join.
-- You should submit a SQL file as a part of your homework.

-- LEFT OUTER JOIN
SELECT e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [Employee FULL Name],
       ISNULL(m.FirstName + ' ' + ISNULL(m.MiddleName + ' ', '') + m.LastName, '') AS [Manager FULL Name]
FROM Employees e LEFT OUTER JOIN Employees m
  ON e.ManagerID = m.EmployeeID;

-- RIGHT OUTER JOIN
SELECT e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [Employee FULL Name],
       ISNULL(m.FirstName + ' ' + ISNULL(m.MiddleName + ' ', '') + m.LastName, '') AS [Manager FULL Name]
FROM Employees m RIGHT OUTER JOIN Employees e
  ON e.ManagerID = m.EmployeeID;

-----------------------------------------------------------------------------------------------------------
-- Problem 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.
-- You should submit a SQL file as a part of your homework.

SELECT e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [Employee FULL Name],
       YEAR(e.HireDate) AS [Hire YEAR],
       d.Name AS [Department Name]
FROM Employees e
  JOIN Departments d
    ON e.DepartmentID = d.DepartmentID
WHERE YEAR(HireDate) BETWEEN 1995 AND 2005 AND d.Name IN('Sales', 'Finance');

-----------------------------------------------------------------------------------------------------------