DBMS
A
database management system (DBMS) is system software for creating and managing
databases. The DBMS provides users and programmers with a systematic way to
create, retrieve, update and manage data.
It
stands for Relational Database Management System. It is a particular type of
DBMS that uses a relational model for its databases. An RDBMS therefore enables
you to create relational databases
Ex: Oracle Database, MySQL, SQL Server, Microsoft Access,
PostgreSQL etc.
SQL
* PLUS
SQL*Plus
is an interactive and batch query tool that is installed with every Oracle
Database Server or Client installation. It has a command-line user interface,
which provides access to databases using SQL language.
Structure
Query Language(SQL) is a database query language used for storing and managing
data in Relational DBMS. SQL was the first commercial language introduced for
E.F Codd's Relational model of database. Today almost all RDBMS(Oracle,MySQL,
Infomix, Sybase, MS Access) use SQL as the standard database query language.
Categories
Of SQL Commands
Data
Definition Language(DDL):
Note
:All DDL commands are auto-committed.
That means it saves all the changes permanently in the database. We need not
give commit command to save the changes.
|
COMMAND |
DESCRIPTION |
|
1.Create |
To
Create a new table or user |
|
2.Alter Options
: Add,Modify,Drop & Rename |
To
change/Edit table’s structure |
|
3.Drop |
To
Drop a table/view |
|
4.Truncate |
To
Delete all rows in a table (can’t do rollback) |
|
5.Rename |
To
Rename a table/view |
CREATE
USER <USERNAME> IDENTIFIED BY <PASSWORD>;
Ex:
CREATE USER GURU IDENTIFIED BY GURU123;
Note1:
To create a new user we have to login
into the system as an admin and also login to Oracle database as admin. As a normal user we cannot create a new user.
Note2:
Now new user “GURU” will be created. But we cannot use it. To make use of it we
need to grant(provide) two roles namely “connect” and “resource” to user
“GURU”.
Syntax
: To grant connect role
Ex:
GRANT CONNECT TO GURU;
Syntax
: To grant resource role
Ex:
GRANT RESOURCE TO GURU;
Note3:
Now user “GURU” is ready to use.
Syntax:
To Create structure of a Table/Relation
CREATE
TABLE <TABLENAME>(COLNAME 1
DATATYPE[(SIZE)],
COLNAME 2
DATATYPE[(SIZE)],
COLNAME 3 DATATYPE[(SIZE)],
......
COLNAME N
DATATYPE[(SIZE)]);
Ex:
CREATE
TABLE PRODUCT(PID NUMBER(3),PNAME VARCHAR2(15),QTY NUMBER(4));
Alter
: ADD
Syntax:
ALTER
TABLE <TABNAME> ADD <COLNAME> DATATYPE[(SIZE)];
Ex:
ALTER
TABLE PRODUCT ADD EMAIL VARCHAR2(15);
Alter
: MODIFY
Note
: With modify option we can change Only
Data type/ Only Size/ both data type and
size.
Syntax:
ALTER
TABLE <TABNAME> MODIFY COLNAME [new DATATYPE][(new SIZE)];
Changing
Only Datatype
Ex:
ALTER
TABLE PRODUCT MODIFY PNAME CHAR(15);
Changing
Only Size
Ex:
ALTER
TABLE PRODUCT MODIFY PNAME CHAR(20);
Changing
Both datatype and size
Ex:
ALTER
TABLE PRODUCT MODIFY PNAME VARCHAR2(10);
Syntax:
ALTER
TABLE <TABNAME> DROP COLUMN <COLNAME>;
Ex:
ALTER
TABLE PRODUCT DROP COLUMN EMAIL;
Alter
: RENAME
Syntax:
ALTER
TABLE <TABNAME> RENAME COLUMN <OLDCOLNAME> TO <NEWCOLNAME>;
Ex:
ALTER
TABLE RENAME COLUMN PNAME TO PRONAME;
Note
: TRUNCATE command removes all the records from a table. But table's structure
remains unchanged.
TRUNCATE
TABLE <TABLENAME>;
Ex:
TRUNCATE
TABLE PRODUCT;
DROP
command completely removes a table from the database. This command will also destroy
the table structure and the data stored in it.
Syntax:
DROP
TABLE <TABLENAME>;
Ex:
DROP
TABLE PRODUCT;
RENAME
command is used to set a new name for any existing table.
Syntax:
RENAME
<OLD_TABLENAME> TO <NEW_TABLENAME>;
Ex:
RENAME
PRODUCT TO PROD;
DML
commands are used for manipulating(for modifying) the data stored in the table.
DML commands are not auto-committed. It means changes are not permanent to
database, they can be rolled back.
|
COMMAND |
DESCRIPTION |
|
Insert |
To
Insert data into table |
|
Update |
To
Modify the data in a table |
|
Delete |
To
Delete data in a table |
Note
: input data should be enclosed in
quotes, if the datatype of a column is char,varchar2 or date.
Syntax1:
It is used if user wants to input values to all existing columns in a table.
INSERT
INTO <TABLENAME> VALUES(VAL1,VAL2,...VALN);
Ex:
INSERT
INTO PRODUCT VALUES(11,'REXONA',5);
INSERT
INTO <TABLENAME>(COL2,COL5)
VALUES(VAL2,VAL5);
INSERT
INTO PRODUCT(PID,QTY) VALUES(33,'LUX');
INSERT
INTO <TABLENAME> VALUES(&COL1,&COL2....&COLN);
(OR)
Note:
If datatype of a column is CHAR / VARCHAR / VARCHAR2/DATE then value should be
enclosed in quotes.
Ex
INSERT
INTO PRODUCT VALUES(&SNO,'&SNAME','&CITY');
Syntax1:
( This syntax applies / modifes all the records in a table)
UPDATE
<TABLENAME> SET <COLUMNNAME>=<VALUE>;
Ex:
UPDATE
PRODUCT SET QTY=45;
Syntax2:
(This syntax applies/modifies specified columns based on a given condition by
using where clause)
UPDATE
<TABLENAME> SET <COLUMNNAME>=<VALUE> WHERE <CONDITION>;
Ex:
UPDATE
PRODUCT SET QTY=45 WHERE PID=4;
Or
UPDATE
PRODUCT SET QTY=45 WHERE PID>24;
Or
UPDATE
PRODUCT SET QTY=45 WHERE PNAME=‘LUX’;
Or
UPDATE
PRODUCT SET QTY=45 WHERE PNAME LIKE ‘P%’;
Syntax1:
( This syntax deletes all the records(Rows) in a table, Structure remains
constant.)
DELETE
FROM <TABLENAME>;
Ex:
DELETE
FROM PRODUCT;
Syntax2:
(This syntax deletes specified columns based on a given condition by using
where clause)
Ex:
DELETE FROM PRODUCT WHERE PNAME LIKE
‘%S’;
Or
DELETE
FROM PRODUCT WHERE PNAME LIKE ‘%S%’ or
QTY>50;
Data
query language is used to fetch data from tables based on a given condition.
|
COMMAND |
DESCRIPTION |
|
Select |
To
Display one or more than one record(data) in a table |
select
<column list> from <tablename>;
Ex:
select pid,pname,qty from product; (or)
select
* from product;
Note
: In above command * will be replaced by all column names at run time.
syntax2: (To
display all columns/specefied columns by using where clause)
select
<column list> from <tablename> where <condition>;
EX:
select * from product where pid <40;
select pname,qty from product where pid >59;
LIKE:
The
Oracle LIKE condition allows wildcards to be used in the WHERE clause of a
SELECT, INSERT, UPDATE, or DELETE statement. This allows you to perform pattern
matching.
Sometimes,
you want to query data based on a specified pattern. For example, you may want
to find contacts whose last names start with 'St' or first names end with 'er'.
In this case, you use the Oracle LIKE operator.
Pattern
The
pattern is a string to search for in the expression.
Syntax:
Expression
[NOT] like pattern;
There
are two wildcards used in conjunction with the LIKE operator for pattern
matching.
The
percent sign (%) - matches any string of zero or more characters.
The
underscore (_) - matches any single
character
The
percent sign represents zero, one or multiple characters. The underscore
represents a single number or character. These symbols can be used in combinations.
SELECT
first_name,last_name,phone FROM contacts WHERE last_name LIKE 'St%';
Ex:
Like with NOT
SELECT
first_name,last_name,phone FROM contacts WHERE last_name NOT LIKE 'St%';
The
Oracle BETWEEN condition is used to retrieve values within a range in a SELECT,
INSERT, UPDATE, or DELETE statement.
Syntax:
Expression
BETWEEN value1 AND value2;
Ex:
SELECT
* FROM customers WHERE customer_id BETWEEN 4000 AND 4999;
Btween
with NOT
Ex:
SELECT * FROM customers WHERE customer_id NOT BETWEEN 4000 AND 4999;
SCHEMA 1 : PRODUCT
1)CREATE
A TABLE PRODUCT(PID,PNAME,QTY)
2)Display
the structure of PRODUCT table.
3)ADD
A COLUMN PRICE(alter)
4)ADD
A COLUMN EMAIL(alter)
5)MAKE
USE OF REST OF ALL ALTER COMMANDS(MODIFY,DROP & RANAME)
(Ex: alter modify, alter drop alter rename)
6)DROP
COLUMN EMAIL
7)INSERT
DATA INTO PRODUCT TABLE - USE 3 SYSNTAXES(Insert minimum 10 rows)
8)ADD
COLUMN MDT- use data type date AND add a value to MDT- use update
9)DISPLAY
ALL ROWS OF PRODUCT TABLE with and without condition(where)
10)UPDATE
MULTIPLE ROWS/SINGLE ROW
11)DISPLAY
ALL ROWS OF PRODUCT TABLE BASED ON VARIOUS CONDITIONS.
12)DELETE
ALL ROWS/DELETE SELECTED ROWS
1)Create
a table employee with attributes eid,ename and sal.
2)add
a new column email to employee table.
3)change
the size of eid as 5
4)add
a new column city
5)change
the data type of ename as char
6)add
a new column dept
7)change
the city column name as PLACE
8)remove
the column dept
9)add
a new column dob.
10)add
records to employee table use 3 syntaxes 3 per syntax.(input total 9 rows)
11)display
table structure
12)display
all the records in the table - without and with condition.
13display
employee name and age of all employees.
14)display
employee id ,age and salary of all employees whose name starts with 's'
15)modify
all records in employee table by giving 1500 hike in their salary.
16)display
all records.
17)modify
all records in employee table by giving 2.5% hike, whose salary is <25000
18)remove
all records in table - delete
19)display
the structure
20)restore
deleted records
21)display
all records
22)remove
all records - truncate
23)change
employee table name as emp.
Data
control language are the commands to grant and take back authority from any
database user.
|
COMMAND |
DESCRIPTION |
|
grant |
Used
to grant permission of right |
|
revoke |
Used
to take back given permission |
We
can grant users various privileges to tables. These privileges can be any
combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL
Syntax
:
GRANT
privilege(s) ON object TO user;
Ex:
If
we want to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table
called product to a user name guru, the command would be:
GRANT
SELECT, INSERT, UPDATE, DELETE ON product TO guru;
GRANT
ALL ON product TO guru;
GRANT
SELECT ON product TO public;
Once
we have granted privileges, you may need to revoke some or all of these
privileges. We can revoke any combination of SELECT, INSERT, UPDATE, DELETE,
REFERENCES, ALTER, INDEX, or ALL.
Syntax
REVOKE
privileges ON object FROM user;
If
we want to revoke DELETE privilege on a table called product from a user named
nammu:
REVOKE
DELETE ON product FROM nammu;
If
we had granted ALL privileges to public (all users) on the product table and
you wanted to revoke these privileges:
REVOKE
ALL ON product FROM public;
Transaction
Control Language(TCL)
These
commands are used to manage transactions in the database. These are used to
manage the changes made to the data in a table by DML statements. It also
allows statements to be grouped together into logical transactions.
Transaction:
A transaction is a sequence of SQL statements that Oracle Database treats as a
single unit.
|
COMMAND |
DESCRIPTION |
|
commit |
Used
to end current transaction and make
permanent all changes performed in the current transaction. |
|
rollback |
It
undoes all changes for the current session up to the savepoint specified by
savepoint_name. If this clause is omitted, then all changes are undone. |
|
savepoint |
A
SAVEPOINT is like a marker within a transaction that allows for a partial
rollback. |
About dual table:
In
Oracle, DUAL is a table which is created with every installation of Oracle
along with the Data Dictionary. It consists of exactly one column named
"dummy" and a single row. The value of that row is "X".
DUAL is owned by the super user "SYS" but DUAL can be accessed and used by every
user regardless of their role or assigned permissions.(Or)
This
is a single row and single column dummy table provided by oracle. This is used
to perform mathematical calculations without using a table.
Ex:
Select * from DUAL
Output:
DUMMY
-------
X
Ex:
Select 777 * 888 from Dual
Output:
777
* 888
---------
689976
Oracle
Built in Functions:
There
are two types of functions in Oracle.
1)
Single Row Functions: Single row or Scalar functions return a value for
every row that is processed in a query.
2)
Group Functions: These functions group the rows of
data based on the values returned by the query. This is discussed in SQL GROUP
Functions. The group functions are used to calculate aggregate values like
total or average, which return just one total or one average value after
processing a group of rows.
Oracle
SQL provides functions (built-in) and Oracle allows you to define custom
functions using PL/SQL.
**We
can see all built-in Oracle functions with this query:
select
distinct object_name from all_arguments where package_name = 'STANDARD';
There
are four types of single row functions. They are:
1)
Numeric Functions: These are functions
that accept numeric input and return numeric values.
2)
Character or Text Functions: These are functions
that accept character input and can return both character and number values.
3)
Date Functions: These are functions that take
values that are of datatype DATE as input and return values of datatype DATE,
except for the MONTHS_BETWEEN function, which returns a number.
4)
Conversion Functions: These are functions
that help us to convert a value in one form to another form. For Example: a
null value into an actual value, or a value from one datatype to another
datatype like NVL, TO_CHAR, TO_NUMBER, TO_DATE etc.
You
can combine more than one function together in an expression. This is known as
nesting of functions.
Numeric
functions are used to perform operations on numbers. They accept numeric values
as input and return numeric values as output. Few of the Numeric functions are:
=========== ========
ABS Returns the absolute value
of a number
Ex:
SQL>
select abs(-35) from dual;
ABS(-35)
----------
35
SQL>
select round(123.546) from dual;
ROUND(123.546)
--------------
124
Ex2:
SQL>
select round(123.564,1) from dual;
ROUND(123.564,1)
----------------
123.6
Ex3:
SQL>
select round(123.568,2) from dual;
ROUND(123.568,2)
----------------
123.57
Ex1:
SQL>
select ceil(123.123) from dual;
CEIL(123.123)
-------------
124
FLOOR
Returns the largest integer value that is equal to or less than a number
Ex:
SQL>
select floor(123.923) from dual;
FLOOR(123.923)
--------------
123
TRUNC Returns a number truncated
to a certain number of decimal places
Ex:
SQL>
select trunc(123.923) from dual;
TRUNC(123.923)
--------------
123
Ex:
SQL> select exp(2) from dual;
EXP(2)
----------
7.3890561
GREATEST Returns the greatest value in a list of
expressions
Ex:
SQL>
select greatest(12,45,78,656,888,3) from dual;
GREATEST(12,45,78,656,888,3)
----------------------------
888
LEAST
Returns the smallest value in a list of expressions
Ex:
SQL>
select least(12,45,78,656,888,3) from dual;
LEAST(12,45,78,656,888,3)
-------------------------
3
MOD Returns the remainder of n
divided by m
SQL>
select mod(13,5) from dual;
MOD(13,5)
----------
3
POWER
Returns m raised to the nth power
Ex:
SQL>
select power(3,4) from dual;
POWER(3,4)
----------
81
ROWNUM
For
each row returned by a query, the ROWNUM pseudo column returns a number
indicating the order in which Oracle selects the row from a table or set of
joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so
on.
You
can use ROWNUM to limit the number of rows returned by a query, as in this
example:
Ex1:
SELECT
* FROM employees WHERE ROWNUM < 10;
SQL>
select rownum from dual;
ROWNUM
----------
1
Ex3:
SQL>
select rownum from std;
ROWNUM
----------
1
2
3
3 rows selected.
SQRT Returns the square root of a number
SQL>
select sqrt(81) from dual;
SQRT(81)
----------
9
AGGREGATE
FUNCTIONS IN ORACLE
Aggregate
functions are SQL functions designed to allow you to summarize data from
multiple rows of a table or view. Aggregate functions return a single value
based on groups of rows, rather than single value for each row. You can use
Aggregate functions in select lists and in ORDER BY and HAVING clauses. They
are commonly used with the GROUP BY clause in a SELECT statement, where Oracle
divides the rows of a queried table or view into groups.
The
important Aggregate functions are :
AVG
AVG( ALL /DISTINCT expr)
Returns
the average value of expr.
Ex:
The following query returns the average salary of all employees.
select
avg(sal) “Average Salary” from emp;
Average
Salary
------------------
2400.40
SUM(ALL/DISTINCT expr)
Returns
the sum value of expr.
Ex:The
following query returns the sum salary of all employees.
Total
Salary
---------------
26500
MAX(ALL/DISTINCT expr)
Returns
maximum value of expr.
Ex:
The following query returns the max salary from the employees.
Maximum
Salary
---------------------
4500
MIN(ALL/DISTINCT expr)
Returns
minimum value of expr.
Ex:
The following query returns the minimum salary from the employees.
Minimum
Salary
--------------------
1200
COUNT(*) OR COUNT(ALL/DISTINCT expr)
Returns
the number of rows in the query. If you specify expr then count ignore nulls.
If you specify the asterisk (*), this function returns all rows, including
duplicates and nulls. COUNT never returns null.
Ex:
The following query returns the number of
employees.
COUNT
----------
14
The
following query counts the number of employees whose salary is not null.
COUNT
----------
12
STDDEV(ALL/DISTINCT expr)
STDDEV
returns sample standard deviation of expr, a set of numbers.
Ex:
The following query returns the standard deviation of salaries.
Stddev
--------
1430
VARIANCE(ALL/DISTINCT expr)
Variance
returns the variance of expr.
Ex:
The following query returns the variance of salaries.
select
variance(sal) from emp;
Variance
----------
1430
Character
or text functions are used to manipulate text strings. They accept strings or
characters as input and can return both character and number values as output.
FunctionName Description
=========== ========
ASCII Returns ASCII number code
that represents the specified character
Ex:
SQL>
select ascii('A') from dual;
ASCII('A')
----------
65
SQL>
select ascii('k') from dual;
ASCII('K')
----------
107
Ex:
SQL>
select chr(97) from dual;
C
-
a
SQL>
select chr(122) from dual;
C
-
z
Ex:
SQL>
select concat('Larry','Ellison') from dual
CONCAT('
--------
LarryEllison
SQL>
select concat('Ora','cle') from dual;
CONCAT(
-------
Oracle
Concat
with || Allows you to concatenate two or more
strings together
Ex:
SQL>
select 'SQL'||'DEV'||'ELO'||'PER' from dual;
'SQL'||'DEV'
------------
SQLDEVELOPER
Ex:
SQL>
select lower('DJANGO') from dual;
LOWER(
------
django
UPPER
Converts all letters in the
specified string to uppercase
Ex:
SQL>
select upper('javabeans') from dual;
UPPER('JA
---------
JAVABEANS
LENGTH Returns the length of the specified string
Ex:
SQL>
select length('Enterprise Edition') from dual;
LENGTH('ENTERPRISEEDITION')
---------------------------
18
Lowercase
Ex:
SQL>
select initcap('oracle cloud infrastructure') from dual;
INITCAP('ORACLECLOUDINFRAST
---------------------------
Oracle
Cloud Infrastructure
SUBSTR
Allows you to extract a substring from a string
Ex:
SQL>
select substr('goldengate',4,3) from dual;
SUB
---
den
INSTR
Returns the location of a substring in a
string
Ex1:
SQL>
select instr('dataguard','tag') from dual;
INSTR('DATAGUARD','TAG')
------------------------
3
Ex2:
SQL>
select instr('datadata','a') from dual;
INSTR('DATADATA','A')
---------------------
2
Note
: It returns the position of first occurrence of substring in main string.
characters
Ex:
SQL>
select replace('goldengate','eng','$') from dual;
REPLACE(
--------
gold$ate
Ex:
SQL>
select ltrim(' MANGODB') from dual;
-------
MANGODB
RTRIM
Removes all specified characters from the right-hand side of a string
Ex:
SQL>
select rtrim('MARIADB ') from dual;
RTRIM('
-------
MARIADB
of a string
Ex:
SQL>
select trim(' INFORMIX ') from dual;
TRIM('IN
--------
INFORMIX
LPAD
Pads the left-side of a string with a
specific set of characters
SQL>
select lpad('SPARC',8,'@') from dual;
LPAD('SP
--------
@@@SPARC
RPAD
Pads the right-side of a string with a
specific set of characters
Ex:
SQL>
select rpad('DBTG',6,'$') from dual;
RPAD('
------
DBTG$$
These
are functions that take values that are of datatype DATE as input and return
values of datatypes DATE, except for the MONTHS_BETWEEN function, which returns
a number as output.
ADD_MONTHS
(date, n) Returns a date value after adding 'n'
months to the date
'x'.
Ex:
SQL>
select sysdate from dual;
SYSDATE
---------
19-SEP-21
SQL>
select add_months(sysdate,2) from dual;
ADD_MONTH
---------
19-NOV-21
MONTHS_BETWEEN (x1, x2) Returns the number of months between dates x1 and x2.
SQL>
select months_between(sysdate,'19-jul-2021') from dual;
MONTHS_BETWEEN(SYSDATE,'19-JUL-2021')
-------------------------------------
2
ROUND
(x, date_format) Returns the date 'x' rounded off to
the nearest century,
year, month, date, hour, minute, or second as specified
by the 'date_format'.
Ex:
SQL>
select sysdate from dual;
SYSDATE
---------
19-SEP-21
SQL>
select round(sysdate,'dd') from dual;
ROUND(SYS
---------
20-SEP-21
ROUND(SYS
---------
01-OCT-21
ROUND(SYS
---------
01-OCT-21
SQL>
select round(sysdate,'yyyy') from dual;
ROUND(SYS
---------
01-JAN-22
ROUND(SYS
---------
01-JAN-22
TRUNC (x, date_format) Returns the date 'x' lesser than or equal to the nearest
century, year, month, date, hour, minute, or second as
specified by the 'date_format'.
Ex:
SQL>
select sysdate from dual;
SYSDATE
---------
19-SEP-21
SQL>
select trunc(sysdate,'dd') from dual;
TRUNC(SYS
---------
19-SEP-21
TRUNC(SYS
---------
01-SEP-21
TRUNC(SYS
---------
01-SEP-21
TRUNC(SYS
---------
01-JAN-21
TRUNC(SYS
---------
01-JAN-21
date 'x' occurs.
Ex:
SQL>
select sysdate from dual;
SYSDATE
---------
19-SEP-21
SQL>
select next_day(sysdate,'tue') from dual;
NEXT_DAY(
---------
21-SEP-21
NEXT_DAY(
---------
24-SEP-21
NEXT_DAY(
---------
26-SEP-21
LAST_DAY
(x) It is used to determine
the number of days remaining in a
month from the date 'x' specified.
Ex:
SQL>
select last_day(sysdate) from dual;
LAST_DAY(
---------
30-SEP-21
LAST_DAY(
---------
28-FEB-21
SQL>
select last_day('23-may-2024') from dual;
LAST_DAY(
---------
31-MAY-24
SYSDATE Returns the systems current date.
Ex:
SQL>
select sysdate from dual;
SYSDATE
---------
19-SEP-21
SQL>
select to_char(sysdate,'YYYY-MM-DD HH:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2021-09-19
11:02:19
TO_CHAR(SYSDATE,'DD
-------------------
19-09-2021
11:02:47
TO_CHAR(SYSDATE,'DD-
--------------------
19-sep-2021
11:03:01
the time in zone1.
SQL>
select to_char(sysdate,'dd-mm-yy hh:mi:ss') from dual;
TO_CHAR(SYSDATE,'
-----------------
19-09-21
11:20:43
NEW_TIME(
---------
19-SEP-21
TO_CHAR(SYSDATE,'
-----------------
19-09-21
11:21:30
NEW_TIME(
---------
20-SEP-21
These
are functions that help us to convert a value in one form to another form. For
Ex: a null value into an actual value, or a value from one datatype to another
datatype like NVL, TO_CHAR, TO_NUMBER, TO_DATE.
Function
Name Return Value
TO_CHAR
(x [,y]) Converts Numeric and
Date values to a character
string value. It cannot be used for calculations since it
is a string value.
Ex1:
SQL>
select to_char(sysdate,'dd-mm-yy hh:mi:ss') from dual;
TO_CHAR(SYSDATE,'
-----------------
19-09-21
11:30:24
Ex2:
SQL>
select to_char(sysdate,'yy-mm-dd hh:mi:ss') from dual;
TO_CHAR(SYSDATE,'
-----------------
21-09-19
11:31:15
TO_CHA
------
$3000
TO_CHAR(SYSDATE,'DAY,MONT
-------------------------
Sunday , September 2021
SQL>
select TO_CHAR (SYSDATE, 'Day, Month Year') from dual;
TO_CHAR(SYSDATE,'DAY,MONTHYEAR')
---------------------------------------------------------------
Sunday , September Twenty Twenty-One
TO_DATE
(x [, date_format]) Converts
a valid Numeric and Character values to a
Date value. Date is formatted to the format specified
By 'date_format'.
Ex:
SQL>
select TO_date('01-Jun-21') from dual;
TO_DATE('
---------
01-JUN-21
NVL
(x, y) If 'x' is
NULL, replace it with 'y'. 'x' and 'y'
must be of The same datatype.
SQL>
select nvl(3,5) from dual;
NVL(3,5)
----------
3
SQL> select nvl(0,5) from dual;
NVL(0,5)
----------
0
NVL(NULL,5)
-----------
5
DECODE
(a, b, c, d, e, default_value) Checks
the value of 'a', if a = b, then
returns 'c'. If a = d, then returns 'e'. Else,
returns default_value.
Ex:
SQL>
select decode(3,5,35,3,4,34) from dual;
DECODE(3,5,35,3,4,34)
---------------------
4
DECODE(3,5,35,13,4,34)
----------------------
34
DECODE(3,3,35,13,4,34)
----------------------
35
SQL>
select decode(3,4,35,13,4,34) from dual;
DECODE(3,4,35,13,4,34)
----------------------
34
SQL> select systimestamp from dual;
SYSTIMESTAMP
-----------------------------------
19-SEP-21 11.50.07.810000 PM +05:30
Group By Clause.
The GROUP BY
clause is used in a SELECT statement to group rows into a set of summary rows
by values of columns or expressions. The GROUP BY clause returns one row per
group. The GROUP BY clause is often used with aggregate functions such as
AVG(), COUNT(), MAX(), MIN() and SUM(). In this case, the aggregate function
returns the summary information per group.
Ex:
SQL> DESC
EMPLOYEES;
Name
Null? Type
EMPLOYEE_ID
NOT NULL NUMBER(6)
FIRST_NAME
VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY
NUMBER(8,2)
COMMISSION_PCT
NUMBER(2,2)
MANAGER_ID
NUMBER(6)
DEPARTMENT_ID NUMBER(4)
Query
1 : Display department_id and sum of
salaries of all employees in that department.
SQL>
select department_id,sum(salary) from
employees group by department_id;
DEPARTMENT_ID SUM(SALARY)
100 51600
30 24900
7000
90 58000
20 19000
70 10000
110 20300
50 156400
80 304500
40 6500
60 28800
10 4400
12 rows
selected.
Query2:
SQL> select city,sum(sal_cont) from salsdept group by
city;
CITY
SUM(SAL_CONT)
--------------- -------------
delhi
385
chennai
120
hyd
105
banglore
250
vizag
120
Having Clause.
A HAVING clause restricts the results of a
GROUP BY in a Select Expression. The HAVING clause is applied to each group of
the grouped table, much as a WHERE clause is applied to a select list. If there
is no GROUP BY clause, the HAVING clause is applied to the entire result as a
single group. The SELECT clause cannot refer directly to any column that does
not have a GROUP BY clause.
Ex:
SQL>
select department_id,sum(salary) from
employees group by department_id having department_id >70;
DEPARTMENT_ID
SUM(SALARY)
100 51600
90 58000
110 20300
80 304500
SQL>
select department_id,sum(salary) from
employees group by department_id having department_id between 50 and 90;
DEPARTMENT_ID SUM(SALARY)
90 58000
70 10000
50 156400
80 304500
60 28800
Query
2: Display departments ids and total
number of employees working in each department.
SQL> select
department_id,count(department_id) "Total Employees" from employees
group by department_id;
DEPARTMENT_ID Total Employees
100 6
30 6
0
90 3
20 2
70 1
110 2
50 45
80 34
40 1
60 5
10 1
12 rows selected.
Note
: To filter null values in department_id
SQL> select
department_id,count(department_id) "Total Employees" from employees
group by department_id having department_id is not null;
DEPARTMENT_ID Total Employees
100 6
30 6
90 3
20 2
70 1
110 2
50 45
80 34
40 1
60 5
10 1
11 rows
selected.
Ex:
SQL>
select city,sum(sal_cont) from salsdept group by city;
CITY SUM(SAL_CONT)
---------------
-------------
delhi 385
chennai 120
hyd 105
banglore 250
vizag 120
SQL>
select city,sum(sal_cont) from salsdept group by city having
sum(sal_cont)>200;
Output:
CITY SUM(SAL_CONT)
--------------- -------------
delhi 385
banglore 250
SQL>
select city,sum(sal_cont) from salsdept group by city having
sum(sal_cont)<200;
CITY SUM(SAL_CONT)
---------------
-------------
chennai 120
hyd 105
vizag 120
Order By clause.
The ORDER BY
clause is used to sort the records in the result set for a SELECT statement
either in ascending or descending order.
Syntax:
SELECT
expressions
FROM tables
[WHERE
conditions]
ORDER BY
expression [ ASC | DESC ];
Note : ASC for
Ascending order and DESC for Descending order.
By default data will be sorted in
Ascending order.
Ex1: SELECT * FROM customers ORDER BY last_name;
Output:
customer_id last_name
8000
Anderson
6000
Ferguson
4000
Jackson
9000
Johnson
7000 Reynolds
5000
Smith
Ex2:SELECT *
FROM suppliers WHERE supplier_id > 400 ORDER BY supplier_id DESC;
Output:
supplier_id
supplier_name
900 Electronic Arts
800 Flowers Foods
700 Dole Food Company
600 SC Johnson
500 Tyson Foods
Constraints.
Constraints
enforce limits to the data or type of data that can be inserted/updated/deleted
from a table. The whole purpose of constraints is to maintain the data
integrity during an update/delete/insert into a table.
Note:
Data
Integrity: Data integrity refers to the
accuracy(correct) and consistency(reliable) of data stored in a database, data
warehouse, data mart or other construct.
Types
Of Constraints
NOT NULL
UNIQUE
DEFAULT
CHECK
Key Constraints
– PRIMARY KEY, FOREIGN KEY
NOT
NULL
NOT NULL
constraint makes sure that a column does not hold NULL value. When we don’t
provide value for a particular column while inserting a record into a table, it
takes NULL value by default. By specifying NOT NULL constraint, we can be sure
that a particular column(s) cannot have NULL values.
Ex:
CREATE TABLE
STUDENT
(
SID NUMBER(3)
NOT NULL,
NAME VARCHAR (35)
NOT NULL
);
UNIQUE
UNIQUE
Constraint enforces a column or set of columns to have unique values. If a
column has a unique constraint, it means that particular column cannot have
duplicate values in a table.
Ex:
CREATE TABLE
STUDENT
(
SID NUMBER(3)
NOT NULL,
NAME VARCHAR
(35) NOT NULL,
PHONE NUMBER(10)
UNIQUE
);
DEFAULT
The DEFAULT
constraint provides a default value to a column when there is no value provided
while inserting a record into a table.
Ex:
CREATE TABLE
STUDENT
(
SID NUMBER(3)
NOT NULL,
NAME VARCHAR
(35) NOT NULL,
PHONE NUMBER(10)
UNIQUE
DONATION NUMBER
DEFAULT 500;
);
CHECK
This constraint
is used for specifying range of values for a particular column of a table. When
this constraint is being set on a column, it ensures that the specified column
must have the value falling in the specified range.
Ex:
CREATE TABLE
STUDENT
(
SID NUMBER(3)
NOT NULL CHECK(SID<100,
NAME VARCHAR
(35) NOT NULL,
PHONE NUMBER(10)
UNIQUE
DONATION NUMBER
DEFAULT 500;
);
No comments:
Post a Comment