Data Base Basic Instructions-2, Lecture notes of Database Programming

Download Data Base Basic Instructions-2 and more Lecture notes Database Programming in PDF only on Docsity!

Database System

CL 203 Lab 02 Functions, Operators And Dates

Count Operator

• (^) To count No of Rows in a Table using COUNT()
  • (^) SELECT COUNT(*) FROM EMP;

Arithmetic Operators

• (^) Simple Computation:
  • (^) SELECT 2*6 FROM dual;
• (^) Dual: Dummy Table
• (^) Date Arithmetic:
  • (^) Select TO_DATE (’31-jul-2012’,’DD-MON-YYYY’) + 2 FROM DUAL;
  • (^) Select TO_DATE (’02-AUG-2012’,’DD-MON-YYYY’) – TO_DATE (’31-JUL-2012’,’DD-MON-YYYY’) FROM dual;
• (^) To_Date: A function that converts string into date

Operator Precedence

• (^) Select (10 * 12 / 3 – 1) FROM DUAL;
• (^) Result will be?
• (^) Now Applying parenthesis:
  • (^) SELECT 10 * ( 12 / 3 -1) FROM DUAL;
• (^) Result?

Distinct Keyword

• (^) Used to refrain from duplicate rows/records
• (^) SELECT DISTINCT COLUMN_NAME FROM TABLE_NAME
• (^) Show names of all jobs in a department.
  • (^) SELECT DISTINCT JOB FROM EMP ;
• (^) Display no of jobs in a department
  • (^) SELECT COUNT(DISTINCT JOB) FROM EMP

Comparison Operators

Operator Description = Equal <> or != Not Equal < Less than > Greater than <= Less than or equal to >= Greater than or Equal to ANY Compares one value with any value in a list ALL Compares one value with all values in a list

Conditions in SQL

• (^) Like Operator: Used in pattern matching
  • (^) Underscore Character (_) Matches one character in a specific position
  • (^) Percent Character (%) Matches any number of characters beginning at the specified position
• (^) Syntax:
• (^) SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern

Like Operator

(Continued..)

• (^) List the employee names starting with ‘K’ and ending with ‘S’. - (^) SELECT ENAME FROM EMP WHERE ENAME LIKE ’K%S’
• (^) List the employees whose names having a character set ‘ll’ together. - (^) SELECT * FROM EMP WHERE ENAME LIKE ‘%LL%’;
• (^) Similarly, use NOT LIKE keyword to reverse the rows retrieved by previous query.
• (^) List the employees whose Employee number not starting with digit78. - (^) SELECT * FROM EMP WHERE EMPNO NOT LIKE ‘78%’;

IN Operator

• (^) It is used to select only those rows whose column value is in a list that you specify.
• (^) Syntax:
• (^) SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
• (^) List the employees who are working for the Department number 10 or20. - (^) SELECT * FROM EMP WHERE DEPTNO IN (10,20);
• (^) Similarly, use NOT IN keyword for the reverse of this query Output
• (^) List all the emps except ‘PRESIDENT’ & ‘MGR”
  • (^) Select * FROM EMP WHERE JOB NOT IN (‘PRESIDENT’,’MANAGER’);

Between Operator

• (^) Selects a range of data between two values
• (^) Syntax:
  • (^) SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value
• (^) List the emps Who Annual sal ranging from 30000 and 50000.
• (^) SELECT * FROM EMP WHERE SAL*12 BETWEEN 30000 AND 50000;

Order By (Continued…)

• (^) Similarly, We can sort data in Ascending and Descending Order
• (^) SELECT * FROM EMP ORDER BY ENAME [Order];
• (^) [Order]
  • (^) Asc: for ascending order
  • (^) Desc: for descending order
• (^) The default is Asc
• (^) Display all the unique job groups in the descending order? - (^) SELECT DISTINCT JOB FROM EMP ORDER BY JOB DESC;

Conversion Of NULL

values

• SELECT

ename,NVL(comm,0),NVL2(comm,sal+comm ,sal)as Income FROM emp;

• (^) NVL converts a NULL value to an actual value in this e.g to zero
• (^) NVL2: if commission is not null then return salary
  • commission else if commission is Null then return salary

Exercise

1.List ANNUAL salary of all employees along with their names and job type. 2.List the employees in the ascending order of their salaries. 3.Display no of Employees present in Employee table 4.List the employees information whose daily salary is more than Rs.100. 5.Select min and max hiredate. 6.Find all those employees which were hired between 23 Jan 81 and 3 Jan 83 7.Create a query that displays the employee name and commission amounts. If an employee doesn’t earn commission, put “No commission”. 8.select ename,nvl(to_char(comm),'no commision') from emp 9.Display each employee name, hiredate, and the day of the week on which the employee started. Label the column day. 10.Display the name of the employee who are working in the company for the past 35 years. 11.Display the total salary being paid to all employees.