Intorduction SQL Prepared BY Mitali Sonar Assistant Professor

Intorduction SQL Prepared BY: Mitali Sonar (Assistant Professor) 1 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

What is database? Collection of information/ meaningful data Ex: - postal address Building name Flat name Road name Area State pin code Values in this fields are data Address book is database 2 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

What is DBMS? Software that designs, manages database DBMS allows insert, update, delete and processing of data in database Ex: Oracle Ingres Dbase MS. Access 3 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

What is relational DBMS based on relational model Stores data in form of related data/tables Dbase can Be viewed in many different ways Ex: Oracle Microsoft SQL server Sybase SQL Server DB 2 My. SQL 4 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

DBMS Vs. RDBMS 5 DBMS Relationship between two tables or files are maintained programmatically Relationship between tables or files are maintained at the time of their creation Does not support client/ server architecture Support client/ server architecture No security of the data Multiple levels of the security • At O/S level • Command Level • Object level Field, records, files Columns (attribute) Rows (tuple) Table (relation) Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Introduction to SQL Structured Query Language Provides interface to RDBMS Language for communication with oracle server to access data Category/ component of SQL DDL commands (data definition language) To create a database objects not data Define relation schema , deleting relations and modifying relational schemas Ex: - CREATE : - Used to create a db object ALTER : - alter structure of DB DROP: - Delete the object from DB TRUNCATE : - Remove all records from DB 6 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Introduction to SQL (conti…) DML commands (Data Manipulation Language) To manipulate data of a database objects Includes commands to insert tuples into , delete tuples from and modify tuples in database. INSERT : - Insert data into table UPDATE: -Updates existing data within table DELETE : - deletes all records from table DQL command (Data Query Language) To retrieve the data from a database Getting data from database and imposing ordering upon it SELECT: - retrieve 7 data from the DB Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Introduction to SQL (conti…) DCL commands (Data Control Language) Controlled access to data GRANT - Used to give privilege to user on object REVOKE - Used to withdraw the privilege that has been granted to the user. COMMIT: - save work done ROLLBACK : - restore the database to original state since last commit 8 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Basic data type char(size) A fixed length character string with only 255 user specified characters Data held is right padded with spaces to whatever size is specified varchar 2(size) A variable character length string with alphanumeric values maximum length 4000 characters Inserted values are not contains white spaces Date a calendar date containing a year, month and day of the month DD-MON-YYYY Number (P, S) Used to store a number (fixed or floating point). Max precision can be 38 digits P – max length of the data S- number of places to the right of decimal 9 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

TABLE fundamentals Table holds user data & Similar to a spreadsheet Consist of rows (tuples) & columns (attribute) Each column have data type associated with it. Syntax : CREATE TABLE table_name (col 1 datatype(size), …. , coln datatype(size) ); OUTPUT: - 10 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Inserting data into tables Loads the value passed by SQL insert command into columns specified INSERT INTO tablename VALUES(<val 1>, <val 2>……. <valn>); 11 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Viewing data in the tables SELECT – is used to retrieve rows from one or more tables All rows & all columns SELECT * FROM tablename; Ex: - show all employee details 12 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Viewing data in the tables (cont. . ) Selected columns & all rows SELECT Column 1, Column 2 FROM tablename; 13 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Viewing data in the tables (cont. . ) Selected Rows and all Columns SELECT * FROM tablename WHERE condition Ex: - Display details of employee who lives in andheri area Ex: - Display details of employee who live in vile parle area 14 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Viewing data in the tables (cont. . ) Selected Rows and selected Columns SELECT Column 1, Column 2 FROM tablename WHERE condition Ex: - list eno and ename who lives in vile parle area 15 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Eliminate duplicate values DISTINCT It scans through the values of columns specified and display only unique values among them SELECT DISTINCT column 1 FROM tablename; 16 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Sorting the data ORDER BY : -retrieves rows from table either ascending or descending order SELECT * FROM tablename ORDER BY colname (sortorder); Ex: - show details of employee according to department name. 17 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

