Name: Akshaya Suresh

Reg No.: 20MID0130

PRINCIPLES OF DATABASE SYSTEMS
LAB EXERCISE 3

Aim: To execute operators and functions in SQL

Creating a table and displaying description of the table:
Command:
create table employees(emp_id number(5), emp_name varchar2(20), phone_num number(10),
salary number(10), address varchar2(20), city varchar2(15), postal_code number(3), country
varchar2(15));
desc employees;
Result: A table named employees is created and its description is displayed.

Inserting values into the table:

Command:
insert into employees values(1, 'Sam', '9976342180', 500000, 'Shanti Nagar', 'Bangalore', 343,
'India');
insert into employees values(2, 'Rahul', '9996348880', 600000, '7/8 Ansari Road', 'Delhi', 550,
'India');
insert into employees values(3, 'Stacy', '9973247700', 600000, '37 Hudson Street', 'Wayne', 778,
'US');
insert into employees values(4, 'Harry', '9973233711', 6000000, '110 Lairg Road', 'newburgh',
440, 'UK');
insert into employees values(5, 'Grace', '9155247120', 8000000, '121 Corsair Place', 'Fitzroy',
511, 'New Zeeland');
Result: The values are entered into the table.

Displaying data from the table:

Command: select * from employees;
Result: The values entered into the table are displayed.

Arithmetic operators:
Operator ‘+’:
Command: Select salary, salary+500 from employees;
Result: Salary of employees are incremented by 500 and displayed.
Operator ‘-’:
Command: Select salary, salary-300 from employees;
Result: Salary of employees are decremented by 300 and displayed.

Operator ‘*’:
Command: Select salary, salary*600 from employees;
Result: Salary of employees are multiplied by 600 and displayed.

Operator ‘/’:
Command: Select salary, salary/100 from employees;
Result: Salary of employees are divided by 100 and displayed.

Concatenation:
Command: Select emp_name || emp_id as "empns" from employees;
Result: emp_name and emp_id are concatenated and displayed.
Comparison operator:
Operator ‘=’:
Command: Select salary from employees where salary =6000000;
Result: Salary of employee having 6000000 is displayed.

Operator !=:
Command: Select salary from employees where salary !=6000000;
Result: Salaries of employees not having 6000000 are displayed.

Operator ‘<’:
Command: Select salary from employees where salary < 8000000;
Result: Salaries of employees having less than 8000000 are displayed.

Operator ‘>’:
Command: Select emp_id from employees where emp_id > 1;
Result: Employee ids of employees having greater than 1 are displayed.
Operator ‘>=’:
Command: Select emp_id from employees where emp_id >= 3;
Result: Employee ids of employees having greater than or equal to 3 are displayed.

Operator ‘<=’:
Command: Select salary from employees where salary <= 700000;
Result: Salaries of employees having less than or equal to 700000 are displayed.

BETWEEN:
Command: Select * from employees where salary between 400000 and 700000;
Result: Data of employees having salaries between 400000 and 700000 are displayed.
NOT BETWEEN:
Command: Select * from employees where salary not between 400000 and 700000;
Result: Data of employees having salaries not between 400000 and 700000 are displayed.

IN:
Command: Select country from employees where country in('India', 'UK');
Result: Employee ids and countries of those employees belonging to India ad UK are
displayed.

NOT IN:
Command: Select country from employees where country not in('India', 'UK');
Result: Countries of those employees not belonging to India ad UK are displayed.
LIKE:
Command: Select emp_name from employees where emp_name like ‘S%’;
Result: Employees whose names begin with S are displayed.

NOT LIKE:
Command: Select emp_name from employees where emp_name not like ‘S%’;
Result: Employees whose names do not begin with S are displayed.

IS NULL:
Command: Select phone_num from employees where phone_num is null;
Result: None of the values in column phone_num is a null value. Hence no rows are
displayed.
NOT NULL:
Command: Select phone_num from employees where phone_num is not null;
Result: None of the values in column phone_num is a null value. Hence all rows are
displayed.

Logical operators:
AND:
Command: Select emp_id, emp_name, salary from employees where salary>=10000 and
emp_id > 3;
Result: Data of employees having salary greater than or equal to 10000 and employee id
greater than 3 are displayed.

OR:
Command: Select emp_id, emp_name, salary from employees where salary>=10000 or
emp_id > 3;
Result: Data of employees having salary greater than or equal to 10000 or employee id greater
than 3 are displayed.
NOT:
Command: Select emp_name from employees where emp_name not in('Sam', 'Stacy');
Result: Employee names that are not Sam and Stacy are displayed.

Sorting:
Ascending order:
Command: SELECT * FROM employees ORDER BY country;
Result: Data from table are arranged in ascending order of country name by default and
displayed.
Descending order:
Command: SELECT * FROM employees ORDER BY country DESC;
Result: Data from table are arranged in descending order of country name and displayed.

Ascending and descending order:

Command: SELECT * FROM employees ORDER BY country ASC, emp_name DESC;
Result: Data from table are arranged in ascending order of country name and descending order
of employee name and displayed.
Functions:- Single row functions:
Character functions:
Case changing:
Command: SELECT lower(‘APPLE’) FROM DUAL;
Result: ‘APPLE’ is changed to lower case ‘apple’

Command: SELECT upper(‘apple’) FROM DUAL;

