realcode4you
- Aug 26, 2023
- 8 min read

Mobile Management System Database Design | Hire Best Database Design and Implementation Expert

Create Database

create database mobile;

Select Database

use mobile;

Creating Database Tables

Creating Distributor Info table

-- Distributor Info table
create table Distributor
(
Distributor_ID varchar(10) ,
Distributor_Name varchar(20),
Address varchar(100),
Mobilenumber int (10),
Email varchar(30), 
constraint pk_distributor primary key(Distributor_ID) );

Creating Mobile Master table

-- Mobile master table
create table Mobile_Master
(
IME_No varchar(10), Model_Name varchar(20), Manufacturer varchar(20), Date_Of_Manufac date,
Warranty_in_Years int(10), Price decimal(7,2), Distributor_ID varchar(10),
constraint pk_ime primary key(IME_No),foreign key(Distributor_ID) references Distributor(Distributor_ID)
);

Creating Mobile Specification table

-- Mobile specification table
create table Mobile_Specification
(IME_No varchar(10), Dimension varchar(20), Weight varchar(20),Display_Type varchar(20), Display_Size varchar(20),Internal_mem_in_MB int(10),Memory_Card_Capacity_GB int(10), Network_3G varchar(5),GPRS varchar(5), EDGE varchar(5), Bluetooth varchar(5),
Camera varchar(5), Camera_Quality varchar(5) , OS varchar(20), Battery_Life_Hrs int(10) ,constraint fk_ime foreign key(IME_No) references Mobile_Master(IME_No));

Creating Customer Info table

-- Customer Information table
create table Customer_Info
(Customer_ID varchar(10),Customer_Name varchar(20),Address varchar(100),Mobile long,Email varchar(30),constraint pk_customer primary key(Customer_ID));

Creating Sales Info table

-- Sales information table
create table Sales_Info
(SalesId int(10),Sales_Date date,IME_No varchar(10),Price int(10),Discount int(10),Net_Amount int(10),Customer_ID varchar(10),constraint fk_sales primary key(SalesId),foreign key(Customer_ID) references Customer_Info(Customer_ID), foreign key(IME_No) references Mobile_Master(IME_No));

Inserting Record Into Table

-- insertion Record Into Distributor

 insert into Distributor values('SA110','Mobile_Store','Kolkata',1234567890,
'mobstore@gmail.com');

 insert into Distributor values('SA111','Samsung_World','Ranchi',1234567891,
'samworld@ymail.com');

 insert into Distributor values('NO110','Nokia_prio','Delhi',1234567892,'nokprio@gmail.com');

insert into Distributor values('NO111','Nokia_Dealers','Chandigarh',1234567893,'nokdeal@ymail.com');

 insert into Distributor values('MC110','Micro_World','Bangalore',1234567894,'micwrld@gmail.com');

insert into Distributor values('MC111','Micro_mania','Bokaro',1234567895,'micromania@gmail.com');

--Inter Record into Mobile_Master Table

insert into Mobile_Master values('SA100010', 'SamsungS2', 'Samsung', '2008-04-12', 2, 25000, 'SA110');

insert into Mobile_Master values('SA100020', 'SamsungS3', 'Samsung', '2010-03-02', 2, 33000, 'SA110');

 insert into Mobile_Master values('SA100030', 'SamsungACE', 'Samsung', '2009-12-25', 2, 15000, 'SA111');

insert into Mobile_Master values('NO100030', 'NokiaLumiaC2', 'Nokia', '2009-11-25', 2, 25000, 'NO111');

 insert into Mobile_Master values('NO100020', 'NokiaLumiaB2', 'Nokia', '2007-10-15', 2, 22000, 'NO110');

 insert into Mobile_Master values('NO100010', 'NokiaAsha', 'Nokia', '2011-9-18', 2, 6500, 'NO111');

 insert into Mobile_Master values('MC100010', 'Ninja37', 'Micromax', '2010-06-08', 2, 7500, 'MC111');

 insert into Mobile_Master values('MC100020', 'Ninja32', 'Micromax', '2011-08-28', 2, 7500, 'MC111');

 insert into Mobile_Master values('MC100030', 'MicromaxQ5', 'Micromax', '2009-03-20', 2, 4500, 'MC110');

--Insert Record Into Mobile Specification

 insert into Mobile_Specification values('SA100010', '5W10H','100gm','Digital', '5inch', 250, 32,'Y', 'Y', 'Y', 'Y', 'Y', '5MP', 'Andriod4S', 8);

 insert into Mobile_Specification values('SA100020', '9W15H','100gm','Digital', '10inch', 550, 32,'Y', 'Y', 'Y', 'Y', 'Y', '5MP', 'Andriod4S', 6);

 insert into Mobile_Specification values('SA100030', '3W5H','50gm','Digital','10inch',200, 16,'Y', 'Y', 'Y', 'Y', 'Y', '3.2MP', 'Andriod1S', 8);

