using System;

using System.Collections.Generic;

using System.Linq;

​

​

public class Salesperson

{

    public int SNUM { get; set; }

    public string SNAME { get; set; }

    public string CITY { get; set; }

    public double COMM { get; set; }

​

    public Salesperson(int snum, string sname, string city, double comm)

    {

        SNUM = snum;

        SNAME = sname;

        CITY = city;

        COMM = comm;

    }

}

​

public class Employee

{

    public int Id { get; set; }

    public string Name { get; set; }

    public string Department { get; set; }

}

​

public class Customer

{

    public int CNUM { get; set; }

    public string CNAME { get; set; }

    public string CITY { get; set; }

    public int RATING { get; set; }

    public int SNUM { get; set; }

​

    public Customer(int cnum, string cname, string city, int rating, int snum)

    {

        CNUM = cnum;

        CNAME = cname;

        CITY = city;

        RATING = rating;

        SNUM = snum;

    }

}

​

public class Order

{

    public int ONUM { get; set; }

    public double AMT { get; set; }

    public DateTime ODATE { get; set; }

    public int CNUM { get; set; }

    public int SNUM { get; set; }

​

    public Order(int onum, double amt, DateTime odate, int cnum, int snum)

    {

        ONUM = onum;

        AMT = amt;

        ODATE = odate;

        CNUM = cnum;

        SNUM = snum;

    }

}

​

​

public class Program

{

    public static void Main()

    {

        var salespeople = new List<Salesperson>

        {

            new Salesperson(1001, "Peel", "London", 0.12),

            new Salesperson(1002, "Serres", "San Jose", 0.13),

            new Salesperson(1004, "Motika", "London", 0.11),

            new Salesperson(1007, "Rifkin", "Barcelona", 0.15),

            new Salesperson(1003, "AxelRod", "New York", 0.10),

            new Salesperson(1005, "Fran", "London", 0.26)

        };

        var customers = new List<Customer>

        {

            new Customer(2001, "Hoffman", "London", 100, 1001),

            new Customer(2002, "Giovanni", "Rome", 200, 1003),

            new Customer(2003, "Liu", "San Jose", 200, 1002),

            new Customer(2004, "Grass", "Berlin", 300, 1002),

            new Customer(2006, "Clemens", "London", 100, 1001),

            new Customer(2008, "Cisneros", "San Jose", 300, 1007),

            new Customer(2007, "Pereira", "Rome", 100, 1004)

        };

​

        var orders = new List<Order>

        {

            new Order(3001, 18.69, new DateTime(1996, 10, 3), 2008, 1007),

            new Order(3003, 767.19, new DateTime(1996, 10, 3), 2001, 1001),

            new Order(3002, 1900.10, new DateTime(1996, 10, 3), 2007, 1004),

            new Order(3005, 5160.45, new DateTime(1996, 10, 3), 2003, 1002),

            new Order(3006, 1098.16, new DateTime(1996, 10, 3), 2008, 1007),

            new Order(3009, 1713.23, new DateTime(1996, 10, 4), 2002, 1003),

            new Order(3007, 75.75, new DateTime(1996, 10, 4), 2002, 1003),

            new Order(3008, 4723.00, new DateTime(1996, 10, 5), 2006, 1001),

            new Order(3010, 1309.95, new DateTime(1996, 10, 6), 2004, 1002),

            new Order(3011, 9891.88, new DateTime(1996, 10, 6), 2006, 1001)

        };

​

        var employees = new List<Employee>

        {

            new Employee { Id = 1, Name = "Alice", Department = "HR" },

            new Employee { Id = 2, Name = "Bob", Department = "Finance" },

            new Employee { Id = 3, Name = "Charlie", Department = "IT" }

        };

        Console.WriteLine("Hello World");

        Console.WriteLine("1. List all the columns of the Salespeople table.");

        var q1 = from s in salespeople

                    select new {SNUM = s.SNUM, SNAME = s.SNAME, s.CITY, s.COMM};

        foreach(var q in q1) Console.WriteLine($"{q.SNUM}, {q.SNAME}, {q.CITY}, {q.COMM}");

        // Console.WriteLine("2. List all customers with a rating of 100.");

        var q2 = from c in customers

                where c.RATING > 100

                select new {cname = c.CNAME};

        // foreach(var q in q2) Console.WriteLine($"{q.cname}");

        Console.WriteLine("3. Find all records in the Customer table with NULL values in the city column.");

        var q3 = from c in customers where c.CITY == null select new {cnum = c.CNUM};

        Console.WriteLine("4. Find the largest order taken by each salesperson on each date.");

        var q4 = from o in orders

                group o by new {odate = o.ODATE, snum = o.SNUM} into g

                select new {

                    odate = g.Key.odate,

                    SNUM = g.Key.snum,

                    maxAmt = g.Max(x => x.AMT)

                };

        Console.WriteLine("5. Arrange the Orders table by descending customer number.");

        var q5 = from o in orders

                    orderby o.CNUM descending

                    select new {

                        onum = o.ONUM,

                        cnum = o.CNUM

                    };

        // foreach(var q in q5) Console.WriteLine($"{q.onum} {q.cnum}");

        Console.WriteLine("6. Find which salespeople currently have orders in the Orders table.");

        Console.WriteLine("7. List names of all customers matched with the salespeople serving them.");

        var q7 = from c in customers

                join s in salespeople on c.SNUM equals s.SNUM

                select new {cname = c.CNAME, sname = s.SNAME};

        foreach(var q in q7) Console.WriteLine($"customerNamr = {q.cname}, salespeopleName {q.sname}");

        Console.WriteLine("8. Find the names and numbers of all salespeople who had more than one customer.");

        var q8 = from c in customers

                group c by c.SNUM into g

                where g.Count() > 1

                join s in salespeople on g.Key equals s.SNUM

                select new {

                    snum = s.SNUM,

                    sname = s.SNAME

                };

        Console.WriteLine("9. Count the orders of each of the salespeople and output the results in descending order.");

        Console.WriteLine("10. List the Customer table if and only if one or more of the customers in the Customer table are located in San Jose.");

​

    }

}