wiproccc

--create table product (prod_id int,pname varchar2(20),price int,amount_paid int);
--insert into product values(101,'OPPO',20000,10000);
--insert into product values(102,'One Plus',30000,25000);
--insert into product values(103,'Iphone',80000,55000);
--insert into product values(104,'Jio',10000,7000);
--insert into product values(105,'OSwordO',15000,10000);
--insert into product values(106,'Jio',10000,7000);
--insert into product values(107,'OSwordO',15000,10000);

--Quetions
--get list of product where product name is having letter 't'
--get list of product where price is less 20000
--get list of product where price is more than 0 and less than 40000
--Solutions
--select * from product where pname like '%t%';
--select * from product where price < 20000;
--select * from product where price > 0 and price < 40000;

--Quetions
--get list of products where price is more than or equal to 10000
--get list of products where price is more than 20000 and name has letter 'O' in beginning
--get list of products to get the expected price after increasing it with 10%
--get list of products where the expected price after increasing it with 10% is more than 30000.
--Solutions
--select pname,price from product where price > 10000 or price =10000;
--select pname,price from product where price > 20000 and pname like 'o%';
--select pname,price ,(price1.10) AS expected_price from product;
--select pname,price ,(price1.10) "expected price" from product where price > 30000;
create table emp (empid int,ename varchar2(20),salary int,designation varchar2(20),experienced char(1),city varchar2(20),deptid int);
insert into emp values(101,'King',45000,'Manager','Y','Banglore',1);
insert into emp values(102,'Queen',55000,'Clerk','N','Pune',2);
insert into emp values(103,'Jack',35000,'Developer','Y','Mumbai',null);
insert into emp values(104,'Sam',60000,'Analyst','N','Chennai',3);
insert into emp values(105,'Mary',60000,'Manager','Y','Banglore',4);
insert into emp(empid,ename,salary,designation,experienced,city) values(106,'Diana',45000,'Clerk','N','Banglore');
Queries to form
--1.Get list of employees who are not experienced
--2.Get list of employees who are experienced
--3.Get the list of employees having letter 'a' in name
--4.get list of employees having either letter 'e' or letter 'n' in name
--5.get list of employees having salary more than 35000
--6.get list of employees who are having either salary 35000 or more or they are residing in Banglore
--7.Get list of employees who are Manager or Analyst
--8.Get list of employees who are Manager and have salary below 50000
--9.Get list of employees whose name is of 4 characters only
--10.Get list of employees who are residing in Pune or Chennai
--Solutions
--select empid,ename AS unexperienced from emp where experienced ='N';
--select empid,ename AS experienced from emp where experienced ='Y';
--select empid,ename AS Having_Letter_A from emp where ename like '%a%';
--select empid,ename AS Having_Letter_E_or_N from emp where ename like '%e%' or ename like '%n%';
--select empid,ename from emp where salary >35000;
--select empid,ename from emp where salary >=35000 or city = 'Banglore';
--select empid,ename AS designation from emp where designation ='Manager'or designation='Analyst';
--select empid,ename from emp where designation ='Manager'and salary>50000;
--select empid,ename from emp where ename like '____';
--select empid,ename from emp where city ='Pune' or city = 'Chennai';
select * from emp where deptid<>2 or deptid<>4;
select * from emp where salary<>12000 and salary<> 40000 and designation is not null;
select * from emp where designation = 'Manager' or deptid in(2,3);
select * from emp where ename like '%a%' and salary > 40000;
select * from emp where designation like '%e%' and deptid is null and city in('Mumbai', 'Chennai');
select * from emp where experienced = 'N' and deptid is not null;
select * from emp where salary > 50000 and experienced = 'N';