using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;

namespace SQL_Homework_Entity_Framework
{
    class Program
    {
        static void Main(string[] args)
        {
            var context = new TelerikAcademyContext();

            // 1.Write a SQL query to find all information about all departments(use "TelerikAcademy" database).
            //var departmets = context.Departments.ToList();
            //departmets.ForEach(d => Console.WriteLine($"Department: {d.Name}, Manager ID: {d.ManagerId}"));

            //2.Write a SQL query to find all department names.
            //var departments = context.Departments
            //    .Select(d => d.Name)
            //    .ToList();

            //3.Write a SQL query to find the salary of each employee.
            //var employees = context.Employees
            //    .Select(e => new
            //    {
            //        e.FirstName,
            //        e.LastName,
            //        e.Salary
            //    });
            //foreach (var e in employees)
            //{
            //    Console.WriteLine($"Name: {e.FirstName} {e.LastName} Salary: {e.Salary}");
            //}

            //4.Write a SQL to find the full name of each employee.
            //var employees = context.Employees
            //    .Select(e => new
            //    {
            //        e.FirstName,
            //        e.LastName,
            //    });
            //foreach (var e in employees)
            //{
            //    Console.WriteLine($"Name: {e.FirstName} {e.LastName}");
            //}

            //5.Write a SQL query to find the email addresses of each employee(by his first and last name).
            //Consider that the mail domain is telerik.com.Emails should look like “John.Doe @telerik.com".
            //The produced column should be named "Full Email Addresses".
            //var fullEmailAddress = context.Employees
            //    .Select(e => new
            //    {
            //        e.FirstName,
            //        e.LastName,
            //    })
            //    .Select(e => e.FirstName + "." + e.LastName + "@telerik.com")
            //    .ToList();

            //6.Write a SQL query to find all different employee salaries.
            //var salaries = context.Employees
            //    .Select(e => new
            //    {
            //        e.Salary
            //    })
            //    .Distinct()
            //    .ToList();


            //7.Write a SQL query to find all information about the employees whose job title is “Sales Representative“.
            //var employees = context.Employees
            //    .Where(e => e.JobTitle == "Sales Representative")
            //    .ToList();

            //foreach (var e in employees)
            //{
            //    Console.WriteLine($"{ e.FirstName} {e.LastName}");
            //}

            //8.Write a SQL query to find the names of all employees whose first name starts with "SA".
            //var employees = context.Employees
            //    .Select(e => new
            //    {
            //        e.FirstName,
            //        e.LastName
            //    })
            //    .Where(e => e.FirstName.StartsWith("Sa"))
            //    .ToList();
            //foreach(var e in employees)
            //{
            //    Console.WriteLine($"{ e.FirstName} {e.LastName}");
            //}

            //9.Write a SQL query to find the names of all employees whose last name contains "ei".
            //var employees = context.Employees
            //    .Select(e => new
            //    {
            //        e.FirstName,
            //        e.LastName
            //    })
            //    .Where(e => e.LastName.ToLower().Contains("ei"))
            //    .ToList();
            //foreach (var e in employees)
            //{
            //    Console.WriteLine($"{ e.FirstName} {e.LastName}");
            //}

            //10.Write a SQL query to find the salary of all employees whose salary is in the range[20000…30000].
            //var salaries = context.Employees
            //    .Select(e => new
            //    {
            //        e.Salary,
            //        e.FirstName,
            //        e.LastName
            //    })
            //    .Where(e => e.Salary >= 20000 && e.Salary <= 30000)
            //    .ToList();
            //foreach (var e in salaries)
            //{
            //    Console.WriteLine($"{ e.FirstName} {e.LastName} - {e.Salary}");
            //}



            //11.Write a SQL query to find the names of all employees whose salary is 25000, 14000, 12500 or 23600.
            //var salaries = context.Employees
            //    .Select(e => new
            //    {
            //        e.Salary,
            //        e.FirstName,
            //        e.LastName
            //    })
            //    .Where(e => e.Salary == 25000 || e.Salary == 14000 || e.Salary ==12500 || e.Salary ==23600)
            //    .ToList();
            //foreach (var e in salaries)
            //{
            //    Console.WriteLine($"{ e.FirstName} {e.LastName} - {e.Salary}");
            //}

            //12.Write a SQL query to find all employees that do not have manager.
            //var employee = context.Employees
            //    .Select(e => new
            //    {
            //        e.FirstName,
            //        e.LastName,
            //        e.ManagerId
            //    })
            //    .Where(e => e.ManagerId == null)
            //    .ToList();

            //foreach (var e in employee)
            //{
            //    Console.WriteLine($"{ e.FirstName} {e.LastName}");
            //}


            //13.Write a SQL query to find all employees that have salary more than 50000.
            //Order them in decreasing order by salary.
            //var salaries = context.Employees
            //    .Select(e => new
            //    {
            //        e.Salary,
            //        e.FirstName,
            //        e.LastName
            //    })
            //    .Where(e => e.Salary >= 50000)
            //    .OrderByDescending(e => e.Salary)
            //    .ToList();
            //foreach (var e in salaries)
            //{
            //    Console.WriteLine($"{ e.FirstName} {e.LastName} - {e.Salary}");
            //}


            //14.Write a SQL query to find the top 5 best paid employees.
            //var salaries = context.Employees
            //    .Select(e => new
            //    {
            //        e.Salary,
            //        e.FirstName,
            //        e.LastName
            //    })
            //    .OrderByDescending(e =>e.Salary)
            //    .Take(5)
            //    .ToList();
            //foreach (var e in salaries)
            //{
            //    Console.WriteLine($"{ e.FirstName} {e.LastName} - {e.Salary}");
            //}


            //15.Write a SQL query to find all employees along with their address.Use inner join with ON clause.
            //var employees = context.Employees
            //    .Include(e => e.Address)
            //    .Select(e => new
            //    {
            //        e.FirstName,
            //        e.LastName,
            //        e.Address.AddressText
            //    })
            //    .ToList();
            //foreach (var e in employees)
            //{
            //    Console.WriteLine($"{ e.FirstName} {e.LastName} - {e.AddressText}");
            //}

            //16.Write a SQL query to find all employees and their address.
            //Use equijoins(conditions in the WHERE clause).


            //17.Write a SQL query to find all employees along with their manager.
            //var employees = context.Employees
            //    .Join(context.Employees,
            //    p => p.ManagerId,
            //    e => e.EmployeeId,
            //    (p, e) => new
            //    {
            //        ManFirstName = e.FirstName,
            //        ManLastName = e.LastName,
            //        EmpFirstName = p.FirstName,
            //        EmpLastName = p.LastName
            //    })
            //    .ToList();

            //foreach (var e in employees)
            //{
            //    Console.WriteLine($"Employee: { e.EmpFirstName} {e.EmpLastName} - Manager: {e.ManFirstName} {e.ManLastName}");
            //}


            //18.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.
            //var employees = context.Employees
            //    .Join(context.Employees,
            //    e => e.ManagerId,
            //    p => p.EmployeeId,
            //    (e, p) => new { e, p })
            //    .Join(context.Addresses,
            //    e1 => e1.e.AddressId,
            //    a => a.AddressId,
            //    (e1, a) => new { e1, a })
            //    .Select(empl => new
            //    {
            //        EmplFirstName = empl.e1.e.FirstName,
            //        EmplLastName = empl.e1.e.LastName,
            //        ManFirstName = empl.e1.p.FirstName,
            //        ManLastName = empl.e1.p.LastName,
            //        EmplAddress = empl.a.AddressText
            //    })
            //    .ToList();

            //    foreach (var e in employees)
            //{
            //    Console.WriteLine($"Employee: {e.EmplFirstName} {e.EmplLastName}, Manager: {e.ManFirstName} {e.ManLastName}, Address: {e.EmplAddress}");
            //}


            //19.Write a SQL query to find all departments and all town names as a single list. Use UNION.

            //NOTDONE //20.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.
            //var employees = context.Employees
            //    .Join(context.Employees,
            //    e => e.ManagerId,
            //    p => p.EmployeeId,
            //    (e, p) => new
            //    {
            //        EmplID = e.EmployeeId,
            //        ManFirstName = p.FirstName,
            //        ManLastName = p.LastName,
            //        EmpFirstName = e.FirstName,
            //        EmpLastName = e.LastName
            //    })
            //    .OrderBy(e => e.EmplID)
            //    .ToList();

            //foreach (var e in employees)
            //{
            //    Console.WriteLine($"Employee:{e.EmplID} { e.EmpFirstName} {e.EmpLastName} - Manager: {e.ManFirstName} {e.ManLastName}");
            //}

            //21.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.
            //var employees = context.Employees
            //    .Join(context.Departments,
            //    e => e.DepartmentId,
            //    d => d.DepartmentId,
            //    (e, d) => new { e, d }
            //    )
            //.Select(empl => new
            //{
            //    EmplFulltName = empl.e.FirstName + empl.e.LastName,
            //    Department = empl.d.Name
            //})
            //.Where(e => e.Department == "Sales" || e.Department == "Finance")
            //.ToList();

            //foreach (var e in employees)
            //{
            //    Console.WriteLine($"{e.Department} - {e.EmplFulltName}");
            //}



            //NOTDONE //22.Write a SQL query to find the names and salaries of the employees that take the minimal salary in the company.
            //    -Use a nested SELECT statement.

            // Throws an Exception for Min()

            //var employees = context.Employees
            //    .Select(e => new
            //    {
            //        FullName = e.FirstName + e.LastName,
            //        Salary = e.Salary
            //    })
            //    .Where(e => e.Salary == (context.Employees.Select(e1 => new { e1.Salary }).Min(e1 => e1.Salary)))
            //    .ToList();


            //foreach (var e in employees)
            //{
            //    Console.WriteLine($"{e.Salary} - {e.FullName}");
            //}


            //23.Write a SQL query to find the names and salaries of the employees that have a salary that is up to 10 % higher than the minimal salary for the company.
            //24.Write a SQL query to find the full name, salary and department of the employees that take the minimal salary in their department.
            //    - Use a nested SELECT statement.
            //25.Write a SQL query to find the average salary in the department #1.
            //var avgSalary = context.Employees
            //    .Select(e => new
            //    {
            //        e.Salary
            //    })
            //    .Average(e => e.Salary);

            //Console.WriteLine($"Average salary: {avgSalary}");

            //26.Write a SQL query to find the average salary  in the "Sales" department.

            //var avgSalary = context.Employees
            //    .Join(context.Departments,
            //    e => e.DepartmentId,
            //    d => d.DepartmentId,
            //    (e, d) => new { e, d })
            //    .Select(empl => new
            //    {
            //        Salary = empl.e.Salary,
            //        Department = empl.d.Name,
            //    })
            //    .Where(d => d.Department == "Sales")
            //    .Average(e => e.Salary);

            //Console.WriteLine(avgSalary);


            //27.Write a SQL query to find the number of employees in the "Sales" department.
            //var numberEmpl = context.Employees
            //    .Join(context.Departments,
            //    e => e.DepartmentId,
            //    d => d.DepartmentId,
            //    (e, d) => new { e, d })
            //    .Select(empl => new
            //{
            //    Salary = empl.e.Salary,
            //    Department = empl.d.Name,
            //})
            //    .Where(d => d.Department == "Sales")
            //    .Count();

            //Console.WriteLine(numberEmpl);

            //28.Write a SQL query to find the number of all employees that have manager.
            //var employeeCount = context.Employees
            //    .Where(e => e.ManagerId != null)
            //    .Count();
            //Console.WriteLine(employeeCount);

            //29.Write a SQL query to find the number of all employees that have no manager.
            //var employeeCount = context.Employees
            //    .Where(e => e.ManagerId == null)
            //    .Count();
            //Console.WriteLine(employeeCount);

            //30.Write a SQL query to find all departments and the average salary for each of them.
            //var query = context.People
            //       .GroupBy(p => p.name)
            //       .Select(g => new { name = g.Key, count = g.Count() });

            //var dptAvgSalary = context.Employees
            //    .Join(context.Departments,
            //    e => e.DepartmentId,
            //    d => d.DepartmentId,
            //    (e, d) => new { e, d })
            //    .Select(empl => new
            //    {
            //        Salary = empl.e.Salary,
            //        Department = empl.d.Name
            //    })
            //    .GroupBy(e => e.Department)
            //    .Select(e => new { name = e.Key, avg = e.Average(s => s.Salary) })
            //    .ToList();

            //foreach (var s in dptAvgSalary)
            //{
            //    Console.WriteLine($"{s.name} - {s.avg}");
            //}

  //NOTDONE //31.Write a SQL query to find the count of all employees in each department and for each town.
            //var employeesPerTown = context.Employees
            //    .Join(context.Departments,
            //    e => e.DepartmentId,
            //    p => p.DepartmentId,
            //    (e, p) => new { e, p })
            //    .Join(context.Addresses,
            //    e1 => e1.e.AddressId,
            //    a => a.AddressId,
            //    (e1, a) => new { e1, a })
            //    .Join(context.Towns,
            //    a1 => a1.a.TownId,
            //    t => t.TownId,
            //    (a1, t) => new { a1, t })
            //    .GroupBy(e => new
            //    {
            //        DepartmentName = e.a1.e1.p.Name,
            //        TownName = e.t.Name
            //    })
            //    .Select(e => new
            //    {
            //        departmentName = e.Key.DepartmentName,
            //        townName = e.Key.TownName,
            //        count = e.Count()
            //    })
            //    .ToList();

            //foreach (var e in employeesPerTown)
            //{
            //    Console.WriteLine($"{e.departmentName} - {e.count}");
            //}


            //32.Write a SQL query to find all managers that have exactly 5 employees.Display their first name and last name.
            //33.Write a SQL query to find all employees along with their managers.For employees that do not have manager display the value "(no manager)".
            //34.Write a SQL query to find the names of all employees whose last name is exactly 5 characters long.Use the built-in LEN(str) function.
            //35.Write a SQL query to display the current date and time in the following format "day.month.year hour:minutes:seconds:milliseconds".

            //   - Search in Google to find how to format dates in SQL Server.
            //36.Write a SQL statement to create a table Users. Users should have username, password, full name and last login time.

            //   - Choose appropriate data types for the table fields.Define a primary key column with a primary key constraint.

            //   - Define the primary key column as identity to facilitate inserting records.

            //   - Define unique constraint to avoid repeating usernames.

            //   - Define a check constraint to ensure the password is at least 5 characters long.
            //37.Write SQL statements to insert in the Users table the names of all employees from the Employees table.

            //  - Combine the first and last names as a full name.

            //   - For username use the first letter of the first name + the last name(in lowercase).

            //   - Use the same for the password, and NULL for last login time.
            //38.Write a SQL statement that changes the password to NULL for all users that have not been in the system since 10.03.2010.
            //39.Write a SQL statement that deletes all users without passwords(NULL password).
            //40.Write a SQL query to display the average employee salary by department and job title.
            //41.Write a SQL query to display the minimal employee salary by department and job title along with the name of some of the employees that take it.
            //42.Write a SQL query to display the town where maximal number of employees work.
            //43.Write a SQL query to display the number of managers from each town.
        }
    }
}