ORACLE SQL: TOP 10 BASIC SQL QUERIES/STATEMENTS(MOST COMMONLY USED)

1. Creating a table

create table employee
(
emp_no number(4),
emp_name varchar2(30),
sex varchar2(1),
age number(3),
desg varchar2(10),
sal number(8),constraint emp_pk primary key(emp_no)
);

The above statement will create a table "employee" (with primary key on column 'emp_no') having no rows in it.

2. Inserting records in a table

insert into employee
(emp_no,emp_name,sex,age,desg,sal)
values
(1,'Adam','M',22,'Clerk',8000);


insert into employee
(emp_no,emp_name,sex,age,desg,sal)
values
(2,'Amy','F',23,'Clerk',8500);

insert into employee
(emp_no,emp_name,sex,age,desg,sal)
values
(3,'Casper','M',25,'Salesman',10500);

insert into employee
(emp_no,emp_name,sex,age,desg,sal)
values
(4,'Daniel','M',28,'Manager',18000);

insert into employee
(emp_no,emp_name,sex,age,desg,sal)
values
(5,'Erwin','M',27,'Salesman',12000);

After inserting records, table data will look like

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         1 Adam         M         22 Clerk            8000
         2 Amy          F         23 Clerk            8500
         3 Casper       M         25 Salesman        10500
         4 Daniel       M         28 Manager         18000
         5 Erwin        M         27 Salesman        12000

3. Counting the number of records in a table

select count(*) from employee

The above query will give you the result as 5.


4. Viewing all records from a table(The most commonly used query)

select * from employee;

Below is the result of this query:

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         1 Adam         M         22 Clerk            8000
         2 Amy          F         23 Clerk            8500
         3 Casper       M         25 Salesman        10500
         4 Daniel       M         28 Manager         18000
         5 Erwin        M         27 Salesman        12000

You can also use sorting using ORDER BY clause:

select * from employee order by sal;

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         1 Adam         M         22 Clerk            8000
         2 Amy          F         23 Clerk            8500
         3 Casper       M         25 Salesman        10500
         5 Erwin        M         27 Salesman        12000
         4 Daniel       M         28 Manager         18000

You can also see the data in descending order of salary by using DESC:

select * from employee order by sal desc;

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         4 Daniel       M         28 Manager         18000
         5 Erwin        M         27 Salesman        12000
         3 Casper       M         25 Salesman        10500
         2 Amy          F         23 Clerk            8500
         1 Adam         M         22 Clerk            8000

5. Viewing selected records from a table

Below is the query to view employee details having salary greater than 10000:

select * from employee where sal>10000;

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         3 Casper       M         25 Salesman        10500
         4 Daniel       M         28 Manager         18000
         5 Erwin        M         27 Salesman        12000

6. Viewing selected records from a table using multiple conditions

Below is the query to view employee details having salary greater than 10000 and their designation should not be 'Manager'

select * from employee where sal>10000 and desg <> 'Manager';

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         3 Casper       M         25 Salesman        10500
         5 Erwin        M         27 Salesman        12000

7. Updating data in a table

If you want to change the age of employee 'Erwin' to 24 then fire the below query

update employee set age=24 where emp_name = 'Erwin';

After updating, the table data will be

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         1 Adam         M         22 Clerk            8000
         2 Amy          F         23 Clerk            8500
         3 Casper       M         25 Salesman        10500
         4 Daniel       M         28 Manager         18000
         5 Erwin        M         24 Salesman        12000

8. Deleting records from a table

Use following query to delete selected records from a table

delete from employee where emp_name = 'Casper';

After deleting, the table data will be

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         1 Adam         M         22 Clerk            8000
         2 Amy          F         23 Clerk            8500
         4 Daniel       M         28 Manager         18000
         5 Erwin        M         24 Salesman        12000

9. Viewing records from a table without knowing exact value of columns

Fire below query if you want to search employees whose name starts with 'A'

select * from employee where emp_name like'A%';

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         1 Adam         M         22 Clerk            8000
         2 Amy          F         23 Clerk            8500

10. Viewing selected columns from a table

If you want to see employee number, employee name and age then fire the below query

select emp_no,emp_name,age from employee;

    EMP_NO EMP_NAME            AGE
---------- ------------ ----------
         1 Adam                 22
         2 Amy                  23
         4 Daniel               28
         5 Erwin                24