SQL Programming Code Challenge Solutions Cognizant

Here are the solutions for SQL Programming Code Challenge which are asked in cognizant.

Note: If you are viewing this page in mobile, use in desktop mode to get the proper structure of code in solutions

Code Challenge – RDBMS Select Statements

Patient Appointment Details based on reason

Refer to the given schema.

Write a query to display the patient id, patient first name, patient age, appointment number, and the date of appointment of those patients whose reason for the appointment is ‘FEVER’. Sort the records based on patient id.

Use the Appointment table and Patient table to retrieve the records. Data is case-sensitive

Solution

SELECT P.PATIENT_ID, P.P_FIRST_NAME, P.P_AGE, A.APP_NUMBER, A.APP_DATE
FROM PATIENT P, APPOINMENT A
WHERE P.PATIENT_ID = A.PATIENT_ID
    AND A.APP_REASON = 'FEVER'
ORDER BY P.PATIENT_ID;

Code Challenge – RDBMS DDL & DML

1. Hostel-Insert Student Records set2

Solution

ALTER TABLE CUSTOMER_MASTER
MODIFY COLUMN PHONE_NO(10);

2. Movie-Modify the datatype set2

Refer to the given schema diagram. Insert the below records into Student_details Table.

STUDENT_ID	STUDENT_NAME	DEPARTMENT	DOB	ADDRESS	PHONE_NO	EMAIL_ID
S1001	Varsha	ECE	1999-06-12	CHENNAI	9845712345	varsha123@gmail.com
S1002	William	ECE	1999-02-04	CALCUTTA	6845712345	william123@gmail.com
S1003	Basha	EEE	1999-06-14	DELHI	9945712345	basha222@gmail.com
S1004	Catherine	CSE	1998-08-16	DELHI	6785712345	cathu123@gmail.com
S1005	Kate	ECE	1999-06-30	BANGALORE	7685712345	katedd@gmail.com
S1006	Michel	ECE	1998-06-04	COIMBATORE	6645712345	michel000@gmail.com

Maintain the same sequence of column order, as specified in the question description

Solution

INSERT INTO STUDENT_DETAILS
VALUES
    ("S1001", "Varsha", "ECE", "1999-06-12", "CHENNAI", "9845712345", "varsha123@gmail.com"),
    ("S1002", "William", "ECE", "1999-02-04", "CALCUTTA", "6845712345", "william123@gmail.com"),
    ("S1003", "Basha", "EEE", "1999-06-14", "DELHI", "9945712345", "basha222@gmail.com"),
    ("S1004", "Catherine", "CSE", "1998-08-16", "DELHI", "6785712345", "cathu123@gmail.com"),
    ("S1005", "Kate", "ECE", "1999-06-30", "BANGALORE", "7685712345", "katedd@gmail.com"),
    ("S1006", "Michel", "ECE", "1998-06-04", "COIMBATORE", "6645712345", "michel000@gmail.com");

Code Challenge – Function-Scalar & Aggregate

Minimum & Maximum Discount Amount

Write a query to display the minimum discount amount and the maximum discount amount offered as per the coupon. Give an alias name as MIN_DISCOUNT to the minimum discount amount and MAX_DISCOUNT to the maximum discount amount.

Maintain the same sequence of column order, as specified in the question description

Solution

SELECT A.MIN_DISCOUNT, B.MAX_DISCOUNT
FROM (
         SELECT MIN(DISCOUNT_AMOUNT) AS `MIN_DISCOUNT`
         FROM DISCOUNT_MASTER
     ) A,
     (
         SELECT MAX(DISCOUNT_AMOUNT) AS `MAX_DISCOUNT`
         FROM DISCOUNT_MASTER
     ) B;

Code Challenge – Functions & SubQueries

Patient Appointment details Based On Month

Refer to the schema. Write a query to display unique patient id, patient first name, patient age, address and contact number of all the patients who booked appointments in the month of JUNE 2019. Sort the records based on patient id.

Use Patient & Appointment tables. The appointment date will be in the format ‘YYYY-MM-DD’.

Solution

SELECT DISTINCT P.PATIENT_ID, P.P_FIRST_NAME, P.P_AGE, P.ADDRESS, P.CONTACT_NUMBER
FROM PATIENT P, APPOINTMENT A
WHERE P.PATIENT_ID = A.PATIENT_ID
    AND MONTH(A.APP_DATE) = 6
    AND YEAR(A.APP_DATE) = 2019
ORDER BY P.PATIENT_ID;