GKM_JOIN, Sql Server

GKM_JOIN

Language: Layout:

create table student(s_id int primary key, 
                      s_name varchar(20));
insert into student values(1, 'Jack');
insert into student values(2, 'Rithvik');
insert into student values(3, 'Jaspreet');
insert into student values(4, 'Praveen');
insert into student values(5, 'Bisa');
insert into student values(6, 'Suraj');
create table marks(school_id int primary key, s_id int, 
                       score int, status varchar(20));
insert into marks values(1004, 1, 23, 'fail');
insert into marks values(1008, 6, 95, 'pass');
insert into marks values(1012, 2, 97, 'pass');
insert into marks values(1016, 7, 67, 'pass'); 
insert into marks values(1020, 3, 100, 'pass');
insert into marks values(1025, 8, 73, 'pass');
insert into marks values(1030, 4, 88, 'pass');
insert into marks values(1035, 9,  13, 'fail');
insert into marks values(1040, 5,  16, 'fail');
insert into marks values(1050, 10, 53, 'pass');                  
create table details(address_city varchar(20), email_ID varchar(20), 
                      school_id int, accomplishments varchar(50));
insert into details values('Banglore',  'jsingh@geeks.com', 
                                1020, 'ACM ICPC selected');
insert into details values('Hyderabad', 'praveen@geeks.com', 
                                1030, 'Geek of the month');
insert into details values('Delhi',     'rithvik@geeks.com', 
                                    1012, 'IOI finalist');
insert into details values('Chennai',   'om@geeks.com', 
                                 1111, 'Geek of the year');
insert into details values('Banglore', ' suraj@geeks.com',
                                 1008, 'IMO finalist');
insert into details values('Mumbai',    'sasukeh@geeks.com',
                                  2211, 'Made a robot');
insert into details values('Ahmedabad', 'itachi@geeks.com',
                               1172, 'Code Jam finalist');
insert into details values('Jaipur',    'kumar@geeks.com',
                                   1972, 'KVPY finalist');
select *from student;
select *from marks;
select *from details;
select s_name, score, status, address_city, accomplishments, email_ID from student inner join marks on student.s_id = marks.s_id inner join details on marks.school_id=details.school_id;

select s_name,school_id,score,status from student s join marks m on s.s_id=m.s_id where m.status = 'pass' order by s.s_id;

select *from student s right join marks m on s.s_id=m.s_id order by m.s_id;

select *from student, marks;

select *from details;

select address_city, count(*) as duplicat from details group by address_city HAVING count(*) > 1;

select s_id from student union select s_id from marks;

select *from student s full join marks m on s.s_id=m.s_id full join details d on m.school_id=d.school_id where m.status='pass';

View schema
Execution time: 0,05 sec, rows selected: 128, rows affected: 24, absolute service time: 0,28 sec, absolute service time: 0,3 sec

	s_id	s_name
1	1	Jack
2	2	Rithvik
3	3	Jaspreet
4	4	Praveen
5	5	Bisa
6	6	Suraj

	school_id	s_id	score	status
1	1004	1	23	fail
2	1008	6	95	pass
3	1012	2	97	pass
4	1016	7	67	pass
5	1020	3	100	pass
6	1025	8	73	pass
7	1030	4	88	pass
8	1035	9	13	fail
9	1040	5	16	fail
10	1050	10	53	pass

	address_city	email_ID	school_id	accomplishments
1	Banglore	[email protected]	1020	ACM ICPC selected
2	Hyderabad	[email protected]	1030	Geek of the month
3	Delhi	[email protected]	1012	IOI finalist
4	Chennai	[email protected]	1111	Geek of the year
5	Banglore	[email protected]	1008	IMO finalist
6	Mumbai	[email protected]	2211	Made a robot
7	Ahmedabad	[email protected]	1172	Code Jam finalist
8	Jaipur	[email protected]	1972	KVPY finalist

	s_name	score	status	address_city	accomplishments	email_ID
1	Jaspreet	100	pass	Banglore	ACM ICPC selected	[email protected]
2	Praveen	88	pass	Hyderabad	Geek of the month	[email protected]
3	Rithvik	97	pass	Delhi	IOI finalist	[email protected]
4	Suraj	95	pass	Banglore	IMO finalist	[email protected]

	s_name	school_id	score	status
1	Rithvik	1012	97	pass
2	Jaspreet	1020	100	pass
3	Praveen	1030	88	pass
4	Suraj	1008	95	pass

	s_id	s_name	school_id	s_id	score	status
