MySql Queary

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<SQL  Query>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

select * from Customer;

select custname,dob,salary from Customer;

select custname as Customer_Name, dob,salary from Customer;

select custname as 'Customer Name', dob,salary from Customer;

select custname,dob,salary, (salary * 0.03) as ETF from Customer;

--Question 01

Customer Name   Salary    ETP   EPF   Commission   Net_Salary

Danapala       300000     255   545     1250        25966.00

ETP = salary x 3%

ETP = salary x 12%

commission = {salary - (epf + etf) } x 3%

net_salary = salary - (epf + etf + commission)

select custname,dob,salary,(salary *0.03) as ETF,(salary*0.12) as EPF,(salary -((salary *0.03)+(salary*0.12)))*0.03 as commission,salary-((salary*0.03)+(salary*0.12)+((salary -((salary *0.03)+(salary*0.12)))*0.03)) as net_salary from customer;

-------------------------------------------------------------------

   ------------ ORDER BY CLAUSE 

-------------------------------------------------------------------

select custname,dob,salary from Customer order by salary;

select custname,dob,salary from Customer order by salary asc;

select custname,dob,salary from Customer order by salary desc;

select custname,dob,salary from Customer order by custname desc;

select custname,dob,salary from Customer order by salary asc,

custname desc ;

select custname,dob,salary from Customer order by 3 asc,

1 desc ;

select custname,dob,salary from Customer order by salary desc 

limit 1;

select custname,dob,salary from Customer order by salary asc 

limit 10;

-------------------------------------------------------------------

   ------------ WHERE  CLAUSE 

-------------------------------------------------------------------

select custname,dob,salary , salary >= 75000 from Customer 

where salary >= 75000;   

select custname,dob,salary , salary >= 75000

from Customer  where salary >= 75000  || salary <=40000;   

-- can use OR or AND 

select custname,dob,salary from Customer

where dob='1980-01-05' and salary >= 75000;    

select custname,dob,salary from Customer

where salary between 40000 and 75000;

select custname,dob,salary from Customer

where salary not between 40000 and 75000;

select custname,dob,salary ,province from Customer

where province IN ('Western','Southern');

select custname,dob,salary ,province from Customer

where province NOT IN ('Western','Southern');

-------------------------------------------------------------------

   ------------ like   -useful for search

-------------------------------------------------------------------

- NOT LIKE 

select custname,dob from Customer

where custname like 'Danapala';

select custname,dob from Customer

where custname like '%pala';

select custname,dob from Customer

where custname like 'Som%';

select custname,dob from Customer

where custname like '%ma%';

Quetions

1. Find the name of the customer who born in 1st of january or april

select custname,dob

from Customer

where day(dob) = 1 && (month(dob)=1 || month(dob)=4);

2. find the name of the customer who has reached to 80 year in age

select custname,dob

from Customer

where year(curdate()) - year(dob) >= 80;

select custname, year(curdate()) - year(dob) >= 80 as age 

from Customer;

3. Find the name of the customer who has reached to 60 year in age 

and earn more than 50000 as their salary. 

select custname,dob

from Customer

where (year(curdate()) - year(dob) >= 60) && salary >= 50000;

-- differences between having and where 

select custname, year(curdate()) - year(dob) as age

from Customer where age >=80; -- this is incorrect bcz manupulated 

-- columns can not be used in 'where' clause 

select custname, year(curdate()) - year(dob) as age

from Customer having age >=80; -- this is correct bcz age has been defined 

-- in select statement

select custname   -- this is incorrect 

from Customer  having salary >= 75000; 

select custname -- this is correct bcoz salary has been defined in customer table

from Customer  where  salary >= 75000; 

<<<<<<<<<<<<<<<<<<<<<<<<<Join  Query / Nested & Other>>>>>>>>>>>>>>>>>>>>>>>>>>

select custid,count(orderid)

from orders

group by custid;

select empid,count(loanid)

from loan

group by empid;

select name,count(loanid)

from loan

group by empid;

------ how to join tables -------------------------------------------

-- cross join

select * from employee cross join loan;

select * from employee cross join loan using(empid);

-- left join

-- this will not run without a key

select * from employee left join loan;

-- giving frist priority to employee table

select * from employee left join loan using(empid);

-- giving first priority to loan table therefore we can

-- avoid null values.

select * from employee right join loan using (empid);

-- traditional join / equivalent join

-- where clause is needed to join table

select * from employee,loan

where employee.empid=loan.empid;

select * from employee e,loan l

where e.empid=l.empid;

-- natural join -------------------------

select * from employee natural join loan;

--  Questions

1. Find the customer names who has taken loans

        select distinct name

        from employee e, loan l

        where e.empid=l.empid;

2. Find the customer name and no of loans have been taken

        select name,count(loanid) as No_of_loans

        from employee e, loan l

        where e.empid= l.empid

        group by e.empid;

