//zmiana hasla do bazy uzytkownikowi SYSTEM
ALTER USER SYSTEM IDENTIFIED BY hasło;
connect SYSTEM/hasło
//odblokowanie konta hr
ALTER USER hr ACCOUNT UNLOCK;
ALTER USER hr IDENTIFIED BY hr;
SELECT * FROM employees;
SELECT first_name, last_name, salary FROM employees;
SELECT first_name, last_name, salary FROM employees WHERE salary > 10000;
SELECT first_name, last_name, salary FROM employees WHERE salary > 10000 AND department_id = 100;
SELECT first_name, last_name, salary FROM employees WHERE salary > 10000 AND (department_id = 100 OR department_id = 80);
SELECT first_name, last_name, salary FROM employees WHERE first_name LIKE 'E%';
SELECT first_name, last_name, salary FROM employees WHERE first_name LIKE '%Ed%';
SELECT first_name, last_name, salary FROM employees WHERE salary BETWEEN 5000 AND 10000;
SELECT first_name, last_name, job_id FROM employees WHERE job_id IN ('IT_PROG','AD_VP');
SELECT first_name, last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG','AD_VP');
SELECT first_name, last_name, department_id FROM employees WHERE department_id IN (90,100);
SELECT first_name, last_name, department_id FROM employees WHERE department_id NOT IN (90,100);
SELECT * FROM employees WHERE hire_date > to_date('01/05/2000','DD/MM/YYYY');
SELECT first_name, last_name FROM employees WHERE last_name = 'King' AND first_name = 'Stephen';
SELECT DISTINCT department_id FROM employees;
SELECT DISTINCT department_id, salary FROM employees;
SELECT first_name, last_name, salary, salary1.15 AS "Salary plus bonus" FROM employees; SELECT first_name AS "Employee First Name", last_name AS "Employee Last Name", salary, salary1.15 AS "Salary plus bonus" FROM employees;
DESCRIBE employees;
DESC employees;
SELECT DISTINCT department_id, salary FROM employees ORDER BY salary DESC;
SELECT SUBSTR(job_title, 1,5) FROM jobs; /(pole, od którego znaku, ile znaków)/
SELECT first_name, last_name, LENGTH(first_name) FROM employees;
SELECT CONCAT(first_name, last_name) FROM employees;
SELECT CONCAT('First Name: ',first_name), concat('Last Name:', last_name) FROM employees;
SELECT 'First Name: ' || first_name || '-----' ||'Last Name: ' || last_name FROM employees;
SELECT first_name, last_name FROM employees WHERE UPPER(first_name) = 'DAVID';
SELECT first_name, last_name, INSTR(phone_number,'650') FROM employees;
SELECT lpad(first_name,10,'+') FROM employees; /wypełnienie z lewej strony znakiem + do 10 znaków/
SELECT rpad(first_name,10,'+') FROM employees; /wypełnienie z prawej strony znakiem + do 10 znaków/
SELECT TO_CHAR(salary,'99G999L') FROM employees;
SELECT last_name, TO_CHAR((salary/12),'9999.99L') AS "zarobki miesięczne" FROM employees;
SELECT first_name, hire_date FROM employees WHERE TO_CHAR(hire_date,'MM') = '03';
SELECT ROUND(999.9456,3) FROM DUAL;
SELECT TRUNC(999.9456,3) FROM DUAL;
SELECT SYSDATE FROM DUAL;
SELECT SYSTIMESTAMP, TO_CHAR(sysdate,'DD-MM-YYYY HH24:MI:SS') FROM DUAL;
SELECT SYSDATE, hire_date, ROUND(MONTHS_BETWEEN(SYSDATE,hire_date),0) FROM employees;
SELECT SYSDATE, ADD_MONTHS(sysdate,1),ADD_MONTHS(sysdate,2),ADD_MONTHS(sysdate,3) FROM DUAL;
TRUNCATE TABLE nazwa_tabeli; /- w przeciwieństwie do DELETE jest komendą DDL (od razu COMMITuje, po jej wykonaniu nie ma już ROLLBACK);/
SELECT employees.first_name, employees.last_name, departments.department_name FROM employees JOIN departments ON employees.department_id = departments.department_id; /JOIN i INNER JOIN to to samo, część wspólna/
SELECT employees.first_name, employees.last_name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id; /zwróci wynik taki jak z JOIN, ale w wyniku będzie również pracownik Kimberely Grant, która nie ma przypisanego działu/
SELECT employees.first_name, employees.last_name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id;
SELECT employees.first_name, employees.last_name, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id;
SELECT first_name, department_name FROM employees emp JOIN departments dept ON emp.department_id = dept.department_id; /alisy tabel/
SELECT first_name, last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id; /składnia JOIN dla ORACLE/
SELECT first_name, last_name, department_name FROM employees, departments WHERE employees.department_id(+) = departments.department_id; /składnia LEFT JOIN dla ORACLE/
SELECT first_name, last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id(+); /składnia RIGHT JOIN dla ORACLE/
SELECT MAX(salary), min(salary), ROUND(AVG(salary)) FROM employees;
SELECT COUNT() FROM employees; SELECT SUM(salary) FROM employees; SELECT COUNT() AS Number_of_Employees, department_id FROM employees GROUP BY department_id ORDER BY number_of_employees DESC;
SELECT round(AVG(salary)),MAX(salary),department_id FROM employees GROUP BY department_id HAVING AVG(salary) > 9000 AND MAX(salary) < 15000;
