SQL Basic Commands

\\SQL Oracle******

WITH  dept_costs AS (SELECT department_name, SUM(salary) dept_total FROM employees e, departments d WHERE e.department_id = d.department_idGROUP BY department_name), avg_cost AS (SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs)SELECT * FROM dept_costs WHERE dept_total > (SELECT avg FROM avg_cost) ORDER BY department_name;SELECT *FROM employees WHERE department_id = 30 ORDER BY last_name;SELECT last_name, job_id, salary, department_id FROM employees WHERE NOT (job_id = 'PU_CLERK' AND department_id = 30) ORDER BY last_name;SELECT a.department_id "Department", a.num_emp/b.total_count "%_Employees",a.sal_sum/b.total_sal "%_Salary" FROM (SELECT department_id, COUNT(*) num_emp, SUM(salary) sal_sum FROM employees GROUP BY department_id) a, (SELECT COUNT(*) total_count, SUM(salary) total_sal FROM employees) b ORDER BY a.department_id;SELECT * FROM sales PARTITION (sales_q2_2000) s WHERE s.amount_sold > 1500 ORDER BY cust_id, time_id, channel_id;SELECT * FROM orders WHERE order_date < TO_DATE('2000-06-15', 'YYYY-MM-DD');SELECT COUNT(*) * 10 FROM orders SAMPLE (10); COUNT(*)*10SELECT salary FROM employees WHERE last_name = 'Langa'; SALARY ---------- 3800 UPDATE employees SET salary = 4000 WHERE last_name = 'Langa'; 1 row updated. SELECT salary FROM employees WHERE last_name = 'Langa';SELECT salary FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE) WHERE last_name = 'Langa';SELECT salary FROM employees VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE AND SYSTIMESTAMP - INTERVAL '1' MINUTE WHERE last_name = 'Langa';

UPDATE employees SET salary =
 (SELECT salary FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' MINUTE)
WHERE last_name = 'Langa')
 WHERE last_name = 'Langa';
1 row updated.
SELECT salary
FROM employees
WHERE last_name = 'Langa';

Share: