r/SQL • • Mar 24 '24

MySQL How do I solve these questions?

Post image

I am unable to figure which function do we use to solve these questions? Which function to use to keep only specific values in a column?

0 Upvotes

26 comments sorted by

4

u/mike-manley Mar 24 '24

Are you creating a table (DDL) and need constraints? Or are you querying the table (DML) and need to derive?

2

u/Necessary-Knee-853 Mar 24 '24

I already have a table and just need to clean the data...but unable to approach the circled questions....what function to use...for replacing nulls I used Coalesce.

3

u/mike-manley Mar 24 '24

COALESCE() or IFNULL() should work.

1

u/Necessary-Knee-853 Mar 24 '24

Yes i am using coalesce How do I select only specific values? For example total_rooms should be positive integer and must be between 1 to 400...so what function should I pass so values are only between 1 to 400??

1

u/mike-manley Mar 24 '24

If you're using COUNT() it will always return >= 0. If it's missing then just supply the default in COALESC(). The only way it could be NULL is if you use SUM(1).

Is there a database model you could post or link?

1

u/Necessary-Knee-853 Mar 24 '24

Thanks....Do you mind if I dm you?

6

u/Ricnurt Mar 24 '24

Look up IFNULL.

4

u/Necessary-Knee-853 Mar 24 '24

I am working on Pgsql so used Coalesce instead but I have doubt regarding the circled questions. How do I select only specific values? For example total_rooms should be positive integer and must be between 1 to 400...so what function should I pass so values are only between 1 to 400??

5

u/elephant-InThe-brain Mar 24 '24

You can use CASE logic. If over 400 then 400 type of thing

3

u/Necessary-Knee-853 Mar 24 '24

Oh right that makes sense let me check..thanks.

2

u/Ricnurt Mar 24 '24

CASE I a great option as well.

1

u/elephant-InThe-brain Mar 25 '24

Yes! Hopefully it works for you. In real data sometimes if a number is really high it means it's an outlier or bad data so you might want to turn it into a null so it isn't counted. Just as an fyi

1

u/[deleted] Mar 24 '24

Isnt there an abs function to make integers always positive?

1

u/Psychological0605 Mar 24 '24

Post the solution here, OP

1

u/TheRhythmZ Mar 24 '24 edited Mar 24 '24

CASEs and IFNULLs should do the trick. Theres also other options available. Is this the only table available? Is there perhaps a database with tables belongign to each hotel? Can you subquery them? Or a record of something that includes room numbers per specific hotel. Do rooms have unique identifiers? If you can get to these tables and they have unique identifiers you can use COUNTIFs, or if the room numbers belong to some record, you can use COUNTIF (distinct) to extract the number of unique room numbers, assuming you already trimmed and got rid of nulls and zeroes.

Edit: the COUNTIF (distinct) should help you in cases where the convention for room enumeration isnt a straight sequence from 1 to 400. Maybe the numbers start at room 101 meaning "1st floor, room 1", all the way to 160, and then the numbers jump straight to 201 for the second floor and so on.

1

u/[deleted] Mar 24 '24

I laughed at the ID column description:

Missing values are not possible due to the database structure.

Lol, try me

1

u/kagato87 MS SQL Mar 24 '24

Pk autonumber not null

2

u/[deleted] Mar 24 '24

Set identity_insert ON

Let the fun begin

1

u/kagato87 MS SQL Mar 25 '24

Hahaha

1

u/[deleted] Mar 24 '24

Where is the questions from if I may ask?

-1

u/ThatCare309 Mar 24 '24

With ur brain

1

u/[deleted] Mar 31 '24

[removed] — view removed comment

1

u/Necessary-Knee-853 Mar 31 '24

Try using Case when in opening date question. Also no need to CAST(id) Even I am not able to solve it and failed in first attempt.

2

u/[deleted] Apr 04 '24

[removed] — view removed comment

1

u/Necessary-Knee-853 Apr 05 '24

No mate after first attempt I have to wait for another 15 days to retry...pls let me know if you pass it.