RDBMS CODEING

Q) Create table 1( create Mobile_master table with constraints mentioned?

A) create table mobile_master(

ime_no varchar(10) primary key,

model_name varchar(20),

manufacturer varchar(20),

date_of_manufac date,

warranty_in_years number(10),

price number(7,2),

distributor_id varchar(10),

spec_no varchar(10),

foreign key(distributor_id) references distributor(distributor_id),

foreign key(spec_no)references mobile_specification(spec_no)

);

Q)Create table 2( create Sales_info with constraints mentioned)?

A) create table sales_info(

salesid number(10) primary key,

sales_date date,

ime_no varchar(10),

price number(10),

discount number(10),

net_amount number(10),

customer_id varchar(10),

foreign key(ime_no)references mobile_master(ime_no),

foreign key(customer_id) references customer_info(customer_id));

Q) ALTER TABELE 1( constraint CHECK_WARRANTY in Mobile_Master table)?

A) ALTER TABLE MOBILE_MASTER ADD CONSTRAINT CHK_WARRANTY

CHECK(WARRANTY_IN_YEARS>0);

Q) ALTER TABLE 2 ( change the name of the table from Sales_Info to Trade_Info)?

A) ALTER TABLE Sales_Info RENAME TO trade_info;

Q) Purge Table ( all the records from the "Customer_Info" table. Write an SQL statement to purge all the records from
the Customer_Info table without allowing rollback activity.)?

A) truncate table customer_info;

Select * from customer_info;

Q) Drop Table( drop Mobile Specification table)?

a) DROP TABLE MOBILE_SPECIFICATION cascade constraints;

Q) Try It Out ( order to create the department table using the structure given below, considering the product table
that you have already created.)?

A) create table Department(dept_id number(4),Prod_id number(4),dept_name varchar(25),dept_head

varchar(25) Not null,constraint PK primary key(dept_id),constraint FK foreign key(Prod_id) Reference
Product(Prod_id),UNIQUE(dept_name));

Q) Update Records 1( update battery life of all the mobiles to 10 hours in the Mobile_Specification
table)?

A) update mobile_specification set battery_life_hrs=10;

Q) Update Records 2 ( update the warranty in years to 2 and price as 20000 for all 'SamsungS2' mobiles
in mobile_master table)?

A) update mobile_master set Warranty_in_years=2,price=20000 where Model_Name=’SamsungS2’;

Q) Delete Records ( remove sales information details of the customer whose id is 'MB10010')?

A) delete from Sales_info where customer_id=’MB10010’;

Q) Insert Records(Insert the following records to the customer_info table)?

A) INSERT INTO
CUSTOMER_INFO(CUSTOMER_ID,CUSTOMER_NAME,ADDRESS,MOBILE,EMAIL)VALUES('C01','Peter','Alabama',9884564566,'
peter@gmail.com');

insert into
CUSTOMER_INFO(CUSTOMER_ID,CUSTOMER_NAME,ADDRESS,MOBILE,EMAIL)values('C02','Johan','Colorado',7324565689,'j
oha@yahoo.com');

Q) Display Customer Details(display the customer details such as customer id, name and phone
number.)?

A) select customer_id,customer_Name,Mobile from Customer_Info;

(
Q) Display Distributor details display the distributor id, name and email of all the distributors. Sort the
records based on the distributor name in ascending order )
A) select distributor_id,distributor_name,Email from distributor order by distributor_name asc;

Q) Select all - Mobile details( display all the details of the mobile. Sort the results based on ime no in
ascending order)?

A) select * from Mobile_master order by IME_No asc;

Q) Select Unique Operating System( display the OS of the mobile. Display unique values and sort them
in asc)?

A) select Unique(OS) from Mobile_Specification order by OS asc;

Q) Display Customers whose name starts with S and ends with a the results based on name in
ascending order?

A) select Customer_id,Customer_name,Address,Email from Customer_Info where Customer_name like'S%a'

order by Customer_name;
Q) Sales details based on price and discount?

A) select Salesid,Sales_date,IME_No from Sales_Info where price<15000 and discount>500 order by Sales_date
desc;

Q) Customers with gmail account(display the customer id, name, address and email id of those
customers who have gmail account. Sort the results based on name in ascending order)?

A) select Customer_ID, Customer_Name, Address, Email from Customer_Info where Email like '%gmail.com%'

order by Customer_Name asc;

Q) Mobile details based on warranty and cost?

A) select IME_No, Model_Name, Manufacturer, Date_of_Manufac from Mobile_Master Where Price >10000 and
Warranty_in_years<5 order by Model_Name asc, IME_No desc;

Q) Mobiles based on manufacturer(manufactured by 'Nokia' or 'Samsung'. results based on

manufacturer in ascending order.)?

A) select IME_No, Model_Name, Manufacturer, Price from Mobile_Master where Manufacturer = 'Nokia' or
Manufacturer = 'Samsung' order by Manufacturer asc;

Q) Mobile details based on price?

A) select IME_No, Model_Name, Manufacturer, Price from Mobile_Master where Price between 10000' and
'20000' order by IME_No asc;

Q) Mobile details based on Distributor?

A) select IME_No, Model_Name, Manufacturer, Price from Mobile_Master where Distributor_ID not in('SA111',
'MC111') order by IME_No asc;

Q) Distributor details based on email?

A) select Distributor_ID, Distributor_Name, Address, Mobilenumber from Distributor where Email is null order by
Distributor_Name desc;

Q) Increase in warranty?