1	1	Jack	1004	1	23	fail
2	2	Rithvik	1012	2	97	pass
3	3	Jaspreet	1020	3	100	pass
4	4	Praveen	1030	4	88	pass
5	5	Bisa	1040	5	16	fail
6	6	Suraj	1008	6	95	pass
7	NULL	NULL	1016	7	67	pass
8	NULL	NULL	1025	8	73	pass
9	NULL	NULL	1035	9	13	fail
10	NULL	NULL	1050	10	53	pass

	s_id	s_name	school_id	s_id	score	status
1	1	Jack	1004	1	23	fail
2	1	Jack	1008	6	95	pass
3	1	Jack	1012	2	97	pass
4	1	Jack	1016	7	67	pass
5	1	Jack	1020	3	100	pass
6	1	Jack	1025	8	73	pass
7	1	Jack	1030	4	88	pass
8	1	Jack	1035	9	13	fail
9	1	Jack	1040	5	16	fail
10	1	Jack	1050	10	53	pass
11	2	Rithvik	1004	1	23	fail
12	2	Rithvik	1008	6	95	pass
13	2	Rithvik	1012	2	97	pass
14	2	Rithvik	1016	7	67	pass
15	2	Rithvik	1020	3	100	pass
16	2	Rithvik	1025	8	73	pass
17	2	Rithvik	1030	4	88	pass
18	2	Rithvik	1035	9	13	fail
19	2	Rithvik	1040	5	16	fail
20	2	Rithvik	1050	10	53	pass
21	3	Jaspreet	1004	1	23	fail
22	3	Jaspreet	1008	6	95	pass
23	3	Jaspreet	1012	2	97	pass
24	3	Jaspreet	1016	7	67	pass
25	3	Jaspreet	1020	3	100	pass
26	3	Jaspreet	1025	8	73	pass
27	3	Jaspreet	1030	4	88	pass
28	3	Jaspreet	1035	9	13	fail
29	3	Jaspreet	1040	5	16	fail
30	3	Jaspreet	1050	10	53	pass
31	4	Praveen	1004	1	23	fail
32	4	Praveen	1008	6	95	pass
33	4	Praveen	1012	2	97	pass
34	4	Praveen	1016	7	67	pass
35	4	Praveen	1020	3	100	pass
36	4	Praveen	1025	8	73	pass
37	4	Praveen	1030	4	88	pass
38	4	Praveen	1035	9	13	fail
39	4	Praveen	1040	5	16	fail
40	4	Praveen	1050	10	53	pass
41	5	Bisa	1004	1	23	fail
42	5	Bisa	1008	6	95	pass
43	5	Bisa	1012	2	97	pass
44	5	Bisa	1016	7	67	pass
45	5	Bisa	1020	3	100	pass
46	5	Bisa	1025	8	73	pass
47	5	Bisa	1030	4	88	pass
48	5	Bisa	1035	9	13	fail
49	5	Bisa	1040	5	16	fail
50	5	Bisa	1050	10	53	pass
51	6	Suraj	1004	1	23	fail
52	6	Suraj	1008	6	95	pass
53	6	Suraj	1012	2	97	pass
54	6	Suraj	1016	7	67	pass
55	6	Suraj	1020	3	100	pass
56	6	Suraj	1025	8	73	pass
57	6	Suraj	1030	4	88	pass
58	6	Suraj	1035	9	13	fail
59	6	Suraj	1040	5	16	fail
60	6	Suraj	1050	10	53	pass

	address_city	email_ID	school_id	accomplishments
1	Banglore	[email protected]	1020	ACM ICPC selected
2	Hyderabad	[email protected]	1030	Geek of the month
3	Delhi	[email protected]	1012	IOI finalist
4	Chennai	[email protected]	1111	Geek of the year
5	Banglore	[email protected]	1008	IMO finalist
6	Mumbai	[email protected]	2211	Made a robot
7	Ahmedabad	[email protected]	1172	Code Jam finalist
8	Jaipur	[email protected]	1972	KVPY finalist

	address_city	duplicat
1	Banglore	2

	s_id
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10

	s_id	s_name	school_id	s_id	score	status	address_city	email_ID	school_id	accomplishments
1	6	Suraj	1008	6	95	pass	Banglore	[email protected]	1008	IMO finalist
2	2	Rithvik	1012	2	97	pass	Delhi	[email protected]	1012	IOI finalist
3	NULL	NULL	1016	7	67	pass	NULL	NULL	NULL	NULL
4	3	Jaspreet	1020	3	100	pass	Banglore	[email protected]	1020	ACM ICPC selected
5	NULL	NULL	1025	8	73	pass	NULL	NULL	NULL	NULL
6	4	Praveen	1030	4	88	pass	Hyderabad	[email protected]	1030	Geek of the month
7	NULL	NULL	1050	10	53	pass	NULL	NULL	NULL	NULL