10. SQL Commands Part 2

SQL Commands Part 2 Learning DQL Statement

How to show all objects of HR user?

code: select * from tab;

[Note: To end a SQL query we use a semicolon ";". Additionally we can write SQL statement in multiple lines and SQL is almost not case sensitive.]

How to set column width in SQL to 20 characters?

code: col tname for a20

[Note: Since it is a command not a query we don't have to end it with semicolon ';'. col is column, tname is column name, for is format and a20 is 20 characters.]

How to repeat the previous query?

code: r (See the picture above)

How to show the last query?

code: l

How to know all the columns of employees table?

code: desc employees

How will I display some column in employees table?

code: select employee_id, first_name, last-name, salary, department_id from employees;

How to change the line width in SQL or set 100 characters in a row?

code: set line 100

How to change the page size?

code: set pagesize 100

[Note: We are setting the page size to 100 because if you look at the previous image the heading are a little close to one another or should I say the page size is too small.]

How to change the display of a heading?

code: select employee_id as empno, first_name as "First Name", last-name as "Last Name", salary as "Monthly Salary", department_id as deptno from employees;

[Note: Internally every heading name is same as before, only for display I have changed. Also in first name we are using "" because it has a space in between, space is treated as a special character and to display special characters we have to use "". It is because what ever is inside the "" will be displayed as such.]

How to display monthly salary and yearly salary?

code: select employee_id as empno, first_name as "First Name", last-name as "Last Name", salary as "Monthly Salary", salary*12 as "Yearly Salary", department_id as deptno from employees;

How to display both the First name and Last name in a single column as Full Name?

code: select employee_id, first_name "First Name", last_name "Last Name", first_name || ' '|| last_name "Full name" from employees;

How to display information of all employees who are earning more than 10000?

code: select employee_id, first_name, last-name, salary, department_id from employees where salary>10000;

[Note: We are using a filter here, so whenever we use a filter we use where before it.]

How to display information of all employees who are earning more than equal to 10000?

code: select employee_id, first_name, last-name, salary, department_id from employees where salary>=10000;

How to display information of all employees who are earning more than equal to 10000 and are working in department_no 80 ?

code: select employee_id, first_name, last-name, salary, department_id, job_id, employee_id from employees where salary>=10000 and employee_id=80;

How to display information of all employees who are working in department_no 80 & 50?

code: select employee_id, first_name, last-name, salary, department_id, job_id, employee_id from employees where employee_id=80 and employee_id=50;

How to display information of all employees who are working in department_no 80 or 50?

code: select employee_id, first_name, last-name, salary, department_id, job_id, employee_id from employees where employee_id=80 or employee_id=50;

[Note: The and operator is states that both conditions should be satisfied and in or operator either one should be satisfied. This is exactly why the previous query will return blank.]

OR

code: select employee_id, first_name, last-name, salary, department_id, job_id, employee_id from employees where employee_id=(80,50);

How to display information of all employees who are earning between 8000 and 10000?

code: select employee_id, first_name, last-name, salary, department_id, job_id from employees where salary between 8000 and 10000;

How to display information of all employees whose name is Peter?

code: select employee_id, first_name, last-name, salary, department_id, job_id from employees where first_name like 'Peter';

[Note: Comparing of String type value is always case sensitive in SQL.]

How to display information of all employees whose first name starts with P?

code: select employee_id, first_name, last-name, salary, department_id, job_id from employees where first_name like 'P%';

[Note: We use '%' to represent n number of characters since we don't know how many characters are there after P.]

How to display information of all employees whose first name contains e in the second place?

code: select employee_id, first_name, last-name, salary, department_id, job_id from employees where first_name like '_e%';

[Note: To represent a single character in a name we use '_'. ]

How to display information of all employees whose first name contains e in the second position and second last position?

code: select employee_id, first_name, last-name, salary, department_id, job_id from employees where first_name like 'e%e';

How to display information of all employees who are earning more than 10000 and who are either working in department 80 or 50?

code: select employee_id, first_name, last-name, salary, department_id, job_id from employees where salary>=10000 and (department_id=50 or department_id=80);

OR

code: select employee_id, first_name, last-name, salary, department_id, job_id from employees where salary>=10000 and department_id in (50,80);

How to sort the above outcome in alphabetical order?

code: select employee_id, first_name, last-name, salary, department_id, job_id from employees where salary>=10000 and department_id in (50,80) order by first_name;

[Note: By default it will display in ascending order.]

How to sort the above outcome in descending order?

code: select employee_id, first_name, last-name, salary, department_id, job_id from employees where salary>=10000 and department_id in (50,80) order by desc;

How to sort the above outcome in ascending order?

code: select employee_id, first_name, last-name, salary, department_id, job_id from employees where salary>=10000 and department_id in (50,80) order by asc;

How to sort the above outcome in multiple fields first the first name should be organized in ascending order and if the names are common then sort the in descending order based on salary?

code: select employee_id, first_name, last-name, salary, department_id, job_id from employees order by first_name asc, salary desc;