Hello friends in this post we are going to discuss about Capgemini Adapt SQL Previous Question Paper with Solution. Which will helpful to you for practising the Questions for your prepration for exam.
BRL1-1Available Books
Q1. Display the books which are available for the rent
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT * FROM bookrental.book WHERE stock>0;
BRL1-2-Available Books with Expected Quantity
Q2.Display allthe books which have total quantity of more than 30
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT * FROM bookrental.book WHERE stock>30;
BRL1-3-Sort Users Based on Name
Q3.Display the users in the ascending order (alphabetically by their name)
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT * FROM user_details ORDER BY first_name;
BRL1-4-Users Based on City
Q4.Display the total number of users based on the city
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT city,count(*) FROM user_details GROUP BY city;
BRL2-1-Books based on Rental Amount
Q5.Fetch the books which have rental as less than 500 for a month
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT * FROM book WHERE (rent per_day*30)<500;
BRL2-2-Books based on Price and Quantity
Q6.Display the books which have a price more than $500 and the available quantity is 10
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT * FROM book WHERE price>500 AND available_quantity=10;
BRL2-3- Books based on Quantity range
Q7.Display thebooks which have more than 30 and less than 50 as total quantity
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT * FROM book WHERE stock between 30 AND 58;
BRL3-1-Books Rented on Given Date
Q8.Display the books which was rented out on 20/11/2020
ANS – SELECT * FROM book WHERE book id in (SELECT book id FROM book_rental WHERE to_char (rental start_date, DD-MM-YYYY )-20-11-2020);
BRL3-2-Current Month Users Details
Q9.List the users who has availed the books current month
ANS – SELECT * FROM book WHERE book id in (SELECT book id FROM book_rental WHERE to_char (rental_start_date, MON)=DEC);
BRL3-3-User Details of Book Rental
Q10.Display all the users who have rented at least 1book with details of book and rental return date.
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT first name, title, rental end_date FROM user_details a, book rental b, book c WHERE a.user id=b.user id(+) AND b.book id=c.book id;
BRL3-4-Maximum Book availed by the User
Q11.Fetch the first name of the user who has availed maximum number of books for rental during the current fiscal
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT first name
FROM user detail1s
WHERE user id-(SELECT *
FROM (SELECT uSer id
FROM book_rental GROUP BY user id
ORDER BY count(*) desc)
WHERE row num<=1);
BRL3-5-Books Availed by Most of the users
Q12.Fetch the book which was availed for rent by most users
ANS – alter session set current schema = bookrental;
SET heading OFF
select from * book where
book id (select from
select book id
from book rental
group by book id
order by count (book id) desc)
where rownum<=1);
BRL3-6-Users Rented Neural Networks Book
Q13.ldentify the users who have opted for book named ‘Neural Networks’
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT * FROM user _details
WHERE user id in(
SELECT a.user id
FROM book rental a,book b,user details c
WHERE b.book_id = a.book_id
AND c.user_id = a.user_id
AND b.title = Neural Networks);
BRL3-7-Book not returned by the user
Q14. Fetch the user information who has not returned the books after the rental period
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT * FROM user_details WHERE user_id in(SELECT user_id
FROM book_ rental WHERE isreturned = no);
BRL3-8-User and Rental information
Q15.Display the user’s first name, book title and the rental end date of
the book for the rental id/s which was rented on 25th of Nov 2020
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT ud.first name,bk.title,br.rental end date FROM user details ud,book bk, book_rental br WHERE ud.user id = br.user id AND br.book_id = bk.book_id AND to_char(br.rental start_date, ‘dd/mm/yy )-25/11/20’;
BRL3-9-All User’s and Rental information
Q16.Display all the users first name, book title, rental start date, rental
end date for all users with the rental details even they don’t avail
any books for rental and sort the output based on first name
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT ud. first name, bk.title, br.rental_start_date, br.ren
FROM user details ud, book bk, book rental br
WHERE ud.user_id = br.user_id(+)
AND br.book_id = bk.book_id(+)
ORDER BY ud. firstname;
BRL3-10-Maximum Rent for the Month
Q17.Display fist name, city, total rent amount of the month for the user whose rental amount is maximum for the month.
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT * FROM (SELECT first name, city,SUM(totalamount) FROM book rental br,user_details ud WHERE br.user_id= ud.user_id AND br.rental_start date like DEC% GROUP BY ud.first_name, ud.city
ORDER BY SUM( total_amount) desc) WHERE rownum=1;
BRL4-1- Month Maximum Books Rented
Q18.Display month and the number of books when the maximum number of books were rented
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT FROM
(SELECT TO_CHAR (RENTAL_START_DATE, Month)
AS MONTH, COUNT (book_id) AS TOTAL_BOOKs
FROM BOOK RENTAL
GROUP BY TO CHAR (RENTAL_START_DATE, Month)
ORDER BY TOTAL_BOOKS DESC)
BRL4-2-Book Rented for Maximum Duration
Q19.Fetch the book’s title which was on rent for maximum duration
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT title FROM book WHERE book id in(SELECT book_id FROM book_rental WHERE (months between (nental start_date, rental_end date) = (SELECT max(months between(rental_start date, rental end_date)) FROM book nental)));
BRL4-3-Book availed by most of the customers
Q20.Fetch the book’s title which was availed for rent by most of the users.
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT title FROM book WHERE book id in(SELECT book_id FROM book_rental HAVING count(*) = (SELECT max(count(*)) FROM book nental
GRoUP BY book_id) GROUP BY book_ id);
BRL4-5-Top 2 Users by Revenue
Q21.Identify the top 2 preferred users in descending order of max revenue generated.
ANS – alter session set current schema = bookrental;
SET heading OFF
select*from (select ud.first_name
from user_details ud,book rental br
where ud. user id br.user id
group by ud.first name
order by sum(total_amount) dese)
where rownums;
BRL5-1-Rental Details of a Book
Q22.For a given book named as Machine Learning,display allthe rental details
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT br.
FROM book b,book rental br
WHERE b.book idbr.book id AND b.title = Machine Learning;
BRL5-2-Rental Book by City
Q23.Display the books title which were taken for rent only in “Bangalore
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT title FROM (SELECT title,b.book id
FROM book b,book rental br,user_detai1s ud
WHERE br.book_id b.book id
AND br.user_id -ud.user id
AND ud.city-‘Bangalore)
WHERE book id NOT 1n
(SELECT book id FROM book rental
WHERE user id in
(SELECT user id FROM user details
WHERE city Bangalore ))
AND rownum-1;
BRL5-3-Book Not Rented by Customer
Q24.Display the books title which was not at all rented out
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT title
FROM book
WHERE book id NOT in(SELECT book id
FROM book_ rental);
BRL5-4-Rental Details of Chandrav
Display the Rental details of a user “Chandrav”
ANS – alter session set current schema = bookrental;
SET heading OFF
SELECT br.
FROM book rental br, user details ud
WHERE br.user id ud.userid AND ud. first name Chandrav