SQL command

SQL: create command

create is a DDL SQL command used to create a table or a database in relational database management system.

---

Creating a Database

To create a database in RDBMS, create command is used. Following is the syntax,

CREATE DATABASE <DB_NAME>;

---

Example for creating Database

CREATE DATABASE Test;

The above command will create a database named Test, which will be an empty schema without any table.

To create tables in this newly created database, we can again use the create command.

---

Creating a Table

create command can also be used to create tables. Now when we create a table, we have to specify the details of the columns of the tables too. We can specify the names and datatypes of various columns in the create command itself.

Following is the syntax,

CREATE TABLE <TABLE_NAME>

(

    column_name1 datatype1,

    column_name2 datatype2,

    column_name3 datatype3,

    column_name4 datatype4

);

create table command will tell the database system to create a new table with the given table name and column information.

---

Example for creating Table

CREATE TABLE Student(

    student_id INT,

    name VARCHAR(100),

    age INT);

The above command will create a new table with name Student in the current database with 3 columns, namely student_id, name and age. Where the column student_id will only store integer, name will hold upto 100 characters and age will again store only integer value.

If you are currently not logged into your database in which you want to create the table then you can also add the database name along with table name, using a dot operator .

For example, if we have a database with name Test and we want to create a table Student in it, then we can do so using the following query:

CREATE TABLE Test.Student(

    student_id INT,

    name VARCHAR(100),

    age INT);

SQL: ALTER command

alter command is used for altering the table structure, such as,

·         to add a column to existing table

·         to rename any existing column

·         to change datatype of any column or to modify its size.

·         to drop a column from the table.

---

ALTER Command: Add a new Column

Using ALTER command we can add a column to any existing table. Following is the syntax,

ALTER TABLE table_name ADD(

    column_name datatype);

Here is an Example for this,

ALTER TABLE student ADD(

    address VARCHAR(200)

);

The above command will add a new column address to the table student, which will hold data of type varchar which is nothing but string, of length 200.

---

ALTER Command: Add multiple new Columns

Using ALTER command we can even add multiple new columns to any existing table. Following is the syntax,

ALTER TABLE table_name ADD(

    column_name1 datatype1,

    column-name2 datatype2,

    column-name3 datatype3);

Here is an Example for this,

ALTER TABLE student ADD(

    father_name VARCHAR(60),

    mother_name VARCHAR(60),

    dob DATE);

The above command will add three new columns to the student table

---

ALTER Command: Add Column with default value

ALTER command can add a new column to an existing table with a default value too. The default value is used when no value is inserted in the column. Following is the syntax,

ALTER TABLE table_name ADD(

    column-name1 datatype1 DEFAULT some_value

);

Here is an Example for this,

ALTER TABLE student ADD(

    dob DATE DEFAULT '01-Jan-99'

);

The above command will add a new column with a preset default value to the table student.

---

ALTER Command: Modify an existing Column

ALTER command can also be used to modify data type of any existing column. Following is the syntax,

ALTER TABLE table_name modify(

    column_name datatype

);

Here is an Example for this,

ALTER TABLE student MODIFY(

    address varchar(300));

Remember we added a new column address in the beginning? The above command will modify the address column of the student table, to now hold upto 300 characters.

---

ALTER Command: Rename a Column

Using ALTER command you can rename an existing column. Following is the syntax,

ALTER TABLE table_name RENAME

    old_column_name TO new_column_name;

Here is an example for this,

ALTER TABLE student RENAME

    address TO location;

The above command will rename address column to location.

---

ALTER Command: Drop a Column

ALTER command can also be used to drop or remove columns. Following is the syntax,

ALTER TABLE table_name DROP(

    column_name);

Here is an example for this,

ALTER TABLE student DROP(

    address);

The above command will drop the address column from the table student.

Truncate, Drop or Rename a Table

In this tutorial we will learn about the various DDL commands which are used to re-define the tables.

TRUNCATE command

