SQL command

o SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data.

o SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set permission for users.

Types of SQL Command:

1. Data definition language (DDL)

o DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.
o All the command of DDL are auto-committed that means it permanently save all the changes in the database.

Here are some commands that come under DDL:

o CREATE
o ALTER
o DROP
o TRUNCATE

a. CREATE It is used to create a new table in the database.

Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
Example:

CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);

b. DROP: It is used to delete both the structure and record stored in the table.

Syntax

DROP TABLE ;
Example

DROP TABLE EMPLOYEE;

c. ALTER: It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a
new attribute.

Syntax:
To add a new column in the table

ALTER TABLE table_name ADD column_name COLUMN-definition; To modify

existing column in the table:

ALTER TABLE MODIFY(COLUMN DEFINITION....);

EXAMPLE

ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20)); ALTER TABLE

STU_DETAILS MODIFY (NAME VARCHAR2(20));

d. TRUNCATE: It is used to delete all the rows from the table and free the space containing the table.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE EMPLOYEE;

2. Data Manipulation Language

o DML commands are used to modify the database. It is responsible for all form of changes in the database.
o The command of DML is not auto-committed that means it can't permanently save all the changes in the database. They can be rollback.

Here are some commands that come under DML:

o INSERT
o UPDATE
o DELETE

a. INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a table.

Syntax:

INSERT INTO TABLE_NAME

(col1, col2, col3,.... col N)
VALUES (value1, value2, value3, .... valueN); Or

INSERT INTO TABLE_NAME

VALUES (value1, value2, value3, .... valueN);

For example:

INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS");

b. UPDATE: This command is used to update or modify the value of a column in the table.

Syntax:

UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]

For example:

UPDATE students
SET User_Name = 'Sonoo'
WHERE Student_Id = '3'

c. DELETE: It is used to remove one or more row from a table.

Syntax:

DELETE FROM table_name [WHERE condition];

For example:

DELETE FROM javatpoint WHERE

Author="Sonoo";

3. Data Control Language

DCL commands are used to grant and take back authority from any database user. Here are some

commands that come under DCL:

o Grant
o Revoke

a. Grant: It is used to give user access privileges to a database.

Example

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

GRANT CREATE TABLE TO username;

b. Revoke: It is used to take back permissions from the user.

Example

REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

REVOKE CREATE TABLE FROM username

4. Transaction Control Language

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.

These operations are automatically committed in the database that's why they cannot be used while creating tables or dropping them.

Here are some commands that come under TCL:

o COMMIT
o ROLLBACK
o SAVEPOINT

a. Commit: Commit command is used to save all the transactions to the database. When we use any DML command like INSERT, UPDATE or DELETE, the changes
made by these commands are not permanent, until the current session is closed, the changes made by these commands can be rolled back. To avoid that, we use
the COMMIT command to mark the changes as permanent.

Syntax:

COMMIT;
 Example:

DELETE FROM CUSTOMERS

WHERE AGE = 25; COMMIT;
b. Rollback: Rollback command is used to undo transactions that have not already been saved to the database. This command restores the database to last commited
state. It is also used with SAVEPOINT command to jump to a savepoint in an ongoing transaction. If we have used the UPDATE command to make some changes
into the database, and realise that those changes were not required, then we can use the ROLLBACK command to rollback those changes, if they were not
commited using the COMMIT command.
Syntax:

ROLLBACK;
Example:

DELETE FROM CUSTOMERS

WHERE AGE = 25; ROLLBACK;

c. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction. SAVEPOINT command is used to temporarily save a
transaction so that you can rollback to that point whenever required.
Syntax:

SAVEPOINT SAVEPOINT_NAME;

5. Data Query Language

DQL is used to fetch the data from the database. It uses only

one command:

o SELECT
a. SELECT: This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by WHERE clause.

Syntax:

SELECT expressions
FROM TABLES
WHERE conditions;

For example:

SELECT emp_name
FROM employee WHERE
age > 20;

 SQL | Join (Inner, Left, Right and Full Joins)

A SQL Join statement is used to combine data or rows from two or more tables based on a common field between
them. Different types of Joins are:
Consider the two tables below:
Student StudentCourse
The simplest Join is INNER JOIN.
1. INNER JOIN: The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies. This
keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of
the common field will be same.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
table1: First table.
table2: Second table
matching_column: Column common to both the tables.
Note: We can also write JOIN instead of INNER JOIN. JOIN is same as INNER JOIN.

Example Queries(INNER JOIN)

 This query will show the names and age of students enrolled in different courses.
SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE FROM Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;
Output:
2. LEFT JOIN: This join returns all the rows of the table on the left side of the join and matching rows for the table on the
right side of join. The rows for which there is no matching row on right side, the result-set will contain null. LEFT JOIN
is also known as LEFT OUTER JOIN.Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

table1: First table.

table2: Second table
matching_column: Column common to both the tables.
Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are same.

Example Queries(LEFT JOIN):

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output:

3. RIGHT JOIN: RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the
join and matching rows for the table on the left side of join. The rows for which there is no matching row on left side,
the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

table1: First table.

