SQL Tutorial – Day – 04

— Select statement
================

select * from employees;

select employee_id,first_name,email,hire_date,salary,department_id from employees;

–column alias
select employee_id as emp_id,first_name,email,hire_date,salary,department_id from employees;

select employee_id emp_id,first_name,email,hire_date,salary,department_id from employees;

select employee_id emp_id,first_name,email,hire_date salary,department_id from employees;

— column concatenation

select employee_id ,first_name,last_name,concat(first_name,last_name),email,
hire_date salary,department_id from employees;

select employee_id ,first_name,last_name,concat(first_name,last_name) full_name,email,
hire_date ,salary,department_id from employees;

select employee_id ,first_name,last_name,concat(first_name,’ ‘,last_name) full_name,email,
hire_date ,salary,department_id from employees;
–ORA-00909: invalid number of arguments

select employee_id ,first_name,last_name,concat(concat(first_name,’ ‘),last_name) full_name,email,
hire_date ,salary,department_id from employees;

— alternate way – pipe
select employee_id emp_id,first_name,last_name,first_name||’ ‘||last_name full_name,email,
hire_date, salary,department_id from employees;

— column calculation

select employee_id,first_name,email,hire_date,salary,salary+1000 new_salary,department_id from employees;

select employee_id,first_name,email,hire_date,salary,salary*12 annual_salary,department_id from employees;

— unique department_id

select department_id from employees;

select count(department_id) from employees;

select distinct department_id from employees;

select count(distinct department_id) from employees;

select count(*) from (
select distinct department_id,job_id from employees);

— where clause

select * from employees where salary >10000;

select * from employees where salary <3000;

select * from employees where salary >5000 and salary <7000;

select * from employees where salary >=5000 and salary <=7000;

select * from employees where salary between 5000 and 7000;

select * from employees where salary not between 5000 and 7000;

select * from employees where department_id=30;

select * from employees where department_id=30,60,90;
— ORA-00933: SQL command not properly ended

select * from employees where department_id IN (30,60,90);

select * from employees where department_id NOT IN (30,50,80);

select * from employees where department_id=80 AND salary >10000;

select * from employees where department_id=60 OR salary >15000;

select * from employees where department_id=60 OR (department_id=80 AND salary >10000);

select * from employees where rownum <=5;

select * from employees where rownum =5; — Wrong

select * from employees where rownum > 5; — wrong

select rownum,rowid,employee_id,first_name from employees;

select rownum,rowid,* from employees; –ORA-00936: missing expression

select rownum,rowid,e.* from employees e;

select * from employees where commission_pct is null;

select * from employees where commission_pct is not null;

select count(*) from employees;

select count(commission_pct) from employees;

select count(*) from employees where commission_pct is null;

select employee_id,hire_date,to_char(hire_date,’yyyy’) from employees;

select employee_id,hire_date,to_number(to_char(hire_date,’yyyy’)) from employees;

select employee_id,hire_date,to_number(to_char(hire_date,’mm’)) from employees;

select employee_id,hire_date,to_char(hire_date,’mon’) from employees;

select employee_id,hire_date,to_char(hire_date,’Month’) from employees;

select employee_id,hire_date,to_char(hire_date,’dd’) from employees;

select employee_id,hire_date,to_char(hire_date,’dd-mm-yyyy hh24:mi:ss’) from employees;

select * from employees where to_char(hire_date,’yyyy’)=’2005′;

select * from employees where to_char(hire_date,’mmyyyy’) between ‘092005’ and ‘092006’;

select * from employees where to_char(hire_date,’mm’)=’02’;

select * from employees where to_char(hire_date,’mmyyyy’)=’032005′;

select * from employees where to_char(hire_date,’FMDay’)=’Monday’;

select * from employees where to_char(hire_date,’D’)=’2′;

select sysdate from dual;

select current_date from dual;

select sysdate from employees;

select systimestamp from dual;

select trunc(systimestamp) from dual;
—————————————– pattern matching — like

select * from employees where first_name like ‘A%’;

select * from employees where first_name like ‘a%’;

select * from employees where upper(first_name) like ‘J%’;

select * from employees where first_name like ‘%s’;

select * from employees where first_name like ‘%an%’;

select * from employees where first_name like ‘S%n’;

select * from employees where first_name like ‘_______’;

select * from employees where length(first_name) =7;

select * from employees where first_name like ‘_a____’;

select * from employees where first_name like ‘_a_t__’;

select * from employees where first_name like ‘A%’ and salary like ‘%200’;

select * from employees where first_name like ‘%\_%’ escape ‘\’;

 

— Sorting — Order by asc | desc

select * from employees;

select ascii(‘A’) from dual; — 65

select ascii(‘a’) from dual; –97

select * from employees order by first_name;

select * from employees order by first_name desc;

select * from employees order by salary asc;

select * from employees order by salary desc;

— null treated as highest value

select * from employees order by commission_pct;

select * from employees order by commission_pct desc;

— order by more than one column

select * from employees order by salary desc,hire_date;

select * from employees order by 8;

select * from employees order by 6,8 desc;