3. Find the customer names and the totoal loan amount

        select name,sum(loan_amount) as TotalLoans

        from employee e,loan l

        where e.empid=l.empid

        group by e.empid;

4. find the customers who have taken loan from western province

        select name,province

        from employee e, loan l

        where e.empid=l.empid and province='Western'

5. find the customers who have taken above 150000.00 of their total

   loans.

        select name,sum(loan_amount) as total_loan

        from employee e,loan l

        where e.empid=l.empid

        having total_loan >= 150000;

6. Find the customers who have taken loans on 01-03-2017

        select name

        from employee e, loan l

        where e.empid=l.empid and loan_date='01-03-2017';

Find the total loan amount of 01-03-2017

 select sum(loan_amount)

from loan

where loan_date =  '2017-03-01';

7. Find the customers who have taken loans from 01-03-2017 to 30-03-2017

----------- lets move to gdse41 database

-- Questions

1. Find the customers who have made orders?

select custname

from Customer c,Orders o

where c.custid=o.custid;

select *

from Customer c,Orders o

where c.custid=o.custid;

2. Find the no of orders of each item

        Keerisamba 10ke            orders 12

-- is there any connection between item and orderdeail tables?

select description, count(orderid) as no_of_orders

from Item i,OrderDetail od

where i.itemcode=od.itemcode

group by i.itemcode;

3. Find  10 fast moving items of the company

select description, count(orderid) as no_of_orders

from Item i,OrderDetail od

where i.itemcode=od.itemcode

group by i.itemcode

order by 2 desc limit 10;

4. Find the total income of each order?

select orderid,sum(orderQty * unitPrice)

from OrderDetail od, Item i

where od.itemcode=i.itemcode

group by orderid;

select orderid,sum(orderQty * unitPrice)

from OrderDetail od, Item i

where od.itemcode=i.itemcode

group by orderid order by 2 desc limit 10;

5.  Find the totoal income of each item which company received.

   item            total icome

Keerisamba           45000.00

select description,sum(orderQty * unitPrice)

from OrderDetail od, Item i

where od.itemcode=i.itemcode

group by i.itemcode

order by 2 desc;

6. Find the total quantity of each item which company sold between

   2008 to 2009

        Item Name               Total_Qty_sold

    Keerisamba 10 kg             75

select description,sum(orderqty)

from Item i,OrderDetail od, Orders o

where o.orderid=od.orderid && od.itemcode=i.itemcode

        && year(orderdate) =2008

group by i.itemcode

order by 2 desc;

7. Find all items that were ordered by Danapala between 1st of march

   2008 and 31st of august 2008

select distinct description

from Customer c, Orders o, OrderDetail od,Item i

where c.custid=o.custid && o.orderid=od.orderid &&

          od.itemcode=i.itemcode  && orderdate between '2008-03-01'

          and  '2008-08-31' && c.custname='Danapala';

select distinct description, sum(orderqty)

from Customer c, Orders o, OrderDetail od,Item i

where c.custid=o.custid && o.orderid=od.orderid &&

          od.itemcode=i.itemcode  && orderdate between '2008-03-01'

          and  '2008-08-31' && c.custname='Danapala'

group by i.itemcode;

8. Find all the customers who have bought items above 500000.00

select custname,sum((orderqty * unitprice) - (unitprice * discount)/100) as Total_Income

from Customer c, Orders o, OrderDetail od,Item i

where c.custid=o.custid && o.orderid=od.orderid &&

          od.itemcode=i.itemcode

group by c.custid

having total_income >= 500000.00;

--------------------------------------------------------------------

-- Sub Query / Neasted Query

-- type 01

select province,max(salary),avg(salary),sum(salary),

(select avg(salary) from Customer) as Full_Avg

from Customer

group by province;

-- type 02

select distinct custname

from Customer c, Orders o

where c.custid=o.custid;

select custname

from Customer

where custid IN (select distinct custid from Orders);

select custname,salary

from Customer

where province IN ('Western','Eastern');

select custname

from Customer

where custid = (select custid from Orders where orderid='D001');

-- for above query

-- '=' sign can use because subquery returns only one value

-- boolean operators can use

select custname

from Customer

where custid = (select custid from Orders);

-------- Delete Query Syntax

delete

from Customer;

delete from Customer

where custid='C001';

-- delete all customers who have made orders

delete from Customer

where custid IN (select distinct custid from Orders);

-- delete all customers who have not made orders yet.

delete from Customer

where custid NOT IN (select distinct custid from Orders);

-- Update Query Syntax

-- Update all customers' name to Danapala

update Customer

set custname = 'Danapala';

update Customer

set custname = 'Danapala Perera' where custid='C001';

-------------------------------------------------------------------

      Database Management System Exam

        on 3/4/2017

Practical Exam : at 8.30 am to 11.30 pm   20%

Theory Exam    : 2.00pm to 4.00pm    70%

Pass mark is 40%