The today over lesson is about the Data Analyst Interview: Write an sql query to return odd number rows, I hope this lesson is useful for you.

SQL QUESTION:

You have a customer table dataset with AGE column that contains both even and odd numbers. Write an SQL query to return ONLY odd number rows in a table.

Before

customer_id
character varying
customer_id_name
character varying
segment
character varying
age
integer
CG-12520Claire GuteConsumer67
DV-13045Darrin Van Huff Corporate31
SO20335Sean O’Donnell Consumer65
BH-11710Brosina HoffmanConsumer20
AA-10480Andrew AllenConsumer50
IM-15070Irene MaddoxConsumer66
HP-14815Harold PawlanHome Office20
PK-19075Pete KrizConsumer46
Before

SQL SOLUTION

This can be achieved using the SQL MOD() function. First assign a row number and then find the odd rows from the age column.

SQL QUERY

SELECT *
FROM Customer
WHERE MOD (age, 2) <> 0;

Result

customer_id
character varying
customer_id_name
character varying
segment
character varying
age
integer
CG-12520Claire GuteConsumer67
DV-13045Darrin Van HuffCorporate31
SO20335Sean O’DonnellConsumer65
RESULT

—————————————————————————————————————————————-

WRITE AN SQL QUERY TO RETURN PATIENT’S RECORD

SQL SOLUTION

Given an Appointment & Patient table, Write and SQL query to rturn the name of the patients & the no of physicians they have taken appointment.

Appointment Table

appointmentidpatientprepnursephysicianstrat_dt_timeend_dt_timeexaminationroom
1231658410000000110112008-04-24 10:00:002008-04-24 11:00:00A
2654891310000000210122008-04-24 10:00:002008-04-24 11:00:00B
3654987910000000110212008-04-25 10:00:002008-04-25 11:00:00A
4684658910000000410342008-04-25 10:00:002008-04-24511:00:00B
5987132110000000442008-04-26 10:00:002008-04-24611:00:00C
Appointment Table

Patient Table

ssnnameaddressphoneinsuranceidpcp
100000001John Smith42 Foobar Lane555-0256684726131
100000002Grace Ritchie37 Snafu Drive555-05123654632112
100000003Random j. Patient101 Omgbbq Street555-1204654654212
100000004Dennis Doe1100 Foobaz Avenue555-2048684218793
Patient Table

SQL SOLUTION

This can be achieved using the SQL JOIN since we are dealing with more than one table.

sql query

SELECT p.name "Patient",
       count (t.patient) "Apprintment for N. of Physicians"
FROM appointment t
JOIN patient P ON t.patient=p.ssn
GROUP BY p.name
HAVING count(p.patient)>=1;

Result

patientappointment for No. of Physicians
Grace Ritchie2
John Smith3
Dennis Doe3
Random J. Patient1
Result

What Is Missing In This Sql Query?

sql query question

This query prompted an erro when the Data Analyyst tried updating a customer’s name. Identify the issue with the query.

update customer
set customer_name - Brosina Adams
where customer_id = 11710;

sql query answer

Here is the issue: the first and last name is a string so it should be enclosed in single or double quoation mark for the quer to execute successfully.

update customer
set custome_name = 'Brosina Adams'
where customer_id = 11710;

I hope this lesson is be useful for you.

Source: https://www.instagram.com/datatechcon/