Basics: Database Basics

Database Basics

Download

1) SQL Developer at http://www.oracle.com/technetwork/developer-tools/sql-developer/

2) Oracle XE

3) Latest JDK

Install

Once downloaded install all softwares

To unlock the sample user account using the SQL command line:

Display the SQL command prompt window. For example, on Windows, click Start, then Programs (or All Programs), then Oracle Database 11g Express Edition, and thenRun SQL Command Line.
Connect as the SYSTEM user:
- Type: connect
- Enter user-name: system
- Enter password: <password-for-system>
Enter the following statement to unlock the HR account:
```
SQL> ALTER USER hr ACCOUNT UNLOCK;
```
Enter a statement in the following form to specify the password that you want for the HR user:
```
SQL> ALTER USER hr IDENTIFIED BY <hr-password>;
```
Optionally, exit SQL*Plus (which also closes the command window):
```
SQL> exit
```

or you can directly run the command

 ALTER USER HR IDENTIFIED BY hr ACCOUNT UNLOCK;

Then go to sql developer & connect as

To see all the tables

select COUNT(DISTINCT DEPARTMENT_ID) AS DEPARTMENTS from DEPARTMENTS
select CONCAT(last_name,FIRST_NAME) as NAME ,SALARY*12 as CTC FROM employees
select last_name||' '||FIRST_NAME as NAME FROM employees
Describe employees
select last_name from employees where salary >10000
select last_name from employees where HIRE_DATE>'10-JUNE-1985'
select last_name from employees where HIRE_DATE BETWEEN '10-JUNE-1985' AND '10-JUNE-2005'
select first_name from employees where first_name like '_u%'
select last_name as NAME from Employees where manager_id IN (101,102,103) AND DEPARTMENT_ID IS NULL

CHAR FUNCTION

select LPAD(salary,10,'*') from employees where salary >10000 (o/p *****24000)
select RPAD(salary,10,'*') from employees where salary >10000 (o/p 24000*****)
select CONCAT(' MR. ', last_name) from employees where lower(first_name)='david'
select substr(last_name,1,5) from employees where lower(first_name)='subrat'
select length(last_name) from employees where lower(first_name)='david'
select instr(last_name,'a') from employees where lower(first_name)='david'
select trim('D' from first_name) from employees where lower(first_name)='david'

NUMBER FUNCTION

o/p 55.92 56 60

select trunc(55.923,2) , trunc(55.923,0) , trunc(55.923,-1) from dual;

o/p 55.92 55 50

 MOD(29,3)                                               |
+---------------------------------------------------------+
| 2

DATE FUNCTION

select sysdate from dual;
select last_name from employees where ((sysdate-hire_date)/(365))>12
To find max experience person : select * from (select round(((sysdate-hire_date)/(365)),1) as Experience from employees order by Experience desc) where ROWNUM = 1

select max(salary) from employees where salary<(select max(salary) from employees)

GROUP FUNCTION

select AVG(salary),MIN(salary),MAX(salary),SUM(salary) from employees
select distinct(department_id), AVG(salary) from employees group by DEPARTMENT_ID having AVG(salary)>9000