SQLize | PHPize | SQLtest

CREATE TABLE dept ( deptno INT(2) UNSIGNED NOT NULL, dname VARCHAR(14) NOT NULL, loc VARCHAR(13) NOT NULL, PRIMARY KEY (deptno) ); CREATE TABLE emp ( empno INT(4) UNSIGNED NOT NULL, ename VARCHAR(10) NOT NULL, job VARCHAR(9) NOT NULL, mgr INT(4) UNSIGNED, hiredate DATE NOT NULL, sal DECIMAL(7,2) NOT NULL, comm DECIMAL(7,2), deptno INT(2) UNSIGNED NOT NULL, PRIMARY KEY (empno), FOREIGN KEY (deptno) REFERENCES dept(deptno) ); INSERT INTO dept (deptno, dname, loc) VALUES (10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON'); INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10), (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30), (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10), (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20), (7788, 'SCOTT', 'ANALYST', 7566, '1987-07-13', 3000, NULL, 20), (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20), (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20), (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30), (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30), (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30), (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30), (7876, 'ADAMS', 'CLERK', 7788, '1987-07-13', 1100, NULL, 20), (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30), (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10); SELECT * FROM emp; SELECT * FROM emp WHERE sal BETWEEN 1600 AND 3000; SELECT ename, deptno, sal FROM emp WHERE sal < 3000 AND sal > 1600; SELECT DISTINCT job FROM emp GROUP BY job; SELECT * FROM emp WHERE deptno IN (10, 30); SELECT ename AS Name, (12*sal + IFNULL(comm, 0)) AS Annual_Remuneration, comm AS Commissions FROM emp WHERE job = 'SALESMAN' AND (12*sal > IFNULL(comm, 0)) ORDER BY Annual_Remuneration DESC; SELECT CONCAT(ename, ' has held the position of ', job, ' in department ', deptno, ' since ', DATE_FORMAT(hiredate, '%d-%b-%y')) AS result FROM emp WHERE ename = 'SMITH' AND job = 'CLERK' AND deptno = 20; SELECT column1 = ename, column2 =sal FROM emp;