Skip to main content

Introduction to SQL

INTRODUCTION : -

  • SQL usually pronounced as “Sequel” stands for Structured Query Language.
  • SQL is the native language of the Oracle Server. It is the language used to communicate with the database. Again SQL consists of SQL statements and SQL*Plus commands. SQL statements are used to talk to the database. When you enter a SQL statement, it is stored in a part of the memory called the SQL Buffer and remains there until a new statement is entered. SQL*Plus is an Oracle tool that recognizes and submits 
  • SQL statements to the Oracle server for execution and contains its own command language.
Features of SQL:-

  • It is very easy to write, it can be used by all kinds of users with little or not programming experience.
  • It is a non-procedural language.
  • It reduces the amount of time required for creating and maintaining systems.
  • It is based on American National Standards Iinstitute (ANSI) standard SQL.
  • It manipulates data and tables definition in the database.
  • It is entered into SQL buffer on one or more lines.
  • It does not have a continuation character.
  • It cannot be abbreviated.
  • It uses a termination character to execute command immediately.
  • Uses functions to perform some formatting.

Features of SQL*Plus :-
  • Recognizes SQL statements and sends them to the server.
  • It accepts SQL input from files.
  • It provides a line editor for modifying SQL statements.
  • It controls environmental settings.
  • Accesses local and remote databases.
  • It doesn’t allow manipulation of values in the database.
  • It is entered one line at a time and is not stored in the SQL buffer.
  • Has a dash(-) as a continuation character if command exceed one line.
  • Doesn’t require any termination character, commands are executed immediately.
  • It uses commands to format data.
Advantages of SQL :-
  • SQL is a high level language that provides a greater degree of abstraction than procedural languages. It is designed so that the programmer can specify what data is needed but need not specify how to retrieves it.
  • Applications written in SQL can be easily ported across systems.
  • SQL as a language is independent of the way it is implemented internally.
  • The language even being simple and easy to learn can handle complex situations.
  • It is not just a query language, it can be used to define data structures, control access to the data and delete, insert, modify occurrences of the data.
  • The results to be expected are well defined i.e, there is no ambiguity about the way a query will interpret the data and produce the result.
Types of SQL :-

SQL can be classified on the basis of its various functionality, listed below.

  • Querying data.
  • Updating, inserting and deleting database objects.
  • Controlling access to the database.
  • Providing data integrity and consistency.
SQL statements are divided into following types.
  1.  Data Definition Language (DDL)
  2.  Data Manipulation Language (DML)
  3.  Transaction Control Lauguage (TCL)
  4.  Data Control Language (DCL)

    1)   Data Definition Language DDL) :-
The DDL commands enables you to perform :--
  • CREATE statement.
  • ALTER statement.
  • DROP statement.
  • RENAME statement.
  • TRUNCATE statement.
The objects that can be created, altered, dropped are :-
Cluster, Database, Database link, Function, Index, Package, Package body, Procedure, Rollback segment, Role, Sequence, Synonym, Table, User, View.

    2)  Data Manipulation Language (DML) :-

A DML command is used when we want to add, update or delete data in the database. A collection of DM statements that form a logical unit of work is called a transaction.

The DML commands are :-
  • INSERT statement.
  • UPDATE statement.
  • DELETE statement.

   3)  Transaction  Control Language (TCL) :-

As we know transaction is nothing but a set of inserts, updated and deletes (performed on the database) that form a logical unit of work. TCL is used to control these transactions i.e , whether or not a transaction should take place is controlled by TCL.

The TCL commands are :-
  • COMMIT statement.
  • ROLLBACK statement.
  • SAVEPOINT statement.

    4)  Data Control Language (DCL) :-

The DCL commands enable you to grant or revoke user privileges and roles.

The DCL commands are :-
  • GRANT statement.
  • REVOKE statement.
==================================================

Labels:

Comments

Post a Comment

Popular posts from this blog

About Oracle Buffer

About Oracle Buffer :- All Commands of SQL are typed at the SQL prompt. Only one SQL statement is managed in the SQL buffer. The current SQL statement replaces the previous SQL statement in the buffer. The SQL statements can be divided into different lines within the SQL buffer. Only one line i.e, the current line can be active at a time in the SQL buffer. At SQL prompt, editing is possible only in the current SQL buffer line. Every statement of SQL should be terminated using semi colon " ; ". One sql statement can contain only one semi colon. To run the previous or current SQL statement in the bufer type " / " at SQL prompt. To open the SQL editor type " ED " at SQL prompt.
Post a Comment
Read more

Oracle Object Oriented Concepts

An Object is a reusable application component that developers need to be aware of, rather than how it works. Object are basic entities in a system. They could represent a person, place, bank account, or any item that is handled by program. Every object consists of an attribute and one or more methods. An attribute could be any property of the object. Class:           It is a collection of attributes and functions (method) to plan the object. Object Table : ·    Object table are created by using the user defined data types. ·        In an object table each row or record is treated as an object. ·        Each row in an object table has an object Identified (OID), which is unique through out       the   database. ·       The rows or objects of an object table can be referenced by other objects with in the  database. ·    ...
Post a Comment
Read more

Filtering and Sorting data using SQL statements

  Filtering & Sorting Data using SQL     Once you know the basics ( SELECT , FROM , WHERE ), filtering and sorting helps you get the exact rows from tables .   1. Filtering with Conditions ( AND , OR , NOT )  - Use WHERE to filter rows. - Combine with logical operators :     • AND: all conditions true      • OR: any condition true      • NOT: negate condition   - - All conditions true (AND) SELECT *  FROM employees WHERE department = 'Sales'    AND salary > 50000; -- Any condition true (OR) SELECT *  FROM employees WHERE department = 'Sales'     OR department = 'Marketing'; -- Negate condition (NOT) SELECT *  FROM employees WHERE NOT department = 'HR';   2. Pattern Matching with LIKE   - % = any sequence, _ = single char     • Names starting with 'J'      • Names with 4 letters starting with 'J'   - - N...
Post a Comment
Read more