TRUNCATE command removes all the records from a table. But this command will not destroy the table's structure. When we use TRUNCATE command on a table its (auto-increment) primary key is also initialized. Following is its syntax,

TRUNCATE TABLE table_name

Here is an example explaining it,

TRUNCATE TABLE student;

The above query will delete all the records from the table student.

In DML commands, we will study about the DELETE command which is also more or less same as the TRUNCATE command. We will also learn about the difference between the two in that tutorial.

---

DROP command

DROP command completely removes a table from the database. This command will also destroy the table structure and the data stored in it. Following is its syntax,

DROP TABLE table_name

Here is an example explaining it,

DROP TABLE student;

The above query will delete the Student table completely. It can also be used on Databases, to delete the complete database. For example, to drop a database,

DROP DATABASE Test;

The above query will drop the database with name Test from the system.

---

RENAME query

RENAME command is used to set a new name for any existing table. Following is the syntax,

RENAME TABLE old_table_name to new_table_name

Here is an example explaining it.

RENAME TABLE student to students_info;

The above query will rename the table student to students_info.

Using INSERT SQL command

Data Manipulation Language (DML) statements are used for managing data in database. DML commands are not auto-committed. It means changes made by DML command are not permanent to database, it can be rolled back.

Talking about the Insert command, whenever we post a Tweet on Twitter, the text is stored in some table, and as we post a new tweet, a new record gets inserted in that table.

---

INSERT command

Insert command is used to insert data into a table. Following is its general syntax,

INSERT INTO table_name VALUES(data1, data2, ...)

Lets see an example,

Consider a table student with the following fields.

s_id

name

age

INSERT INTO student VALUES(101, 'Adam', 15);

The above command will insert a new record into student table.

s_id	name	age
101	Adam	15

---

Insert value into only specific columns

We can use the INSERT command to insert values for only some specific columns of a row. We can specify the column names along with the values to be inserted like this,

INSERT INTO student(id, name) values(102, 'Alex');

The above SQL query will only insert id and name values in the newly inserted record.

---

Insert NULL value to a column

Both the statements below will insert NULL value into age column of the student table.

INSERT INTO student(id, name) values(102, 'Alex');

Or,

INSERT INTO Student VALUES(102,'Alex', null);

The above command will insert only two column values and the other column is set to null.

S_id	S_Name	age
101	Adam	15
102	Alex

---

Insert Default value to a column

INSERT INTO Student VALUES(103,'Chris', default)

S_id	S_Name	age
101	Adam	15
102	Alex
103	chris	14

Suppose the column age in our tabel has a default value of 14.

Also, if you run the below query, it will insert default value into the age column, whatever the default value may be.

INSERT INTO Student VALUES(103,'Chris')

Using UPDATE SQL command

Let's take an example of a real-world problem. These days, Facebook provides an option for Editingyour status update, how do you think it works? Yes, using the Update SQL command.

Let's learn about the syntax and usage of the UPDATE command.

---

UPDATE command

UPDATE command is used to update any record of data in a table. Following is its general syntax,

UPDATE table_name SET column_name = new_value WHERE some_condition;

WHERE is used to add a condition to any SQL query, we will soon study about it in detail.

Lets take a sample table student,

student_id	name	age
101	Adam	15
102	Alex
103	chris	14

UPDATE student SET age=18 WHERE student_id=102;

S_id	S_Name	age
101	Adam	15
102	Alex	18
103	chris	14

In the above statement, if we do not use the WHERE clause, then our update query will update age for all the columns of the table to 18.

---

Updating Multiple Columns

We can also update values of multiple columns using a single UPDATE statement.

UPDATE student SET name='Abhi', age=17 where s_id=103;

The above command will update two columns of the record which has s_id 103.

s_id	name	age
101	Adam	15
102	Alex	18
103	Abhi	17

---

UPDATE Command: Incrementing Integer Value

When we have to update any integer value in a table, then we can fetch and update the value in the table in a single statement.

For example, if we have to update the age column of student table every year for every student, then we can simply run the following UPDATE statement to perform the following operation:

