cms by valli, Sql Server

Language: Layout:

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'

create table dept_pro
(
dept_id numeric(1) primary key,
deptname char(50),
hod nvarchar(50)
);

insert into dept_pro values
(1,'Civil','Arun'),
(2, 'Electrical', 'Karpaga'),
(3,'Electronics','valli'),
(4, 'Computer', 'Saravana'),
(5, 'Mechanical', 'Kavin');

create table student_pro
(
rollno numeric(10) primary key,
sname varchar(25) not null,
dept_id numeric(1) not null foreign key references dbo.dept_pro(dept_id),
mobile nvarchar(13),
addr varchar(25) not null
);

insert into student_pro values
(101,'Siva',3,9678909867, 'chennai'),
(102,'deepa',2,9673409867, 'madurai'),
(103,'Anand',5,9988909867, 'chennai'),
(104,'arjun',4,9611409867, 'theni'),
(105,'deepak',1,9678999867, 'trichy'),
(106,'deepa',2,9677409867, 'vilupuram'),
(107,'Sangeetha',2,9777909867, 'chennai'),
(108,'rajesh',2,9673433367, 'karaikal'),
(109,'kalai',4,9674545867, 'chennai'),
(110,'selvi',1,9860409867, 'madurai');

select * from dept_pro;
select * from student_pro;

update student_pro set sname='leela' where rollno=106;

select * from student_pro;

delete from student_pro where rollno=109;

select * from student_pro;

create table student_marks
(
rollno numeric(10) not null foreign key references dbo.student_pro(rollno),
marks numeric(4) not null);

insert into student_marks values
(101,82),(102,84),(103,85),(104,86),(105,88),
(106,95),(107,98),(108,55),(110,50);

select * from student_marks;

create table emp_pro
(
empid numeric(10) primary key,
ename varchar(25) not null,
dept_id numeric(1) not null foreign key references dbo.dept_pro(dept_id));

insert into emp_pro values
(1001,'Mariappan',2),(1002,'Sankari', 2),(1003,'Nithya', 1),(1004,'Lavanya', 1),(1005,'Vidhanya', 3),(1006,'Suganya', 4),(1007,'Saranya', 5),(1008,'Suresh',2),
(1100,'Rakesh', 2);

select * from emp_pro;

select marks from student_marks inner join student_pro on student_marks.rollno=student_pro.rollno;

select marks from student_marks left join student_pro on student_marks.rollno=student_pro.rollno;

select marks from student_marks right join student_pro on student_marks.rollno=student_pro.rollno;

select marks from student_marks full join student_pro on student_marks.rollno=student_pro.rollno;

select marks,dept_id from student_marks inner join student_pro on student_marks.rollno=student_pro.rollno where marks>80 and dept_id=1;

select marks,dept_id from student_marks inner join student_pro on student_marks.rollno=student_pro.rollno where marks>80 and dept_id=2;

select marks,dept_id from student_marks inner join student_pro on student_marks.rollno=student_pro.rollno where marks>80 and dept_id=3;

select marks,dept_id from student_marks inner join student_pro on student_marks.rollno=student_pro.rollno where marks>80 and dept_id=4;

select marks,dept_id from student_marks inner join student_pro on student_marks.rollno=student_pro.rollno where marks>80 and dept_id=5;

select deptname from dept_pro inner join emp_pro on dept_pro.dept_id=emp_pro.dept_id 
group by dept_pro.deptname having count(*)>2;

View schema
Execution time: 0,12 sec, rows selected: 98, rows affected: 35, absolute service time: 0,33 sec, absolute service time: 0,35 sec