how to write sql queries

Last updated on June 21st, 2018 at 05:59 pm

What is SQL?

Relational Database Management system(RDBMS) contains many tables . These tables are the basic storage in Database. Tables contains the information about something in the real world for example customers ,employees ,deptTables in the database contain zero to many rows.

SQL which stands for “Structured Query Language” which is pronounced as “SQL” or “sequel . SQL is a query language used for accessing and modifying information in one or more data tables and rows of a database.

Sql has  the following advantages
1) Efficient
2) Simple to write
3) Functionally complete as they can be used to completely retrieve  and manipulate  the data in the tables

Related: Oracle sql decode processing

It is an ANSI/ISO standard. It has become a Standard Universal Language used by most of the relational database management systems (RDBMS). Some of the RDBMS systems are: Oracle, Microsoft SQL server, Sybase ,MySQL, Postgres ,teradata etc.

Some of the sql basics which will help in writing sql queries

Select : It is used to retrieve data from the RDBMS
Insert: It is used to insert new rows in the RDBMS
Update: It is used to update the rows in the RDBMS

It can be said that , SQL is a non-procedural, English-like language that processes data in groups of records rather than one record at a time.

How to write sql queries

1) We must first understand the requirement i.e which column are required,what all tables will be used etc
2) All RDBMS  have same Sql format but few things in syntax may differ.So we should refer the specfic database manual to learn about it
3) If we are just retrieving the data,then select is the first keyword to use
a) We must describe the table from where data has to retrive
b) We choose the column according to the requirement
c) If any filter condition is there, we should specify it
d)if  multiple tables are there,then join condition must be used

e) Filtering can be done using where clause

g) order by can be used to present the data in ascending or descending order

h) group by function can be used to find sum,average etc

Related: Oracle sql tutorial: Basic Sql statement

Sql Queries Examples

1) select emp_id,sal from emp where sal > 10000;

Here emp_id,sal are the column choosen
emp is the table
where sal > 10000 is the filter condition

2) If we are manipulating data in the table,then update or insert need to be used

Example

update emp set sal=120000 where emp_id=2;

Here

emp is the table
update is the keyword for manipulation
set  is the keyword used in conjunction with update
sal=12000 is  the manipulation
where emp_id=2 is the filter condition

insert into emp (’emp_name’,’emp_id’,’dept_no’,’salary’) values (‘Tom hanks’, 569797,45,1000000);

Here

we are inserting into emp table.

3) If multiple tables are involved,we may need join condition

Example

select emp_name,emp_no,dept_no,dept_name from emp ,dept where emp.dept_no=dept.dept_no

here

emp and dept are tables

We are selecting records where dept_no is equals on both the tables.It is an example of inner join

These are different types of joins available in SQL −

INNER JOIN − returns rows when there is a match in both tables.

LEFT JOIN − returns all rows from the left table, even if there are no matches in the right table.

RIGHT JOIN − returns all rows from the right table, even if there are no matches in the left table.

FULL JOIN − returns rows when there is a match in one of the tables.

SELF JOIN − is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

CARTESIAN JOIN − returns the Cartesian product of the sets of records from the two or more joined tables.

4) If we need to retrieve data group by some column, then group by is used

SELECT dept, SUM (salary)  FROM employee  GROUP BY dept;

Here we are summing of the salaries of all the employees  dept-wise

5)If you want to sort the emp table by salary of the employee, the sql query would be.

SELECT name, salary FROM emp ORDER BY salary;

6) if we want to delete any rows ,the sql query would

delete from emp where emp_id=’798696′;


Leave a Reply