Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
db6
--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.dept_id having count (*)>2;
run
|
edit
|
history
|
help
0
Database ownership
student table create
bc160401693
TEST 1
product_sales
Mywall
SQL_Joins_RankingFunctions
comparison and logical
sadasd
Arif