NULL FIRST



----CREATE TABLE TEST
DROP TABLE test;
create table test (id int,name char(10),score char(10))
----INSERT INTO TEST
INSERT INTO test VALUES (1,'LILY',90);
INSERT INTO test VALUES (2,'LUCY',91);
INSERT INTO test VALUES (3,'TOM',92);
INSERT INTO test VALUES (4,'JIM',NULL);
INSERT INTO test VALUES (5,'AO',NULL);
INSERT INTO test VALUES (2,'LUCY',91);
INSERT INTO test VALUES (5,'AO',NULL);
----IN OR EXIST
SELECT * FROM test WHERE score  IN (SELECT DISTINCT score FROM TEST);
SELECT * FROM test A WHERE  EXISTS (SELECT 1 FROM test B WHERE A.score=B.score);
---- NOT IN OR NOT EXISTS
SELECT * FROM test WHERE score NOT IN (SELECT DISTINCT score FROM TEST);
SELECT * FROM test A WHERE NOT EXISTS (SELECT 1 FROM test B WHERE A.score=B.score);
---- DIFFRENCE BETWEEN (SELECT *) AND  (SELECT 1)
SELECT * FROM test;
SELECT 1 FROM test WHERE TEST.score<99;
SELECT COUNT(*) FROM test WHERE  TEST.score IS NULL;
SELECT COUNT(1) FROM test WHERE TEST.score IS NULL;
SELECT COUNT(*) FROM test WHERE  TEST.score=NULL;
SELECT COUNT(1) FROM test WHERE TEST.score=NULL;

drop index pk_dept;
SELECT * FROM test;
select * from test order by score desc
-----value 9999 is just a  example which is greater than max value
select * from test order by (case when score>0 then score else 93 end ) desc ;
insert into test values ('8','lj','998')
select * from test order by (case when score IS null  then 999 else score end ) desc ;

============*Q1:ORACLE*===============
DROP TABLE employee;
CREATE TABLE employee(
empno INT
CONSTRAINT PK_employee PRIMARY KEY,
deptno NUMBER NULL,
empname VARCHAR(30) NOT NULL,
CONSTRAINT UN1EMP UNIQUE(deptno,empname)
);

insert into employee values (1,null,'shangsan');
insert into employee values (2,10,'shangsan');
insert into employee values (3,20,'shangsan');
insert into employee values (5,40,'shangsan');
insert into employee values (6,50,'shangsan');

SELECT * FROM
(SELECT deptno FROM employee ORDER BY deptno ) WHERE ROWNUM<=3;
SELECT * FROM
(SELECT deptno FROM employee ORDER BY deptno NULLS FIRST) WHERE ROWNUM<=3;

=============*Q1:MSSQL*===============
----CREATE TABLE TEST
DROP TABLE test;
create table test (id int,name char(10),score char(10))
----INSERT INTO TEST
INSERT INTO test VALUES (1,'LILY',90);
INSERT INTO test VALUES (2,'LUCY',91);
INSERT INTO test VALUES (3,'TOM',92);
INSERT INTO test VALUES (4,'JIM',NULL);
INSERT INTO test VALUES (5,'AO',NULL);
INSERT INTO test VALUES (2,'LUCY',91);
INSERT INTO test VALUES (5,'AO',NULL);
----IN OR EXIST
SELECT * FROM test WHERE score  IN (SELECT DISTINCT score FROM TEST);
SELECT * FROM test A WHERE  EXISTS (SELECT 1 FROM test B WHERE A.score=B.score);
---- NOT IN OR NOT EXISTS
SELECT * FROM test WHERE score NOT IN (SELECT DISTINCT score FROM TEST);
SELECT * FROM test A WHERE NOT EXISTS (SELECT 1 FROM test B WHERE A.score=B.score);
---- DIFFRENCE BETWEEN (SELECT *) AND  (SELECT 1)
SELECT * FROM test;
SELECT 1 FROM test WHERE TEST.score<99;
SELECT COUNT(*) FROM test WHERE  TEST.score IS NULL;
SELECT COUNT(1) FROM test WHERE TEST.score IS NULL;
SELECT COUNT(*) FROM test WHERE  TEST.score=NULL;
SELECT COUNT(1) FROM test WHERE TEST.score=NULL;

drop index pk_dept;
SELECT * FROM test;
select * from test order by score desc
-----value 9999 is just a  example which is greater than max value
select * from test order by (case when score>0 then score else 93 end ) desc ;
insert into test values ('8','lj','998')
select * from test order by (case when score IS null  then 999 else score end ) desc ;

