SQL Basics

SQL(STRUCTURED QUERY LANGUAGE)

• SQL is a Non-procedural Language.
• SQL provides a fixed set of datatypes you cannot define new datatypes.
• Every SQL statement is terminated by a semicolon.
• An SQL statement can be entered on one line or split across several lines for clarity.
• SQL is not a case sensitive; you can mix uppercase and lowercase when referencing SQL keywords (such as select and Insert), table names and column names.
• SQL determines how to perform the request.
• SQL is the official and de facto standard language for interfacing with relational database.
• SQL is Fourth Generation Language (4GLs).
• SQL*Plus is one of the Front end tool of ORACLE.
• SQL is the interfacing language between the user and the ORACLE database.
• SQL as non-procedural, does not bother how to get data - Interested in what data to get.

SQL HISTORY AND STANDARDS* Found in mid 1970's by IBM for system R.
* ORACLE incorporated it in the year 1979.
* SQL used by IBM/DBZ and DS database systems.
* SQL adopted as a standard language for RDBMS by ANSI in 1986.




SQL statements can be broadly categorized into Three types:-
1)DATA DEFINITION LANGUAGE(DDL)Which defines the structure of the data. The statements are CREATE, ALTER,etc.
2)DATA MANIPULATION LANGUAGE(DML)which retrives or modify data.
The category of DML contains four basic statements:
i>select - which retrieves rows from a table.
ii>Insert - Which adds rows to a table.
iii>Update - Which modifies existing rows in a table.
iv>Delete - Which removes rows from a table.
3)TRANSACTION CONTROL LANGUAGE(TCL)Which defines the privileges granted to database users. TCL statements are used for securing the database.TCL statement such as COMMIT,SAVEPOINT and ROLLBACK affirm or and revoke database transactions.
(commands will be explained later).




LOGIN PROCEDURE FOR SQL*PLUS
FOR LINE MODE VERSION1) C:\>SQLPLUS
2) C:\>PLUS80


FOR GRAPHICAL USER INTERFACE(GUI) MODE1) C:\>PLUS80W
2) START -> PROGRAMS -> ORACLE -> SQL*PLUS.


NOTE:-The above path are define considering most common path used, their can be other path for same procedure depending upon the way of installation of particular software.








CONNECTING TO AN ORACLE DATABASE
Before you create a new user, you will have to know how to connect to an oracle database. The dialog window prompts you for three items:


i) User Name :- The oracle database user you are using for connection.


ii) Password :- The User Password associated with username.


iii) Host string :- A database or connect string is require while you are trying to connect to an Oracle database on server.




The starter database comes with some default user names and user password which are shown in the following table. Use these default values until you establish your own user_name and user_password.They are given as ROLES, USER_NAME AND PASSWORD respectively.
1)[DBA - INTERNAL - ORACLE]
2)[CONNECT RESOURCES - SCOTT - TIGER]
3)[DBA - SYSTEM - MANAGER]
4)[DBA/CONNECT RESOURCES - SYS - CHANGE_ON_INSTALL]
5)[CONNECT RESOURCES - DEMO - DEMO]
6)[DBA - PO8 - PO8]


NOTE:-This are the commonly use user_name and password still there are many for longon purpose.


TIPS FOR USER NAME AND PASSWORDA user name must be associated with a user password. The maximum length for the user_name is 30 characters.
A user Password is a word or phrase (no space or commas allowed) associated with a user_name. Keep this password secret in order save your data from modified by other users. The maximum length is 30 Characters.


To create usersyntax:-create user identified by ;
example:-create user vivek identified by vr1;
where vivek is user and password is vr1.

To Give Rights after creating userssyntax:- grant to ;
grant on to ;
example:- 1)grant DBA to vivek;
where DBA is Right and vivek is user_name.
2)grant update,delete on library to nihar;

To Remove the Grant given to userssyntax :- revoke on from
;
example :- revoke insert on library from tushar;
where Right is insert table is library and user is tushar.

To see Rights of userssyntax :- show all user;

To Connect to another user from current loginsyntax :- connect ;


To see the name of user in which you are currently been logged onsyntax :- select user from dual;
or
show user;

DUAL:- DUAL is usually a pseudocolumn which doesn't require any argument, It can be considered as built-in value that returns specified piece of information by quering a table.
for example:-sysdate always returns the current date and time. similarly here it provides information about the current user who is connected to the oracle database.


Before Ending this topic let us learn common command which is require frequently that is
Command To Clear Screan
syntax
cl scr;

Posted in: SQL