Tuesday, 4 February 2020

Structured Query Language


Structured Query Language

? The Structured Query Language (SQL) is a 4th Generation Language (4GL) generally used
for querying the database. Following is a consolidated list of SQL statements:
? SELECT Data retrieval statement
? INSERT Add rows
? UPDATE Update row
? DELETE Delete rows
? CREATE Create new tables/views
? ALTER Alter the schema or view
? DROP Delete the table
? RENAME Rename a table
? COMMIT Buffer to disk
? ROLLBACK Rollback the changes made
? GRANT Assign privileges to users
? REVOKE Remove the assigned privileges
? The basic commands that can be used under Oracle 8i environment were discussed. For
example, @,/are some of the commands.
? Oracle9i SQL *PLUS offers a rich set of data types like integer, float, number, date, etc.
? The SELECT statements is used retrieve a set of rows from a specified table depending
upon the WHERE clause.
? SQL supports many functions that can be used with dates, numbers, and character strings.
You can also convert from one data type to another implicitly or explicitly. For example,
TO_DATE, TO_CHAR, and so on.
? Another set of functions that are used for data manipulation are group functions like AVG,
? To join two or more tables we have equi join, self join, and outer (uses a + sign). To write
certain complicated queries, use of subqueries makes things easier. A subquery is written
in the where condition of the outer query along with operators like =, IN, ANY, ALL or
? SQL also supports set operations like UNION, DIFFERENCE, and MINUS. There a number
of database objects that can be created and altered:
? CREATE TABLE Creating a new table
? CREATE VIEW Creating a new view
? CREATE SEQUENCE Creating an automatic sequence of numbers
? INDEX Creating an index on columns other than primary key for
efficient data retrieval
? The two statements GRANT and REVOKE are important for assigning or removing
privileges of a table or column or any database object.
? It is possible to set locks to tables in a multi-user environment for efficient security
purpose. Finally, several additional examples were discussed on SQL.

Creating table: To create a table, the create statement specifies the name of the table and the
names and data types of each column of the table.
Data Definition Language (DDL): This part of SQL supports the creation, deletion and modification
of tables. Integrity constraints can be defined on tables, either when the table is created or later.
The DDL also provides commands for specifying access rights to tables. The commercial
implementations also provide commands for creating and deleting indexes.
Data Manipulation Language (DML): This part of SQL allows users to pose queries, insert
tuples, delete tuples and modify tuples (rows) in the database.
Select clause: SELECT is a command from DML language which is used to select specific columns
of the tables.

No comments:

Post a Comment