Pages

SQL

Monday, 26 March 2012



SQL - Overview

SQL tutorial gives unique learning on Structured Query Language and it helps to make practice on SQL commands which provides immediate results. SQL is a language of database, it includes database creation, deletion, fetching rows and modifying rows etc.
SQL is an ANSI (American National Standards Institute) standard but there are many different versions of the SQL language.
What is SQL?
SQL is structured Query Language which is a computer language for storing, manipulating and retrieving data stored in relational database.
SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server uses SQL as standard database language.
Also they are using different dialects, Such as:
MS SQL Server using T-SQL,
Oracle using PL/SQL,
MS Access version of SQL is called JET SQL (native format )etc
Why SQL?
Allow users to access data in relational database management systems.
Allow users to describe the data.
Allow users to define the data in database and manipulate that data.
Allow to embed within other languages using SQL modules, libraries & pre-compilers.
Allow users to create and drop databases and tables.
Allow users to create view, stored procedure, functions in a database.
Allow users to set permissions on tables, procedures, and views
History:
1970 -- Dr. E.F. "Ted" of IBM is known as the father of relational databases. He described a relational model for databases.
1974 -- Structured Query Language appeared.
1978 -- IBM worked to develop Codd's ideas and released a product named System/R.
1986 -- IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software and its later becoming Oracle.
SQL Process:
When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task.
There are various components included in the process. These components are Query Dispatcher, Optimization engines, Classic Query Engine and SQL query engine etc. Classic query engine handles all non-SQL queries but SQL query engine won't handle logical files.
Following is a simple digram showing SQL Architecture:
SQL Commands:
The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE, and DROP. These commands can be classified into groups based on their nature:
DDL - Data Definition Language:

command :
create ,alter , drop,truncate,RENAME
  emp no                empname          sal    =>  ( DATA DEFINITION)
 1                            scott               10000  =>(DATA)

   CREATE :  To create data definition
   example :  empno   empname    empsal
   ALTER : To modify the data definition
  example :  job  empno   empname
   DROP:   To drop data definition
  TRUNCATE : To reduce memory allocate for table
  it delete all the data  , it empty the table definition
  RENAME : To change name of the table
  DIFFERENCE B/W  DROP AND TRUNCATE
   TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
 DIFFERENCE B/W  DELETE  AND TRUNCATE

TRUNCATE is a DDL command whereas DELETE is a DML command.
TRUNCATE is much faster than DELETE.
DML - Data Manipulation Language:
command :insert update delete

DCL - Data Control Language:

command:   grant , revoke
grant: to give permission
revoke :to take back the permission  from user

DRL - Data RETRIEVAL  Language :  command : select

Select: to retrieve the data from data base

TCL - Transaction Control Language :  command :  commit  , rollback , save path

 commit : used to save transaction
rollback : to cancel transaction  
save path :to save the
when  you Accidently delete one record  ,want u to  restart the record .
use  command is rollback
oracle is secured and multiused system
SQL,PL/SQL those are languages  TOAD is a not a language
 sql*plus is a tool form oracle corporation
using this tool we can execute all sql commands and some extra commands called sql plus commands
oracle is a database
sql is a language
sql *plus
 difference b/w sql and sql*plus
sql:                                                                   sql*plus
language                                                            tool
developed by IBM                                            from oracle corporation
 commands must be terminated with                 not required
semicolon
 command cannot be abbreviations                   can br  abbreviations
sechema  :
                a user in oracle database is called sechema

ORACLE
   username: scott
   password: tiger
   
   when you install the oracle scott is  created entirely database
* how to connect oracle   server
 methods of open sql*plus files
using shortcut on desktop
and start menu  --> to go
command prompt     (sql*plus)
run command   (sql*plus)
to connect  to oracle server submit  following details
username : scott
password : tiger

sql> show user  enter
         user is "scott "
sql> clser enter  clear the screen
sql> exit enter
     disconnect the oracle
=> creating schema /user /account
only DNA can create schema
permission :
sql> grant connect , Resource to batch4pm
 connect  ---> connect to db
 create basic objects
like tables ,views
resource -->create advanced objects
dba --> all permissions
grant   succeed

how to change password
both server and dba can change password
sql> alter user batch4pm identified by oracle 11g;
user altered  (oracle 11g is a password)
locking account :
only dba can lock and unlock  the account
sql> alter user batch4pm account lock;
user altered
sql> alter user batch4pm account un lock ;
user altered
droping account :
only dba can drop the account
 sql>drop user <username > [cascade]
if user is empty cascade is not required
if user is not empty than cascade is required

 

Most Reading