oracle tutorial
select * from tabs;
describe employees;
select * from employees;
select distinct job_id from employees;
select employee_id,sum(salary) from employees group by employee_id;
select first_name,employee_id from employees;
select department_id,max(salary) from employees group by department_id;
select department_id from employees where salary=max(salary);
select max(salary) as maximum_salary,min(salary),avg(salary),count(*) from employees;
select min(salary) as "Minimum Salary" from employees;
select max(salary),min(salary),sum(salary),avg(salary),department_id from employees group by department_id
having sum(salary)>10000
select salary,department_id from employees where department_id=100
select sum(salary),department_id from employees group by department_id having avg(salary)>10000;
select * from employees order by department_id asc;
create table students
(
stid number(5) primary key,
st_name varchar2(10) not null,
dob date,
email varchar2(50) unique,
city varchar2(10) default 'Delhi',
age number check (age between 15 and 25)
)
describe students;
select * from ALL_CONSTRIANTS
where TABLE_NAME='students';
SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME='STUDENTS';
ALTER TABLE STUDENTS ENABLE CONSTRAINT SYS_C004173;
describe students;
alter table students modify
(city varchar2(22));
create table stmarks
(
regno number(5) primary key,
marks1 number(5) not null default 40,
marks2 number(5) not null default 45,
marks number(5) not null,
stid varchar2(6) references STID(students))
describe students;
STID
alter table students drop (phone_no);
select * from students;
insert into students
values(:stid,:st_name,:dob,:email,:city,:age);
ALTER TABLE person
ADD CONSTRAINT pk_person
PRIMARY KEY (person_id)
USING INDEX
PCTFREE 5;
ALTER TABLE person_role_ie
ADD CONSTRAINT fk_person_role_ie_person
FOREIGN KEY (person_id)
REFERENCES person(person_id);
For example you want to see all employees whose name starts with S char. Then you can use LIKE condition as follows
SQL> select * from emp where ename like ‘S%’ ;
Similarly you want to see all employees whose name ends with “d”
SQL>select * from emp where ename like ‘%d’;
You want to see all employees whose name starts with ‘A’ and ends with ‘d’ like ‘Abid’, ’Adward’, ’Arnold’.
SQL>select * from emp where ename like ‘A%d’;
You want to see those employees whose name contains character ‘a’ anywhere in the string.
SQL> select * from emp where ename like ‘%a%’;
To see those employees whose name contains ‘a’ in second position.
SQL>select * from emp where ename like ‘_a%’;
To see those employees whose name contains ‘a’ as last second character.
SQL>select * from emp where ename like ‘%a_’;
To see those employees whose name contain ‘%’ sign. i.e. ‘%’ sign has to be used as literal not as wild char.
SQL> select * from emp where ename like ‘%\%%’ escape ‘\’;
CREATE TABLE person (
person_id NUMBER(10),
first_name VARCHAR2(25) NOT NULL,
last_name VARCHAR2(25) NOT NULL,
title_1 VARCHAR2(5),
title_2 VARCHAR2(5))
PCTFREE 20;
ALTER TABLE person
ADD CONSTRAINT pk_person
PRIMARY KEY (person_id)
USING INDEX
PCTFREE 5;
CREATE TABLE person_role (
role_id VARCHAR2(1),
role_name VARCHAR2(20) NOT NULL);
ALTER TABLE person_role
ADD CONSTRAINT pk_role
PRIMARY KEY (role_id)
USING INDEX
PCTFREE 5;
CREATE TABLE person_role_ie (
person_role_id NUMBER(10),
person_id NUMBER(10) NOT NULL,
role_id VARCHAR2(1) NOT NULL);
ALTER TABLE person_role_ie
ADD CONSTRAINT pk_person_role_ie
PRIMARY KEY (person_role_id)
USING INDEX
PCTFREE 5;
CREATE TABLE title (
title_abbrev VARCHAR2(5),
title_name VARCHAR2(20))
PCTFREE 0;
ALTER TABLE title
ADD CONSTRAINT pk_title
PRIMARY KEY (title_abbrev)
USING INDEX
PCTFREE 0;
ALTER TABLE person_role_ie
ADD CONSTRAINT fk_person_role_ie_person
FOREIGN KEY (person_id)
REFERENCES person(person_id);
ALTER TABLE person_role_ie
ADD CONSTRAINT fk_person_role_ie_role
FOREIGN KEY (role_id)
REFERENCES person_role(role_id);
ALTER TABLE person
ADD CONSTRAINT fk_person_title1
FOREIGN KEY (title_1)
REFERENCES title(title_abbrev);
ALTER TABLE person
ADD CONSTRAINT fk_person_title2
FOREIGN KEY (title_2)
REFERENCES title(title_abbrev);
Demo Table Data Load INSERT INTO title VALUES
('BA', 'Bachelor of Arts');
INSERT INTO title VALUES
('BS', 'Bachelor of Science');
INSERT INTO title VALUES
('MS', 'Master of Science');
INSERT INTO title VALUES
('PhD', 'Doctor of Philosophy');
INSERT INTO title VALUES
('MD', 'Doctor of Medicine');
INSERT INTO person
(person_id, first_name, last_name, title_1)
VALUES
(1, 'Daniel', 'Morgan', 'BS');
INSERT INTO person
(person_id, first_name, last_name, title_1)
VALUES
(2, 'Jack', 'Cline', 'BA');
INSERT INTO person
(person_id, first_name, last_name, title_1)
VALUES
(3, 'Muriel', 'Dance', 'PhD');
INSERT INTO person
(person_id, first_name, last_name, title_1)
VALUES
(4, 'Elizabeth', 'Scott', 'MS');
INSERT INTO person
(person_id, first_name, last_name)
VALUES
(5, 'Jacqueline', 'Stough');
INSERT INTO person_role VALUES (1, 'Administrator');
INSERT INTO person_role VALUES (2, 'Professor');
INSERT INTO person_role VALUES (3, 'Instructor');
INSERT INTO person_role VALUES (4, 'Employee');
INSERT INTO person_role VALUES (5, 'Student');
INSERT INTO person_role VALUES (9, 'Alumni');
CREATE SEQUENCE seq_pr_id START WITH 1;
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 1, 2);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 1, 9);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 2, 3);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 1, 5);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 3, 1);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 3, 9);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 4, 4);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 5, 5);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 5, 9);
describe person;
select * from person;
select * from title;
describe person;
insert into person(person_id,first_name,last_name,title_1)
values(3,'manish','mandal','MS');
insert into person
values(:person_id,:first_name,:last_name,:title_1);
INSERT INTO person_role VALUES (1, 'Administrator');
INSERT INTO person_role VALUES (2, 'Professor');
INSERT INTO person_role VALUES (3, 'Instructor');
INSERT INTO person_role VALUES (4, 'Employee');
INSERT INTO person_role VALUES (5, 'Student');
INSERT INTO person_role VALUES (9, 'Alumni');
select * from person_role;
select * from person_role_ie;
select * from person;
create sequence seq_pr_id start with 1;
insert into person_role_ie values
(seq_pr_id.NEXTVAL,1,4);
select * from person;
select * from title;
select p.last_name,t.title_name
from person p,title t
where p.title_1=t.title_abbrev;
select * from person;
CREATE TABLE SCHOOOLs
(
NAMES VARCHAR2(30),
ROLL NUMBER,
constraint pkroll primary key(ROLL)
)
CREATE TABLE OFFICE
(
NAME VARCHAR2(100),
IDS NUMBER(2),
CONSTRAINT FKIDS FOREIGN KEY(IDS)
REFERENCES SCHOOOLs(ROLL)
)
ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id);
ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name);
describe employees;
select * from employees;
select distinct job_id from employees;
select employee_id,sum(salary) from employees group by employee_id;
select first_name,employee_id from employees;
select department_id,max(salary) from employees group by department_id;
select department_id from employees where salary=max(salary);
select max(salary) as maximum_salary,min(salary),avg(salary),count(*) from employees;
select min(salary) as "Minimum Salary" from employees;
select max(salary),min(salary),sum(salary),avg(salary),department_id from employees group by department_id
having sum(salary)>10000
select salary,department_id from employees where department_id=100
select sum(salary),department_id from employees group by department_id having avg(salary)>10000;
select * from employees order by department_id asc;
create table students
(
stid number(5) primary key,
st_name varchar2(10) not null,
dob date,
email varchar2(50) unique,
city varchar2(10) default 'Delhi',
age number check (age between 15 and 25)
)
describe students;
select * from ALL_CONSTRIANTS
where TABLE_NAME='students';
SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME='STUDENTS';
ALTER TABLE STUDENTS ENABLE CONSTRAINT SYS_C004173;
describe students;
alter table students modify
(city varchar2(22));
create table stmarks
(
regno number(5) primary key,
marks1 number(5) not null default 40,
marks2 number(5) not null default 45,
marks number(5) not null,
stid varchar2(6) references STID(students))
describe students;
STID
alter table students drop (phone_no);
select * from students;
insert into students
values(:stid,:st_name,:dob,:email,:city,:age);
ALTER TABLE person
ADD CONSTRAINT pk_person
PRIMARY KEY (person_id)
USING INDEX
PCTFREE 5;
ALTER TABLE person_role_ie
ADD CONSTRAINT fk_person_role_ie_person
FOREIGN KEY (person_id)
REFERENCES person(person_id);
For example you want to see all employees whose name starts with S char. Then you can use LIKE condition as follows
SQL> select * from emp where ename like ‘S%’ ;
Similarly you want to see all employees whose name ends with “d”
SQL>select * from emp where ename like ‘%d’;
You want to see all employees whose name starts with ‘A’ and ends with ‘d’ like ‘Abid’, ’Adward’, ’Arnold’.
SQL>select * from emp where ename like ‘A%d’;
You want to see those employees whose name contains character ‘a’ anywhere in the string.
SQL> select * from emp where ename like ‘%a%’;
To see those employees whose name contains ‘a’ in second position.
SQL>select * from emp where ename like ‘_a%’;
To see those employees whose name contains ‘a’ as last second character.
SQL>select * from emp where ename like ‘%a_’;
To see those employees whose name contain ‘%’ sign. i.e. ‘%’ sign has to be used as literal not as wild char.
SQL> select * from emp where ename like ‘%\%%’ escape ‘\’;
CREATE TABLE person (
person_id NUMBER(10),
first_name VARCHAR2(25) NOT NULL,
last_name VARCHAR2(25) NOT NULL,
title_1 VARCHAR2(5),
title_2 VARCHAR2(5))
PCTFREE 20;
ALTER TABLE person
ADD CONSTRAINT pk_person
PRIMARY KEY (person_id)
USING INDEX
PCTFREE 5;
CREATE TABLE person_role (
role_id VARCHAR2(1),
role_name VARCHAR2(20) NOT NULL);
ALTER TABLE person_role
ADD CONSTRAINT pk_role
PRIMARY KEY (role_id)
USING INDEX
PCTFREE 5;
CREATE TABLE person_role_ie (
person_role_id NUMBER(10),
person_id NUMBER(10) NOT NULL,
role_id VARCHAR2(1) NOT NULL);
ALTER TABLE person_role_ie
ADD CONSTRAINT pk_person_role_ie
PRIMARY KEY (person_role_id)
USING INDEX
PCTFREE 5;
CREATE TABLE title (
title_abbrev VARCHAR2(5),
title_name VARCHAR2(20))
PCTFREE 0;
ALTER TABLE title
ADD CONSTRAINT pk_title
PRIMARY KEY (title_abbrev)
USING INDEX
PCTFREE 0;
ALTER TABLE person_role_ie
ADD CONSTRAINT fk_person_role_ie_person
FOREIGN KEY (person_id)
REFERENCES person(person_id);
ALTER TABLE person_role_ie
ADD CONSTRAINT fk_person_role_ie_role
FOREIGN KEY (role_id)
REFERENCES person_role(role_id);
ALTER TABLE person
ADD CONSTRAINT fk_person_title1
FOREIGN KEY (title_1)
REFERENCES title(title_abbrev);
ALTER TABLE person
ADD CONSTRAINT fk_person_title2
FOREIGN KEY (title_2)
REFERENCES title(title_abbrev);
Demo Table Data Load INSERT INTO title VALUES
('BA', 'Bachelor of Arts');
INSERT INTO title VALUES
('BS', 'Bachelor of Science');
INSERT INTO title VALUES
('MS', 'Master of Science');
INSERT INTO title VALUES
('PhD', 'Doctor of Philosophy');
INSERT INTO title VALUES
('MD', 'Doctor of Medicine');
INSERT INTO person
(person_id, first_name, last_name, title_1)
VALUES
(1, 'Daniel', 'Morgan', 'BS');
INSERT INTO person
(person_id, first_name, last_name, title_1)
VALUES
(2, 'Jack', 'Cline', 'BA');
INSERT INTO person
(person_id, first_name, last_name, title_1)
VALUES
(3, 'Muriel', 'Dance', 'PhD');
INSERT INTO person
(person_id, first_name, last_name, title_1)
VALUES
(4, 'Elizabeth', 'Scott', 'MS');
INSERT INTO person
(person_id, first_name, last_name)
VALUES
(5, 'Jacqueline', 'Stough');
INSERT INTO person_role VALUES (1, 'Administrator');
INSERT INTO person_role VALUES (2, 'Professor');
INSERT INTO person_role VALUES (3, 'Instructor');
INSERT INTO person_role VALUES (4, 'Employee');
INSERT INTO person_role VALUES (5, 'Student');
INSERT INTO person_role VALUES (9, 'Alumni');
CREATE SEQUENCE seq_pr_id START WITH 1;
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 1, 2);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 1, 9);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 2, 3);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 1, 5);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 3, 1);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 3, 9);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 4, 4);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 5, 5);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 5, 9);
describe person;
select * from person;
select * from title;
describe person;
insert into person(person_id,first_name,last_name,title_1)
values(3,'manish','mandal','MS');
insert into person
values(:person_id,:first_name,:last_name,:title_1);
INSERT INTO person_role VALUES (1, 'Administrator');
INSERT INTO person_role VALUES (2, 'Professor');
INSERT INTO person_role VALUES (3, 'Instructor');
INSERT INTO person_role VALUES (4, 'Employee');
INSERT INTO person_role VALUES (5, 'Student');
INSERT INTO person_role VALUES (9, 'Alumni');
select * from person_role;
select * from person_role_ie;
select * from person;
create sequence seq_pr_id start with 1;
insert into person_role_ie values
(seq_pr_id.NEXTVAL,1,4);
select * from person;
select * from title;
select p.last_name,t.title_name
from person p,title t
where p.title_1=t.title_abbrev;
select * from person;
CREATE TABLE SCHOOOLs
(
NAMES VARCHAR2(30),
ROLL NUMBER,
constraint pkroll primary key(ROLL)
)
CREATE TABLE OFFICE
(
NAME VARCHAR2(100),
IDS NUMBER(2),
CONSTRAINT FKIDS FOREIGN KEY(IDS)
REFERENCES SCHOOOLs(ROLL)
)
ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id);
ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name);
Comments
Post a Comment