insert into Mobile_Specification values('NO100030', '8W8H','100gm','Digital', '10inch',500, 32,'Y', 'Y', 'Y', 'Y', 'Y', '5MP', 'SymbianGT', 10);

 insert into Mobile_Specification values('NO100020', '4W8H','50gm','Digital', '10inch',250, 16,'Y', 'Y', 'Y', 'Y', 'Y', '5MP', 'SymbianAT', 10);

 insert into Mobile_Specification values('NO100030', '4W8H','70gm','Digital', '5inch',150, 16,'N', 'Y', 'Y', 'Y', 'Y', '2MP', 'Java', 8);

--Insert Record Into Customer_Info

 insert into Customer_Info values('MB10010', 'Debarun', 'Kolkata', 9899554411, 'chattdeb@gmail.com');
 
insert into Customer_Info values('MB10020', 'Manish', 'Bokaro', 9899554412,'rajman@ymail.com');

insert into Customer_Info values('MB10030', 'Sameer', 'Bokaro', 9899554413,'sameerwaa@gmail.com');

insert into Customer_Info values('MB10040', 'Sumit', 'Deogarh', 9899554414,'rajsumit@ymail.com');

insert into Customer_Info values('MB10050', 'Rahul', 'Patna', 9899554415, 'sharmarahul@gmail.com');

--Insert Record Into Sales_Info

 insert into Sales_Info values(1002, '2012-01-11', 'SA100020', 33000, 1000,32000, 'MB10010');

 insert into Sales_Info values(1003, '2012-02-19', 'SA100030', 15000, 200,14800, 'MB10030');

insert into Sales_Info values(1004, '2012-03-30', 'NO100010', 6500, 100, 6400, 'MB10050');

 insert into Sales_Info values(1005, '2012-01-25', 'MC100010', 7500, 50, 7450, 'MB10020');

 insert into Sales_Info values(1006, '2012-02-11', 'SA100010', 25000, 500,24500, 'MB10020');

--Display Table Records

select * from distributor;
select * from mobile_master;
select * from mobile_specification;
select * from customer_info;
select * from sales_info;

Practice Queries

1. Write a Query to Display the IME Number, Model Name of mobiles which is manufactured by "Nokia".

2. Write a Query to display IME number, Model Name, Manufacturer and Camera Quality of mobiles whose camera quality is 5MP.

3. "Write a Query to display the Mobile Model Name and respective number of mobiles sold on the date 23-Apr-2012 for each mobile model.

Hint: For example, if 2 ""Nokia 1100"" and 1 ""Nokia C5-03"" are sold on the date 23-Apr-2012 then display both the records. Use ""NoofMobilesSold"" as alias name for the number of mobiles field."

4. "Write a Query to display the distributor id, mobile model name, number of mobiles of the particular model name supplied to the distributors group by model name and distributor id and sort by the distributor id.

Hint: For example, if 3 ""Nokia 1100"" and 1 ""Nokia C5-03"" are sold to one distributor then display both the records. Display the distributor id, model name and number of mobiles of a particular model name. Use ""NoofMobilesSupplied"" as alias name for the number of mobiles."

5. "Write a Query to display the IME number, model name, manufacturer, price and discount of all mobiles regardless of whether the mobile is sold or not. Hint: Fetch the price, IME no and model name from mobile_master table.

Example: For the mobile model ""Samsung GalaxyTAB with IME NO ""MC1000103"" is sold and other with IME No ""MC1000110"" is not sold. Then both the mobiles details namely IME number, model name, manufacturer, price and discount needs to be displayed. "

6. Write a Query to display the distributor name, mobile number and email of distributors selling model 'Nokia 1100'.

7. Write a Query to display the IME Number and Model Name of mobiles which are not sold. Hint: The details of the sold mobiles are available in the "SALES_INFO" table and the overall mobile models are available in the mobile_master table.

8. Write a Query to display the IME Number, Model Name and net amount of the mobile which has the highest net amount.

9. "Write a Query to display the IME Number, Model Name, Manufacturer, Price and New Price of all mobiles. Hint: Fetch the price, name and IME number from mobile master table. Add 10% to the old price to find new price and display with alias name ""NewPrice"". Formula = price + (price * 10/100)"

10. Write a Query to display mobile model name, manufacturer and price for the mobiles having a price range between 8500 and 25300.

11. Write a Query to display the Model Name, Manufacturer, Price, Warranty, Internal memory, memory card capacity, gprs support, Bluetooth, camera quality and OS for the mobile with IME NO "MC1000104"

12. "Write a Query to display IME Number, Model Name, Manufacturer, Price ,GPRS information, Memory card capacity of mobiles which has GPRS support with memory card capacity 16GB or above.

