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 ,dept Tables 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 specific 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 retrieve
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 chosen
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';