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