Hint: For GPRS support use GPRS = “Yes”."

13. Write a Query to display the customer name, IME Number, Model Name, Sales Date and Net amount paid by the customer and sort by customer name in ascending order.

14. "Write a Query to display the IME Number, model name, manufacturer, price and discount of all mobiles regardless of whether the mobile is sold or not. Hint: If not sold, display discount as ""Not Sold""

Hint: Fetch the price and model name from mobile_master table. Use “discount” as alias name for displaying the discount of all mobiles."

15. Write a Query to display the sales date and total net amount of all the mobiles based on the sales date that are sold between 20-APR-12 and 25-APR-12. Hint: Total net amount column should be displayed as "TotalNetAmount" (alias)

16. Write a Query to display mobile IME number, model name, manufacturer, price and battery life of the mobiles which are having the longest battery life. Hint: Use the field "battery_life_hrs" for calculating maximum battery life.

17. Write a Query to display the IME Number, Model Name, Manufacturer and Price of the mobile which is having the maximum price. Hint: Assume 2 mobiles have highest price then both the mobiles should be displayed.

18. Write a Query to display the Customer ID, Customer Name, Address, Total net amount of each customer. For example, assume customer_1 has purchased 2 mobiles such as "Nokia C5-03" and "Nokia Lumia" then sum the prices of both the mobiles and should be displayed against his customer id. Hint: Use Total_Net_Amount as alias.

19. Write a Query to display the unique mobile model, manufacturer and price of the mobile which has highest price and manufactured by "Samsung".

20. Write a Query to display the IME number, model name, manufacturer, distributor id, distributor name and price supplied by the distributor named 'AXA Ltd'.

21. Write a Query to display the distributor id, name, address , mobile no, email of the distributor who has supplied the maximum number of mobiles. Hint: Get the maximum number of mobile provided by a distributor from mobile master and use it to get the details.

22. "Write a Query to display the Customer ID, Customer Name and Address of the customers who have purchased the maximum amount.

Hint: Sum the net amount for the each customer for all the purchases and find the customer who has maximum amount. And display the details of all the customers who has purchase amount equal to the maximum amount."

23. Write a Query to determine whether the mobile "Samsung GalaxyTAB" has been sold out or not and display the model name, ime_no and sales status. If sold display status as "Sold Out" else display "-"with column name "SalesStatus" (alias).

24. Write a Query to display the distinct distributor id, distributor name, address, mobile of all the distributors who supplies mobile with the following specifications network should support 3G Network and OS should be Android and camera quality should be 3.5 MP Camera. Hint: The above specifications are found in the "Mobile_Specification" table.

25. "Write a Query to Display the unique mobile model name and manufacturer of the mobile which has highest sales. Hint: Get the maximum count of mobile model from Sales info and get the manufacturer using the mobile model.

For example, Nokia has three models M1, M2, M3 and Samsung has 2 models S1,S2. Assume M1 model has 5 sales, M2, 3, M3, 2 and S1, 3 and S2 2. If that is the case the records to be displayed as

M1 Nokia since it has the highest sales." Reply Reply to all Forward

Solution

select IME_No,Model_Name
from mobile_master
where Manufacturer='Nokia';

select IME_No,Model_Name,Manufacturer,Camera_Quality
from mobile_master join mobile_specification using (IME_No)
where Camera_Quality='5MP';

select mobile_master.Model_Name,count(sales_info.Model_Name) as NoofMobilesSold
from mobile_master join sales_info using (IME_No)
where Sales_Date='2012-04-23'
group by Model_Name;

select Distributor_ID,Model_Name,count(Distributor_ID) as NoofMobilesSupplied
from distributor join mobile_master using (Distributor_ID)
group by Model_Name,Distributor_ID
order by DISTRIBUTOR_ID;

select m.IME_No,m.Model_Name,Manufacturer,m.Price,Discount
from mobile_master m left outer join sales_info using (IME_No);

select Distributor_Name,Mobile,Email
from distributor join mobile_master using (distributor_id)
where model_name='Nokia 1100';

select IME_No,Model_Name
FROM mobile_master
where IME_NO NOT IN(
select IME_No
from mobile_master m join sales_info s using (IME_No));

select IME_No,Model_Name
FROM mobile_master
where IME_NO NOT IN(
select IME_No
from sales_info);

SELECT IME_NO,Model_Name,MAX(NET_AMOUNT)
FROM sales_info;
SELECT * FROM SALES_INFO;

SELECT IME_No,Model_Name,Manufacturer,Price,ROUND((PRICE+(PRICE*(10/100))),2)AS NEW_PRICE
FROM mobile_master;

#10,11

