Notes

--SQL QUERY
create database CollegeData

create table Department
(
DeptID int,
DeptName nvarchar(50),
CourseID int,
TeacherID int,
StudentID int
)

insert into Department
values
(1,'CSE',211,53,103),
(2,'ECE',215,50,101),
(3,'EE',213,51,100),
(4,'CE',210,54,104),
(5,'ML',212,52,102)

create table Infrastructure
(
CollegeID int,
Rating int,
Building nvarchar(20),
LabFacility nvarchar(20)
)

insert into Infrastructure
values
(1001,3,'Good','Bad'),
(1002,5,'Excellent','Excellent'),
(1003,2,'Bad','Bad'),
(1004,4,'Good','Good'),
(1005,3,'Bad','Good')

create table Course
(
courseID int,
courseName nvarchar(50),
TheoryID int,
LabID int
)

insert into Course
values
(210,'Plotting and Graph',20,30),
(211,'JAVA',21,31),
(212,'K-Mapping',22,32),
(213,'Basic Electrical',23,33),
(214,'Signal & System',24,34)

create table Teachers
(
TeacherID int,
TeacherName nvarchar(50)
)

insert into Teachers
values
(50,'Sumanta Karmakar'),
(51,'Soumen Sen'),
(52,'Rupam Singh'),
(53,'Dhiran Kumar'),
(54,'Avik Dey')

create table Students
(
StudentID int,
StudentName nvarchar(50)
)

insert into Students
values
(100,'Ayush Kumar'),
(101,'Anjali Kumari'),
(102,'Sushmita Sharma'),
(103,'Sagnik Salui'),
(104,'Anand Singh')

create table Theory
(
TheoryID int,
TheoryName nvarchar(50)
)

insert into Theory
values
(20,'Plotting'),
(21,'JAVA'),
(22,'K-Map'),
(23,'Basic Electrical'),
(24,'Systems')

create table Lab
(
LabID int,
LabName nvarchar(20)
)

insert into Lab
values
(30,'Graph'),
(31,'JAVA'),
(32,'K-Map'),
(33,'Voltage Measuring'),
(34,'Signals')

select * from Department
select * from Infrastructure
select * from Course
select * from Teachers
select * from Students
select * from Theory
select * from Lab

create Procedure SP_getDepartmentDetails
AS
Begin
select D.DeptID, D.DeptName,C.CourseName,T.TeacherName,S.StudentName
from Department D
join Course C on D.CourseID=C.courseID
join Teachers T on D.TeacherID=T.TeacherID
join Students S on D.StudentID=S.StudentID
End

exec SP_getDepartmentDetails

CREATE PROCEDURE SP_getCourseDetails
(
@CourseID int
)
AS
BEGIN
select D.DeptID, D.DeptName,C.CourseName,T.TeacherName,S.StudentName
from Department D
join Course C on D.CourseID=C.courseID
join Teachers T on D.TeacherID=T.TeacherID
join Students S on D.StudentID=S.StudentID
WHERE C.courseID=@CourseID

end

EXEC SP_getCourseDetails 211;


using System;
using System.Data;
using System.Data.SqlClient;