DELETE operation Deletes rows from table that satisfies the condition provided by where clause Removal of all rows form the table DELETE FROM tablename; Removal of specific rows DELETE FROM tablename where Condition; 18 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Update the content of table Change or modify the data values in table Updating all rows UPDATE tablename SET col 1 = expression 1; Ex: - update the department name to research for all employees 19 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Update the content of table Change or modify the data values in table Updating rows conditionally UPDATE tablename SET col 1 = expression 1 WHERE condition; Ex: - update the address of employee sanket from sion to navi mumbai NEW TABLE 20 Prepared By: Mitali Sonar (Assistant Professor) OLD TABLE 12/28/2021

Modifying the structure of the tables ALTER TABLE : -Changes the structure of existing table Add, delete columns / changes the datatype of columns /rename the columns or table itself Adding a new column ALTER TABLE tablename ADD (new_column _name datatype(size)); Ex: - Enter a new field called contactno in table 21 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

tables(cont. . ) Dropping a column from a table ALTER TABLE tablename DROP COLUMN column name; Ex- drop the column contactno from the employee table 22 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

tables(cont. . ) Modifying Existing columns ALTER TABLE tablename MODIFY (colnname newdatatype (newsize)); Ex: - Change the employee name field to hold Maximum 30 characters 23 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Restriction to ALTER TABLE Following task can’t be performed using ALTER TABLE Change the name of the table /columns Decrease size of column RENAME TABLES REANAME tablename to newtablename; Ex: - change the name of table to emp_data; RENAME employee_data TO emp_data; 24 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

TRUNCATING TABLES Empties a table completely Different from delete in following ways Drop and recreates the table that is much faster than deleting Truncate are not transaction-safe No. of deleted rows are not returned TRUNCATE TABLE tablename; Ex: - clear all records from employee table Output DESTROYING TABLES When table becomes obsolete and needs to be discarded. Destroys a specific table DROP TABLE tablename; Ex: -DROP TABLE emp_data; O/P : - table dropped. 25 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Examining objects created by users Finding out tables created by user SELECT table_name FROM user_tables; Displaying the table structure To display information about columns defined in a table DESCRIBE tablename; 26 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Data constraints Set of rules that must be applied to data prior to being stored in DB Ex: - no employee in sales department should have salary more than 10, 000 If there is any violation between the constraint and the data action, the action is aborted by the constraint. Once constraints is attached to table any SQL INSERT or UPDATE statement automatically causes these constraints to be applied to data before its being stored in table 2 types I/O constraint Business rule constraints 27 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Data constraints In SQL, we have the following constraints: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK DEFAULT 28 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

I/O Constraint PRIMARY KEY CONSTRAINTS One or more column in table that uniquely identify each tuple No primary key column can contain NULL value primary key column is a mandatory column Data held across column must be unique. Single column primary key is Simple key A multi column primary key is a Composite primary key Defined either in CREATE TABLE statement or in ALTER TABLE statement 29 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

PRIMARY KEY CONSTRAINTS Syntax : Column-name datatype(size) primary key Ex: CREATE TABLE Employee( ENO number(4) primary key, ENAME varchar 2(20)); PRIMARY KEY (column-name 1, column-name 2) Ex: CREATE TABLE Employee( ENO number(4), ENAME varchar 2(20), CONSTRAINT pk_ENO primary key (ENO)); EX: - ALTER TABLE Employee ADD CONSTRAINT pk_ENO primary key (ENO)); 30 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Foreign key constraints Represents relationship between two tables Its value is derived from primary key of the other table Table in which foreign key is defined is called foreign table Table in which primary key is defined is called master table Syntax : - REFERENCES tablename. columnname Features Parent that is being referenced has to be unique Child may have duplicate Parent record can be deleted provided no child exists Master table cannot be updated if child record exists 31 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Foreign key constraints create table branch (branch_name char(15), branch_city char(30), assets number(12, 2), primary key (branch_name )); create table account (account_number char(10), branch_name char(15), balance integer, primary key (account_number), references branch (branch_name) ); 32 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Foreign key constraint Insert/update operations in foreign A value cannot be inserted in foreign table if corresponding value is not presented in master table 33 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Foreign key constraints Delete operation on primary key table(master table) Oracle display error message when deleting a record form master table and corresponding record exists in foreign key table This will prevent the delete operation form execution 34 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