SELECT Model_Name,Manufacturer,Price 
FROM mobile_master
WHERE PRICE between 8500 and 25300;

#12

select Model_Name,Manufacturer,Price,Warranty_in_Years,Internal_mem_in_MB,Memory_Card_Capacity_GB,GPRS,Bluetooth,Camera_Quality,OS
from mobile_master JOIN mobile_specification USING (IME_NO)
where ime_no='MC1000104';

#13

SELECT IME_No,Model_Name,Manufacturer,Price,GPRS,Memory_Card_Capacity_GB
FROM mobile_specification join mobile_master USING (IME_NO)
WHERE GPRS='YES' AND Memory_Card_Capacity_GB>16;

#14

SELECT Customer_Name,IME_No,MOBILE_MASTER.Model_Name,Sales_Date,Net_Amount
FROM customer_info join sales_info USING (CUSTOMER_ID) JOIN mobile_master USING (IME_NO)
ORDER BY Customer_Name;

#15

select m.IME_No,m.Model_Name,Manufacturer,m.price,Discount,case Discount
when null then 'not sold'
else Discount
end as status1
from mobile_master m left outer join sales_info using (IME_No);
use mobile;

select m.IME_No,m.Model_Name,Manufacturer,m.price,coalesce(Discount,'not sold')
from mobile_master m left outer join sales_info using (IME_No);


select m.IME_No,m.Model_Name,Manufacturer,m.price,ifnull(Discount,'not sold')
from mobile_master m left outer join sales_info using (IME_No);

select m.IME_No,m.Model_Name,Manufacturer,m.price,coalesce(Discount,'not sold')
from mobile_master m left outer join sales_info using (IME_No);

select *
from mobile_master left outer join sales_info using (IME_No)
where discount is null;
select * from sales_info;

#16

select Sales_Date,sum(Net_Amount) as TotalNetAmount
from sales_info
group by Sales_Date;

select * from mobile_specification;

#17

select m.IME_No,Model_Name,manufacturer,price,Battery_Life_Hrs
from mobile_master m join mobile_specification using (IME_No)
where battery_life_hrs in (select max(battery_life_hrs) from mobile_specification);

select * from mobile_master;

#18

select IME_No,Model_Name,Manufacturer,Price
from mobile_master
where price in (select max(price) from mobile_master);

#19

select * from customer_info;
select * from sales_info;
select c.Customer_ID,Customer_Name,Address,sum(Net_Amount) as Total_Net_Amount
from customer_info c join sales_info using (customer_id)
group by customer_id;

#20

select * from mobile_master;
#select * from mobile_specification;

select distinct Model_Name,Manufacturer,Price
from mobile_master
where PRICE in (select max(price) from mobile_master
WHERE MANUFACTURER='SAMSUNG');

#21

select * from distributor;
select * from mobile_master;

select IME_No,Model_Name,Manufacturer,Distributor_ID,Distributor_Name,Price
from mobile_master join distributor using (distributor_id)
where Distributor_Name='AXA Ltd';

#22

select Distributor_ID,Distributor_Name,Address,Mobile,Email
from distributor  
where distributor_id = (select distributor_id 
      from mobile_master 
      group by distributor_id 
      having count(distributor_id) = (select count(distributor_id) 
              from mobile_master 
              group by distributor_id 
              order by distributor_id 
              limit 1));

#23

select * from customer_info;
select * from sales_info;

select Customer_ID,Customer_Name,Address
from customer_info
where Customer_ID = (select customer_id 
     from sales_info
      group by customer_id
     having sum(net_amount) = (select sum(net_amount) from sales_info group by customer_id order by sum(net_amount) desc limit 1));

#23

use mobile;
select * from sales_info;
select * from mobile_master;

#24

select m.IME_No,m.Model_Name,case coalesce(discount,0) 
when 0 then 'not sold'
else 'sold' 
end as salestatud
from mobile_master m left outer join sales_info using (IME_No)
where m.Model_Name like 'S%B';

select * from mobile_specification;

select * from distributor;

select * from mobile_master;

#25

select Distributor_ID,Distributor_Name,Address,Mobile
from distributor
where Distributor_ID in (
select DISTRIBUTOR_ID 
from distributor join mobile_master using (distributor_id)
where IME_No in (
select IME_No 
from mobile_master join mobile_specification using (ime_no)
where network_3g='yes' and os like 'A%' and camera_quality='3.5mp'));

use mobile;

#26

select * from mobile_master;
select * from sales_info;
select distinct Model_Name,Manufacturer 
from mobile_master 
where model_name = (select model_name from sales_info group by model_name having count(model_name) = (select count(model_name) from sales_info group by model_name order by count(model_name) desc limit 1)limit 1);

RealCode4You

Mobile Management System Database Design | Hire Best Database Design and Implementation Expert

Recent Posts