table2: Second table
matching_column: Column common to both the tables.
 Note: We can also use RIGHT OUTER JOIN instead of RIGHT JOIN, both are same.

Example Queries(RIGHT JOIN):

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output:

4. FULL JOIN: FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set
will contain all the rows from both the tables. The rows for which there is no matching, the result-set will
contain NULL values.Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

table1: First table.

table2: Second table
matching_column: Column common to both the tables.

Example Queries(FULL JOIN):

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output
 SQL Trigger | Student Database
Trigger: A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. For
example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.
Syntax:
create trigger [trigger_name]
[before | after]
{insert | update | delete}
on [table_name]
[for each row]
[trigger_body]
Explanation of syntax:
1. create trigger [trigger_name]: Creates or replaces an existing trigger with the trigger_name.
2. [before | after]: This specifies when the trigger will be executed.
3. {insert | update | delete}: This specifies the DML operation.
4. on [table_name]: This specifies the name of the table associated with the trigger.
5. [for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected.
6. [trigger_body]: This provides the operation to be performed as trigger is fired
BEFORE and AFTER of Trigger:
BEFORE triggers run the trigger action before the triggering statement is run.
AFTER triggers run the trigger action after the triggering statement is run.
Example:
Given Student Report Database, in which student marks assessment is recorded. In such schema, create a trigger so that the total and
average of specified marks is automatically inserted whenever a record is insert.
Here, as trigger will invoke before record is inserted so, BEFORE Tag can be used.
Suppose the database Schema –
mysql> desc Student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| tid | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
| subj1 | int(2) | YES | | NULL | |
| subj2 | int(2) | YES | | NULL | |
| subj3 | int(2) | YES | | NULL | |
| total | int(3) | YES | | NULL | |
| per | int(3) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
SQL Trigger to problem statement.
create trigger stud_marks
before INSERT
on
Student
for each row
set new.total = new.subj1 + new.subj2 + new.subj3, new.per = new.total * 60 / 100;
Above SQL statement will create a trigger in the student database in which whenever subjects marks are entered, before inserting this
data into the database, trigger will compute those two values and insert with the entered values. i.e.,
mysql> insert into Student values(0, "ABCDE", 20, 20, 20, 0, 0);
Query OK, 1 row affected (0.09 sec)

mysql> select * from Student;

+-----+-------+-------+-------+-------+-------+------+
| tid | name | subj1 | subj2 | subj3 | total | per |
+-----+-------+-------+-------+-------+-------+------+
| 100 | ABCDE | 20 | 20 | 20 | 60 | 36 |
+-----+-------+-------+-------+-------+-------+------+
1 row in set (0.00 sec)
In this way trigger can be creates and executed in the databases.

 SQL | Views
Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a real table in the database. We
can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows
of a table or specific rows based on certain condition.
In this article we will learn about creating , deleting and updating Views.
Sample Tables:
StudentDetails StudentMarks

CREATING VIEWS
We can create View using CREATE VIEW statement. A View can be created from a single table or multiple tables.
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;

view_name: Name for the View

table_name: Name of the table
condition: Condition to select rows
Examples:
 Creating View from a single table:
 In this example we will create a View named DetailsView from the table StudentDetails.
Query:
CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM StudentDetails
WHERE S_ID < 5;
To see the data in the View, we can query the view in the same manner as we query a table.
SELECT * FROM DetailsView;
Output:

 In this example, we will create a view named StudentNames from the table StudentDetails.
Query:
 CREATE VIEW StudentNames AS

 SELECT S_ID, NAME

 FROM StudentDetails

 ORDER BY NAME;

If we now query the view as,

SELECT * FROM StudentNames;
Output:

 Creating View from multiple tables: In this example we will create a View named MarksView from two tables
StudentDetails and StudentMarks. To create a View from multiple tables we can simply include multiple tables in the
SELECT statement. Query:
 CREATE VIEW MarksView AS
 SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS
 FROM StudentDetails, StudentMarks
  WHERE StudentDetails.NAME = StudentMarks.NAME;
To display data of View MarksView:
SELECT * FROM MarksView;
Output:

DELETING VIEWS
We have learned about creating a View, but what if a created View is not needed any more? Obviously we will
want to delete it. SQL allows us to delete an existing View. We can delete or drop a View using the DROP
statement.
Syntax:
DROP VIEW view_name;

view_name: Name of the View which we want to delete.

For example, if we want to delete the View MarksView, we can do this as:
DROP VIEW MarksView;
UPDATING VIEWS
There are certain conditions needed to be satisfied to update a view. If any one of these conditions is not met,
then we will not be allowed to update the view.
1. The SELECT statement which is used to create the view should not include GROUP BY clause or ORDER BY clause.
2. The SELECT statement should not have the DISTINCT keyword.
3. The View should have all NOT NULL values.
4. The view should not be created using nested queries or complex queries.
5. The view should be created from a single table. If the view is created using multiple tables then we will not be
allowed to update the view.
 We can use the CREATE OR REPLACE VIEW statement to add or remove fields from a view.
Syntax:
 CREATE OR REPLACE VIEW view_name AS
 SELECT column1,coulmn2,..
 FROM table_name
 WHERE condition;
For example, if we want to update the view MarksView and add the field AGE to this View
from StudentMarks Table, we can do this as:
CREATE OR REPLACE VIEW MarksView AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS, StudentMarks.AGE
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;
If we fetch all the data from MarksView now as:
SELECT * FROM MarksView;
 Output:

 Inserting a row in a view:

We can insert a row in a View in a same way as we do in a table. We can use the INSERT INTO statement of SQL to
insert a row in a View.Syntax:
 INSERT view_name(column1, column2 , column3,..)
 VALUES(value1, value2, value3..);


 view_name: Name of the View

Example:
In the below example we will insert a new row in the View DetailsView which we have created above in
the example of “creating views from a single table”.
INSERT INTO DetailsView(NAME, ADDRESS)
VALUES("Suresh","Gurgaon");
If we fetch all the data from DetailsView now as,
SELECT * FROM DetailsView;
Output:

 Deleting a row from a View:

Deleting rows from a view is also as simple as deleting rows from a table. We can use the DELETE statement of SQL
to delete rows from a view. Also deleting a row from a view first delete the row from the actual table and the
change is then reflected in the view.Syntax:
 DELETE FROM view_name
 WHERE condition;


 view_name:Name of view from where we want to delete rows

 condition: Condition to select rows
Example:
In this example we will delete the last row from the view DetailsView which we just added in the above
example of inserting rows.
DELETE FROM DetailsView
 WHERE NAME="Suresh";
If we fetch all the data from DetailsView now as,
SELECT * FROM DetailsView;
Output:

WITH CHECK OPTION

The WITH CHECK OPTION clause in SQL is a very useful clause for views. It is applicable to a updatable view. If
the view is not updatable, then there is no meaning of including this clause in the CREATE VIEW statement.
 The WITH CHECK OPTION clause is used to prevent the insertion of rows in the view where the condition in the
WHERE clause in CREATE VIEW statement is not satisfied.
 If we have used the WITH CHECK OPTION clause in the CREATE VIEW statement, and if the UPDATE or INSERT clause
does not satisfy the conditions then they will return an error.
Example:
In the below example we are creating a View SampleView from StudentDetails Table with WITH CHECK OPTION
clause.
CREATE VIEW SampleView AS
SELECT S_ID, NAME
FROM StudentDetails
WHERE NAME IS NOT NULL
WITH CHECK OPTION;
In this View if we now try to insert a new row with null value in the NAME column then it will give an error
because the view is created with the condition for NAME column as NOT NULL.
For example,though the View is updatable but then also the below query for this View is not valid:
INSERT INTO SampleView(S_ID)
VALUES(6);
NOTE: The default value of NAME column is null.

 SQL LIKE clause

LIKE clause is used in the condition in SQL query with the WHERE clause. LIKE clause compares data with an expression using wildcard operators
to match pattern given in the condition.

Wildcard operators
There are two wildcard operators that are used in LIKE clause.

 Percent sign %: represents zero, one or more than one character.

 Underscore sign _: represents only a single character.
Example of LIKE clause
Consider the following Student table.
s_id s_Name age
101 Adam 15
102 Alex 18
103 Abhi 17
SELECT * FROM Student WHERE s_name LIKE 'A%';
The above query will return all records where s_name starts with character 'A'.
s_id s_Name age
101 Adam 15
102 Alex 18
103 Abhi 17

Using _ and %
SELECT * FROM Student WHERE s_name LIKE '_d%';
The above query will return all records from Student table where s_name contain 'd' as second character.
s_id s_Name age
101 Adam 15

Using % only
SELECT * FROM Student WHERE s_name LIKE '%x';
The above query will return all records from Student table where s_name contain 'x' as last character.
s_id s_Name age
102 Alex 18

 ORDER BY Clause
Order by clause is used with SELECT statement for arranging retrieved data in sorted order. The Order by clause by default sorts the retrieved
data in ascending order. To sort the data in descending order DESC keyword is used with Order by clause.

Syntax of Order By
SELECT column-list|* FROM table-name ORDER BY ASC | DESC;

 Group By Clause
Group by clause is used to group the results of a SELECT query based on one or more columns. It is also used with SQL functions to group the
result from one or more tables.
Syntax for using Group by in a statement.
SELECT column_name, function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name

 HAVING Clause
Having clause is used with SQL Queries to give more precise condition for a statement. It is used to mention
condition in Group by based SQL queries, just like WHERE clause is used with SELECT query.
Syntax for HAVING clause is,
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name condition
GROUP BY column_name
HAVING function(column_name) condition

Example of SQL Statement using HAVING

Consider the following Sale table.
oid order_name previous_balance customer
11 ord1 2000 Alex
12 ord2 1000 Adam
13 ord3 2000 Abhi
14 ord4 1000 Adam
15 ord5 2000 Alex
Suppose we want to find the customer whose previous_balance sum is more than 3000.
We will use the below SQL query,
SELECT * FROM sale GROUP BY customer
HAVING sum(previous_balance) > 3000
Result will be,
oid order_name previous_balance customer
11 ord1 2000 Alex