SQL

 Structured Query Language (SQL) helps users to communicate with the database.
SQL lets you access, manipulate and managing data in a relational database management system (RDBMS).

SQL is domain specific language .

SQL is declaration language (what should be done is only thing to be told, how to do is not required).

SQL commands:

1)DDL: data definition language ,it works on structure of the table/relation.

2)DML: Data manipulation language ,once the table is created ,to store ,manage, access, delete data in the table DML is used.

3)DCL: Each and every user will have their predefined privileges. so to give and take back particular privilege to user we use DCL.

4)TCL: Transaction Control Language commands are used to manage transactions in the database.

constraints in SQL

SQL constraints are used to specify rules for the data in a table.
Constraints are used to limit the type of data that can go into a table. 
commonly used constraints in SQL:

1)UNIQUE - Ensures that all values in a column are different. duplicates shouldn't be present.

2)NOT NULL - Ensures that a column doesn't have a NULL value.(empty)

PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table.

FOREIGN KEY -  it is used to link to tables.

CHECK - Ensures that the values in a column satisfies a specific condition

DEFAULT - if no value is given in any column ,default value for a column is given.

CREATE INDEX - Used to create and retrieve data from the database very quickly.

Create command:

It is used to create tables in a database .

syntax:

create table <table_name>
{
column1 name datatype;
column2 name datatype;
column3 name datatype
}
describe table_name; 

DESCRIBE =will give each and every details about the table(description of the table ).

example:

create table employee
{
id int;
name varchar(20);
age number(10);
}
describe emp; 

+----+----------+-----+
| id | name     | age |     
+----+----------+-----+
|    |          |     | 
|    |          |     |
|    |          |     | 
|    |          |     | 
|    |          |     | 
+----+----------+-----+

drop command:

It is used to delete a whole database or just a table. 

let us assume we have a database with name company.

1)to drop the database:

syntax:
drop database database_name;
example:
drop database company;

2)to drop the table:
when we drop a table , all the data and even the structure of the table is deleted.

lets us assume we have a table employee with  id int, name varchar(20), age number(10); 
and has 1st row=> 1,Ravi,30
and 2nd row=> 4,Pooja,37
and 3rd row=> 6,Arjun,28

syntax:
drop table table_name;
example:
drop database employee;

Truncate command:

It is used to delete the whole table data, but table structure wont be deleted

let us assume we have a database with name company.

lets us assume we have a table employee with  id int, name varchar(20), age number(10); 
and has 1st row=> 1,Ravi,30
and 2nd row=> 4,Pooja,37
and 3rd row=> 6,Arjun,28

syntax:
truncate table table_name;
example:
truncate database employee;
+----+----------+-----+
| id | name     | age |     
+----+----------+-----+
|    |          |     | 
|    |          |     | 
|    |          |     | 
+----+----------+-----+

Alter command:
alter command is used to make changes on table structure.
lets us assume we have a table employee with  id int, name varchar(20), age number(10); 
and dep table with int dep_id , name varchar(20);

alter table is used to :

1)add column:
alter table employee add address varchar(30);
2)remove column:
alter table employee drop column age;
3)modify datatype:
alter table employee modify id varchar(10);
4)modify datatype length:
alter table employee modify id varchar(30);
5)rename column:
alter table employee rename column id to roll_no;
6)add constraints:
alter table employee add primary key(roll_no); 
alter table employee add foreign key(dep_id) references dep(dep_id);
7)remove constraints:
   alter table employee drop primary key;
   alter table employee drop foreign key dep_id;
8)rename table:
alter table employee rename to emp;

select command:

it is used to display some data from a database. The data displayed will be displayed in the form of table .

lets us assume we have a table employee with  id int, name varchar(20), age number(10); 
and has 1st row=> 1,Ravi,30
and 2nd row=> 4,Pooja,37
and 3rd row=> 6,Arjun,28

case 1) if we are selecting all columns:

syntax:
select * from table_name;
example:
select * from employee;
+----+----------+-----+
| id | name     | age |     
+----+----------+-----+
| 1  | Ravi     |  30 | 
| 4  | Pooja    |  37 |
| 6  | Arjun    |  28 |
+----+----------+-----+

case 2) if we want to select some particular columns:

syntax:
select column1, column2,...
from table_name;
example:
select id, name
from employee;
+----+----------+
| id | name     |      
+----+----------+
| 1  | Ravi     | 
| 4  | Pooja    |
| 6  | Arjun    | 
+----+----------+

insert command:

used to insert data in the table.

lets us assume we have a empty table employee with  id int, name varchar(20), age number(10); 

case 1) if we are inserting in all columns:

syntax:
insert into table_name
values (value1, value2, value3, ...);
example:
insert into employee
values (1,Raju,24);
+----+----------+-----+
| id | name     | age |     
+----+----------+-----+
| 1  |  Raju    | 24  | 
+----+----------+-----+

case 2) if we want to insert in particular columns:

syntax:
insert into table_name (column1, column2, column3, ...)
values (value1, value2, value3, ...);
example:
insert into employee (id, name)
values (3, Tigadi);
+----+----------+-----+
| id | name     | age |     
+----+----------+-----+
| 1  |  Raju    | 24  | 
| 3  |  Tigadi  | null|
+----+----------+-----+

Update command:

update command is used to change the data.

lets us assume we have a table employee with  id int, name varchar(20), salary number(20); 
and has 1st row=> 1,Ravi,30,000
and 2nd row=> 4,Pooja,37,000
and 3rd row=> 6,Arjun,28,000

case 1) if we want to change all the rows of selected columns:

syntax:
update table_name set column1=value1,column2=value2...;
example:
update employee salary=salary+10000;
+----+----------+--------+
| id | name     | salary |     
+----+----------+--------+
| 1  |  Raju    | 34,000 | 
| 4  |  Pooja   | 47,000 |
| 6  |  Arjun   | 38,000 |
+----+----------+--------+

case 2) if we want to change particular rows in colums:

syntax:
update table_name set column1=value1,column2=value2...
where condition;


example:
update employee salary=salary+10000 
where id<5;
(id with 1 and 4 are smaller than 5 so their 
salary gets increased by 10,000)

+----+----------+--------+
| id | name     | salary |     
+----+----------+--------+
| 1  |  Raju    | 34,000 | 
| 4  |  Pooja   | 47,000 |
| 6  |  Arjun   | 28,000 |  <- is not effected
+----+----------+--------+

Delete command:
It is used to delete rows /tuples.

lets us assume we have a table employee with  id int, name varchar(20), age number(10); 
and has 1st row=> 1,Ravi,30
and 2nd row=> 4,Pooja,37
and 3rd row=> 6,Arjun,28

case 1) if we want to delete all the rows without deleting table:

syntax:
delete from table_name;
example:
delete from employee ;
+----+----------+-----+
| id | name     | age |     
+----+----------+-----+
|    |          |     | 
|    |          |     |
|    |          |     | 
+----+----------+-----+

case 2) if we want to delete in particular columns:

syntax:
delete from table_name where condition;
example:
delete from employee where name='Pooja';
+----+----------+-----+
| id | name     | age |     
+----+----------+-----+
| 1  | Ravi     |  30 | 
| 6  | Arjun    |  28 |
+----+----------+-----+