A) select IME_No, Model_Name, Warranty_in_Years+5 as Warranty from Mobile_master where Manufacturer

like 'Nokia%' order by Model_Name asc;

Q) Credentials ( CUSTOMER_ PASSWORD)

A) select Customer_NAME, ((substr(Customer_Name, 1,3))||(length(Customer_Name))||(substr(mobile,-3))) as

CUSTOMER_PASSWORD from Customer_Info ORDER BY Customer_Name DESC;

Q) Create View (Create a view named student_registration_details )?

A) create view student_registration_details as select

a.studid, a.firstname, a.lastname,b.coursename, b.duration,doj

from registration c, student a, course b where c.studid=a.studid and c.courseid=b.courseid;

Q) Fetch Third highest cabin cost ( display the third highest cabin cost rate)?

A) select max(rate) as third_max from cabincost

where (rate<(select max(rate) from cabincost where rate<(select max(rate) from cabincost)));
Q) Customer details based on discount(TOTAL_MOBILES_PURCHASED)?

A) select c.customer_id, c.customer_name, c.mobile, count(c.customer_id)as total_mobiles_purchased

from customer_info c,sales_info s where c.customer_id=s.customer_id and s.discount is null group by

c.customer_id, c.customer_name, c.mobile having count(c.customer_id) >1;

Q) Mark with student name( MAX_MARK)?

A) select student.student_name,max(mark.value) as max_mark from student inner join mark on

student.student_id=mark.student_id group by student.student_name having lower (student.student_name) like
'%i' order by student.student_name desc;

Q) Mobile details with discount?

A) SELECT D.DISTRIBUTOR_NAME,M.MODEL_NAME,S.SALES_DATE,S.DISCOUNT FROM DISTRIBUTOR D,

MOBILE_MASTER M, SALES_INFO S WHERE D.DISTRIBUTOR_ID=M.DISTRIBUTOR_ID AND M.IME_NO=S. IME_NO
ORDER BY D.DISTRIBUTOR_NAME;

Q) Customer purchase details?

A) SELECT C.CUSTOMER_ID,C.CUSTOMER_NAME,S.PRICE FROM CUSTOMER_INFO C,SALES_INFO S WHERE

C.CUSTOMER_ID=S.CUSTOMER_ID AND S.PRICE>=15000 AND S.PRICE<=25000 ORDER BY C.CUSTOMER_ID ASC;

Q) Android based mobiles?

A) SELECT M.IME_NO,M.MODEL_NAME,M.DATE_OF_MANUFAC FROM MOBILE_MASTER

M,MOBILE_SPECIFICATION S WHERE S.SPEC_NO=M.SPEC_NO AND LOWER(S.OS) LIKE android%' and
BATTERY_LIFE_HRS>=8 ORDER BY M.IME_NO ASC;

Q) Temperature with town?

A) SELECT (TOWNNAME ||’has'||wintertemp ||‘Degree celsius') as temperature from town where

wintertemp>(select wintertemp from town where lower (townname)= 'jaipur') order by temperature;

Q) Display model count?

A) SELECT manufacturer, COUNT (MODEL_NAME) AS MOBILE_MODEL_COUNT FROM MOBILE_MASTER GROUP

BY manufacturer HAVING COUNT (MODEL_NAME)=3 ORDER BY manufacturer DESC;

Q) Discount based on month?

A) select salesid, sales_date, ime_no, price, coalesce (TO_CHAR(discount), 'Not Applicable') as DISCOUNT FROM
SALES_INFO where to char(sales_date, 'Mon')='Feb' order by IME_NO;

Q) Maximum and minimum price?

A) select max(price) as MAX_PRICE,min(price) as MIN_PRICE from mobile_master;

Q) Price Calculation?

A) select sum(price) as TOTAL_PRICE, round(AVG(price),2) as AVG_PRICE from mobile_master;

Q) Display Discount Details based on condition?

A) SELECT (TO_CHAR(SALES_DATE, 'MONTH')) AS MONTH, PRICE, CASE WHEN discount < 250 THEN ‘Low
Discount' WHEN 250<discount AND discount<750 THEN 'Medium Discount’ ELSE 'High Discount’ END AS
DISCOUNT_INFO FROM SALES_INFO order by PRICE;
Q) Mobile count based on warranty

A) SELECT manufacturer, COUNT (MODEL_NAME) AS "COUNT" FROM MOBILE_MASTER GROUP BY manufacturer

HAVING AVG(WARRANTY_IN_YEARS)>3;

Q) Low price mobiles?

A) SELECT IME_NO, MODEL_NAME, DATE_OF_MANUFAC, WARRANTY_IN_YEARS FROM MOBILE_MASTER where

PRICE=(SELECT MIN(PRICE) FROM MOBILE_MASTER);

Q) Course details based on fees?

A) select r.courseid, course.coursename,sum(fees) as totalfees from registration R inner join student on

r.studid=student.studid inner join course on r.courseid=course.courseid group by r.courseid, course.coursename
having sum(fees) >15000 order by r.courseid;

Q) Display Specification number?

A) select (spec_no||' has '|| dimension ||' dimension') as MOBILE_INFO, camera_quality from
mobile_specification order by MOBILE INFO desc;

Q) Customer purchase date?

A)select (‘The customer id ‘||Customer_id||’has purchased the mobile in the month of

‘||(to_char(sales_date,’Month’))) as PURCHISE_DATE FROM SALES_INFO ORDER BY CUSTOMER_ID DESC;