Powered by Blogger.

Introduction of SQL

,
SQL, 'Structured Query Language', is a programming language designed to manage data stored in relational databases. SQL operates through simple, declarative statements. This keeps data accurate and secure, and helps maintain the integrity of databases, regardless of size. SQL can execute queries against a database
What SQL can do
  • SQL can retrieve data from a database
  • SQL can insert records in a database
  • SQL can update records in a database
  • SQL can delete records from a database
  • SQL can create new databases
  • SQL can create new tables in a database
  • SQL can create stored procedures in a database
  • SQL can create views in a database
  • SQL can set permissions on tables, procedures, and views
The Most Important SQL Commands
  • SELECT - extracts data from a database
  • UPDATE - updates data in a database
  • DELETE - deletes data from a database
  • INSERT INTO - inserts new data into a database
  • CREATE DATABASE - creates a new database
  • ALTER DATABASE - modifies a database
  • CREATE TABLE - creates a new table
  • ALTER TABLE - modifies a table
  • DROP TABLE - deletes a table
  • CREATE INDEX - creates an index (search key)
  • DROP INDEX - deletes an index
SELECT:  SELECT statements allow you to Fetch  cell data in a table.

SELECT column_name(s) FROM table_name WHERE column_1 = value_1 AND column_2 = value_2;

                                                                  Or

SELECT * FROM table_name WHERE column_1 = value_1 AND column_2 = value_2;
Note: 
1.       Column_name (s) are used for access some table cell name which is your write cell name.
2.       Asterisk (*) is used for access all table cell data it is used column_name (s) place.
3.       WHERE clause is used to extract only those results from a SQL statement, such as: SELECT, INSERT, UPDATE, or DELETE statement. It is not mandatory. 
UPDATE:  UPDATE statements allow you to edit rows in a table.

UPDATE table_name SET some_column = some_value WHERE some_column = some_value; 

DELETE:  DELETE statements are used to remove rows from a table.

DELETE FROM table_name WHERE some_column = some_value; 

INSERT INTO: INSERT statements are used to add a new row to a table.

INSERT INTO table_name (column_1, column_2, column_3) VALUES (value_1, 'value_2', value_3); 

CREATE DATABASE:  The CREATE DATABASE statement is used to create a new SQL database.

CREATE DATABASE databasename;

ALTER DATABASE:  The ALTER DATABASE Statement is used to change characteristics of a database.

ALTER DATABASE database_name [COLLATE collation_name ];
CREATE TABLE: CREATE TABLE creates a new table in the database. It allows you to specify the name of the table and the name of each column in the table.
CREATE TABLE table_name (column_1 datatype, column_2 datatype, column_3 datatype);
ALTER TABLE:  ALTER TABLE lets you add columns to a table in a database.
ALTER TABLE table_name ADD column datatype;

DROP TABLE: The DROP TABLE statement is used to drop an existing table in a database.

DROP TABLE table_name;

CREATE INDEX: Creates an index on a table. Duplicate values are allowed.

CREATE INDEX index_name ON table_name (column1, column2, column3,....);

DROP INDEX: The DROP INDEX statement is used to delete an index in a table.

DROP INDEX table_name.index_name;
Predicates 
Predicates use one of the operators =, <>, >, >=, <, <=, IN, BETWEEN, LIKE, IS NULL or IS NOT NULL.
IN:   IN will find any values existing in a set of candidates.

SELECT ename WHERE ename IN ('value1', 'value2', ...);

BETWEEN:  BETWEEN will find any values within a range.

SELECT ename WHERE ename BETWEEN 'value1' AND 'value2'; 

LIKE: LIKE will find a string fitting a certain description.

SELECT ename FROM emp WHERE ename LIKE 'S%'; 
Example:
SELECT ename FROM emp WHERE ename LIKE '[a-zA-Z0-9_]%';

The Most Important SQL procedures:

CREATE OR REPLACE PROCEDURE TEST_PROCEDURE_NAME AS 
CURSOR TESTCCURSOR IS   
select * from dual;
OPCR   TESTCCURSOR%ROWTYPE; 
BEGIN
 FOR OPCR IN TESTCCURSOR     
  LOOP
IF OPCR.COLL_NAME is not null THEN
 DBMS_OUTPUT.PUT_LINE(OPCR.COLL_NAME); 
--Write some Statement like insert, update, delete etc. 
  END IF;
        END LOOP;
       COMMIT ;   
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END TEST_PROCEDURE_NAME;