Set of A-Z SQL Command 1

  Write Basic SQL select statement

1. select * from employees;

2. select last_name,first_name from employees;

3. select distinct department_id from employees;

4. select distinct department_id,last_name from employees;

   

Arithmetic expressions (+,-,*,/)

5. select last_name,salary+1000 from employees;

6. select last_name,salary-100 from employees;

7. select salary,last_name,salary*12 from employees;

8. select salary,last_name,salary/2 from employees;

9. select salary,last_name,salary+1000/2 from employees;

10.select salary,last_name,(salary+1000)/2 from employees;

Null value statement

11. select salary,last_name salary+commission_pct from employees;

Column Alias (nick name)

12.    select salary,last_name,salary*12 Annual_sal from employees;

13. select salary,last_name,salary*12 “annual_sal” from employees;

14. select last_name,salary*12 as Annual_sal from employees;

Concatenation Operations ( || )

15. select last_name||salary from employees;

16. select last_name||salary as “Complete” from employees;

17. select last_name||Kamata hai Rs. ||salary from employees; (literal)

Restriction and Sorting Data

1.    Select * from employees where department_id=50;

2.    select last_name ,salary from employees where  department_id=50;

3.    select last_name ,salary from employees where last_name =’Feeney’;

Comparison Conditions (<,>,=,>=,<=,<>)

4.  select * from employees where salary > 10000;

5.    select last_name,salary from employees where salary>=10000;

6.    select last_name,salary from employees where salary<>10000;

       

Other Comparision (between,in,like,is null)

7.    select * from employees where salary between 8000 and 12000;

8.    select * from employees where last_name between ‘A’ and ‘B’; //show  alphabet who start ‘A’ to ‘B’ don’t show ‘B’ char//

9.    SELECT * FROM EMPLOYEES WHERE JOB_ID IN ('SA_REP','ST_CLERK','ST_MAN');

10.  SELECT * FROM EMPLOYEES WHERE JOB_ID NOT IN ('SA_REP','ST_CLERK','ST_MAN');

11.  select * from employees where last_name like ‘S%’; //who alphabet start with ‘S’ //

12.  select * from employees where last_name like ‘%S’; //start with any char but end with ‘S’//

13.  select * from employees where last_name like ‘%S%’; // ’S’ anywhere //

14.  select * from employees where last_name like ‘_S%’; // start with any char but second char is ‘S’

15.  select * from employees where commission_pct is null;

16.  select * from employees where commission_pct is not null;

                     

Logical Condition (AND,OR, NOT)

17.  select * from employees where salary >10000 and department_id=50; //both condition should be true//

18.  select * from employees where salary >10000 or department_id=50; //either condition 1 or condition 2 should be true

19.  select * from employees where salary not in (3000,4000,10000);

20.  select * from employees where salary >10000 and last_name like 'H%';

21.  select * from employees where salary >10000 and last_name like '%H%' AND department_id >50;

22.  select * from employees where (salary >10000 and last_name like '%H%') AND department_id >50;

Setting Order (Sorting)

23.select last_name,salary,commission_pct from employees order by last_name;

24.select  * from employees order by salary;

25. select last_name,salary,commission_pct from employees order by last_name desc;

26.select * from employees order by department_id,salary;

27. select * from employees order by department_id,salary desc;

28. select * from employees order by department_id desc ,salary desc;

Sorting by column Alias

29. select last_name,salary,salary*12 “Annual Sal” from employees order by “Annual Sal”;

30. select last_name,salary,salary*12 annual  from employees order by annual;

31. select last_name,salary,salary*12 annual  from employees order by annual desc;

Single Row Functions

Contents to cover

Character function

1.    select lower('TINKU') from dual;

2.    select lower(last_name) from employees;

3.    select lower(last_name),salary,job_id from employees;

4.    select upper(last_name) from employees;

5.    select initcap(last_name),salary,job_id from employees;

6.    select * from employees where last_name =’Feeney’;

7.    select * from employees where initcap(last_name) =’Feeney’;

               

character Manipulate function

8.    select concat(‘hello’,’student’) from dual;

9.    select concat(last_name,salary) from employees;

10.  select substr(‘helloworld’,1,4) from dual;

11.  select substr(last_name,1,4) from employees;

12.  select length(‘Amitabh Bachchan’) from dual;

13.  select instr(‘helloworld’,’w’) from dual;

14.  select instr(‘helloworld’,’o’,6) from dual;

15.  select lpad(salary,10,’*’) from employees;

16.  select rpad(salary,10,’*’) from employees;

17.  select trim(‘h’ from ‘helloworld’) from dual;

Number Function

18.  select round(123.456,2) from dual;

19.  select round(123.456,-2) from dual;

20.  select trunc(123.79,1) from dual;

21.  select trunc(123.79,-1) from dual;

22.  select mod(5,2) from dual;

Data Functions

23.  select sysdate from dual;

24.  select last_name,(sysdate-hire_date) from employees;

25.   select last_name,(sysdate-hire_date)/365 from employees;

26.   select last_name,(sysdate-hire_date)/7 from employees;

27.  select last_name,round((sysdate-hire_date)/365,0) from employees;

28.  select months_between(‘2-mar-2009’,’24-aug-2010’) from dual;

29.  select months_between(sysdate,hire_date) from employees;

30.  select add_months(‘2-mar-2009’,22) from dual;

31.  select add_months(hire_date,22) from employees;

32.  select next_day(‘2-mar-2009’,’Friday’) from dual;

33.  select last_day(‘2-sep-2010’) from dual;

34.  select round(sysdate,’month’) from dual;

35.  select hire_date,round(hire_date,’month’) from employees;

36.  select hire_date,round(hire_date,’year’) from employees;

37.  select hire_date,trunc(hire_date,'month') from employees;

38.  select hire_date,trunc(hire_date,'year') from employees;