Oracle

What is sequence?
To generate sequential integers/numbers in database.

all sequences are maintain in system table called user_sequences;

desc user_sequences;

To check all sequences in the table as follows:

select sequence_name from user_sequences;


//Sequence creation:
Create sequence demo_seq
start with 1
increment by 1
maxvalue 99999999999

Or simply we can create:

create sequence demo_seq

Because start with, increment by, maxvalue etc are optional

default starting value of sequence is 1 and increment value of sequence is also 1

Sequence is an independent object without a table but views, indexes, synonims are dependent and we can't create them without a table.

There are two schedo columns:
1. currval --It access current value of sequence
2. nextval --It access next value of sequence

To see next value:
select demo_seq.nextval from dual;

To see current value:
select demo_seq.currval from dual;

Here, dual is a dummy table and we don't need to specify any table because it an independent object.

How to insert:

insert into student values (sid.nextval, 'Bablu', 'Oracle')

Manually create a serial no:

alter table trans_info
add (trans_no number);

update trans_info
set trans_no = trans_seq.Nextval;

For delete a sequence:
drop sequence tbl_name;


Ex 01:
Create sequence demo_seq
start with 1
maxvalue 99999999999
minvalue 1
increment by 1

To see next value:
select demo_seq.nextval from dual;

To see current value:
select demo_seq.currval from dual;

In practical now apply it on newly created table:

create table demo_seq_table(
	id number,
	name varchar2(50)
);

insert into demo_seq_table values(demo_seq.nextval, 'Bablu Ahmed');

Now check it,
select * from demo_seq_table;

To delete a sequence:
drop sequence demo_seq;


//Alter
Alter statement is used for edit/modify a table.

ALTER TABLE ATI_EXAM1.EMPLOYEES ADD (
CONSTRAINT FK_DEPARTMENT_ID 
FOREIGN KEY (DEPARTMENT_ID) 
REFERENCES ATI_EXAM1.DEPARTMENT (DEPARTMENT_ID));

//View Creation 
Ex 01:
CREATE OR REPLACE VIEW v_employee
AS
(SELECT e.emp_no, e.first_name, e.last_name, e.email, e.phone_no,
   e.hire_date, d.department_name
FROM employees e, department d
WHERE e.department_id = d.department_id)

Ex 02:
CREATE VIEW SAMP.V1 (COL_SUM, COL_DIFF)
	AS SELECT COMM + BONUS, COMM - BONUS
	FROM SAMP.EMPLOYEE;
	
Ex 03:
Creating an XMLType View: Example 
In some cases you may have an object-relational table upon which you would like to build an XMLType view. The following example creates an object-relational table resembling the XMLType column warehouse_spec in the sample table oe.warehouses, and then creates an XMLType view of that table:

CREATE TABLE warehouse_table 
(
  WarehouseID       NUMBER,
  Area              NUMBER,
  Docks             NUMBER,
  DockType          VARCHAR2(100),
  WaterAccess       VARCHAR2(10),
  RailAccess        VARCHAR2(10),
  Parking           VARCHAR2(20),
  VClearance        NUMBER
);

INSERT INTO warehouse_table 
   VALUES(5, 103000,3,'Side Load','false','true','Lot',15);

commit;

CREATE VIEW warehouse_view OF XMLTYPE
 XMLSCHEMA "http://www.example.com/xwarehouses.xsd" 
    ELEMENT "Warehouse"
    WITH OBJECT ID 
    (extract(OBJECT_VALUE, '/Warehouse/Area/text()').getnumberval())
 AS SELECT XMLELEMENT("Warehouse",
            XMLFOREST(WarehouseID as "Building",
                      area as "Area",
                      docks as "Docks",
                      docktype as "DockType",
                      wateraccess as "WaterAccess",
                      railaccess as "RailAccess",
                      parking as "Parking",
                      VClearance as "VClearance"))
  FROM warehouse_table;
You query this view as follows:

SELECT VALUE(e) FROM warehouse_view e;


//Synonyms:

Synonyms are also objects and they are permanent alias/short name for any other objects like table, views, sequence, procedure etc.

Syntax:

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema .] synonym_name
  FOR [schema .] object_name [@ dblink];


There are two types:

1. Private
2. Public

By default synomyms are private that means they can only be used for specific user and public synomys are for all users but needed privillages or permission from dba.

How to create:
Create synonym <syno_name> for <table_name>

Ex 01:

create synonym cd for student

To create public synonyms:

create public synonym cd for student

To drop synonyms:

drop synonym <name>

Practical with Synonyms:

Insufficient privileges:

To get privileges we shuld contact dba as follows:

conn system

Now enter password and get grant from dba as follows:

grant create synonym to bablu;

If it shows grant succeeded.

Now connect bablu user:

conn bablu

and enter passeord if connected go ahed to create synonym.

Finally create synonym:

create synonym bd for bangladesh_tbl;

Check it as follows:

select * from bd;

To drop:

drop synonym bd;

To see system table for synonym:
desc user_synonyms


//Group By

Syntax:

The syntax for the GROUP BY clause in Oracle/PLSQL is:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;
ORDER BY expression_n

expression1, expression2, ... expression_n
The expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY clause.

Ex 01:

SELECT test_name, test_id, count(*)
from test
group by test_name,test_id
order by test_name;

N.B, In above group by expression means test_name and test_id are a combination if another combination is found then it will make a logical group with thease combinations.
and count performs how many combinations are in the logical group.

Ex 02:

SELECT test_name, test_id, test_dept, count(*)
from test
group by test_name,test_id, test_dept
order by test_name;

N.B, Here test_name,test_id, test_dept are a combination if another the same combination found it will consider a group

Ex 03:

SELECT test_name, test_id, test_dept
from test
group by test_name,test_id, test_dept
order by test_name;

N.B, We can also get the same result without count because the count is used for how many combinations are in a group and filtering these groups(group records) using the having clause.

Ex 04:

select deptno, sum(sal)
from emp
group by deptno
having sum(sal)>10000
order by deptno;


//Distinct

The Oracle DISTINCT clause is used to remove duplicate rows from the result set. It ensures that rows returned are unique for the column or columns specified in the SELECT clause.

Syntax
The syntax for the DISTINCT clause in Oracle/PLSQL is:

SELECT DISTINCT expression1, expression2...expression_n
FROM tables
[WHERE conditions];
ORDER BY expression1

Ex 01:

SELECT distinct test_name, test_id, test_dept
from test
order by test_name;

N.B, Here order by must needed and the same result as the group by example 02 and it is the easiest way