new, Sql Server - rextester

new

Language: Layout:

CREATE TABLE EMP (EMPNO INT, EMPNAME CHAR(20), JOB CHAR(20), HIREDATE CHAR(20), SAL FLOAT, COMM FLOAT, DEPT_NO INT);
INSERT INTO EMP (EMPNO,EMPNAME,JOB,HIREDATE,SAL,COMM,DEPT_NO)
VALUES (320,'SUMIT KUMAR','ANALYST','14-MARCH-2019',50000,600,48),
(322,'GITESH KUMAR','ASSISTANT MANAGER','14-APRIL-2019',40000,400,45),
(305,'MUKESH KUMAR','PRESIDENT','10-MARCH-2010',100000,6008,01),
(323,'HARSH','ANALYST','14-OCTOBER-2017',45000,1500,09),
(329,'HARSHIT','ASSOCIATE ANALYST','14-MARCH-2019',40000,900,89),
(31,'ARVIND','HR','14-JUNE-2010',49000,2000,51),
(309,'SAJAL','SENIOR ANALYST','14-NOVEMBER-2009',40000,900,69);
INSERT INTO EMP (EMPNO,EMPNAME,JOB,SAL)
VALUES (325,'NAVEED','CLERK',50000);
create table emp2 (address varchar (100) , age int , empno int );
insert into emp2 values ( 'mrt' ,25 , 323 ),
('gzb' , 24 , 31),
('delhi', 25 , 329),
('meerut', 27, 309);
select * from emp2;
select * from emp;
select top 3 sal from emp;
select * from emp where job= 'analyst' and sal = (select max (sal) from emp where job = 'analyst');
select * from emp where empno%2=1;
 select * from emp where sal <=50000 and (job= 'analyst' or job ='HR');
 select empname, charindex(' ', empname)  from emp;
 
 select* from emp where len( empname) <=5 and empname like 'h%';
alter table emp add phoneno bigint;
update emp set phoneno=8755228333 where empno= 320;

update emp set empname ='tarun' where empno=320 or empno=305;
select * from emp;
select max(sal) from emp;
select max(sal) from emp where sal < ( select max(sal) from emp);
select * from emp where sal =(select max(sal) from emp where sal < ( select max(sal) from emp));

select * from emp where job!='hr' and job!= 'analyst';

View schema
Execution time: 0,05 sec, rows selected: 51, rows affected: 15, absolute service time: 0,23 sec, absolute service time: 0,24 sec

	address	age	empno
1	mrt	25	323
2	gzb	24	31
3	delhi	25	329
4	meerut	27	309

	EMPNO	EMPNAME	JOB	HIREDATE	SAL	COMM	DEPT_NO
1	320	SUMIT KUMAR	ANALYST	14-MARCH-2019	50000	600	48
2	322	GITESH KUMAR	ASSISTANT MANAGER	14-APRIL-2019	40000	400	45
3	305	MUKESH KUMAR	PRESIDENT	10-MARCH-2010	100000	6008	1
4	323	HARSH	ANALYST	14-OCTOBER-2017	45000	1500	9
5	329	HARSHIT	ASSOCIATE ANALYST	14-MARCH-2019	40000	900	89
6	31	ARVIND	HR	14-JUNE-2010	49000	2000	51
7	309	SAJAL	SENIOR ANALYST	14-NOVEMBER-2009	40000	900	69
8	325	NAVEED	CLERK	NULL	50000	NULL	NULL

	sal
1	50000
2	40000
3	100000

	EMPNO	EMPNAME	JOB	HIREDATE	SAL	COMM	DEPT_NO
1	320	SUMIT KUMAR	ANALYST	14-MARCH-2019	50000	600	48

	EMPNO	EMPNAME	JOB	HIREDATE	SAL	COMM	DEPT_NO
1	305	MUKESH KUMAR	PRESIDENT	10-MARCH-2010	100000	6008	1
2	323	HARSH	ANALYST	14-OCTOBER-2017	45000	1500	9
3	329	HARSHIT	ASSOCIATE ANALYST	14-MARCH-2019	40000	900	89
4	31	ARVIND	HR	14-JUNE-2010	49000	2000	51
5	309	SAJAL	SENIOR ANALYST	14-NOVEMBER-2009	40000	900	69
6	325	NAVEED	CLERK	NULL	50000	NULL	NULL

	EMPNO	EMPNAME	JOB	HIREDATE	SAL	COMM	DEPT_NO
1	320	SUMIT KUMAR	ANALYST	14-MARCH-2019	50000	600	48
2	323	HARSH	ANALYST	14-OCTOBER-2017	45000	1500	9
3	31	ARVIND	HR	14-JUNE-2010	49000	2000	51

	empname	(No column name)
1	SUMIT KUMAR	6
2	GITESH KUMAR	7
3	MUKESH KUMAR	7
4	HARSH	6
5	HARSHIT	8
6	ARVIND	7
7	SAJAL	6
8	NAVEED	7

	EMPNO	EMPNAME	JOB	HIREDATE	SAL	COMM	DEPT_NO
1	323	HARSH	ANALYST	14-OCTOBER-2017	45000	1500	9

	EMPNO	EMPNAME	JOB	HIREDATE	SAL	COMM	DEPT_NO	phoneno
1	320	tarun	ANALYST	14-MARCH-2019	50000	600	48	8755228333
2	322	GITESH KUMAR	ASSISTANT MANAGER	14-APRIL-2019	40000	400	45	NULL
3	305	tarun	PRESIDENT	10-MARCH-2010	100000	6008	1	NULL
4	323	HARSH	ANALYST	14-OCTOBER-2017	45000	1500	9	NULL
5	329	HARSHIT	ASSOCIATE ANALYST	14-MARCH-2019	40000	900	89	NULL
6	31	ARVIND	HR	14-JUNE-2010	49000	2000	51	NULL
7	309	SAJAL	SENIOR ANALYST	14-NOVEMBER-2009	40000	900	69	NULL
8	325	NAVEED	CLERK	NULL	50000	NULL	NULL	NULL

	(No column name)
1	100000

	(No column name)
1	50000

	EMPNO	EMPNAME	JOB	HIREDATE	SAL	COMM	DEPT_NO	phoneno
1	320	tarun	ANALYST	14-MARCH-2019	50000	600	48	8755228333
2	325	NAVEED	CLERK	NULL	50000	NULL	NULL	NULL

	EMPNO	EMPNAME	JOB	HIREDATE	SAL	COMM	DEPT_NO	phoneno
1	322	GITESH KUMAR	ASSISTANT MANAGER	14-APRIL-2019	40000	400	45	NULL
2	305	tarun	PRESIDENT	10-MARCH-2010	100000	6008	1	NULL
3	329	HARSHIT	ASSOCIATE ANALYST	14-MARCH-2019	40000	900	89	NULL
4	309	SAJAL	SENIOR ANALYST	14-NOVEMBER-2009	40000	900	69	NULL
5	325	NAVEED	CLERK	NULL	50000	NULL	NULL	NULL