SQL

Index

Databse Codd Rules SQL Codd Rules PL-SQL Transactions Joins

Databse Codd Rules

SQL

SQL Datatype

PL SQL Concepts

Transactions- ACID Properties

Joins in Oracle

---

SQL Script for Joins:

CREATE TABLE customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50) NOT NULL,
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);
/
CREATE TABLE suppliers
( supplier_id number(10) NOT NULL,
  supplier_name varchar2(50) NOT NULL,
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10)
);
/
CREATE TABLE departments
( department_id number(10) NOT NULL,
  department_name varchar2(50) NOT NULL,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);
/
CREATE TABLE employees
( employee_number number(10) NOT NULL,
  employee_name varchar2(50) NOT NULL,
  department_id number(10),
  salary number(6),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number),
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);
/
INSERT ALL
  INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
  INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

/

create table suppliers(supplier_id number(20) primary key,
supplier_name varchar2(30));

create table orders(order_id number(20),
supplier_id number(20),
order_date date,FOREIGN KEY(supplier_id) REFERENCES suppliers(supplier_id));
insert into suppliers VALUES(10000,'IBM');
insert into suppliers VALUES(10001,'Google');
insert into suppliers VALUES(10002,'Yahoo');
insert into suppliers VALUES(10003,'Mocrosoft');
insert into suppliers VALUES(10004,'CDAC');
insert into orders values(500125,10000,to_date('11-07-2020','DD-MM-YYYY'));
insert into orders values(500126,10001,to_date('12-07-2020','DD-MM-YYYY'));
insert into orders values(500127,10004,to_date('13-07-2020','DD-MM-YYYY'));
--Inner Join:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers 
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
--
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
--
SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
--
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

select * from suppliers;
select * from orders;


/
select count(*) from suppliers_dk;
/

create table suppliers_dk as select * from suppliers;


select s.rowid,s.rownum,s.* from suppliers_dk s ;
/
delete from suppliers_dk where id=123;

create table suppl(id num)

select * from suppliers;
update suppliers_dk set supplier_name='FNU'
where supplier_id=10000;
/


commit;
rollback;