UPDATE student SET age = age+1;

As you can see, we have used age = age + 1 to increment the value of age by 1.

NOTE: This style only works for integer values.

---

Using DELETE SQL command

When you ask any question in Studytonight's Forum it gets saved into a table. And using the Deleteoption, you can even delete a question asked by you. How do you think that works? Yes, using the Delete DML command.

Let's study about the syntax and the usage of the Delete command.

---

DELETE command

DELETE command is used to delete data from a table.

Following is its general syntax,

DELETE FROM table_name;

Let's take a sample table student:

s_id	name	age
101	Adam	15
102	Alex	18
103	Abhi	17

---

Delete all Records from a Table

DELETE FROM student;

The above command will delete all the records from the table student.

---

Delete a particular Record from a Table

In our student table if we want to delete a single record, we can use the WHERE clause to provide a condition in our DELETE statement.

DELETE FROM student WHERE s_id=103;

The above command will delete the record where s_id is 103 from the table student.

S_id	S_Name	age
101	Adam	15
102	Alex	18

---

Isn't DELETE same as TRUNCATE

TRUNCATE command is different from DELETE command. The delete command will delete all the rows from a table whereas truncate command not only deletes all the records stored in the table, but it also re-initializes the table(like a newly created table).

For eg: If you have a table with 10 rows and an auto_increment primary key, and if you use DELETEcommand to delete all the rows, it will delete all the rows, but will not re-initialize the primary key, hence if you will insert any row after using the DELETE command, the auto_increment primary key will start from 11. But in case of TRUNCATE command, primary key is re-initialized, and it will again start from 1.

---

Commit, Rollback and Savepoint SQL commands

Transaction Control Language(TCL) commands are used to manage transactions in the database. These are used to manage the changes made to the data in a table by DML statements. It also allows statements to be grouped together into logical transactions.

---

COMMIT command

COMMIT command is used to permanently save any transaction into the database.

When we use any DML command like INSERT, UPDATE or DELETE, the changes made by these commands are not permanent, until the current session is closed, the changes made by these commands can be rolled back.

To avoid that, we use the COMMIT command to mark the changes as permanent.

Following is commit command's syntax,

COMMIT;

---

ROLLBACK command

This command restores the database to last commited state. It is also used with SAVEPOINT command to jump to a savepoint in an ongoing transaction.

If we have used the UPDATE command to make some changes into the database, and realise that those changes were not required, then we can use the ROLLBACK command to rollback those changes, if they were not commited using the COMMIT command.

Following is rollback command's syntax,

ROLLBACK TO savepoint_name;

---

SAVEPOINT command

SAVEPOINT command is used to temporarily save a transaction so that you can rollback to that point whenever required.

Following is savepoint command's syntax,

SAVEPOINT savepoint_name;

In short, using this command we can name the different states of our data in any table and then rollback to that state using the ROLLBACK command whenever required.

---

Using Savepoint and Rollback

Following is the table class,

id	name
1	Abhi
2	Adam
4	Alex

Lets use some SQL queries on the above table and see the results.

INSERT INTO class VALUES(5, 'Rahul');

COMMIT;

UPDATE class SET name = 'Abhijit' WHERE id = '5';

SAVEPOINT A;

INSERT INTO class VALUES(6, 'Chris');

SAVEPOINT B;

INSERT INTO class VALUES(7, 'Bravo');

SAVEPOINT C;

SELECT * FROM class;

NOTE: SELECT statement is used to show the data stored in the table.

The resultant table will look like,

id	name
1	Abhi
2	Adam
4	Alex
5	Abhijit
6	Chris
7	Bravo

Now let's use the ROLLBACK command to roll back the state of data to the savepoint B.

ROLLBACK TO B;

SELECT * FROM class;

Now our class table will look like,

id	name
1	Abhi
2	Adam
4	Alex
5	Abhijit
6	Chris

Now let's again use the ROLLBACK command to roll back the state of data to the savepoint A

ROLLBACK TO A;

SELECT * FROM class;

