sql_cheatsheet.sql

select * from V$version;
## DB Version

desc hr.employees;
-- shortcut: select table_name and hit: shift + f4

info hr.employees;
info+ hr.employees;
-- additional info to desc cmd
-- primary key, cols(min,max,distinct), default, comments, indexes references

CREATE OR REPLACE PUBLIC SYNONYM EMPLOYEES for HR.EMPLOYEES;
-- Create synonym


SELECT 'My Name is Foo and my friend''s name is Bar' AS my_text FROM DUAL;
-- escape char: single quote(')

SELECT 
  q'[My Name is Foo and my friend's name is Bar]' AS my_text 
  FROM DUAL;
-- no need for escape and hence retains readability
-- '

SELECT DISTINCT JOB_ID FROM EMPLOYEES ORDER BY JOB_ID;
-- select distinct rows based on JOB_ID from table.

SELECT DISTINCT JOB_ID, DEPARTMENT_ID FROM EMPLOYEES ORDER BY JOB_ID;  
-- select distinct rows based on combination of JOB_ID, DEPARTMENT_ID from table.

SELECT 'My Name is Foo' || ' and ' || ' i like chocolates' AS Sentence FROM DUAL;
-- concatenate and print sentence

SELECT 'My Name is ' || first_name AS INTRODUCTION FROM EMPLOYEES;
-- concatenate string and col value

SELECT * FROM EMPLOYEES 
WHERE JOB_ID = 'IT_PROG';
-- restrict data returned

CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES;
-- CREATE TABLE (METADATA + DATA) FROM ANOTHER TABLE