Prac 11 – Write the code to
Q1. Open the database named ‘ClassXIIPrac’
Q2. Create a table named ‘Customer_Detail’ as per the understanding for the structure of the
table given below
Cus_ID Name Type Country Pur_Order Prod_ID
1001 Shivalik Temporary India 90000.00 1
1002 Meghnath Permanent Nepal 80000.00 3
1003 Lokesh Temporary Sri Lanka 75000.00 4
1004 Himadri Permanent Nepal 80000.00 6
1005 Samiksha Permanent Nepal 85000.00 3
1006 Dharvi Temporary Sri Lanka 70000.00 2
1007 Mukesh Temporary Sri Lanka 75000.00 5
1008 Devesh Permanent India 80000.00 6
Q3. Insert the records as above.
Q4. Show the name of all the permanent customers who all are non-Indians and their
purchase order is more than 80000.00
Q5. Show the total number of customers from each country from the table.
Q6. Show the total number of unique customer countries.
Q7. Show the total number of customers in decreasing values (Highest on top) from each
country.
Q8. Show the number of customers of those countries where number of customers are more
than 2.
Q9. Show the number of customers of those countries where number of customers are more
than 2 and in descending order.
Q10. Change the Customer type to temporary of those customers whose Pur_Order is less
than 85000.00
******
Code 11
Statement 1 USE ClassXIIPrac;
Statement 2
CREATE TABLE Customer
( Cus_ID INTEGER(5) PRIMARY KEY ,
Name VARCHAR(15),
Type VARCHAR(15),
Country VARCHAR(15),
Pur_Order DECIMAL(12,2),
Prod_ID INTEGER(2)
);
Statement 3 INSERT INTO Customer
( Cus_ID , Name , Type , Country, Pur_Order, Prod_ID)
VALUES ( 1001, ‘Shivalik’, ‘Temporary’, ‘India’, 90000.00, 1),
(1002, ‘Megnath’, ‘Temporary’, ‘Nepal’, 80000.00, 3),
(1003, ‘Lokesh’, ‘Temporary’, ‘Sri Lanka’, 75000.00, 4),
(1004, ‘Himadri’, ‘Temporary’, ‘Nepal’, 80000.00, 6),
(1005, ‘Samiksha’, ‘Temporary’, ‘Nepal’, 85000.00, 3),
(1006, ‘Dharvi’, ‘Temporary’, ‘Sri Lanka’, 70000.00, 2),
(1007, ‘Mukesh’, ‘Temporary’, ‘Sri Lanka’, 75000.00, 5),
(1008, ‘Devesh’, ‘Temporary’, ‘Nepal’, 80000.00, 6)
);
Query 4 SELECT Name FROM Customer
WHERE Type = Permanent AND Country != INDIA AND Pur_order > 80000.00;
Query 5 SELECT COUNT(Cust_ID), Country
FROM Customer
GROUP BY Country;
Query 6 SELECT COUNT(DISTINCT Country) FROM Customer;
Query 7 SELECT COUNT(Cust_ID), Country
FROM Customer
GROUP BY Country;
ORDER BY COUNT(Cust_ID) DESC;
Query 8 SELECT COUNT(Cust_ID), Country
FROM Customer
GROUP BY Country
HAVING COUNT(Cust_ID) > 2;
Query 9 SELECT COUNT(Cust_ID), Country
FROM Customer
GROUP BY Country
HAVING COUNT(Cust_ID) > 2
ORDER BY COUNT(Cust_ID) DESC;
Query10 UPDATE Customer SET Type=Temporary where Pur_Order>85000.00;
*******
