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:
- 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.
- (i). event – The change in the database that activates the trigger is event.
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 SELECT, UPDATE, DELETE, INSERT, WHERE) 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:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
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.
| Statement | Description |
|---|---|
| CREATE | Create new database/table. |
| ALTER | Modifies the structure of database/table. |
| DROP | Deletes a database/table. |
| TRUNCATE | Remove all table records including allocated table spaces. |
| RENAME | Rename the database/table. |
Data Manipulation Language (DML)
Data manipulation statement are use for managing data within table object.
| Statement | Description |
|---|---|
| SELECT | Retrieve data from the table. |
| INSERT | Insert data into a table. |
| UPDATE | Updates existing data with new data within a table. |
| DELETE | Deletes the records rows from the table. |
| MERGE | MERGE (also called UPSERT) statements to INSERT new records or UPDATE existing records depending on condition matches or not. |
| LOCK TABLE | LOCK 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.
| Statement | Description |
|---|---|
| GRANT | Gives privileges to user for accessing database data. |
| REVOKE | Take back for given privileges. |
| ANALYZE | ANALYZE statement to collect statistics information about index, cluster, table. |
| AUDIT | To track the occurrence of a specific SQL statement or all SQL statements during the user sessions. |
| COMMENT | Write comment to the data table. |
Transaction Control Statement (TCS)
Transaction control statement are use to apply the changes permanently save into database.
| Statement | Description |
|---|---|
| COMMIT | Permanent work save into database. |
| ROLLBACK | Restore database to original form since the last COMMIT. |
| SAVEPOINT | Create SAVEPOINT for later use ROLLBACK the new changes. |
| SET TRANSACTION | SET 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.
| Statement | Description |
|---|---|
| ALTER SESSION | ALTER SESSION statement to modify conditions or parameters that are affect to your database connection. |
| SET ROLE | SET 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
