SQL commands : Fundamentals of SQL Programming

sql commandsSQL is a query language used to access database. It is a special purpose programming language used to manage database systems. Based on Tuple relations, it uses queries to manage data. In addition, it uses distributed databases which makes it the first choice for PC databases. SQL stores values in the form of rows and columns. SQL queries are also used to store and manipulate data. Every table in the database has a name for instance like the table below has the name “Person”.

Person

S1
Want to know more about SQL programming? Take a class at Udemy.com

SQL commands are directions or instructions for making contact with the database and for performing different tasks that work with records. Depending on functionality, the SQL commands are divided into four main groups:

1) Data Definition Language (DDL):

For defining the database, schema data definition language statements are used. Data Definition Language (DDL) uses different commands to create, drop and modify the structure of the database. DDL generally includes the commands CREATE, RENAME, TRUNCATE, ALTER and DROP.

CREATE command:

The CREATE command is of two types. First is CREATE DATABASE and second is CREATE TABLE command. CREATE DATABASE is used for creating new database whereas CREATE TABLE command is used for adding a new table into the existing database. Without creating a database you won’t be able to create a table. Therefore, we need to create a database first and then we create a table into that database.

Learn SQL from scratch at Udemy.com

General syntax for CREATE DATABASE:

CREATE DATABASE databasename; 

General syntax for CREATE TABLE:

CREATE TABLE table_name

(

Columnname1 datatype(size),

Columnname2 datatype(size),

Columnname3 datatype(size),

…..

);

Learn SQL programming in 6 days through a tutorial at Udemy.com

Example:

CREATE DATABASE my_first;

We have created our database. Now we have to create our table into this database.

CREATE TABLE Person

(

PersonID int,PersonName varchar(250), City varchar(250), Country varchar(250)

);

S2

ALTER TABLE command:

This command can be used for adding and deleting columns from an existing table.

General syntax:

ALTER TABLE table_name  ADD column_name datatype

This syntax is used to add column.

ALTER TABLE table_name DROP COLUMN column_name

This syntax will delete the column.

DROP TABLE command:

DROP TABLE command can be used to delete the entire table from database. DROP TABLE will completely delete the table contents including its structure and schema.

General syntax:

DROP TABLE Person;

This will completely delete the table “Person” from the database.

RENAME command:

RENAME command can be used to rename the table.

General syntax:

RENAME TABLE {table_name} TO {new table_name};

 

Example:

RENAME TABLE Person TO Workers;

TRUNCATE command:

TRUNCATE command deletes the complete data from the table. Unlike DROP command, TRUNCATE command saves the structure and schema of the table.

General syntax:

TRUNCATE TABLE table_name;

Data Manipulation Language (DML):

To retrieve, delete, store and modify the data from table Data Manipulation Language, commands are used. The commands which are used by the DML generally includes SELECT, INSERT,UPDATE and DELETE. When read only queries are performed it is sometimes considered as the component of DML.

SELECT command:

SELECT command is used to retrieve the data or records from the database table. It can be used to retrieve complete data or any particular record, which is required for the task.

General syntax:

SELECT * FROM table_name;

Example:

Let us take our “Person” table for this example.

SELECT * FROM Person;

This will retrieve the complete data from the “Person” table.

INSERT command:

INSERT INTO command is used to insert the records into the database table. INSERT INTO command basically adds rows and columns into the table.

General syntax:

INSERT INTO table_name(column1,column2,column3,…..) VALUES (value1,value2,value3,….);

Example:

Let us take our table “Person” for this.

INSERT INTO name(PersonID, PersonName , City , Country) VALUES (‘3’,’Nick’,’Naples’,’Italy’);

S3


UPDATE command:

UPDATE command is used to update the present records in the database table. UPDATE command uses WHERE command to specify which row would be affected and if we don’t use WHERE clause then the whole data of rows would be affected.

General Syntax:

UPDATE table-name

SET column_1=value1,column_2=value2,column_3=value3,……. WHERE any column=any value;

Example:

UPDATE Person SET PersonName=’Brat’, City=’Berlin’ WHERE PersonName=’Angela’;

DELETE commands:

DELETE command is used for deleting rows from the table. It can also delete all the data from the table without deleting the database table and it also protects the structure and attributes of the table.

General Syntax:

DELETE FROM table_name  WHERE any column=any value;

And the syntax for deleting whole data from the table is

DELETE * FROM table_name;

Example:

DELETE FROM Person WHERE PersonName=’brat’;

Transaction Control Language (TCL):

It is usual for the records or data to be affected sometimes when some changes occur. Therefore, Transaction Control Language, also known as TCL is used to manage any transactions in the database. Basically, these commands are helpful to manage any changes made by the DML statements. In addition, it allows these statements to group together.

The commands it includes are COMMIT, ROLLBACK, and SAVEPOINT.

COMMIT command:

This command is generally used to keep the information safe and save any transactions into the database permanently when transactions occur in the database.

General syntax:

COMMIT;

ROLLBACK command:

For storing the database into the last committed state ROLLBACK command is used. For jumping into the savepoint in the transaction sometimes, this command  is also used along with the savepoint command. In addition, ROLLBACK command is also used undo transactions that are not saved into the database.

GENERAL syntax is:

ROLLBACK to savepoint-name;

SAVEPOINT command:

This command is used to create a checkpoint in the database so that you can rollback to the same point whenever required.

General syntax:

SAVEPOINT savepoint-name;

Data Control Language (DCL):

Databases are always filled with different kinds of information and data, which should be kept secured and safe from different threats on the Internet. Therefore, DCL commands are used to provide security.  It allows the administrator of the database to manage the security access. It generally uses GRANT and REVOKE command for providing security to the database.

GRANT command:

The administrator of the database objects have the power of providing or removing the privileges on the database objects.

General syntax:

GRANT privilege-name ON object-name

TO {user-name |PUBLIC| role-name} [with GRANT option]

Privilege name is privilege given to user.

Object name is name of the object of database.

GRANT option lets one user to give access rights to some other user.

 

REVOKE command:

REVOKE command is used for removing access or privileges given to the user from database objects. Privileges basically define the access rights given to the user on database objects. There are two types of privileges

1) System privileges

2) Object privileges

General syntax:

REVOKE privilege-name ON object-name

FROM {user-name |PUBLIC| Role-name}