SQL Programming Cognizant Handson Solutions

SQL Programming Cognizant Handson Solutions

Bymadwithcode

In this post we are covering all SQL programming handson solutions of cognizant.

Additional Handson will be added soon in this post.

Insert Records – Department

insert into department (department_id,department_name,department_block_number) VALUES(1,"CSE",3);

insert into department (department_id,department_name,department_block_number) VALUES(2,"IT",3);

insert into department (department_id,department_name,department_block_number) VALUES(3,"SE",3);

Department name based on block number

select department_name from department where department_block_number=3 order by department_name asc;

Delivery Partner details based on rating

select partner_id,partner_name,phone_no from delivery_partners where rating between 3 and 5 order by partner_id;

Car & owner details based on car type

select car_id,car_name,owner_id from cars where car_type="Hatchback" or car_type="SUV" order by car_id;

Car details based on type and name

select car_id,car_name,car_type from cars where car_name Like '%Maruthi%' and car_type="Sedan" order by car_id;

car rental system – Insert values

insert into rentals (rental_id,customer_id,car_id,pickup_date,return_date,km_driven,fare_amount) values("R001","C007","V004","2018-03-10","2018-03-10",800,9000);
insert into rentals (rental_id,customer_id,car_id,pickup_date,return_date,km_driven,fare_amount) values("R002","C001","V007","2018-03-11","2018-03-12",200,3000);
insert into rentals (rental_id,customer_id,car_id,pickup_date,return_date,km_driven,fare_amount) values("R003","C007","V003","2018-04-15","2018-04-15",100,1500);
insert into rentals (rental_id,customer_id,car_id,pickup_date,return_date,km_driven,fare_amount) values("R004","C007","V001","2018-05-16","2018-05-18",1000,10000);
insert into rentals (rental_id,customer_id,car_id,pickup_date,return_date,km_driven,fare_amount) values("R005","C004","V005","2018-05-10","2018-05-12",900,11000);
insert into rentals (rental_id,customer_id,car_id,pickup_date,return_date,km_driven,fare_amount) values("R006","C004","V006","2018-05-20","2018-05-21",200,2500);

Hunger eats – update table

update customers set phone_no=9876543210 where customer_id="CUST1004";

Customers having gmail id

select customer_id,customer_name,address,phone_no from customers where email_id like '%@gmail.com';

Student and their Department Based on City

select Student.student_name,Department.department_name from Student inner join Department on Student.department_id=department.department_id where Student.city="Coimbatore" order by student_name;

Concatenating Details

select CONCAT(address,",",city) as Address from student order by Address desc;

Password Generation

select name, CONCAT(substr(name,1,3),substr(phno,1,3)) as password from users order by name;

Customer using HDFC bank

select distinct u.name, u.address
from users u join bookingdetails b 
           on u.user_id=b.user_id
           where u.user_id not in (select user_id from bookingdetails
           where lower (name)='hdfc')
           order by 1;

Rental details based on date

select rental_id,car_id,customer_id,km_driven from rentals where monthName(return_date)='August' && year(return_date)=2019;

Hotels that took order based on month

select distinct hotel_details.hotel_id,hotel_details.hotel_name,hotel_details.rating from hotel_details,orders where hotel_details.hotel_id=orders.hotel_id 
and monthname(order_date)='July';

Hotel_info

select concat(hotel_name, ' is a ', hotel_type, ' hotel') as hotel_info
from hotel_details
order by hotel_name desc;

Similar Posts