Result: ‘apple’ is changed to upper case ‘APPLE’

Command: SELECT initcap(‘AKSHAYA SURESH’) FROM DUAL;

Result:Changes first letter to capital as Akshaya Suresh.

Concatenation:
Command: SELECT CONCAT(‘Global’,’Warming’) FROM DUAL;
Result: Global Warming is concatenated to a single word.
Replacing:
Command: SELECT replace(‘DATA SYSTEMS’, ‘DATA’, ‘DATABASE’) FROM DUAL;
Result: The word DATA in DATA SYSTEMS is replaced with DATABASE.

Substrings:
Command: SELECT substr(‘DATA SCIENCE’,6,3) FROM DUAL;
Result: A substring SCI is displayed from DATA science.

Command: SELECT subtr(‘DATA SCIENCE’,-8) FROM DUAL;

Result: A substring SCIENCE is displayed from DATA science.

Left pad:
Command: SELECT lpad(‘200’,6,’$’) FROM DUAL;
Result: $ symbol is padded on the right side of 200.

Right pad:
Command: SELECT rpad(‘200’,6,’$’) FROM DUAL;
Result: $ symbol is padded on the left side of 200.
Length of the string:
Command: SELECT length(‘Akshaya Suresh’) FROM DUAL;
Result: Length of the string is displayed.

Command: SELECT instr(‘Computer Science’,’Science’) FROM DUAL;

Result: Position of the substring Science from computer Science is displayed.

Command: SELECT trim(‘n’ FROM ‘Education’) FROM DUAL;

Result: The letter ‘n’ is trimmed from the word Education and displayed.

Numeric functions:
Command: SELECT round(533.55) FROM DUAL;
Result: The number 533.55 is rounded to 534 and displayed.

Command: SELECT trunc(533.55,1) FROM DUAL;

Result: The number 533.55 is truncated to 533.5 and displayed.
Command: SELECT mod(9,2) FROM DIAL;
Result: Mod(9,2) = 1 is found and displayed.

SysDate functions:
Command: SELECT sysdate FROM DUAL;
Result: Present date is displayed.

Command: SELECT sysdate+3 “add_days” FROM DUAL;

Result: 3 days are added to present date and is displayed.

Command: SELECT sysdate-4 “sub_days” FROM DUAL;

Result: 4 days are deducted from present date and is displayed.

Command: SELECT sysdate+4/24 “add_hours” FROM DUAL;

Result: 4 hours are added to present date and is displayed.
Command: SELECT months_between(’01-JUN-2021’, ’01-JAN-2021’) FROM DUAL;
Result: Number of months between 1st of January and 1st of June 2021 are displayed.

Command: SELECT add_months(sysdate, 5) FROM DUAL;

Result: 5 months are added to present date and is displayed.

Command: SELECT add_months(sysdate, -5) FROM DUAL;

Result: 5 months are deducted from present date and is displayed.

Command: SELECT add_months(’03-JUL-2021’,10) FROM DUAL;

Result: 10 months are added to 3rd July 2021 and is displayed.

Command: SELECT add_months(’03-JUL-2021’,-10) FROM DUAL;

Result: 10 months are deducted from 3rd July 2021 and is displayed
Command: SELECT next_day(sysdate, ‘SUNDAY’) FROM DUAL;
Result: The date of next Sunday from present date is displayed.

Command: SELECT next_day(sysdate,7) FROM DUAL;

Result: The date of 7th day from 21st september 2021 is displayed

Command: SELECT next_day(‘21-SEP-2021’, ‘SUNDAY’) FROM DUAL;

Result: The date of next Sunday from 21st september 2021 is displayed.

Command: SELECT last_day(’01-SEP-2021’) FROM DUAL;

Result: The last date of september 2021 is displayed.

Command: SELECT round(sysdate, ‘MONTH’) FROM DUAL;

Result: The month is rounded from present date and is displayed.
Command: SELECT round(sysdate, ‘YEAR’) FROM DUAL;
Result: The year is rounded from present date and is displayed.

Group functions:
Number of rows:
Command: SELECT COUNT(emp_name) FROM employees;
Result: Returns number of rows.

Minimum value in the column:

Command: SELECT MIN(salary) FROM employees;
Result: Returns minimum value in the column salary.

Maximum value in the column:

Command: SELECT MAX(salary) FROM employees;
Result: Returns maximum value in the column salary.
Average value in the column:
Command: SELECT AVG(salary) FROM employees;
Result: Returns average value in the column salary.

Sum of values in the column:

Command: SELECT SUM(salary) FROM employees;
Result: Returns sum of values of the column salary.

Grouping by clause:
Creating a table with department code and employee ids belonging to a
department and inserting values.
Command:
create table dept(dept_code number(3), empid number(3));
insert into dept values(300, 3);
insert into dept values(300, 5);
insert into dept values(300, 7);
insert into dept values(300, 21);
insert into dept values(300, 55);
insert into dept values(100, 2);
insert into dept values(100, 4);
insert into dept values(100, 6);
insert into dept values(200, 11);
insert into dept values(200, 12);
Result: The table is created with values inserted.
Grouping based on number of employees in a department:
Command:
SELECT dept_code "Department Code",
2 COUNT(*) "No of Employees"
3 FROM dept
4 GROUP BY dept_code;
Result: The table is grouped based on number of employees in a particular department.