11. SQL Commands Part 3

SQL Commands Part 3

What is null?

Ans: Null is neither 0 nor space. It is basically a junk value.

Display those employees who are earning commission?

code: select employee_id, first_name, salary, commission_pct from employees where commission_pct is not null;

Display those employees who are not earning commission?

code: select employee_id, first_name, salary, commission_pct from employees where commission_pct is null;

Display those employees whose department_id is null or employees who desn't belong to any department?

code: select employee_id, first_name, salary, commission_pct from employees where department_id is null;

How many employees are there in my employees table?

code: select count(employee_id) from employees;

How many employees are getting commission?

code: select count(commission_pct) from employees;

Understanding count(*)?

Ans: count(*) counts all rows that it finds any value in. You can understand it by watching the image below.

How to find out the sum of salaries of all employees?

Ans: select sum(salary) from employees;

How to find out the average of salaries of all employees?

Ans: select avg(salary) from employees;

How to find the average until 2 decimal places?

code: select round(avg(salary),2) from employees;

How to find the average without using the avg function?

code: select sum(salary)/count(*) from employees;

How to find the maximum and minimum salary of all employees?

code: select max(salary), min(salary) from employees;

[Note: With single value return functions we cannot use any field name. Single value return functions are those functions that return single value like avg, min, max, etc... This happens because SVR functions gives 1 value and field name or columns have multiple values. ]

Display total number of employees, the total salary and max salary?

code: select count(*) "Total no of Employees", sum(salary), max (salary) from employees;

[Note: This works because every single one of them is a single value return function.]

Convert all first name letters to lower Case and last name letters to upper Case

code: select lower(first_name), upper(last_name) from employees;

Display all details of a employees who has first_name 'peter'.

code: select employee_id, first_name, salary, department_id, job_id from employees where lower(first_name) like 'peter';

Using and uderstanding initcap function.

code: select job_id, initcap(job_id) from employees;

[Note: initcap concerts the first letter to uppercase and the rest to lowercase. initcap full form is initial capital.]

Display the number of characters in everyone's first name.

code: select first_name, length(first_name) from employees;

[Note: Remember it will count space too. Like for 'Josh Manual' it will return 11.]

Using padding

There are two kinds of padding left padding and right padding.

left padding

code: select first_name, lpad(first_name, 20, #) from employees; [The 20 is for 20 characters. It means that the first name will be displayed as 20 characters long and to fill the blank spaces it uses #. It wouldlook something like this '###############Akash']

right padding

code: select first_name, rpad(first_name, 20, #) from employees;

Display first 4 characters of first name.

code: select first_name, substr(first_name, 1, 4) from employees;

[substr takes three fields 1st is name, 2nd is position in this case from 1st character , 3rd is no of characters to be taken in this case 4]

Display last 4 characters of first name.

code: select first_name, substr(first_name, length(first_name)-3, 4) from employees;

OR

code: select first_name, substr(first_name, -4, 4) from employees;

How to replace all 'a' characters from the names with '@';

code: select first_name, replace(first_name, 'a', '@') from employees;

[Note: Problem with this query is that it will only replace small letter a's not capital letter A's. To solve that.]

code: select first_name, replace(upper(first_name), 'A', '@') from employees;

[Note: Now it will replace all capital A's with @.]

How to find out the first occurrence or position of any character?

code: select first_name, instr(first_name,e) from employees;

[Note: The problem with this query is that it will not take in account of the first characters since they are in capital letter, so to remove this issue we need to convert the first_name inside instr to lower case.]

code: select first_name, instr(lower(first_name),e) from employees;

Display those first name which has 5 characters?

code: select first_name from employees where length(first_name)=5;

Display salary and it's square of all the employees.

code: select salary, power(salary, 2) from employees;

Display hire date of all employees in dd-mm-yyyy format.

code: select hire_date, to_char(hire_date, 'dd-mm-yyyy') from employees;

Display hire date of all employees in day and dd-Mon-yyyy format.

code: select hire_date, to_char(hire_date, 'day, dd-Mon-yyyy') from employees;

How to find out todays date?

code: select sysdate from dual;

[Note: There is only 1 dummy field in the temporary table dual called sysdate.]

Display all the hire date years?

code: select hire_date, to_char(hire_date, 'yyyy') from employees;

Using extract to display the year from the hire date?

code: select hire_date, extract(year from hire_date) from employees;

Show me the number of months between hire date and today.

code: select hire_date, months_between(sysdate, hire_date) from employees;

[Note: anything we write from keyboard gets converted to string, to undo that we use to_date.]

Display the number of months between 19th December 2024 till today.

code: select months_between (sysdate, to_date('19-dec-2024','dd-mm-yyyy')) from dual.

[Note: Whenever we are typing any date from keyboard it gets converted to string to change this conversion to date we use to-date.]

How to add two months from a date.

code: select add_months(sysdate, 2) from dual;

How to get last day of this date.

code: select last_date(sysdate) from dual.

How to find out the date for next 'Monday'?

code: select next_day(sysdate,'Monday') from dual;

How to find out the date for next 'Friday'?

code: select next_day(sysdate,'Friday') from dual;

Display those employees who joined after 31-Dec-2006?

code: select employee_ide, first_name, salary, hire_date from employees where hire_date > to_date('31-12-2006 ','dd-mm-yyyy');

Display those employees who joined after 31-Dec-2006 and before 01-Jan-2008?

code: select employee_ide, first_name, salary, hire_date from employees where hire_date > to_date('31-12-2006 ','dd-mm-yyyy') and hire_date < to_date('01-01-2008','dd-mm-yyyy');

OR

code:select employee_ide, first_name, salary, hire_date from employees where hire_date between to_date('31-12-2006 ','dd-mm-yyyy') and to_date('01-01-2008','dd-mm-yyyy');

Show me how many years a employee is working in an organizaton.

code: select hire_date, extract(year from sysdate) -extract(year from hirer-date) from employees;

Structure of todays SQL command.

code: select , from <table / view name> where order by