SQL QUERIES - HELP....I am not able to form this big queries

Question

Hi all, I need help in forming SQL Query ..

My Master Tables-

1. Employees

ID	EmployeeName	ManagerName	EmailID	Dept	Flag
1	Girish.Shah	Ashish.Chakraborty	[email protected]	A	1
2	Grishma.Sharma	Ashish.Chakraborty	[email protected]	B	1
3	Ashish.Chakraborty	Sanjay.Dutta	[email protected]	C	1
4	Arpita.Sinha	Jignasa.Ved	[email protected]	A	1
5	Alok.Kadu	Jignasa.Ved	[email protected]	C	1
6	Jignasa.Ved	Sanjay.Dutta	[email protected]	B	1
7	Amrita.Verma	Pooja.Kapil	[email protected]	B	1
8	Pooja.Kapil	Sanjay.Dutta	[email protected]	C	1
9	Sumit.Sharma	Pooja.Kapil	[email protected]	C	1
10	Sanjay.Dutta	Sanjay.Dutta	[email protected]	C	1

 

Column-“EmployeeName” – List of all employess. (Even Managers are employess).

Column – ID and EmployeeName – are unique.

2. TrainingType

ID	Training Type	Flag
1	Technical	1
2	Non - Technical	1

 

3. Training Level

ID	TrainingLevel	Flag
1	Beginner	1
2	Intermediate	1
3	Professional	1

 

4. Training Name

ID	TrainingLevelID	TrainingTypeID	TrainingName	Flag
1	1	1	Oracle 12c	1
2	1	2	Email writing skills	1
3	3	2	Presentation Skills	1
4	2	1	Adobe	1

TrainingLEvelID – ForeignKEy of ID column of TrainingLevel table.

TrainingTypeID – ForeignKey of ID Column of TrainingType table.

5. Other subject

ID	TrainingLevelID	TrainingTypeID	Other TrainingName	Flag
1	1	1	LINQ	1
2	1	2	Business meetings	1
3	3	2	Managing Top performers	1
4	2	1	Video Streaming	1

TrainingLEvelID – ForeignKEy of ID column of TrainingLevel table.

TrainingTypeID – ForeignKey of ID Column of TrainingType table.

Logic-

If the user does not find appropriate training name in the dropdownlist in front-end, he will select a subject from “Other Subject” in other dropdownlist from front-end.

TRANSACTION TABLE –

ID	ManagerID	EmployeeID	TrainingLevelID	TrainingTypeID	TrainingID	OtherSubjectID	TimeLine	Remarks	Flag
1	3	1	1	1	1	0	Q1	Abcm	1
2	6	4	2	1	4	0	Q2	Dsf	1
3	6	5	1	1	0	1	Q3	Sdf	1
4	3	2	1	2	0	2	Q4	T	1
5	8	9	2	2	0	3	Q4	S	1
6	8	7	3	2	4	0	Q3	D	1
7	10	8	3	2	0	3	Q2	df	1

ManagerID – ID in EmployeeTable – 3,6,8 are managers.

Logic – “select count(*) from Employees where managerName = @ManagerName (parameter from front-end).

If count(*) > 0; select employeesname from Employees where managername = @ManagerName.

EmployeeID – it can be anyID (Every Manager is employee and each each employee has a manager).

TrainingTypeID – Foreign Key of TrainingType table.

If user selects training name from 1^st dropdownlist, then that ID will be inserted to TrainingID and OtherSubjectID will be 0.

If user select “Other Subject”, then TrainingTypeID will be 0.

 

 

 

I need the queries for following output -

1.

Subject Name	Other Subject	Total Reports	Level
Oracle 12c	NULL	1 (Only 1 employee was send to oracle training)	Beginner - 1
Email writing skills	NULL	0	Beginner
Presentation Skills	NULL	0	Beginner
Adobe	NULL	2 (Only 2 person went for adobe training)	Beginner-1 Intermediate -1
NULL	LINQ	1	Beginner

How do I get this query ?

One Columns – List of Subjects

Second Column – List of other subject

Total Report – Count (*) of how many employee attended that training

Level Wise division – How many for beginner, how many for intermediate, how many for expert

2. The “Total Reports” column will be a hyperlink – When the user will click on “1” or “2”, he must get the following –

 

 

 

 

 

 

 

 

 

 

 

Subject	Oterh Subject	Employee	Manager	EmailID	Dept	LevelOfTraining	Quarter	Percentage
Oracle 12c	NULL	Girish.Shah	Ashish.Chakraborty	[email protected]	A	Beginner	Q4	50% ( 2 persons report to Ashish.Chakraborty, he sent only 1 person to oracle 12c Training)
Adobe	NULL	Jignasa.Ved	Sanjay.Dutta	[email protected]	B	Beginner	Q2	75% ( 4 persons report to Sanjay.Dutta , he sent only 3 persons to adobe training ).
Adobe		Pooja.Kapil	Sanjay.Dutta	[email protected]	C	Intermediate	Q3

And likewise – the entire list…

Please guide..