Learn SQL easily part-1

What is SQL?

SQL stands for STRUCTURED QUERY LANGUAGE. We can communicate with database through SQL,  a standard language for database.

How to create a TABLE?

Syntax: CREATE TABLE table_name
(
column_one_name  type,
column_two_name  type,
column_three_name  type,
-------------------------,
-------------------------,
column_n_name    type     <----------------------- Don't use comma here.
);    <-------------------Here is semicolon.
Here n=1, 2, 3, 4, ---------------------------------


For example we want to create a table for student information: 
SQL CODE:
CREATE TABLE student
(
id number,
name varchar(10),
cgpa float,
dept varchar(10)
);
Here is the blank table as we have not inserted any values yet. 









How to INSERT values into the TABLE?

SYNTAX: INSERT INTO table_name VALUES(elements);
We can insert several values into TABLE.

SQL CODE:
INSERT INTO student VALUES(1001, 'Hasan', 3.5, 'CSE');
INSERT INTO student VALUES(1002, 'Mahadi', 3.6, 'CSE');
INSERT INTO student VALUES(1003, 'Suravi', 3.9, 'CSE');
INSERT INTO student VALUES(1004, 'Shuchi', 3.7, 'CSE');
INSERT INTO student VALUES(1005, 'Alauddin', 3.6, 'CSE');











SELECT statement  to show TABLE data: 

SYNTAX:  SELECT  *  FROM table_name;
SQL CODE:  SELECT  *  FROM student;












We can also show some of the columns.For this we have to write :
SQL CODE: SELECT  id,  cgpa  FROM student;














DISTINCT statement: 

If any table contains duplicate data but we want only different values then we use DISTINCT statement. For example if we use 
SQL CODE: SELECT name FROM student; 














We see that Hasan appears twice here. But we want the distinct values then.
SQL CODE: SELECT DISTINCT name FROM student;












Here Hasan appears once for using DISTINCT.

WHERE clause:

We want to see the name and cgpa of the student whose id is 1001. For this case we use WHERE.
SYNTAX: SELECT id, name FROM table_name WHERE column_name=value;
SQL CODE: SELECT id, name FROM student WHERE id=1001;








WHERE clause using AND, OR: 

Suppose we want the cgpa of a student whose id is 1001 and name is Hasan, then we have to use AND with WHERE clause.
SQL CODE: SELECT id, name, cgpa FROM student WHERE id=1001 AND name='Hasan';
For AND statement all the conditions must be true.









SQL CODE: SELECT id, name, cgpa FROM student WHERE id=1001  OR name='Hasan';
For OR statement one condition is required true.

WHERE clause using IN: 

SQL CODE: SELECT id, name, cgpa FROM student WHERE id IN(1001,1002,1005);
This statement shows id, name and cgpa of students whose ids are 1001, 1002, 1005 from student table.


WHERE clause using BETWEEN AND:

SQL CODE: SELECT id, name, dept FROM student WHERE id BETWEEN 1001 AND 1003;
This statement shows id, name and dept of those students who have id between 1001 and 1003 from student table.






WHERE clause using LIKE:


SQL CODE: SELECT name, dept FROM student WHERE name LIKE 'M%';
This statement shows the name and dept of those students whose name starts with M.










Here 'M%' means the word starts with M and after M there are several letters. Again 
'%m' means the last alphabet of the word is m. 

DROP statement:

If we want to delete the table we use DROP statement.
SYNTAX: DROP TABLE table_name.
SQL CODE:  DROP TABLE student;

TRUNCATE statement:

In the previous section we see the drop statement. This statement is used to delete the whole table. Now we want to delete the records of the table. That means we want to delete the rows of the table but the not the whole table. We just want to make the table empty. Then we use TRUNCATE statement.
SQL CODE: TRUNCATE TABLE student;