Structured Query Language

 

# Introduction

• File System vs. Database Management Systems: Application programs are used to access data in a file system, while database management systems utilize query languages to access and manipulate data from the database.
• SQL as a Query Language: SQL is the most popular query language used by major relational database management systems such as MySQL, ORACLE, SQL Server, etc.
• Ease of Learning and Usage: SQL is easy to learn as its statements comprise descriptive English words and are not case sensitive. It allows for the creation and interaction with a database easily.
• Functionality: SQL not only allows querying, but also provides statements for defining the structure of the data, manipulating data in the database, declaring constraints, and retrieving data in various ways based on requirements.

Here are the important points to be kept in mind while using SQL:

• Case Insensitivity: SQL is case insensitive; for example, the column names 'salary' and 'SALARY' are treated as the same in SQL.
• Semicolon Usage: It is important to always end SQL statements with a semicolon (;).

Advantages of MySQL :

• SQL is portable It is not platform dependent, it can be used in all types of devices; PCs, laptops and even mobile phones also.
• High speed SQL queries can be used to retrieve large amounts of records from a database quickly and efficiently.
• Easy to learn and understand SQL generally consists of English language statements and it is very easy to learn and understand.
• SQL is used for relational database SQL is widely used for relational database.
• SQL act as both programming language and interactive language SQL can do both jobs of being programming and interactive language at the same time.
• Client/Server language SQL provides client server architecture. It is used fo linking front end computers and back end databases.

# SQL Statements

SQL command or statement is a special kind of sentence that contains clauses and all end with a semicolon(;) just as a sentence ends with a period.

There are four types of SQL statements :

(a) DDL (Data Definition Language)

It provides statements for creation and deletion of the database tables, views, etc.

The DDL provides a set of definitions to specify the storage structure in a database system.

 Some DDL statements are as follows:

1. CREATE: This command is used to create new databases, tables, views, indexes, and stored procedures.
2. ALTER: The ALTER command is used to modify the structure of an existing database object, such as adding or dropping columns from a table.
3. DROP: It is used to delete databases, tables, views, and indexes.
4. RENAME: The RENAME command is used to rename an existing database object.

(b) DML (Data Manipulation language)

It provides statements for manipulating the database objects. It is used to query the databases for information retrieval.

Some DML statements are as follows:

• INSERT: used to insert data into a table.
• SELECT: used to retrieve data from a database.
• UPDATE: used to update existing data within a table.
• DELETE: used to delete all records from a table.

(c) DCL (Data Control Language)

It is used to assign security levels in a database, which involves multiple user setups. They are used to grant defined role and access privileges to the users.

Some DCL statements are as follows:

• GRANT: used to give user's access privileges to database.
• REVOKE: used to withdraw access privileges given with grant command.

(d) TCL (Transaction Control Language)

It is used for controlling the transactions in a database system. Theses are also used to manage the changes made by DML.

Some TCL statements are as follows:

• COMMIT used to save the work done.
• SAVEPOINT used to identify a point in a transaction to which you can later rollback.
• ROLLBACK used to restore database to original since the last COMMIT.
• SET TRANSACTION establishes properties for he current transaction.