SQL Tutorial 7 Group by Clause

Follow on Facebook:

Subscribe to our other channel:
Telusko Hindi :

[visheshpahuja]

Create and Insert Scripts, Good Luck :-

create table emp
(eno int,
ename varchar(40),
desg varchar(30),
sal int,
mgr int,
deptno int
);

create table dept
(
dname varchar(40),
loc varchar(30),
deptno int
);

insert into dept values ('sales', 'mumbai', 10);
insert into dept values ('hr', 'delhi', 20);
insert into dept values ('accounts', 'chennai', 30);
insert into dept values ('production', 'bangalore', 40);

insert into emp values (1, 'aaa', 'salesman', 7000, 2, 10);
insert into emp values (2, 'bbb', 'mgr', 17000, 3, 10);
insert into emp values (3, 'ccc', 'president', 40000, null, 30);
insert into emp values (4, 'ddd', 'clerk', 5000, 5, 20);
insert into emp values (5, 'eee', 'mgr', 20000, 3, 20);

[nileshpadmagiriwar]

I am using MySQL and queries are according to it...
1) create table emp and insert data into it-
CREATE TABLE emp
(
eno INT,
ename VARCHAR(10),
designation VARCHAR(20),
sal INT,
mgr INT,
deptno INT
);

INSERT INTO emp VALUES(1, 'aaa', 'salesman', 7000, 2, 10);

INSERT INTO emp VALUES(2, 'bbb', 'manager', 17000, 3, 10);

INSERT INTO emp VALUES(3, 'ccc', 'president', 40000, null, 30);

INSERT INTO emp VALUES(4, 'ddd', 'clerk', 5000, 5, 20);

INSERT INTO emp VALUES(5, 'eee', 'manager', 20000, 3, 20);

2) create table dept and insert data into it-
CREATE TABLE dept
(
deptno INT,
dname VARCHAR(20),
loc VARCHAR(20)
);

INSERT INTO dept VALUES (10, 'sales', 'mumbai');

INSERT INTO dept VALUES (20, 'hr', 'delhi');

INSERT INTO dept VALUES (30, 'accounts', 'chennai');

INSERT INTO dept VALUES (40, 'production', 'bengaluru');

[rajul]

Where are the SQL scripts in description ???

[bobin]

emp table
create table emp
(
eno number,
ename varchar2(5),
designation varchar2(10),
sal number,
mgr number,
deptno number
);

[mohammedsuhailbasha]

Very nice explaination sir thank you so much sir

[ankittiwary]

there is no description regarding sql scripts u r telling sir for emp and dept table

[AnitaPatole]

how to get correct answer for last query?

[calliart]

Good but lack of providing exact information

[ProfesorBruno]

I don't know if it's the simplest answer for the last query but it works:


select A.designation, count(B.eno)
from emp A
LEFT JOIN emp B
on A.eno=B.eno and B.sal>10000
group by A.designation

[AmitKumar-pwln]

where are the files, men ? didnt find any files

[balakumars]

CREATE TABLE emp
(
eno INT NOT NULL,
ename VARCHAR(20),
designation VARCHAR(20),
sal INT,
mgr VARCHAR(20),
deptno INT,
PRIMARY KEY(eno)
);

CREATE TABLE dept
(
deptno INT,
dname VARCHAR(20),
loc VARCHAR(20),
PRIMARY KEY(deptno)
);

ALTER TABLE emp
ADD CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno);

INSERT INTO dept
VALUES (10,
'sales',
'mumbai');

INSERT INTO dept
VALUES (20,
'hr',
'delhi');

INSERT INTO dept
VALUES (30,
'accounts',
'chennai');

INSERT INTO dept
VALUES (40,
'production',
'bengaluru');

INSERT INTO emp
VALUES (1,
'aaa',
'salesman',
7000,
'2',
10);

INSERT INTO emp
VALUES (2,
'bbb',
'manager',
17000,
'3',
10);

INSERT INTO emp
VALUES (3,
'ccc',
'president',
40000,
'',
30);

INSERT INTO emp
VALUES (4,
'ddd',
'clerk',
5000,
'5',
20);

INSERT INTO emp
VALUES (5,
'eee',
'manager',
20000,
'3',
20);