Database management system lecture note.

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education SQL Operators: Arithmetic, Comparison, Logical Operator, SQL BETWEEN Operator, NOT BETWEEN Operator, SQL IN Operator, NOT IN Operator, Like etc.

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education SQL Operator:- SQL Operator: Arithmetic Operator, Comparison Operator, Logical Operator, In & Not In Operator, Between and Not Between Operator, LIKE Operator. Arithmetic Operator: +, - , * , / and % Comparison Operator: >, <, >=, <=, =, != Logical Operator: AND, OR, NOT, IN, BETWEEN, LIKE

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education SQL Operator :- SQL IN and NOT IN operators used to specify multiple values in a WHERE clause. SQL IN:- SQL IN condition allow only specific value in INSERT, UPDATE, DELETE, SELECT statement. Syntax: WHERE column_name IN (value1, value2, ...); SQL> SELECT * FROM emp WHERE deptno IN (10, 20); SQL NOT IN:- Syntax: WHERE column_name NOT IN (value1, value2, ...); SQL> SELECT * FROM emp WHERE deptno NOT IN (10, 20);

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education SQL Operator :- The SQL BETWEEN Operator:- The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included. Syntax: SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; Example: SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education SQL Operator :- The SQL NOT BETWEEN Operator:- The NOT BETWEEN operator selects values within a given not range. The values can be numbers, text, or dates. Syntax: SELECT column_name(s) FROM table_name WHERE column_name NOT BETWEEN value1 AND value2; Example: SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education LIKE Operator: • The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. • There are two wildcards often used in conjunction with the LIKE operator: • The percent sign (%) represents zero, one, or multiple characters • The underscore sign (_) represents one, single character • LIKE Syntax • SELECT column1, column2, … FROM table_name WHERE columnN LIKE pattern;

BCA Sem - III DBMS - II Unit III – Join and Oracle Function Centre for Distance and Online Education LIKE Operator:

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education Like Operator Example: Q1. list names of employee having ‘A’ as second letter in their names. SQL> select ename from emp where ename like '_A%'; ENAME ---------- JAMES MARTIN WARD Q2. list names of employee having ‘A’ as first letter in their names. SQL> select ename from emp where ename like 'A%'; ENAME ---------- ADAMS ALLEN

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education Like Operator Example: Q3. list names of employee having ‘R’ as last letter in their names. SQL> select ename from emp where ename like '%R'; ENAME ---------- MILLER TURNER

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education Like Operator Example: Q4. list names of employee having ‘R’ as last letter in their names. SQL> select ename from emp where ename like '%R'; ENAME ---------- MILLER TURNER Q5. list names of employee having ‘I’ as second last letter in their names. SQL> select ename from emp where ename like '%I_'; ENAME ---------- MARTIN

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education SET Operations in SQL: SET Operations in SQL • SQL supports few Set operations which can be performed on the table data. These are used to get meaningful results from data stored in the table, under different special conditions. • In this tutorial, we will cover 4 different types of SET operations, along with example: 1. UNION 2. UNION ALL 3. INTERSECT 4. MINUS

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education SET Operations in SQL:UNION Operation UNION is used to combine the results of two or more SELECT statements. However it will eliminate duplicate rows from its result set. In case of union, number of columns and data type must be same in both the tables, on which UNION operation is being applied.

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education SET Operations in SQL:UNION Operation

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education SET Operations in SQL:UNION ALL • UNION ALL • This operation is similar to Union. But it also shows the duplicate rows.

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education SET Operations in SQL:UNION ALL

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education SET Operations in SQL:INTERSECT Operation • INTERSECT • Intersect operation is used to combine two SELECT statements, but it only retuns the records which are common from both SELECT statements. In case of Intersect the number of columns and datatype must be same.

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education SET Operations in SQL:INTERSECT Operation

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education SET Operations in SQL:MINUS Operation

BCA Sem - III DBMS - II Unit II – SQL OPERATORS Centre for Distance and Online Education Thank You Email-ID: nalawadesantosh011@gmail.com

Database management system lecture note.

More Related Content

Similar to Database management system lecture note.

More from chibunnajoe31

Recently uploaded

Database management system lecture note.