Now the table will look like,

id	name
1	Abhi
2	Adam
4	Alex
5	Abhijit

So now you know how the commands COMMIT, ROLLBACK and SAVEPOINT works.

Using GRANT and REVOKE

Data Control Language(DCL) is used to control privileges in Database. To perform any operation in the database, such as for creating tables, sequences or views, a user needs privileges. Privileges are of two types,

·         System: This includes permissions for creating session, table, etc and all types of other system privileges.

·         Object: This includes permissions for any command or query to perform any operation on the database tables.

In DCL we have two commands,

·         GRANT: Used to provide any user access privileges or other priviliges for the database.

·         REVOKE: Used to take back permissions from any user.

---

Allow a User to create session

When we create a user in SQL, it is not even allowed to login and create a session until and unless proper permissions/priviliges are granted to the user.

Following command can be used to grant the session creating priviliges.

GRANT CREATE SESSION TO username;

---

Allow a User to create table

To allow a user to create tables in the database, we can use the below command,

GRANT CREATE TABLE TO username;

---

Provide user with space on tablespace to store table

Allowing a user to create table is not enough to start storing data in that table. We also must provide the user with priviliges to use the available tablespace for their table and data.

ALTER USER username QUOTA UNLIMITED ON SYSTEM;

The above command will alter the user details and will provide it access to unlimited tablespace on system.

NOTE: Generally unlimited quota is provided to Admin users.

---

Grant all privilege to a User

sysdba is a set of priviliges which has all the permissions in it. So if we want to provide all the privileges to any user, we can simply grant them the sysdba permission.

GRANT sysdba TO username

---

Grant permission to create any table

Sometimes user is restricted from creating come tables with names which are reserved for system tables. But we can grant privileges to a user to create any table using the below command,

GRANT CREATE ANY TABLE TO username

---

Grant permission to drop any table

As the title suggests, if you want to allow user to drop any table from the database, then grant this privilege to the user,

GRANT DROP ANY TABLE TO username

---

To take back Permissions

And, if you want to take back the privileges from any user, use the REVOKE command.

REVOKE CREATE TABLE FROM username

Using the WHERE SQL clause

WHERE clause is used to specify/apply any condition while retrieving, updating or deleting data from a table. This clause is used mostly with SELECT, UPDATE and DELETEquery.

When we specify a condition using the WHERE clause then the query executes only for those records for which the condition specified by the WHERE clause is true.

---

Syntax for WHERE clause

Here is how you can use the WHERE clause with a DELETE statement, or any other statement,

DELETE FROM table_name WHERE [condition];

The WHERE clause is used at the end of any SQL query, to specify a condition for execution.

---

Time for an Example

Consider a table student,

s_id	name	age	address
101	Adam	15	Chennai
102	Alex	18	Delhi
103	Abhi	17	Banglore
104	Ankit	22	Mumbai

Now we will use the SELECT statement to display data of the table, based on a condition, which we will add to our SELECT query using WHERE clause.

Let's write a simple SQL query to display the record for student with s_id as 101.

SELECT s_id, 

    name, 

    age, 

    address 

    FROM student WHERE s_id = 101;

Following will be the result of the above query.

s_id	name	age	address
101	Adam	15	Noida

---

Applying condition on Text Fields

In the above example we have applied a condition to an integer value field, but what if we want to apply the condition on name field. In that case we must enclose the value in single quote ' '. Some databases even accept double quotes, but single quotes is accepted by all.

SELECT s_id, 

    name, 

    age, 

    address 

    FROM student WHERE name = 'Adam';

Following will be the result of the above query.

s_id	name	age	address
101	Adam	15	Noida

---

Operators for WHERE clause condition

Following is a list of operators that can be used while specifying the WHERE clause condition.

Operator	Description
=	Equal to
!=	Not Equal to
<	Less than
>	Greater than
<=	Less than or Equal to
>=	Greate than or Equal to
BETWEEN	Between a specified range of values
LIKE	This is used to search for a pattern in value.
IN	In a given set of values