SELECT department_id,
job_id,
AVG(salary)
FROM employees
WHERE SUBSTR(first_name, 0, 1) = 'A'
GROUP BY job_id,
department_id HAVING AVG(salary) > 5000
ORDER BY department_id,
job_id;
SELECT SUM(salary) AS
toplam,
AVG(salary) AS
ortalama,
MIN(salary) AS
mini,
MAX(salary) AS
maxi,
STDDEV(salary) AS
standartsapma,
VARIANCE(salary) AS
karakok,
COUNT(salary) AS
sayi
FROM employees;
SELECT AVG(nvl(commission_pct, 0)) AS
tumcomisyonlar,
AVG(commission_pct) AS
nullolmayankomisyonlar
FROM employees;
SELECT ROUND(MAX(AVG(salary)))
FROM employees
GROUP BY department_id;
SELECT department_id,
job_id,
SUM(salary)
FROM employees
GROUP BY rollup(department_id,job_id) HAVING SUM(salary) > 10000
ORDER BY department_id;
SELECT department_id,
job_id,
SUM(salary)
FROM employees
GROUP BY cube(department_id,job_id) HAVING SUM(salary) > 10000
ORDER BY department_id;
SELECT COUNT(DISTINCT manager_id)
FROM employees;
SELECT COUNT(*) AS
"TOTAL",
COUNT(
CASE to_char(hire_date, 'YY')
WHEN '95' THEN 1
END) "1995",
COUNT(
CASE to_char(hire_date, 'YY')
WHEN '96' THEN 1
END) "1996",
COUNT(
CASE to_char(hire_date, 'YY')
WHEN '97' THEN 1
END) "1997",
COUNT(
CASE to_char(hire_date, 'YY')
WHEN '98' THEN 1
END) "1998"
FROM employees;
SELECT COUNT(*) AS
"TOTAL",
SUM(decode(to_char(hire_date, 'YY'), '95', 1, 0)) "1995",
SUM(decode(to_char(hire_date, 'YY'), '96', 1, 0)) "1996",
SUM(decode(to_char(hire_date, 'YY'), '97', 1, 0)) "1997",
SUM(decode(to_char(hire_date, 'YY'), '98', 1, 0)) "1998"
FROM employees;
SELECT job_id,
SUM(
CASE department_id
WHEN 20 THEN salary
END) "Dept 20",
SUM(
CASE department_id
WHEN 50 THEN salary
END) "Dept 50",
SUM(
CASE department_id
WHEN 80 THEN salary
END) "Dept 80",
SUM(
CASE department_id
WHEN 90 THEN salary
END) "Dept 90",
SUM(salary)
FROM employees
GROUP BY job_id;
SELECT job_id,
SUM(decode(department_id, 20, salary, 0)) "Dept 20",
SUM(decode(department_id, 50, salary, 0)) "Dept 50",
SUM(decode(department_id, 80, salary, 0)) "Dept 80",
SUM(decode(department_id, 90, salary, 0)) "Dept 90"
FROM employees
GROUP BY job_id;
SELECT *
FROM employees minus
SELECT *
FROM employees
WHERE job_id = 'IT_PROG';
SELECT *
FROM user_constraints;
--R foreign key
--P primart key
--U unique
--C check
SELECT employees.last_name,
employees.department_id,
departments.department_name,
locations.city
FROM employees
JOIN departments ON employees.department_id = departments.department_id
JOIN locations ON departments.location_id = locations.location_id;
SELECT e.last_name,
e.department_id,
d.department_name,
l.city
FROM employees e,
departments d,
locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
SELECT department_id,
department_name,
location_id,
city
FROM departments NATURAL JOIN locations;
SELECT employee_id,
last_name,
location_id,
department_id
FROM employees JOIN departments USING(department_id)
WHERE department_id = 50;
--and whereden daha performansli çalisir
SELECT e.employee_id,
e.last_name,
d.location_id,
e.department_id
FROM employees e JOIN departments d ON(e.department_id = d.department_id)
AND d.department_id = 50;
SELECT e.employee_id,
e.last_name,
m.last_name
FROM employees e JOIN employees m ON(e.manager_id = m.employee_id);
SELECT e.last_name,
e.department_id,
d.department_name
FROM employees e RIGHT
OUTER JOIN departments d ON(e.department_id = d.department_id);
SELECT e.last_name,
e.department_id,
d.department_name
FROM employees e,
departments d
WHERE(e.department_id(+) = d.department_id);
--(+) right outher ise sol tarafa koyuyoruz artiyi
SELECT e.last_name,
e.department_id,
d.department_name
FROM employees e FULL
OUTER JOIN departments d ON(e.department_id = d.department_id);
SELECT e.last_name,
e.department_id,
d.department_name
FROM employees e,
departments d
WHERE(e.department_id = d.department_id(+))
UNION
SELECT e.last_name,
e.department_id,
d.department_name
FROM employees e,
departments d
WHERE(e.department_id(+) = d.department_id);
SELECT last_name, department_name
FROM employees CROSS JOIN departments;
SELECT last_name, department_name
FROM employees, departments;