ON DELETE CASCADE If a record is deleted in master table , all corresponding records in foreign key table is also deleted. create table "branch" ( `"branch_name" char(15), "branch_city" char(30), "assets" number(12, 2), constraint "branch_con" foreign key ("branch_name") references "account" ("branch_name") ON DELETE CASCADE ENABLE ) 35 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

36 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

ON DELETE SET NULL If a record is deleted from Master table, the value held by foreign key table is set to Null The records in foreign key table will not deleted create table "branch" ( "branch_name" char(15), "branch_city" char(30), "assets" number(12, 2), constraint "branch_con" foreign key ("branch_name") references "account" ("branch_name") ON DELETE SET NULL) 37 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Unique key constraint It permits multiple entries of NULL into column It will not allowed duplicate value A table can have more than one unique key that is not possible for primary key ALTER TABLE customer ADD CONSTRAINT Cust_con UNIQUE (cust_no); 38 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

BUSINESS RULE CONSTRAINT Rules are applied to data prior the data is being inserted This will ensure that data in table have integrity Ex; - Rule ” no employee in company shall get a salary less than 10000” Inserted to column or table using create table or alter table Constraints can be defined at Column level Table level 39 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

NULL VALUE CONCEPTS Some fields do not have values because information is not available at time of data entry Will place NULL value that is different from blank(if data type is character) or zero(if data type is number) Null can be inserted in columns of any data type If a column has NULL value than oracle ignore UNIQUE, FOREIGN KEY, CHECK constraints NOT NULL concept Ensures that column cannot left empty Only be applied at column level If column is defined as not null, that column becomes a mandatory column 40 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

CHECK CONSTRAINT used to limit the value range that can be placed in a column. Specified as logical expression that evaluates as TRUE or FALSE CHECK constraint on a single column allows only certain values for this column. CHECK constraint on a table can limit the values in certain columns based on values in other columns in the row. CREATE TABLE Persons ( P_Id int NOT NULL, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Address varchar(255), City varchar(255), CONSTRAINT chk_Person CHECK (P_Id>0 and City=‘Ahmedabad') ) 41 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Create table customer_master ( cust_no varchar 2(5), Fname varchar 2(10), Lname varchar 2(10), CHECK (cust_no like ‘C%’), CHECK (Fname =UPPER (FNAME)) ); If condition has false value error message will be displayed and processing stops there 42 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Default Constraint used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. CREATE TABLE Persons( P_Id int NOT NULL, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Address varchar(255), City varchar(255) DEFAULT ‘ahmedabad'); 43 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Create the tables described as follow Table name: Product Master 44 Column Name Data Type Size Attribute PRODUCT NO Varchar 2 6 PRIMARY KEY/ first letter must start with ‘P’ DESCRIPTION Varchar 2 15 Not Null PROFITPERCE NT Number 4, 2 Not Null SELLPRICE Number 8, 2 Not Null, cannot be 0 COSTPRICE Number 8, 2 Not null, cannot be 0 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Computations done on table data manipulates individual data items and returns a result. Ex: - display all employee’s detail with their annual salary (salary *12) Arithmetic operators / * + - 45 Division (numbers and dates) Multiplication Addition (numbers and dates) Subtraction (numbers and dates) SELECT SAL / 10 FROM EMP; SELECT SAL * 5 FROM EMP; SELECT SAL + 200 FROM EMP; SELECT SAL - 100 FROM EMP; Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Customer Table C_code cname city area country grade C 00013 Holmes London UK New C 00001 Micheal USA York opening receive_ payment outstand _amt ing_amt 2 6000. 00 5000. 00 7000. 00 4000. 00 2 3000. 00 5000. 00 2000. 00 6000. 00 SELECT cname, opening_amt, receive_amt, (opening_amt + receive_amt) FROM customer WHERE (opening_amt + receive_amt)>15000; 46 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

expression SQL aliases are used to give a column in a table, a temporary name. SELECT column_name AS alias_name FROM table_name; C_ID C_Name 1 Alfreds Maria Futterkiste Anders Obere Str. Berlin 57 12209 Germany 2 Ana Trujillo Emparedad os y helados Avda. de la México Constitució D. F. n 2222 05021 Mexico 3 Antonio Moreno Taquería Mataderos México 2312 D. F. 05023 Mexico 47 Contact. Na Address me Antonio Moreno Prepared By: Mitali Sonar (Assistant Professor) City Postal. Code Country 12/28/2021

SELECT C_Name, Address+City+Postal. Code+Country AS Address FROM Customers; 48 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Logical operators in SQL will return either true or false value. Logical Description Operators OR 49 At least one of the conditions must be true. AND All the specified conditions must be true. NOT Reversed the result of logical expression Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

"OR" Logical Operator: find the details of students who are studying either Maths or Science, the query would be like, SELECT * FROM studentdetails WHERE sub 1 = 'Maths' OR sub 1= 'Science' 50 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

AND logical operator Joins two or more conditions, and returns results only when all of the conditions are true find the details of students, who got marks more than 70 and less than 100 Select * from studentdetails Where Marks >70 and Marks<100; 51 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

NOT logical operator Reversed the result of logical expression Find rows that do not satisfy a condition Find details of the students who are not Studying Maths SELECT * FROM studentdetails WHERE NOT sub 1= ‘Maths' ; 52 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Combining OR and AND Operators When the AND operator is combined with OR Oracle server will evaluate the condition connected by the AND first before any conditions connected with OR. • Parenthesis must be used to force an order of operation. Find out details of the students Who studying Maths or Science And marks >=70 • • SELECT * FROM studentdetails where Sub 1=‘Maths’ or sub 1 =‘Science’ AND Marks>=70 SELECT * FROM studentdetails where ( Sub 1=‘Maths’ or sub 1 =‘Science’) AND Marks>=70 53 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Employee First name Last Name Address 1 City State P_name Basic_cost Profit_rate Selling _Price Sales-master P_no List all employee who stay in city ‘Banglore’ or ‘Manglore’ List details of employee who are not inhabit in the state of ‘Maharashtra’ List the products whose selling price is more than 500 and less than 750. Calculate a new selling price as basic_cost plus profit rate and rename this column in output as new_price 54 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Range searching IN and NOT IN operator testing a condition by comparing it with a list of fixed values. column value is equal to any one of a specified set of values find the names of students who are studying either Maths or Science, SELECT * FROM studentdetails WHERE sub 1 IN ('Maths', 'Science'); 55 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Find out details of students who got marks either 90 or 80 or 60 Select * from studentdetails where marks in (90, 80, 60); Select * from studentdetails where marks not in (90, 80, 60); 56 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

BETWEEN and NOT BETWEEN operator Tests an expression against a range In between two values This eliminates the need to use a more complex WHERE clause involving the use of the AND logical operator. Find out details of students who got marks in the range 70 to 90 Select * from studentdetails where marks BETWEEN 70 AND 90 Select * from studentdetails where marks NOT BETWEEN 70 AND 90 57 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

LIKE AND NOT LIKE operator used to search for data rows containing incomplete or partial character strings within a data column. When one does not know the exact value for the search conditions Find the details of employee Whose name start with ‘pa’ Select * from employee Where empname 1 like ‘pa%’; Select * from employee Where empname 1 not like ‘pa%’; 58 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

LIKE AND NOT LIKE operator Symbol Represents % match any string of any length _ match on a single character [ ] (brackets) any single character within a specified range such as 'a' to 'd', inclusive [a-d] or a set of characters such as [aeiouy] [^] (not brackets) any single character not in the specified range or set. (e. g. , [^a-f] ) LIKE '%en%' will search for every word that has the letters 'en' in it (Green, Benten) LIKE '_heryl' will search for every six-letter name ending with 'heryl' (Cheryl). 59 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

LIKE '[CK]ars[eo]n ' will search for every six-letter name begins with a 'C' or 'K' (not both of them) and Second last letter should be 'e' or 'o' (e. g. , 'Carsen, ' 'Karsen, ' 'Carson, ' and 'Karson‘) LIKE '[P-T]inger' will search for all the names ending with 'inger‘ begin with any single letter ‘P' thru ‘T' (Singer, Ringer). LIKE 'M[^c]%' will search for all the names begin with 'M' not having 'c' as the second letter. 60 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

SQL GROUP Functions built-in SQL functions that operate on groups of rows return one value for the entire group. COUNT, MAX, MIN, AVG, SUM, DISTINCT COUNT (): Returns the number of rows in the table that satisfies the condition specified in the WHERE condition. SELECT COUNT(marks) from Studnetdetails Where marks>70 SELECT COUNT(*) from studentdetails. 61 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

SQL GROUP Functions MAX(): used to get the maximum value from a column. To get the maximum marks SELECT MAX(MARKS) from studentdetails MIN(): is used to get the minimum value from a column. To get the minimum marks SELECT MIN(MARKS) from studentdetails 62 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

SQL GROUP Functions AVG(): The average value of a numeric column. To get the average marks, SELECT AVG (marks) FROM studentdetail; SUM(): sum of a numeric column To get the total marks given out to the students, SELECT SUM (marks) FROM studentdetail; DISTINCT(): to select the distinct rows. SELECT distinct(marks) from studentdetail; 63 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

List out the names of students having ‘a’ as the second letter in their name Count the total number of students. Determine max and min marks of the students. Count the number of students who got more than 50 marks in science subject 64 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Mathematical functions CEIL (or CEILING) and FLOOR round the numbers CEIL roundup to the nearest integer value FLOOR round down to the next least integer value. SELECT CEIL(10. 19) AS ceil_value, FLOOR(10. 19) AS floor_value OUTPUT ceil_value floor_value ---------- 65 11 10 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

RAND Function used to generate some random numbers at run time SELECT RAND() AS random_value OUTPUT random_value -------0. 4179369289595 66 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

ABS function returns the absolute value of an numeric input argument. EX SELECT ABS(-10) as arg 1, ABS(10) AS arg 2 OUTPUT arg 1 arg 2 ------10 10 67 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

SQRT function extracts the square root from the positive numeric input argument. SELECT SQRT(100) AS sqrt_of_100 Output Sqrt of 100 10 POWER function SELECT POWER(10, 2) AS power_value OUTPUT Power_value 100 68 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

ROUND Function rounds a number to a specific length or precision SELECT ROUND(10. 09 , 1) positive_rounded_value, ROUND(10. 09 , -1) negative_rounded_value OUTPUT positive_rounded_value negative_rounded_value ---------------------10. 1 10 69 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Character Functions Character functions Case conversion functions LOWER UPPER INITCAP Character manipulation functions CONCAT SUBSTR LENGTH INSTR LPAD

String function LOWER( Converts string into ) lowercase Select LOWER(‘ABCD’) from dual UPPER() Select UPPER(‘abc’) from dual Converts string into uppercase INITCAP Converts first letter of each () word in uppercase 71 Prepared By: Mitali Sonar (Assistant Professor) Select INITCAP(‘abcd’) from dual 12/28/2021

LTRIM(string, trimlist) & RTRIM(string, trimlist) Returns string with the leftmost or rightmost characters that match the characters in trimlist SUBSTR(string, pos, len) Returns the substring of string which begins at pos and is len characters long ASCII() Return numeric value of left-most character 72 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

INSTR() Return the index of the first occurrence of substring LENGTH(string) Return the length of a string in characters LPAD(string, length, pad) & RPAD(string, length, pad) Return the string argument, left-padded or right padded with the specified string 73 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

REPLACE(string, target, replacement) Returns string with all occurrences of target replaced with replacement REVERSE(str) Returns the string str with the order of the characters reversed. CONCAT(str 1, str 2) Returns the string that results from concatenating the arguments. 74 Prepared By: Mitali Sonar (Assistant Professor) 12/28/2021

Working with Dates Oracle stores dates in an internal numeric format: century, year, month, day, hours, minutes, seconds. The default date format is DD-MON-YY. SYSDATE is a function returning date and time. DUAL is a dummy table used to view SYSDATE.

Date Functions Function Description MONTHS_BETWEEN Number of months between two dates ADD_MONTHS Add calendar months to date NEXT_DAY Next day of the date specified LAST_DAY Last day of the month

Using Date Functions • MONTHS_BETWEEN ('01 -SEP-95', '11 -JAN-94') 19. 6774194 • ADD_MONTHS ('11 -JAN-94', 6) '11 -JUL-94' • NEXT_DAY ('01 -SEP-95', 'FRIDAY') '08 -SEP-95' • LAST_DAY('01 -SEP-95') '30 -SEP-95'