/*class SqlCommandDemo
{
SqlConnection conn;

public SqlCommandDemo() {
conn = new SqlConnection("Data Source=(localdb)MSSQLLocalDB;Initial Catalog=CollegeData;Integrated Security=SSPI");

}
static void Main()
{
SqlCommandDemo scd = new SqlCommandDemo();
Console.WriteLine("======================Before Inserting=======================");
scd.Readdata();
Console.WriteLine("======================After Inserting=======================");
scd.InsertData();
scd.Readdata();
Console.WriteLine("======================After Updating======================");
scd.UpdateData();
scd.Readdata();

Console.WriteLine("======================After Deleting======================");
scd.DeleteData();
scd.Readdata();

Console.WriteLine("======================Get Count======================");
Console.WriteLine(scd.GetCount());

Console.WriteLine("======================Order By ASC======================");
scd.OrderByAscData();

Console.WriteLine("======================Order By DESC======================");
scd.OrderByDescData();

Console.WriteLine("======================Group By======================");
scd.GroupByData();
}
public void Readdata()
{
SqlDataReader rdr = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select * from Department", conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr[1]+" " + rdr[2]);
}

}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
public void OrderByAscData()
{
SqlDataReader rdr = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select * from Department order by StudentID ASC", conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr[1] + " " + rdr[2]);
}

}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
public void OrderByDescData()
{
SqlDataReader rdr = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select * from Department order by StudentID DESC", conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr[1] + " " + rdr[2]);
}

}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
public void GroupByData()
{
SqlDataReader rdr = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select DeptName,SUM(CourseID) from Department group by DeptName", conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr[0] + " " + rdr[1]);
}

}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}


public void InsertData()
{
try
{

conn.Open();
string query = @"INSERT INTO Teachers(TeacherID,TeacherName)
values(55,'Amit Kumar')";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}

}
}
public void UpdateData()
{
try
{

conn.Open();
string query = @"UPDATE Teachers SET TeacherName='Rahul Kumar' WHERE TeacherName='Amit Kumar'";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}

}
}
public void DeleteData()
{
try
{

conn.Open();
string query = @"DELETE FROM Teachers WHERE TeacherName='Rahul Kumar'";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}

}
}
public int GetCount()
{
int count = -1;
try
{

conn.Open();
string query = @"SELECT count(*) from Course";
SqlCommand cmd = new SqlCommand(query, conn);
count = Convert.ToInt32(cmd.ExecuteScalar());
}
finally
{
if (conn != null)
{
conn.Close();
}

}
return count;
}
}


public void spCalling()
{
SqlDataReader rdr = null;
try {

conn.Open();
SqlCommand cmd = new SqlCommand("spDepartmentDetails", conn)
{
CommandType = CommandType.StoredProcedure
};
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr["DeptID"] + " ----%--- " + rdr["DeptName"]);
}
}
finally
{
if (rdr != null)
{
rdr.Close();
}

if (conn != null)
{
conn.Close();
}
}
}

public void spCalling1()
{
SqlDataReader rdr = null;

try
{
conn.Open();
SqlCommand cmd = new SqlCommand("spGetCourseDetails", conn)
{
CommandType = CommandType.StoredProcedure
};
SqlParameter depname = new SqlParameter
{
ParameterName = "@DeptID",
SqlDbType = SqlDbType.Int,
Value = "2",
Direction = ParameterDirection.Input
};
cmd.Parameters.Add(deptname);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr["CourseName"] + " -------- " + rdr["DeptID"]);
}
}

finally
{
if (rdr != null)
{
rdr.Close();
}

if (conn != null)
{
conn.Close();
}
}
}

public void spCalling2()
{
SqlDataReader rdr = null;

try
{
conn.Open();
SqlCommand cmd = new SqlCommand("spCourseOfDepartment", conn)
{
CommandType = CommandType.StoredProcedure
};
SqlParameter course = new SqlParameter
{
ParameterName = "@Course",
SqlDbType = SqlDbType.VarChar,
Size=50,
Value = "Communication Systems",
Direction = ParameterDirection.Input
};
SqlParameter depname = new SqlParameter
{
ParameterName = "@DeptName",
SqlDbType = SqlDbType.VarChar,
Size= 50,
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(course);
cmd.Parameters.Add(depname);
rdr = cmd.ExecuteReader();

Console.WriteLine(cmd.Parameters["@DeptName"].Value);

}

finally
{
if (rdr != null)
{
rdr.Close();
}

if (conn != null)
{
conn.Close();
}
}
}

public void spCalling3()
{
SqlDataReader rdr = null;

try
{
conn.Open();
SqlCommand cmd = new SqlCommand("spStudentinCourse", conn)
{
CommandType = CommandType.StoredProcedure
};
SqlParameter depname = new SqlParameter
{
ParameterName = "@StudentID",
SqlDbType = SqlDbType.Int,
Value = "3",
Direction = ParameterDirection.Input
};
SqlParameter returnval = new SqlParameter
{
ParameterName = "@Returnvaluee",
SqlDbType = SqlDbType.Int,

Direction = ParameterDirection.ReturnValue
};

cmd.Parameters.Add(depname);
cmd.Parameters.Add(returnval);
cmd.ExecuteNonQuery();
Console.WriteLine((int)cmd.Parameters["@Returnvaluee"].Value);
}

finally
{
if (rdr != null)
{
rdr.Close();
}

if (conn != null)
{
conn.Close();
}
}
}


}
}