============*Q2:ORACLE*===============
DROP TABLE employee;
CREATE TABLE employee(
empno INT
CONSTRAINT PK_employee PRIMARY KEY,
deptno NUMBER NULL,
empname VARCHAR(30) NOT NULL,
CONSTRAINT UN1EMP UNIQUE(deptno,empname)
);

insert into employee values (1,null,'shangsan');
insert into employee values (2,10,'shangsan');
insert into employee values (3,20,'shangsan');
insert into employee values (5,40,'shangsan');
insert into employee values (6,50,'shangsan');

CREATE TABLE employee_1(
empno INT
CONSTRAINT PK_employee_1 PRIMARY KEY,
deptno NUMBER NULL,
empname VARCHAR(30) NOT NULL,
CONSTRAINT UN1EMP_1 UNIQUE(deptno,empname)
);

insert into employee_1 values (1,null,'shangsan');
insert into employee_1 values (2,10,'shangsan');
insert into employee_1 values (3,20,'shangsan');
insert into employee_1 values (9,90,'s9n');
COMMIT;

SELECT * FROM EMPLOYEE E LEFT JOIN EMPLOYEE_1 E1 ON E1.EMPNO = E.EMPNO AND E.EMPNO =2;
SELECT * FROM EMPLOYEE E LEFT JOIN EMPLOYEE_1 E1 ON E1.EMPNO = E.EMPNO  WHERE E.EMPNO=2 ;
SELECT * FROM EMPLOYEE E right JOIN EMPLOYEE_1 E1 ON E1.EMPNO = E.EMPNO AND E.EMPNO =2;
SELECT * FROM EMPLOYEE E right JOIN EMPLOYEE_1 E1 ON E1.EMPNO = E.EMPNO  WHERE E.EMPNO=2 ;
SELECT * FROM EMPLOYEE E inner JOIN EMPLOYEE_1 E1 ON E1.EMPNO = E.EMPNO AND E.EMPNO =2;
SELECT * FROM EMPLOYEE E inner JOIN EMPLOYEE_1 E1 ON E1.EMPNO = E.EMPNO  WHERE E.EMPNO=2 ;

=============*Q2:MSSQL*===============
DROP TABLE employee;
CREATE TABLE employee(
empno INT
CONSTRAINT PK_employee PRIMARY KEY,
deptno int NULL,
empname CHAR(30) NOT NULL,
CONSTRAINT UN1EMP UNIQUE(deptno,empname)
);

insert into employee values (1,null,'shangsan');
insert into employee values (2,10,'shangsan');
insert into employee values (3,20,'shangsan');
insert into employee values (5,40,'shangsan');
insert into employee values (6,50,'shangsan');

SELECT * FROM
(SELECT deptno FROM employee ORDER BY deptno ) WHERE ROWNUM<=3;
SELECT * FROM
(SELECT deptno FROM employee ORDER BY deptno NULLS FIRST) WHERE ROWNUM<=3;

CREATE TABLE employee_1(
empno INT
CONSTRAINT PK_employee_1 PRIMARY KEY,
deptno int NULL,
empname CHAR(30) NOT NULL,
CONSTRAINT UN1EMP_1 UNIQUE(deptno,empname)
);

insert into employee_1 values (1,null,'shangsan');
insert into employee_1 values (2,10,'shangsan');
insert into employee_1 values (3,20,'shangsan');
insert into employee_1 values (9,90,'s9n');

SELECT * FROM EMPLOYEE E LEFT JOIN EMPLOYEE_1 E1 ON E1.EMPNO = E.EMPNO AND E.EMPNO =2;
SELECT * FROM EMPLOYEE E LEFT JOIN EMPLOYEE_1 E1 ON E1.EMPNO = E.EMPNO  WHERE E.EMPNO=2 ;
SELECT * FROM EMPLOYEE E right JOIN EMPLOYEE_1 E1 ON E1.EMPNO = E.EMPNO AND E.EMPNO =2;
SELECT * FROM EMPLOYEE E right JOIN EMPLOYEE_1 E1 ON E1.EMPNO = E.EMPNO  WHERE E.EMPNO=2 ;
SELECT * FROM EMPLOYEE E inner JOIN EMPLOYEE_1 E1 ON E1.EMPNO = E.EMPNO AND E.EMPNO =2;
SELECT * FROM EMPLOYEE E inner JOIN EMPLOYEE_1 E1 ON E1.EMPNO = E.EMPNO  WHERE E.EMPNO=2 ;