MIZAN TEPI UNIVERSITY

DEPARTMENT OF SOFTWARE ENGINEERING

FUNDAMENTALS OF DATABASE SYSTEMS

SEng 2041

LAB MANUAL
 1. SQL Commands

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 Commands

There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.

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), D

OB DATE);

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

Syntax
DROP TABLE table_name;

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 table_name MODIFY(column_definitions....);

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 staffsub (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 staffsub 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 staffsub 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 Privilege name ON object name TO {User name/Role name/Public}

[with grant option];

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

Example

REVOKE Privilege name ON object name FROM {User name/Role

name/Public};

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.

Syntax:

COMMIT;

Example:

Transaction T1

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.

Syntax:

ROLLBACK;

Example:

Transaction T1

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.

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;

2. INTEGRITY CONSTRAINTS:
There are 5 important Integrity Constraints

1. Not NULL
2. Primary Key
3. Unique
4. Foreign Key
5. Check Constraints

Not NULL:

 By default in SQL, column can hold NULL values.

 This constraint enforce a column to Not accept NULL
values. This enforces a field to always contain a value, which means that
you cannot insert a new record (or) update a record without adding a
value to this field.
Eg:
Create table Customers
( Cust_ID int Not NULL,
Name VarChar(20) Not NULL,
Primary Key(Cust_ID)
);

Unique Constraints:

 It is also referred to as Unique Key Constraints.

  It forbids duplicate values in one (or) more columns within a table.
 Unique key and Primary key are both supported Unique constraints.
 For eg: In Student table, if Stud_name is unique constraints means the
same name is not given to two students.
 Unique constraints allow NULL values.
Eg:
Create table student
( stud_ID int Not NULL,
Stud_name VarChar(20) unique,
Primary Key(stud_ID)
);

Primary Key Constraints:

 Primary key constraints have same properties of Unique constraints.

 It doesn’t support NULL values.
 We can use Primary key and foreign key constraints to define
relationships between tables.

Foreign Key Constraints:

 It is also called as referential constraint (or) a referential integrity

constraint.
 It is logical rule about values one (or) more columns in one (or) more
tables.
 This constraints prevent insert, update (or) delete operations that would
otherwise result in missing information.
 Foreign key is used to link two tables together.
 Foreign key in a table points to a Primary key in another table.

Consider two tables

Person table:

PersonID LastName FirstName Age

1 Kumar Ravi 30

2 Giri Raj 31

3 Peter David 25
 Order Table:

OrderID OrderNumber Person ID

1 77895 3

2 44678 3

3 22456 2

4 24562 1

 Here PersonID column in the order table points to the PersonID in the
Person Table.
 PersonID column in the “order” table is foreign key.
 Foreign key constraint is used to prevent actions that would destroy
links between tables.
 Foreign key constraint also prevents invalid data from being inserted into
the foreign key column.

SQL Server:

Create table Orders (orderid int Not NULL

Primary key, OrderNumber int Not NULL, Person ID

int foreign key references Person(Person ID));

Check Constraints:

 It is also called table constraint.

 It sets restrictions on data added to a specific table.
 For eg, ensure that the salary level for an employee is atleast 20,000 birr
whenever salary data is added (or) updated in a table it checks.
Eg:
Create table Persons
( ID int Not NULL,
LName Varchar (50) Not NULL,
Fname Varchar ( 50 ),
Age int Check ( age>=18 ));
 3. JOINS:
There are different types of joins available in SQL −
 INNER JOIN − returns rows when there is a match in both tables.
 LEFT JOIN − returns all rows from the left table, even if there are no
matches in the right table.
 RIGHT JOIN − returns all rows from the right table, even if there are no
matches in the left table.
 FULL JOIN − returns rows when there is a match in one of the tables.
 SELF JOIN − is used to join a table to itself as if the table were two
tables, temporarily renaming at least one table in the SQL statement.
 CARTESIAN JOIN − returns the Cartesian product of the sets of records
from the two or more joined tables.

SQL INNER JOIN Keyword

The INNER JOIN keyword selects records that have matching values in both
tables.
INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Demo Database

Below is a selection from the "Orders" table:

OrderID CustomerID EmployeeID OrderDate ShipperID

10308 2 7 1996-09-18 3

10309 37 3 1996-09-19 1

10310 77 8 1996-09-20 2

And a selection from the "Customers" table:

Custome CustomerNa ContactNa Address City PostalCo Count
rID me me de ry

1 Alfreds Maria Obere Str. Berli 12209 Germa

Futterkiste Anders 57 n ny

2 Ana Trujillo Ana Avda. de Méxi 05021 Mexico

Emparedado Trujillo la co
s y helados Constituc D.F.
ión 2222

3 Antonio Antonio Matadero Méxi 05023 Mexico

Moreno Moreno s 2312 co
Taquería D.F.

SQL INNER JOIN Example

The following SQL statement selects all orders with customer information:

Example
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all records from the left table (table1), and the
matched records from the right table (table2). The result is NULL from the right
side, if there is no match.

LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;

Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

Custome CustomerNa ContactNa Address City PostalCo Count

rID me me de ry

1 Alfreds Maria Obere Str. Berli 12209 Germa

Futterkiste Anders 57 n ny

2 Ana Trujillo Ana Avda. de Méxi 05021 Mexico

Emparedado Trujillo la co
s y helados Constituc D.F.
ión 2222
3 Antonio Antonio Matadero Méxi 05023 Mexico
Moreno Moreno s 2312 co
Taquería D.F.

And a selection from the "Orders" table:

OrderID CustomerID EmployeeID OrderDate

10308 2 7 1996-09-18

10309 37 3 1996-09-19

10310 77 8 1996-09-20

SQL LEFT JOIN Example

The following SQL statement will select all customers, and any orders they
might have:

Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all records from the right table (table2), and
the matched records from the left table (table1). The result is NULL from the
left side, when there is no match.

RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

Example
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT OUTER JOIN Employees ON Orders.EmployeeID =
Employees.EmployeeID
ORDER BY Orders.OrderID;

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword return all records when there is a match in
either left (table1) or right (table2) table records.

Note: FULL OUTER JOIN can potentially return very large result-sets!

Tip: FULL OUTER JOIN and FULL JOIN are the same.

FULL OUTER JOIN Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

The following SQL statement selects all customers, and all orders:

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

4. 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:
Simple View (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:

Complex View (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;

5. Set Operations:
SET operators are special type of operators which are used to combine the
result of two queries.

Operators covered under SET operators are:

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS

There are certain rules which must be followed to perform operations using
SET operators in SQL. Rules are as follows:

1. The number and order of columns must be the same.

2. Data types must be compatible.

Let us see each of the SET operators in more detail with the help of examples.
Consider we have the following tables with the given data.

Table 1: t_employees

ID Name Department Salary Year_of_Experience

1 Aakash Singh Development 72000 2

2 Abhishek Pawar Production 45000 1

3 Pranav Deshmukh HR 59900 3

4 Shubham Mahale Accounts 57000 2

5 Sunil Kulkarni Development 87000 3

6 Bhushan Wagh R&D 75000 2

7 Paras Jaiswal Marketing 32000 1

Table 2: t2_employees

ID Name Department Salary Year_of_Experience

1 Prashant Wagh R&D 49000 1

2 Abhishek Pawar Production 45000 1

3 Gautam Jain Development 56000 4

4 Shubham Mahale Accounts 57000 2

5 Rahul Thakur Production 76000 4

6 Bhushan Wagh R&D 75000 2

7 Anand Singh Marketing 28000 1

Table 3: t_students

ID Name Hometown Percentage Favourite_Subject

1 Soniya Jain Udaipur 89 Physics

2 Harshada Sharma Kanpur 92 Chemistry

3 Anuja Rajput Jaipur 78 History

4 Pranali Singh Nashik 88 Geography

5 Renuka Deshmukh Panipat 90 Biology

6 Swati Kumari Faridabad 93 English

7 Prachi Jaiswal Gurugram 96 Hindi

Table 4: t2_students

ID Name Hometown Percentage Favourite_Subject

1 Soniya Jain Udaipur 89 Physics

2 Ishwari Dixit Delhi 86 Hindi

3 Anuja Rajput Jaipur 78 History

4 Pakhi Arora Surat 70 Sanskrit

5 Renuka Deshmukh Panipat 90 Biology

6 Jayshree Patel Pune 91 Maths

7 Prachi Jaiswal Gurugram 96 Hindi

1. UNION:
o UNION will be used to combine the result of two select statements.
o Duplicate rows will be eliminated from the results obtained after performing the
UNION operation.

Example 1:

Write a query to perform union between the table t_employees and the table
t2_employees.

Query:

SELECT *FROM t_employees UNION SELECT *FROM t2_employees;

Here, in a single query, we have written two SELECT queries. The first SELECT
query will fetch the records from the t_employees table and perform a UNION
operation with the records fetched by the second SELECT query from the
t2_employees table.

You will get the following output:

ID Name Department Salary Year_of_Experience

1 Aakash Singh Development 72000 2

2 Abhishek Pawar Production 45000 1

3 Pranav Deshmukh HR 59900 3

4 Shubham Mahale Accounts 57000 2

5 Sunil Kulkarni Development 87000 3

6 Bhushan Wagh R&D 75000 2

7 Paras Jaiswal Marketing 32000 1

1 Prashant Wagh R&D 49000 1

3 Gautam Jain Development 56000 4

5 Rahul Thakur Production 76000 4

7 Anand Singh Marketing 28000 1

Since we have performed union operation between both the tables, so only the
records from the first and second table are displayed except for the duplicate
records.

Example 2:

Write a query to perform union between the table t_students and the table
t2_students.

Query:

SELECT *FROM t_students UNION SELECT *FROM t2_students;

Here, in a single query, we have written two SELECT queries. The first SELECT
query will fetch the records from the t_students table and perform a UNION
operation with the records fetched by the second SELECT query from the
t2_students table.

You will get the following output:

ID Name Department Salary Year_of_Experience

1 Soniya Jain Udaipur 89 Physics

2 Harshada Sharma Kanpur 92 Chemistry

3 Anuja Rajput Jaipur 78 History

4 Pranali Singh Nashik 88 Geography

5 Renuka Deshmukh Panipat 90 Biology

6 Swati Kumari Faridabad 93 English

7 Prachi Jaiswal Gurugram 96 Hindi

2 Ishwari Dixit Delhi 86 Hindi

4 Pakhi Arora Surat 70 Sanskrit

6 Jayshree Patel Pune 91 Maths

Since we have performed union operation between both the tables, so only the
records from the first and second table are displayed except for the duplicate
records.

2. UNION ALL
o This operator combines all the records from both the queries.
o Duplicate rows will be not be eliminated from the results obtained after
performing the UNION ALL operation.

Example 1:

Write a query to perform union all operation between the table t_employees and
the table t2_employees.
Query:

SELECT *FROM t_employees UNION ALL SELECT *FROM t2_employees;

Here, in a single query, we have written two SELECT queries. The first SELECT
query will fetch the records from the t_employees table and perform UNION ALL
operation with the records fetched by the second SELECT query from the
t2_employees table.

You will get the following output:

ID Name Department Salary Year_of_Experience

1 Aakash Singh Development 72000 2

2 Abhishek Pawar Production 45000 1

3 Pranav Deshmukh HR 59900 3

4 Shubham Mahale Accounts 57000 2

5 Sunil Kulkarni Development 87000 3

6 Bhushan Wagh R&D 75000 2

7 Paras Jaiswal Marketing 32000 1

1 Prashant Wagh R&D 49000 1

2 Abhishek Pawar Production 45000 1

3 Gautam Jain Development 56000 4

4 Shubham Mahale Accounts 57000 2

5 Rahul Thakur Production 76000 4

6 Bhushan Wagh R&D 75000 2

7 Anand Singh Marketing 28000 1

Since we have performed union all operation between both the tables, so all the
records from the first and second table are displayed, including the duplicate
records.

Example 2:

Write a query to perform union all operation between the table t_students and
the table t2_students.

Query:

SELECT *FROM t_students UNION ALL SELECT *FROM t2_students;

Here, in a single query, we have written two SELECT queries. The first SELECT
query will fetch the records from the t_students table and perform UNION ALL
operation with the records fetched by the second SELECT query from the
t2_students table.

You will get the following output:

ID Name Hometown Percentage Favourite_Subject

1 Soniya Jain Udaipur 89 Physics

2 Harshada Sharma Kanpur 92 Chemistry

3 Anuja Rajput Jaipur 78 History

4 Pranali Singh Nashik 88 Geography

5 Renuka Deshmukh Panipat 90 Biology

6 Swati Kumari Faridabad 93 English

7 Prachi Jaiswal Gurugram 96 Hindi

1 Soniya Jain Udaipur 89 Physics

2 Ishwari Dixit Delhi 86 Hindi

3 Anuja Rajput Jaipur 78 History

4 Pakhi Arora Surat 70 Sanskrit

5 Renuka Deshmukh Panipat 90 Biology

6 Jayshree Patel Pune 91 Maths

7 Prachi Jaiswal Gurugram 96 Hindi

Since we have performed union all operation between both the tables, so all the
records from the first and second table are displayed, including the duplicate
records.

3. INTERSECT:
o It is used to combine two SELECT statements, but it only returns the records
which are common from both SELECT statements.

Example 1:

Write a query to perform intersect operation between the table t_employees and
the table t2_employees.

Query:

SELECT *FROM t_employees INTERSECT SELECT *FROM t2_employees;

Here, in a single query, we have written two SELECT queries. The first SELECT
query will fetch the records from the t_employees table and perform
INTERSECT operation with the records fetched by the second SELECT query
from the t2_employees table.

You will get the following output:

ID Name Hometown Percentage Favourite_Subject

2 Abhishek Pawar Production 45000 1

4 Shubham Mahale Accounts 57000 2

6 Bhushan Wagh R&D 75000 2

Since we have performed intersect operation between both the tables, so only
the common records from both the tables are displayed.

Example 2:

Write a query to perform intersect operation between the table t_students and
the table t2_students.

Query:

SELECT *FROM t_students INTERSECT SELECT *FROM t2_students;

Here, in a single query, we have written two SELECT queries. The first SELECT
query will fetch the records from the t_students table and perform an
INTERSECT operation with the records fetched by the second SELECT query
from the t2_students table.

You will get the following output:

ID Name Hometown Percentage Favourite_Subject

1 Soniya Jain Udaipur 89 Physics

3 Anuja Rajput Jaipur 78 History

5 Renuka Deshmukh Panipat 90 Biology

7 Prachi Jaiswal Gurugram 96 Hindi

Since we have performed intersect operation between both the tables, so only
the common records from both the tables are displayed.

4. MINUS

o It displays the rows which are present in the first query but absent in the
second query with no duplicates.

Example 1:

Write a query to perform a minus operation between the table t_employees and
the table t2_employees.

Query:

SELECT *FROM t_employees MINUS SELECT *FROM t2_employees;

Here, in a single query, we have written two SELECT queries. The first SELECT
query will fetch the records from the t_employees table and perform MINUS
operation with the records fetched by the second SELECT query from the
t2_employees table.

You will get the following output:

ID Name Department Salary Year_of_Experience

1 Aakash Singh Development 72000 2

3 Pranav Deshmukh HR 59900 3

5 Sunil Kulkarni Development 87000 3

7 Paras Jaiswal Marketing 32000 1

Since we have performed Minus operation between both the tables, so only the
unmatched records from both the tables are displayed.

Example 2:

Write a query to perform a minus operation between the table t_students and
the table t2_students.

Query:

SELECT *FROM t_students MINUS SELECT *FROM t2_students;

Here, in a single query, we have written two SELECT queries. The first SELECT
query will fetch the records from the t_employees table and perform a MINUS
operation with the records fetched by the second SELECT query from the
t2_employees table.

You will get the following output:

ID Name Hometown Percentage Favourite_Subject

2 Harshada Sharma Kanpur 92 Chemistry

4 Pranali Singh Nashik 88 Geography

6 Swati Kumari Faridabad 93 English

Since we have performed a minus operation between both the tables, so only
the Unmatched records from both the tables are displayed.

5. Arithmetic Operators:

Arithmetic Operators are:

+ [Addition]
- [Subtraction]
/ [Division]
 * [Multiplication]
% [Modulus]

Addition (+) :
It is used to perform addition operation on the data items, items include
either single column or multiple columns.

Implementation:
SELECT employee_id, employee_name, salary, salary + 100 AS "salary +
100" FROM addition;
Output:

employee_id employee_name salary salary+100

1 alex 25000 25100

2 rr 55000 55100

3 jpm 52000 52100

4 ggshmr 12312 12412

Here we have done addition of 100 to each Employee’s salary i.e, addition
operation on single column.
Let’s perform addition of 2 columns:
SELECT employee_id, employee_name, salary, salary + employee_id AS
"salary + employee_id" FROM addition;
Output:

employee_id employee_name salary salary+employee_id

1 alex 25000 25001

2 rr 55000 55002
3 jpm 52000 52003

4 ggshmr 12312 12316

Here we have done addition of 2 columns with each other i.e, each employee’s
employee_id is added with its salary.
Subtraction (-) :
It is use to perform subtraction operation on the data items, items include
either single column or multiple columns.

Implementation:
SELECT employee_id, employee_name, salary, salary – 100 AS "salary - 100"
FROM subtraction;
Output:

employee_id employee_name salary salary-100

12 Finch 15000 14900

22 Peter 25000 24900

32 Warner 5600 5500

42 Watson 90000 89900

Here we have done subtraction of 100 to each Employee’s salary i.e,

subtraction operation on single column.
Let’s perform subtraction of 2 columns:
SELECT employee_id, employee_name, salary, salary - employee_id AS
"salary - employee_id" FROM subtraction;
Output:

employee_id employee_name salary salary – employee_id

12 Finch 15000 14988

22 Peter 25000 24978

32 Warner 5600 5568

42 Watson 90000 89958

Here we have done subtraction of 2 columns with each other i.e, each
employee’s employee_id is subtracted from its salary.
Multiplication (*) :
It is use to perform multiplication of data items.

Implementation:
SELECT employee_id, employee_name, salary, salary * 100 AS "salary * 100"
FROM addition;
Output:

employee_id employee_name salary salary * 100

1 Finch 25000 2500000

2 Peter 55000 5500000

3 Warner 52000 5200000

4 Watson 12312 1231200

Here we have done multiplication of 100 to each Employee’s salary i.e,

multiplication operation on single column.
Let’s perform multiplication of 2 columns:
SELECT employee_id, employee_name, salary, salary * employee_id AS
"salary * employee_id" FROM addition;
Output:

employee_id employee_name salary salary * employee_id

1 Finch 25000 25000

2 Peter 55000 110000

3 Warner 52000 156000

4 Watson 12312 49248

Here we have done multiplication of 2 columns with each other i.e, each
employee’s employee_id is multiplied with its salary.