Learn SQL easily Part -2

UPDATE any TABLE: 

Suppose we have a table named student below: 









Now we want to modify the table. Like we want to rename 'Hasan' to 'Saurav'. 
SYNTAX:  UPDATE table_name SET column_name=value where column_name=value;
SQL CODE:  UPDATE student SET name='Saurav' WHERE name='Hasan'; 

We can update all the columns.  Now we want to UPDATE the NAME and CGPA of the student whose id is 1002.  We have to write:
SQL CODE: UPDATE student SET name='Abir',  cgpa=3.47 WHERE id=1002;
Warning: If we don't use WHERE clause like 
SQL CODE: UPDATE student SET name='Saurav'; 
Then all the names in the table have been changed to 'Saurav';

DELETE statement: 

If we want to DELETE any specific row we use the DELETE statement.
SYNTAX: DELETE FROM table_name WHERE column_name=value;
SQL CODE: DELETE FROM student WHERE name='Saurav';
We can delete all the rows from the table.
SQL CODE: DELETE * FROM student;

SQL ALIASES: 

We use SQL ALIASES to give a temporary name of the TABLE or any COLUMN.
SYNTAX: SELECT column_name AS alias_name FROM table_name;
SQL CODE: SELECT cgpa AS student_gpa FROM student;










ALTER statement: 

 We use ALTER statement to add any column, delete columns, or modify columns in an existing table.
SYNTAX: ALTER TABLE table_name ADD column_name type;
SQL CODE: ALTER TABLE student ADD home_district varchar(10);  <------ Here don't use COLUMN keyword.

SYNTAX: ALTER TABLE table_name DROP COLUMN  column_name type;
SQL CODE: ALTER TABLE student DROP COLUMN cgpa;  <------ Here must use COLUMN keyword.

SQL VIEWS: 

VIEWS are virtual tables. We create views with those columns which we use frequently. 
CREATING VIEWS: 
SYNTAX: CREATE or REPLACE VIEW view_name AS SELECT columns FROM table_name;
SQL CODE: CREATE or REPLACE VIEW student_view AS SELECT id, name FROM student; 
We can DROP a view:
SYNTAX: DROP VIEW view_name;
SQL CODE: DROP VIEW student_view;

We can create view taking data from several tables. For this, we have to use the JOIN statement. We will learn to JOIN in the next part. Just see the view codes.
Suppose we have two tables named student and student_info.
















Now we want to CREATE a VIEW with id, name, cgpa, dept.
SQL CODE: CREATE or REPLACE VIEW student_view AS 
SELECT  student.id,  student.name,  student.cgpa,  student_info.dept  FROM 
student INNER JOIN student_info ON student.id = student_info.id.
Here id is common in both tables. So we use inner join on id. We will learn to JOIN details in the next part.
SQL CODE: SELECT * FROM student_view;












ORDER BY statement:

Suppose we have a table named student.










Now we want the ids and their cgpas ordered by cgpa. That means the highest cgpa in the top and then the rest of these serially.
SYNTAX: SELECT column_one, column_two,----- FROM table_name ORDER BY column_name ASC/DESC;
Here ASC = ASCENDING and DESC = DESCENDING.
SQL CODE: SELECT  id, cgpa FROM student ORDER BY cgpa DESC;
SQL CODE: SELECT  id, cgpa FROM student ORDER BY cgpa ASC;




0/Post a Comment/Comments