EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
SQL QUERY PRACTICE
create database ankit ;
use ankit ;
create table dept(deptno int primary key, dname varchar(15) not null, loc varchar(20)) ;
###################
create table emp (
empno int(4) primary key,
ename varchar(10),
job varchar(9), mgr int(4),
hiredate date,
sal float(7,2),
comm float(7,2) ,
deptno int(4),
foreign key (deptno) references dept(deptno)
);
###############################
insert into dept(deptno, dname, loc) values(10, 'accounting','newyork') ;
insert into dept(deptno, dname, loc) values(20, 'research','chicago') ;
insert into dept(dname, deptno,loc) values('chicago', 30, 'sales') ;
insert into dept values(40, 'operations','boston') ;
SHOW DATABASES;
DESCIRBE EMP;
DESC dept;
SHOW TABLES ;
DESCRIBE DEPT ;
insert into emp values(7839, 'king', 'president', NULL, '1981-11-17' , 5000, NULL, 10);
insert into emp values( 7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30 );
insert into emp values( 7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10 );
insert into emp values( 7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20 );
insert into emp values( 7788, 'SCOTT', 'ANALYST', 7566, '1987-07-13', 3000, null, 20 );
insert into emp values( 7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20 );
insert into emp values( 7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20 );
insert into emp values( 7499, 'ALLEN', 'SALESMAN', 7698,'1981-02-20', 1600, 300, 30 );
insert into emp values( 7521, 'WARD', 'SALESMAN', 7698, '1981-2-22', 1250, 500, 30 );
insert into emp values( 7654, 'MARTIN', 'SALESMAN', 7698,'1981-09-28',1250, 1400, 30);
insert into emp values( 7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08',1500, 0, 30);
insert into emp values( 7876, 'ADAMS', 'CLERK', 7788, '1981-07-13', 1100, null, 20);
insert into emp values( 7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp values( 7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
insert into emp values(101, 'ritesh', 'clerk', 4555, '2025-10-07', 5000, NULL, 20) ;
update emp set job='jsa', sal = 50000 where job='clerk' ;
update emp set job='jsa', sal = 50000 ; --------------------Very Dangerous
delete from emp where empno <105 ;
delete from emp; --------------------Very Dangerous
delete from emp where job='clerk ' ; --------------------dangerous
delete from emp where empno=7934 ;
SELECT ENAME, MGR, HIREDATE FROM EMP;
SELECT MGR, HIREDATE , ename "name of employee" FROM EMP;
SELECT ENAME, SAL, COMM FROM EMP;
SELECT * FROM EMP;
SELECT * FROM EMP WHERE JOB='SALESMAN' ;
SELECT JOB, ENAME FROM EMP WHERE JOB='SALESMAN' ORDER BY ENAME asc;
SELECT JOB, ENAME FROM EMP WHERE JOB='SALESMAN' ORDER BY ENAME DESC ;
SELECT JOB, ENAME, HIREDATE FROM EMP WHERE HIREDATE >'1981-02-20' ORDER BY HIREDATE ASC;
SELECT JOB, ENAME, HIREDATE FROM EMP WHERE HIREDATE <'1981-02-20 ';
SELECT JOB, ENAME, HIREDATE FROM EMP WHERE year(HIREDATE) ='1981' ORDER BY HIREDATE ASC;
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB='SALESMAN' AND SAL<3000 ORDER BY sal ;
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB='SALESMAN' AND SAL<3000 ORDER BY SAL;
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB='SALESMAN' AND SAL<3000 ORDER BY SAL ASC;
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB='SALESMAN' AND SAL<3000 ORDER BY SAL DESC;
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB='SALESMAN' OR SAL<3000 DESC;
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB='SALESMAN' OR SAL<3000 ASC;
SELECT ENAME, SAL FROM EMP WHERE JOB='SALESMAN' OR JOB='JSA' OR JOB='MANAGER' ;
SELECT ENAME, SAL FROM EMP WHERE JOB IN ('SALESMAN', 'MANAGER' , 'JSA') ;
SELECT ENAME, SAL FROM EMP WHERE JOB NOT IN ('SALESMAN', 'MANAGER' , 'JSA') ;
SELECT ENAME, JOB, SAL FROM EMP WHERE SAL>1000 AND SAL <2000 ORDER BY SAL ;
SELECT ENAME, JOB, SAL FROM EMP WHERE SAL>=1000 AND SAL <=3000 ORDER BY SAL ASC;
SELECT ENAME, JOB, SAL FROM EMP WHERE SAL BETWEEN 1000 AND 3000 ORDER BY SAL;
SELECT ENAME, JOB, SAL FROM EMP WHERE SAL NOT BETWEEN 1000 AND 3000 ORDER BY SAL DESC;
SELECT ENAME, JOB, SAL FROM EMP WHERE SAL BETWEEN 1000 AND 3000 ORDER BY SAL;
SELECT JOB, ENAME FROM EMP WHERE ENAME LIKE 'M%' ;
SELECT JOB, ENAME FROM EMP WHERE ENAME LIKE '%M' ;
SELECT JOB, ENAME FROM EMP WHERE ENAME LIKE '%M%' ;
SELECT JOB, ENAME FROM EMP WHERE ENAME LIKE 'A____' ;
SELECT JOB, ENAME FROM EMP WHERE ENAME LIKE '_A%' ;
SELECT JOB, ENAME FROM EMP WHERE ENAME LIKE '_A_%' ;
SELECT JOB, ENAME FROM EMP WHERE ENAME LIKE '__A_%' ;
select ename, comm from emp where comm=NULL ;
select ename, comm from emp where comm IS NULL ;
select count(comm) from emp where comm IS NULL ;
SELECT ALL JOB FROM EMP;
SELECT JOB FROM EMP;
SELECT DISTINCT JOB FROM EMP;
##############################################
SELECT COUNT(JOB) FROM EMP;
SELECT COUNT(ALL JOB) FROM EMP;
SELECT COUNT(DISTINCT JOB) FROM EMP;
SELECT COUNT(*) FROM EMP;
SELECT COUNT(COMM) FROM EMP;
SELECT COUNT(JOB) FROM EMP;
SELECT COUNT(JOB) FROM EMP WHERE JOB='SALESMAN';
SELECT COUNT(JOB) FROM EMP WHERE JOB='JSA';
SELECT COUNT(JOB) FROM EMP WHERE JOB='MANAGER';
SELECT COUNT(*) FROM EMP ;
SELECT JOB, COUNT(JOB) FROM EMP WHERE JOB='JSA';
SELECT JOB, COUNT(JOB) FROM EMP GROUP BY JOB ;
SELECT JOB, COUNT(JOB) FROM EMP GROUP BY JOB HAVING JOB='JSA';
SELECT JOB, COUNT(JOB) FROM EMP GROUP BY JOB HAVING COUNT(JOB) >2;
SELECT sum(sal) FROM EMP ;
SELECT max(sal) FROM EMP ;
SELECT min(sal) FROM EMP WHERE JOB='clerk';
SELECT avg(sal) FROM EMP group by job;
SELECT job, avg(sal) FROM EMP group by job;
SELECT job, sum(sal) FROM EMP GROUP BY JOB ;
SELECT JOB, min(sal) FROM EMP GROUP BY Job;
SELECT JOB, min(sal), max(sal), avg(sal), count(sal) FROM EMP GROUP BY Job;
SELECT JOB, COUNT(JOB) FROM EMP GROUP BY JOB HAVING COUNT(JOB) >2;
alter table emp add column bonus int ;
alter table emp modify column job varchar(15) ;
alter table emp drop column job ;
drop table emp;
# joining two tables
select ename, deptno, dname from emp, dept ; ########### error
select ename, [Link], dname from emp, dept ; Cartesian product
select [Link], [Link], dname from emp e, dept d; # Cartesian product
select [Link], [Link], dname from emp e, dept d # equi join
where [Link] = [Link];
SELECT ename,dname FROM emp NATURAL JOIN dept ;
# extra work other than syllabus
SELECT ename,dname FROM emp JOIN dept ON [Link] = [Link]; # join
SELECT ename,dname FROM emp INNER JOIN dept ON [Link] = [Link]; # EQUI join
SELECT ename,dname FROM emp LEFT JOIN dept ON [Link] = [Link]; # LEFT join
SELECT ename,dname FROM emp RIGHT JOIN dept ON [Link] = [Link]; # RIGTH join
SELECT ename,dname FROM emp e RIGHT JOIN dept d ON [Link] ; # RIGTH join