Brief details ablout SQL and its type command by using terminal

* What is SQL?

SQL stands for Structure Query Language, basically SQL is used to communicate / interact with database. And it is a programming langauge that is used to manage relational databases and perform various operations.

SQL is used for the following:

• Modifying structure and data into table.
• Adding and Updating rows of data into table.
• Deleting rows of data.
• Retrieving data from the database.

*How many type of command in SQL

1. DDL (Data Definition Language).

2. DML (Data Manipulation Language).

3. TCL (Transition Control Language).

4. DQL (Data Query Language).

* What are the commands comes under the DDL

• CREATE
• ALTER
• DROP
• TRUNCATE
• RENAME

* What are the commands comes under the DML

• UPDATE
• INSERT
• DELETE

* What are the commands comes under the TCL

• COMMIT 
• ROLLBACK

* What are commands comes under the DQL

• SELECT

* What we can do by using CREATE command?

• In SQL, CREATE command is used to create database in DBMS and create new table in database. Or simply we can say CREATE command used to create new database and table.For CREATE command we need to do first--
• Install mysql
• Open Terminal
• Run 'sudo mysql' command on Terminal
• Enter password which you enter earlier

Now before creating a database we need to check all the database in existing DBMS. By doing this, we avoid the error that if the database we are creating and if it is already in the DBMS, then it will throw the error on the terminal.

1. show databases;

By running this command we will get all the database which is existing in DBMS.

Now create our own database in DBMS. Here needs to make sure after every command we need to put semi colon (' ; ') fot run the command.

For creating database command:- create database (Database_Name)

for ex:- create database Musical_School; / By using this command we can see now our database in existing DBMS by running (show databases;) command

As you can see in the image Musical_School database created successfully. After create DB we need to use our database for creating table.

run this command:- Use Musical_School;

Now we need to create table in our database--

For create table Command:-

CREATE TABLE Table_Name (

         >  column1 datatype,

         >  column2 datatype,

         >  column3 datatype,

           .......

);

For example:- CREATE TABLE School (SNO INT NOT NULL, SCHOOL_NAME VARCHAR(100) NULL, SCHOOL_STATE VARCHAR(100) NULL);

As we can see in the upper image table created successfully. If you wants to see your table then run this command

show tables;

and if you wants to see how many columns exist in your table then run this command-

desc Table_Name

Now we need to entered value into our table by using DML type command (INSERT)

*What we can do by using INSERT command?

The INSERT command is used to inserting one or more rows into the table with specified table column values.

For INSERT command:-

INSERT INTO TABLE_NAME

VALUE('COLUMN1', 'COLUMN2', 'COLUMN3',.......'COLUMNN');

INSERT command:- 

insert into School value ('1', 'DML Music School', 'Mumbai');

insert into School value ('1', 'DML Music School', 'Mumbai');

insert into School value ('1', 'DML Music School', 'Mumbai');

insert into School value ('1', 'DML Music School', 'Mumbai');

insert into School value ('1', 'DML Music School', 'Mumbai');

if you want to see your value which you entered in table for that run this command-

select * from Table_Name;

*What we can do by using ALTER command?

    By using ALTER command we can do-

• Add new column.
• Drop existing column.
• Modify existing column.
• Renaming a column.

* How to add a new column in table?

Command for that:- 1. alter table School add column SCHOOL_CITY varchar(100);

  2. alter table School add column SCHOOL_COUNTRY varchar(100); 

* How to add a column after a particular column.

Command:- alter table School add column SCHOOL_COUNTRY varchar(100) after SCHOOL_STATE;

as we can see column added successfully in upper image.   

* What we can do by using DROP command?

DROP command is used to drop an existing table in a database.

Command for DROP:- alter table School drop SCHOOL_COUNTRY;

Now here after drop table SCHOOL_COUNTRY, and SCHOOL_CITY. We get only table column of (SNO, SCHOOL_NAME, SCHOOL_STATE) as you can see in upper image

* What we can do by using RENAME command?

Rename command is used to change the name of existing column/table to new name.

Command for RENAME:- ALTER TABLE "TABLE_NAME" RENAME "COLUMN1" TO "COLUMN2";

For example:- alter table School rename SCHOOL_STATE to SCHOOL_CITY;

* What we can do by using TRUNCATE command?

TRUNCATE command is used to removes all the rows from the table, but the structure of table and its columns and indexs and so on still remain. We can not rollback our data.

Command for TRUNCATE:- TRUNCATE TABLE (TABLE_NAME);

For example:- truncate table School;