SQL

0
Share

[ad_1]

 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:

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 |
+----+----------+-----+

 








.

[ad_2]

Source link

%d bloggers like this: