Home > java > Basic sql queries examples

Basic sql queries examples

oracle sql

To learn before Basic SQL queries examples, we need to know Some basic things.

Database: Collection of object in the form of table.

Table: it is an object of the database where data stored in structural manners. Collection of records.

Records: Collection of data.

NOTE: SQL query is not case-sensitive. i.e ‘a’ and ‘A’ both are treated as same.


Oracle Database Software Downloads: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index-092322.html

To create, show, use and drop DATABASE


SQL>create database DB_t4tution;
SQL>show databases;
SQL>use DB_t4tution;
SQL>drop database DB_t4tution;

To create New Table


SQL> create table emp(
     eid int,ename varchar(10),
     salary decimal(10,5),
     address varchar(10) ,
     primary key(eid)
    );

Table created.

SQL> desc emp;
Name              Null?   Type
---------------- -------- -------------
EID              NOT NULL NUMBER(38)
ENAME                     VARCHAR2(10)
SALARY                    NUMBER(10,5)
ADDRESS                   VARCHAR2(10)

To create new table with help of existing table: here existing table is “emp” and new table is “dept”


SQL> create table dept as select eid,ename,salary from emp;

Table created.
     
SQL> desc dept;
Name           Null?    Type
-------------- -------- ------------
EID                     NUMBER(38)
ENAME                   VARCHAR2(10)
SALARY                  NUMBER(10,5)

To show table and its property


SQL>desc emp;

To drop table completely along with data: it means delete everything data as well as table structure


SQL>drop table emp;

To Alter / Change property of a Table


Before modification of table structure

SQL> desc dept; 
Name           Null?    Type 
-------------- -------- ------------ 
EID                     NUMBER(38) 
ENAME                   VARCHAR2(10) 
SALARY                  NUMBER(10,5)

To modify the existing column

SQL> alter table dept modify eid number(5) primary key;

Table altered.

Before modification of table structure:

SQL> desc dept;
Name         Null?    Type
----------- -------- ----------------------
EID          NOT NULL NUMBER(5)
ENAME                 VARCHAR2(10)
SALARY                NUMBER(10,5)

To add the new column to the existing table


SQL> alter table dept add deptid number(4) not null;

Table altered.

SQL> desc dept;
Name        Null?    Type
----------- -------- ----------------
EID         NOT NULL NUMBER(5)
ENAME                VARCHAR2(10)
SALARY               NUMBER(10,5)
DEPTID      NOT NULL NUMBER(4)

SQL>

INSERT Query: Insert data into Table


SQL> insert into emp values(1,'John',50000,'LA');

1 row created.

SQL> select * from emp;

EID  ENAME   SALARY    ADDRESS
---  ------  --------- ----------
1    John    50000      LA

SELECT Query: Show data of Table


SQL> select * from emp;

EID        ENAME      SALARY     ADDRESS
---------- ---------- ---------- ----------
1          John       50000      LA
SQL> select eid,ename,address from emp;

EID        ENAME      ADDRESS
---------- ---------- ----------
1          John       LA
2          Mark       NY

SQL>

WHERE Clause: select with some condition


SQL> select eid,ename,address from emp where salary=50000;

EID        ENAME      ADDRESS
---------- ---------- ----------
1          John       LA

SQL>

AND and OR Conjunctive Operators


SQL> select * from emp ;

EID        ENAME      SALARY     ADDRESS
---------- ---------- ---------- ----------
1          John       50000      LA
2          Mark       60000      NY
3          Clark      60000      NY
4          Kiara      60000      LA

SQL> select * from emp where salary=60000 and address='LA';

EID        ENAME      SALARY     ADDRESS
---------- ---------- ---------- ----------
4          Kiara      60000       LA

SQL> select * from emp where salary=50000 or ename='Mark';

EID        ENAME      SALARY     ADDRESS
---------- ---------- ---------- ----------
1          John       50000      LA
2          Mark       60000      NY

SQL>

UPDATE Query


SQL> select * from emp;

EID        ENAME      SALARY     ADDRESS
---------- ---------- ---------- ----------
1          John       50000      LA
2          Mark       60000      NY
3          Clark      60000      NY
4          Kiara      60000      LA

SQL> update emp set salary=65000 where eid=3;

1 row updated.

SQL> select * from emp;

EID        ENAME      SALARY     ADDRESS
---------- ---------- ---------- ----------
1          John       50000      LA
2          Mark       60000      NY
3          Clark      65000      NY
4          Kiara      60000      LA

SQL>

DELETE Query


DELETE FROM EMPLOYEE WHERE ID = 6;


Delete complete data from Table

DELETE FROM EMPLOYEE;

LIKE Clause


Below query display all the records from the EMPLOYEE  table, where the SALARY starts with 200

SELECT * FROM EMPLOYEE 
WHERE SALARY LIKE '200%';

TOP, LIMIT or ROWNUM Clause


The SQL TOP clause is used to fetch a TOP N number or X percent records from a table.

Note − All the databases do not support the TOP clause. For example MySQL supports the LIMIT clause to fetch limited number of records while Oracle uses the ROWNUM command to fetch a limited number of records.

SELECT TOP 3 * FROM EMPLOYEE;

In MySQL

SELECT * FROM EMPLOYEE LIMIT 3;


IN Oracle

SELECT * FROM EMPLOYEE WHERE ROWNUM <= 3;

ORDER BY Clause


By Default , it work in ascending order.

SELECT * FROM EMPLOYEE ORDER BY NAME, SALARY;
SELECT * FROM EMPLOYEE ORDER BY NAME DESC;

Group By


used in collaboration with the SELECT statement to arrange identical data into groups.

SELECT NAME, SUM(SALARY) FROM EMPLOYEE GROUP BY NAME;

Distinct Keyword


used in conjunction with the SELECT statement to eliminate all the duplicate records and fetching only unique records.

SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY;
Admin
We are group of application developer and admin.We are providing good content of information about IT technology and services .

Leave a Reply

Your email address will not be published. Required fields are marked *