Notes

create database College

create table Department(dept_id int, dept_name nvarchar(20))
Insert into Department values (1, 'HR')
Insert into Department values (2, 'IT')
Insert into Department values (3, 'ServiceNow')
Insert into Department values (4, 'Training')
Insert into Department values (5, 'Management')

SElect * from Department

create table Course(course_id int, course_name nvarchar(20))
Insert into Course values(101, 'Physics')
Insert into Course values(102, 'Chemistry')
Insert into Course values(103, 'Maths')
Insert into Course values(104, 'Bio')
Insert into Course values(105, 'Physics')

SElect * from Course


create table Teacher(teacher_id int, name nvarchar(20), dept_id int)

Insert into Teacher values(201, 'Ram', 4)
Insert into Teacher values(202, 'Shyam', 3)
Insert into Teacher values(203, 'Sita', 5)
Insert into Teacher values(204, 'Riya', 1)
Insert into Teacher values(205, 'Raman', 2)

Select * from Teacher

create table Students(student_id int, name nvarchar(20), gender nvarchar(10), course_id int)

Insert into Students values(1001, 'Shreya', 'Female', 105)
Insert into Students values(1002, 'Rohan', 'Male', 103)
Insert into Students values(1003, 'Sneha', 'Female', 101)
Insert into Students values(1004, 'Rohit', 'Male', 104)
Insert into Students values(1005, 'Adam', 'Male', 102)

Select * from Students

create table Theory(id int, name nvarchar(15), student_id int, teacher_id int)

Insert into Theory values(601, 'MWE', 1003, 202)
Insert into Theory values(602, 'BEE', 1001, 204)
Insert into Theory values(603, 'EDC', 1004, 205)
Insert into Theory values(604, 'MME', 1002, 201)
Insert into Theory values(605, 'AEC', 1005, 203)

Select * from Theory

create table Labs(id_lab int, name nvarchar(15), student_id int, teacher_id int)

Insert into Labs values(901, 'bio lab', 1002, 203)
Insert into Labs values(902, 'chem lab', 1003, 201)
Insert into Labs values(903, 'phy lab', 1002, 205)
Insert into Labs values(904, 'comp lab', 1004, 202)
Insert into Labs values(905, 'geo lab', 1005, 204)

Select * from Labs

create table Infrastructures(name nvarchar(20), numbers int)

Insert into Infrastructures values('Funnel', 100)
Insert into Infrastructures values('Test tube', 120)
Insert into Infrastructures values('Compputers ', 200)
Insert into Infrastructures values('Measuring Rod', 50)

Select * from Infrastructures

CREATE PROCEDURE spstu
AS BEGIN
SELECT s.student_id, s.name, s.gender, s.course_id ,c.course_name
FROM Course c
full join Students s
ON s.course_id=c.course_id
END;
EXEC spstu;

CREATE PROCEDURE spteacher
@new int
AS BEGIN
SELECT * from Teacher t JOIN Department d ON t.dept_id=d.dept_id where d.dept_id = @new;
END
EXEC spteacher 4;

using Microsoft.Data.SqlClient;
using static Azure.Core.HttpHeader;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.Intrinsics.Arm;
using System.Text;
using System.Threading.Tasks;
********************************###########################******************************************
namespace ConsoleApp4
{
internal class SQLCommand
{
SqlConnection conn;
string connectionString = "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=College;Integrated Security=SSPI";
public SQLCommand()
{
conn = new SqlConnection(connectionString);
}
static void Main()
{
SQLCommand sQLCommand = new SQLCommand();
Console.WriteLine("======================Before Inserting=======================");
sQLCommand.Readdata();
Console.WriteLine("======================After Inserting=======================");
sQLCommand.InsertData();
sQLCommand.Readdata();
Console.WriteLine("======================After Updating======================");
sQLCommand.UpdateData();
sQLCommand.Readdata();

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

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


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

}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
public void InsertData()
{
try
{

conn.Open();
string query = @"INSERT INTO Students(student_id,name, gender, course_id)
values(1006,'Kriti', 'Female', 103)";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}

}
}
public void UpdateData()
{
try
{

conn.Open();
string query = @"UPDATE Students SET name='Roma' WHERE name='Sneha'";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}

}
}
public void DeleteData()
{
try
{

conn.Open();
string query = @"DELETE FROM Students WHERE name='Rohit'";
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 Students";
SqlCommand cmd = new SqlCommand(query, conn);
count = Convert.ToInt32(cmd.ExecuteScalar());
}
finally
{
if (conn != null)
{
conn.Close();
}

}
return count;
}
}
}