Sql is a database computer language designed for the retrieval and management of data in relational database. Sql stands for Structured Query Language.
this reference has been prepared for the beginners to help them understand the basic to advanced concepts related to Sql languages.
Sql language is an extension of Sql language developed by Oracle Corporation and the full name it is the Procedural Language/Structured Query Language.
Sql language is a procedural programming language which allows data manipulation and sql query to be included in blocks. Sql can be used to group multiple instructions in a single block and sending the entire block to the server in a single call
Main sections on language Sql are:
1. DML Statements
2. Select Query
3. Table Joins
4. Stored Procedures
5. Functions
6. Collections and Records
7. Cursors
8. Exception Handling
9. Triggers
10Views
11.Error Messages
now
DML Statements
Data Manipulation Language,In this page we have a list of Sql Data Manipulation Language (DML) statements that you can use as a small introduction to dml statements.
- Select
Syntax:
|
1
2
3
|
select * from table;
select column_name1,
column_name2 from table_name;
select column_name1,
column_name2 from table_name where condition;
|
Examples:
|
1
2
3
|
select * from employees;
select * from employees where dep_id=10;
select name, salary from employees where dep_id=10;
|
Insert statement is part of Data Manipulation Language and allows the user to insert a single record or multiple records into a table.
Syntax:
|
1
2
3
|
INSERT INTO table VALUES (value1, value2,
value3, ...);
INSERT INTO table (column1, column2,
column3, ...) VALUES (value1, value2, value3, ...);
INSERT INTO table1 (column1,
column2) SELECT column1, column2 FROM table2;
|
Example 1:
|
1
2
|
INSERT INTO employees VALUES ('Steve', 20, 250);
INSERT INTO employees (name,
dep_id, salary) VALUES ('David', 10, 300);
|
Example 2:
|
1
2
3
4
5
|
INSERT ALL
INTO employees (name,
dept_id, salary) VALUES ('Anne', 20, 250)
INTO employees (name,
dept_id, salary) VALUES ('George', 10, 200)
INTO employees (name,
dept_id, salary) VALUES ('Olivia', 40, 400)
SELECT * FROM dual;
|
Example 3:
|
1
|
INSERT INTO products(id, name,
price) SELECT id, name, price FROM old_products;
|
- Update
Update statement is part of Data Manipulation Language and allows the user to update a single record or multiple records in a table.
Syntax:
|
1
2
|
UPDATE table SET column1 = new_value1;
UPDATE table SET column1 = value1, column2 = value2, column3
= value3, ... WHERE condition;
|
Example 1:
|
1
2
|
UPDATE employees SET salary = 100;
UPDATE employees SET salary = 2000, city_name='London' WHERE dept_id=10;
|
Example 2:
|
1
2
3
|
UPDATE employees e
SET e.city_id = (SELECT c.city_id FROM cities c WHERE c.city_name='London')
WHERE EXISTS (SELECT d.dept_id FROM departments d WHERE d.dept_id=e.dept_id and d.dept_id=10);
|
- Delete
Delete statement is part of Data Manipulation Language and allows the user to delete a single record or multiple records in a table.
|
1
2
3
|
DELETE FROM employees; -- delete all records
DELETE FROM employees WHERE name='Olivia' and id=7900 ; -- delete single record;
DELETE FROM employees WHERE dept_id=10; --
delete single or multiple records;
|
Example 2:
|
1
2
3
|
DELETE FROM employees e
WHERE e.city_id = (SELECT c.city_id FROM cities c WHERE c.city_name='London')
AND EXISTS (SELECT d.dept_id FROM departments d WHERE d.dept_id=e.dept_id and d.dept_id=10);
|
