RBD_SQL

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE SALGRADE;

CREATE TABLE EMP
       (EMPNO INT NOT NULL,
        ENAME VARCHAR(10),
        JOB VARCHAR(9),
        MGR INT,
        HIREDATE DATETIME,
        SAL INT,
        COMM INT,
        DEPTNO INT);

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        CONVERT(DATETIME,'17-DEC-1980'),  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        CONVERT(DATETIME,'20-FEB-1981'), 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        CONVERT(DATETIME,'22-FEB-1981'), 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        CONVERT(DATETIME,'2-APR-1981'),  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        CONVERT(DATETIME,'28-SEP-1981'), 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        CONVERT(DATETIME,'1-MAY-1981'),  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        CONVERT(DATETIME,'9-JUN-1981'),  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        CONVERT(DATETIME,'09-DEC-1982'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        CONVERT(DATETIME,'17-NOV-1981'), 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        CONVERT(DATETIME,'8-SEP-1981'),  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        CONVERT(DATETIME,'12-JAN-1983'), 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        CONVERT(DATETIME,'3-DEC-1981'),   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        CONVERT(DATETIME,'3-DEC-1981'),  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        CONVERT(DATETIME,'23-JAN-1982'), 1300, NULL, 10);

CREATE TABLE DEPT
       (DEPTNO INT,
        DNAME VARCHAR(14),
        LOC VARCHAR(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAdS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE SALGRADE
        (GRADE INT,
         LOSAL INT,
         HISAL INT);

INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

--1.
SELECT DEPTNO, ENAME, EMPNO FROM EMP;

--2.
SELECT * FROM EMP;

--3.
SELECT SAL*12 FROM EMP;

--4.
SELECT (SAL+250)*12 FROM EMP;

--5.
SELECT SAL*12 ROCZNA FROM EMP;

--6.
SELECT SAL*12 "R PENSJA" FROM EMP;

--7.
SELECT CONCAT(EMPNO, ' ' , ENAME) AS EMPLOYEE FROM EMP;

--8.
SELECT CONCAT(ENAME, ' pracuje w dziale ', DEPTNO) AS EMPLOYEE FROM EMP

--9.
SELECT SAL*12+ISNULL (COMM,0) ROCZNAPENSJA FROM EMP


--10/11.
SELECT DISTINCT DEPTNO FROM EMP;


--12.
SELECT DISTINCT DEPTNO, JOB FROM EMP;


--13.
SELECT * FROM EMP
ORDER BY ENAME;

--14.
SELECT * FROM EMP
ORDER BY HIREDATE;

--15.
SELECT * FROM EMP
ORDER BY DEPTNO ASC, SAL DESC;

--16.
SELECT ENAME, EMPNO, JOB, DEPTNO FROM EMP
WHERE JOB='CLERK'
ORDER BY DEPTNO DESC;

--17.
SELECT JOB, DEPTNO FROM EMP
WHERE DEPTNO>=20;

--18.
SELECT CONCAT(ENAME, ' o numerze ', EMPNO) AS PRACOWNIK, COMM, SAL FROM EMP
WHERE COMM>SAL;

--19.
SELECT ENAME, EMPNO, SAL FROM EMP
--WHERE SAL+ISNULL (COMM, 0) <2000 AND SAL+ISNULL (COMM, 0) >1000;
WHERE SAL BETWEEN 1000 AND 2000;

--20.
SELECT ENAME, EMPNO, MGR FROM EMP
WHERE MGR=7902 OR MGR=7566 OR MGR=7788;

--21.
SELECT ENAME FROM EMP
WHERE ENAME LIKE 'S%';

--22.
SELECT ENAME FROM EMP
WHERE LEN(ENAME) = 4;

--23.
SELECT ENAME, EMPNO, MGR FROM EMP
WHERE MGR IS NULL;

--24.
SELECT ENAME, EMPNO, SAL FROM EMP
WHERE SAL NOT BETWEEN 1000 AND 2000;

--25.
SELECT ENAME FROM EMP
WHERE ENAME NOT LIKE 'M%';

--26.
SELECT ENAME, EMPNO, MGR FROM EMP
WHERE MGR IS NOT NULL;

--27.
SELECT EMPNO, ENAME, JOB, SAL FROM EMP
WHERE JOB='CLERK' AND SAL BETWEEN 1000 AND 2000;

--28.
SELECT EMPNO, ENAME, JOB, SAL FROM EMP
WHERE JOB='CLERK' OR SAL BETWEEN 1000 AND 2000;

--29.
SELECT EMPNO, ENAME, JOB, SAL FROM EMP
WHERE (JOB='MANAGER' AND SAL>1500) OR JOB='SALESMAN';

--30.
SELECT EMPNO, ENAME, SAL, JOB FROM EMP
WHERE JOB='MANAGER' OR (JOB='SALESMAN' AND SAL>1500);

--31.
SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP
WHERE JOB='MANAGER' OR (JOB='MANAGER' AND DEPTNO=10);

--32.
SELECT * FROM SALGRADE;

--33.
SELECT * FROM DEPT;

--34.
SELECT DEPTNO, DNAME FROM DEPT
ORDER BY DEPTNO;

--35.
SELECT DISTINCT JOB FROM EMP;

--36.
SELECT EMPNO, ENAME, DEPTNO FROM EMP
WHERE DEPTNO=10 OR DEPTNO=20
ORDER BY ENAME;