Dear readers, We have already been with the Introduction of SQL in earlier article.
Now, we will start learning to work practically on SQL with its commands and queries.
Some of the Most Important SQL Commands
- CREATE DATABASE – creates a new database
- ALTER DATABASE – modifies a database
- CREATE TABLE – creates a new table
- ALTER TABLE – modifies a table
- DROP TABLE – deletes a table
- SELECT – extracts data from a database
- UPDATE – updates data in a database
- DELETE – deletes data from a database
- INSERT INTO – inserts new data into a database
Let’s start using commands…
CREATE DATABASE/TABLE:
Used to create a new SQL database(db)/Table
- create database db_name;
- create table table_name (
column_name1 datatype,
column_name2 datatype,
column_name3 datatype,
… ); - With defined Constraints:
create table table_name (
column_name1 datatype constraint1,
column_name2 datatype,
column_name3 datatype constraint 2,
… );
e.g.,
create table CandidateDetail(
CandidateName varchar(255) Not null,
RollNumber int Not null Unique,
FatherName varchar(255)
);
NOTE:
1. Column/Attribute name should not contain any space or symbol.
e.g., Name, DateofBirth, FatherName etc.
2. We can define size of column with datatype, e.g. varchar(10), int(255), etc.
3. For detailed description of data types, Go to Introduction to SQL.
4. For detailed description of Constraints, we will see in later articles.
OPEN DATABASE/TABLE:
Used to open an existing SQL database/Table
- For Database,
use db_name; - For table,
select * from table_name;
DROP DATABASE/TABLE:
Used to delete an existing SQL database/Table
- drop database db_name;
- drop table table_name;
TRUNCATE TABLE:
Used to delete the complete data inside a table, but not the table
- truncate table table_name;
ALTER TABLE:
– Used to add, delete, or modify columns in an existing table
- Add Column
alter table table_name
add column_name datatype;
- Delete Column
alter table table_name
drop column column_name;
- Modify column (SQL server/ MS Access)
alter table table_name
alter column column_name datatype;
- Modify column (My SQL)
alter table table_name
modify column column_name datatype;
– Used to add and drop various constraints on an existing table
- Add Constraint
alter table table_name
add constraint constraint_name condition;
- Delete Constraint
alter table table_name
drop constraint constraint_name;
SELECT command:
- For selected columns,
select column1,column2
from table_name;
- For all columns of a table,
select * from table_name;
- For distinct(different) value in a column,
select distinct column1,column2
from table_name;
- To get count of values in a column,
select count(distinct column1)
from table_name;
NOTE: Counts of multiple columns will not work simultaneously.
WHERE clause:
- Used to filter records
- Used to extract only those records that fulfil a specified condition
select column1, column2
from table_name
where condition1;
NOTE: It needs to be very careful while updating/deleting any record, because The WHERE clause specifies which record(s) that to be updated/deleted. Without using WHERE clause, all records in the table will be updated/deleted!
| Operator | Description |
| = | Equal |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal |
| <= | Less than or equal |
| <> | Not equal. Note: In some versions of SQL this operator may be written as != |
| BETWEEN | Between a certain range |
| LIKE | Search for a pattern |
| IN | To specify multiple possible values for a column |
e.g.,
| select * from table_name where age between 18 and 30; |
select * from table_name where age=20; |
| select * from table_name where name like ‘a%’ ; |
select * from table_name where name in (‘Amrit’,’Sundaram’); |
AND, OR, NOT Operators:
- Used with “where” command
- Used to filter records based on more than one condition
- The AND operator displays a record if all the conditions separated by AND are TRUE.
- The OR operator displays a record if any of the conditions separated by OR is TRUE.
- The NOT operator displays a record if the condition(s) is NOT TRUE.
select * from table_name
where name = ’Amrit’ and age = 20 ;
select * from table_name
where name = ’Amrit’ or age = 20 ;
select * from table_name
where not name = ’Amrit’ and not age =20 ;
ORDER BY keyword:
- Used to sort the result-set in ascending or descending order
- It sorts the records in ascending order by default. To sort the records in descending order, “DESC” keyword is used.
select * from table_name
ORDER BY name DESC;
select * from table_name
ORDER BY name, age;
select * from table_name
ORDER BY name ASC, age DESC;
UPDATE command:
- Used to modify the existing records in a table
update table_name
set column1=value1, column2=value2, …
where condition1;
e.g.,
update FeeStatus
set status=’Paid’
where class=’10’;
This query will update all those records’ status as “Paid” for class 10th only.
If I would not used ‘where’ clause, all records get updated as “Paid”. So, where clause has to be used carefully while updating any record.
DELETE command:
- Used to delete existing records in a table
delete table_name
where condition1;
e.g.,
delete FeeStatus
where CandidateId=12345;
This query will delete all records of candidateId 12345 only. If I would not have used ‘where’ clause, all records get deleted. So, where clause has to be used carefully while deleting any record.
INSERT INTO:
Used to add/insert new records in a table
- With specified column names and values into all columns
Insert into table_name (column1,column2,…)
values (value1,value2,…);
NOTE: On inserting data into all columns, It is not needed to specify the column names, only the order of values should be the same as of the columns in the table, See below query to understand…
- Without specified column names and values into all columns
Insert into table_name
values (value1,value2,…);
- With specified column names and values into selected columns
Insert into table_name(column1,column3)
values (value1,value3);
- With data from another table
We can populate the data into a table from another existing table too by using
Insert into table1_name [(column1b,column3b)]
select column1a,column3a
from table2_name
[where condition];
So, we have learnt about some basic and commonly used commands, clauses & operators. Will learn about some more basic & advanced SQL commands in next articles.
Having any clarifications or explanation or queries?? Please ask it in the comment section.
Keep learning!!
