- select * from jobs, departments
- -- having clause
- -- having clause is used in combination with the group by clause
- select department_id, sum(salary) total_salary from employees group by department_id;
- select department_id, sum(salary) total_salary from employees group by department_id having sum(salary) > 58000;
- create table supplier(
- supplier_id number(10) not null primary key,
- supplier_name varchar(20) not null,
- supplier_contact_name varchar(50)
- )
- insert into supplier (supplier_id, supplier_name, supplier_contact_name) values (10000, 'IBM', 'abc');
- insert into supplier (supplier_id, supplier_name, supplier_contact_name) values (10001, 'HP', 'def');
- insert into supplier (supplier_id, supplier_name, supplier_contact_name) values (10002, 'Microsoft', 'ghi');
- insert into supplier (supplier_id, supplier_name, supplier_contact_name) values (10003, 'NVIDIA', 'jkl');
- alter table supplier drop primary key;
- alter table supplier add PRIMARY KEY (supplier_id);
- create table customer(
- customer_id number(10) not null,
- customer_name varchar(30) not null,
- customer_city varchar(20) null,
- CHECK (customer_id > 0)
- );
- To make sure that customer_id is greater than 0
- insert into customer(customer_id, customer_name, customer_city) values (-2, 'Test', 'Testing');
- -- alter table table_name check (col_name > condition);
- select * from t1 natural join t2;
- to get all common
- Inner Join
- -- inner join
- -- syntax
- --select columns from table1 inner join table2 ON table1.column = table2.column
- create table orders(
- order_id number(10) not null,
- supplier_id number(10) not null,
- order_data varchar(20) null);
- insert into orders(order_id, supplier_id, order_data) values (500125, 10000, '2003');
- insert into orders(order_id, supplier_id, order_data) values (500126, 10001, '2005');
- insert into orders(order_id, supplier_id, order_data) values (500127, 10003, '2006');
- -- applying inner join
- select supplier.supplier_id, supplier.supplier_name, orders.order_data from supplier inner join orders on supplier.supplier_id = orders.supplier_id;
Recent Pastes