Sql Statements Types

Sql statements

What is SQL?

  • SQL stands for Structured Query Language
  • SQL lets you access and manipulate databases
  • SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system

SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database


What Can SQL do?

  • SQL can execute queries against a database
  • SQL can retrieve data from a database
  • SQL can insert records in a database
  • SQL can update records in a database
  • SQL can delete records from a database
  • SQL can create new databases
  • SQL can create new tables in a database
  • SQL can create stored procedures in a database
  • SQL can create views in a database
  • SQL can set permissions on tables, procedures, and views

Features of Structured Query Language (SQL

 is the standard language used for writing queries in a databases. It was approved by ISO (International Standard Organization) and ANSI(American National Standards Institute). 

SQL contains of some important features and they are: 

  1. Data Definition language (DDL): 
    It contains of commands which defines the data. The commands are: 
  • create: It is used to create a table. 
    Syntax: 
create table 
tablename(attribute1 datatype......attributen datatype); 
  • drop: It is used to delete the table including all the attributes. 
    Syntax: 
drop table tablename; 
  • alter: alter is a reserve word which modifies the structure of the table. 
    Syntax: 
alter table 
tablename add(new column1 datatype......new columnx datatype); 
  • rename: A table name can be changed using the reserver ‘rename’ 
    Syntax: 
rename old table name to new table name; 

        2. Data Manipulation Language (DML): 
            Data Manipulation Language contains commands used to manipulate the data. 
            The commands are: 

  • insert: This command is generally used after the create command to insert a set of values into the table. 
    Syntax: 
insert into tablename values(attribute1 datatype);
:
:
:
insert into tablename values (attributen datatype); 
  • delete: A command used to delete particular tuples or rows or cardinality from the table. 
    Syntax: 
delete from tablename where condition; 
  • update: It updates the tuples in a table. 
    Syntax: 
update tablename set tuplename='attributename'; 
  • Triggers: 
    Triggers are actions performed when certain conditions are met on the data. A trigger contains of three parts. 
    • (i). event – The change in the database that activates the trigger is event. 
       
    • (ii). condition – A query or test that is run when the trigger is activated. 
       
    • (iii). action – A procedure that is executed when trigger is activated and the condition met is true. 
       

         2. Client server execution and remote database access: 
             Client server technology maintains a many to one relationship of clients(many) and server(one). We have commands in SQL that control how a client                          application can access the database over a network. 

          3. Security and authentication: 
              SQL provides a mechanism to control the database meaning it makes sure that only the particular details of the database is to be shown the user and the                 original database is secured by DBMS. 

          4. Embedded SQL: 
                SQL provides the feature of embedding host languages such as C, COBOL, Java for query from their language at runtime. 

          5. Transaction Control Language: 
                Transactions are an important element of DBMS and to control the transactions, TCL is used which has commands like commit, rollback and savepoint. 

  • commit: It saves the database at any point whenever database is consistent. 
    Syntax: 
commit; 
  • rollback: It rollbacks/undo to the previous point of the transaction. 
    Syntax: 
rollback; 
  • savepoint: It goes back to the previous transaction without going back to the entire transaction. 
    Syntax: 
savepoint; 

        6. Advanced SQL: 
            The current features include OOP ones like recursive queries, decision supporting queries and also query supporting areas like data mining, spatial data and             XML(Xtensible Markup Language).


SQL is a Standard – BUT….

Although SQL is an ANSI/ISO standard, there are different versions of the SQL language.

However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECTUPDATEDELETEINSERTWHERE) in a similar manner.

SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system

Using SQL in Your Web Site

To build a web site that shows data from a database, you will need:

  • An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
  • To use a server-side scripting language, like PHP or ASP
  • To use SQL to get the data you want
  • To use HTML / CSS to style the page

SQL Syntax


Database Tables

A database most often contains one or more tables. Each table is identified by a name (e.g. “Customers” or “Orders”). Tables contain records (rows) with data.

In this tutorial we will use the well-known Northwind sample database (included in MS Access and MS SQL Server).

Below is a selection from the “Customers” table:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1
Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico
4
Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK
5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22Sweden

The table above contains five records (one for each customer) and seven columns (CustomerID, CustomerName, ContactName, Address, City, PostalCode, and Country).

Type of SQL Statements

Type of SQL statements are divided into five different categories: Data definition language (DDL), Data manipulation language (DML), Data Control Language (DCL), Transaction Control Statement (TCS), Session Control Statements (SCS).

Data Definition Language (DDL)

Data definition statement are use to define the database structure or table.

StatementDescription
CREATECreate new database/table.
ALTERModifies the structure of database/table.
DROPDeletes a database/table.
TRUNCATERemove all table records including allocated table spaces.
RENAMERename the database/table.

Data Manipulation Language (DML)

Data manipulation statement are use for managing data within table object.

StatementDescription
SELECTRetrieve data from the table.
INSERTInsert data into a table.
UPDATEUpdates existing data with new data within a table.
DELETEDeletes the records rows from the table.
MERGEMERGE (also called UPSERT) statements to INSERT new records or UPDATE existing records depending on condition matches or not.
LOCK TABLELOCK TABLE statement to lock one or more tables in a specified mode. Table access denied to a other users for the duration of your table operation.
CALL
EXPLAIN PLAN
Statements are supported in PL/SQL only for executed dynamically. CALL a PL/SQL program or EXPLAIN PATH access the data path.

Data Control Language (DCL)

Data control statement are use to give privileges to access limited data.

StatementDescription
GRANTGives privileges to user for accessing database data.
REVOKETake back for given privileges.
ANALYZEANALYZE statement to collect statistics information about index, cluster, table.
AUDITTo track the occurrence of a specific SQL statement or all SQL statements during the user sessions.
COMMENTWrite comment to the data table.

Transaction Control Statement (TCS)

Transaction control statement are use to apply the changes permanently save into database.

StatementDescription
COMMITPermanent work save into database.
ROLLBACKRestore database to original form since the last COMMIT.
SAVEPOINTCreate SAVEPOINT for later use ROLLBACK the new changes.
SET TRANSACTIONSET TRANSACTION command set the transaction properties such as read-write/read only access.

PL/SQL Transaction commit, rollback, savepoint, autocommit, Set Transaction 

Session Control Statement (SCS)

Session control statement are manage properties dynamically of a user session.

StatementDescription
ALTER SESSIONALTER SESSION statement to modify conditions or parameters that are affect to your database connection.
SET ROLESET ROLE statement to enable or disable the roles that are currently enabled for the session.

Advantages of SQL

There are the following advantages of SQL:

High speed

Using the SQL queries, the user can quickly and efficiently retrieve a large amount of records from a database.

No coding needed

In the standard SQL, it is very easy to manage the database system. It doesn’t require a substantial amount of code to manage the database system.

Well defined standards

Long established are used by the SQL databases that are being used by ISO and ANSI.

Portability

SQL can be used in laptop, PCs, server and even some mobile phones.

Interactive language

SQL is a domain language used to communicate with the database. It is also used to receive answers to the complex questions in seconds.

Multiple data view

Using the SQL language, the users can make different views of the database structure.

Drop your comment

Related Post

Leave a Reply

Your email address will not be published.