using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp3
{
    internal class Program
    {
        static void Main(string[] args)
        {
            string stringConnection = "Data Source=k2113;User Id=sa;Password=ucenici2023;Initial Catalog=TSK;MultipleActiveResultSets=true";//konekcioni string
            using (SqlConnection connection = new SqlConnection(stringConnection))//objekat konekcije
            {
                connection.Open();
                Dictionary<int, string> Predmeti = new Dictionary<int, string>();//lista koja sadrzi dva tipa podataka,medjusobno poveznih
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = connection;
                    cmd.CommandText = "select predaje.predmetId,naziv from predmet,predaje,predavac where ime='Zorana' and prezime='Babic' and predavac.predavacId=predaje.predavacId and predaje.predmetId=predmet.predmetId";
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if(reader.HasRows)
                        {
                            while(reader.Read())
                            {
                                int idpr = reader.GetInt32(0);
                                string naziv = reader.GetString(1);
                                Predmeti.Add(idpr, naziv); 

                            }
                        }
                    }
                    cmd.CommandText = "select skolaId,naziv from skola";
                    foreach(int idpr in Predmeti.Keys)
                    {
                        Console.WriteLine("Predmet koji predaje zorana babic je {0}", Predmeti[idpr]);
                        using(SqlDataReader reader1 = cmd.ExecuteReader())
                        {
                            if(reader1.HasRows)
                            {
                                while(reader1.Read())
                                {
                                    int idsk = reader1.GetInt32(0);
                                    string nazivSkole = reader1.GetString(1);
                                    using( SqlCommand cmd1=new SqlCommand())
                                    {
                                        cmd1.Connection = connection;
                                        cmd1.CommandText = "select count(*) from ucenik,pohadja where ucenik.skolaId=" + idsk + " and pohadja.predmetId=" + idpr + " and ucenik.ucenikId=pohadja.ucenikId";
                                        int brojucenika = (int)cmd1.ExecuteScalar();
                                        Console.WriteLine("iz skole{0} ima {1} ucenika", nazivSkole, brojucenika);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            Console.ReadKey();

        }
    }
}