วีดีโอ

thank you. see ya!!

Thank you very much. See you on Discord @ www.nbktechworld/discord

What's not working? Be specific, share your code and what your expectations are.

Thank you for the suggestion! See you on Discord @ discord.com/invite/MJwGcwcdae

Not sure what you mean. How about you ask on Discord? See you on Discord @ discord.com/invite/MJwGcwcdae

You're welcome. See you on Discord @ discord.com/invite/MJwGcwcdae

You're welcome! Join us on Discord @ discord.com/invite/MJwGcwcdae

Join us on Discord discord.com/invite/MJwGcwcdae and message @nbkhope there Alternatively, message me on social media such as twitter.com/nbktechworld

MySQL syntax requires semicolons. Try omitting the semicolon and see what happens. As for the changing of the delimiter when writing a stored procedure, it's due to the following: "If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server." Source: dev.mysql.com/doc/refman/8.3/en/stored-programs-defining.html

You're welcome. Stop by our Discord @ discord.com/invite/MJwGcwcdae

You're welcome. See you on Discord discord.com/invite/MJwGcwcdae

Was that question? Try in Oracle and let us know if it works the same.

No the solution was not working for oracle SQL

@@honeyworldshop, let us your query and what specific errors messages you got.

@@honeyworldshop , assuming you tried the query with the REGEXP, I tried it out in Oracle SQL and learned they don't use the same way as MySQL, so you have to use Oracle SQL's REGEXP_LIKE. For example the following worked for me: SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY, '^[aeiou]', 'i'); REGEXP_LIKE arguments, in order: 1) column name to target 2) the regular expression 3) option for case insensitive

We need an aggregate function to use with the GROUP BY. In the inner SELECT, we build a table whose rows are like this: 1 NULL NULL NULL Eve 2 NULL NULL NULL Jennifer ... 1 Aamina NULL NULL NULL ... We need to combine all the rows (group them) by the row number (the first column). For the example, above, for number 1, it would consolidate the two separate rows into one: 1 Aamina NULL NULL Eve But to get the name of the person there we have to aggregate a value that is supposed to be derived after going through the rows with same row number. One example of aggregation is consolidating all the rows into a specific group, then counting them with COUNT to get a count of how many rows were consolidated into that category. In the case here, we don't want to count, but rather extract the single non-NULL value among all the rows with the same row number. The way the rows are built, there's only one column value for the specific person for that specific row number, all the other values in other rows for that specific row number being NULL. You can also use MAX and it works the same way. To better understand the query, try breaking it into pieces, starting from the simple SELECT * FROM OCCUPATIONS; Try to understand each step of the way as you build the query. See what the inner SELECT by itself does. See what having only one column does. Then keep incrementing and seeing how the query evolves.

Hi, I first checked what version of MySQL HackerRank was using: SELECT VERSION(); It was 8.0.33, so I know ROW_NUMBER() function is available. Next, you should isolate the pieces of the query to debug it. Like start with a small easy statement and see if it works. Then keep building more and running it and seeing it still works. Using that process I was able to determine that the problem is from the ROW_NUMBER() line. You used an alias "AS row_number", but that causes problems because row_number is a reserved word. Change that to row_number1 or any other name. Also make sure to update the new name in the WHERE clause at the end (row_number1 IN ...). With that, it should work.

@@nbktechworld yes! Got it

Yes, you can use any SQL database. The process is very similar. Instead of PGAdmin, just use the database management client of your flavor of SQL. For example, for MySQL you can use MySQL Workbench. Instead of the postgresql library/drivers, you'd use the mysql one.

You're welcome! See you on Discord @ discord.com/invite/MJwGcwcdae

You're welcome. See you on Discord @ discord.com/invite/MJwGcwcdae :)

You're welcome. Let's talk more on Discord @ discord.com/invite/MJwGcwcdae

Thank you for the comment. See you on Discord @ discord.com/invite/MJwGcwcdae

Ah I figured it out, when you create the procedure, you enter IN n INT into the parentheses lke this: CREATE PROCEDURE stars(IN n INT) and then change the DECLARE clause: DECLARE i INT DEFAULT n;

I'm glad you figured it out. See you on Discord @ discord.com/invite/MJwGcwcdae

Hi, what is your SQL option? MySQL? You have to split your where into two to be correct. CITY NOT REGEXP '^[aeiou]' AND CITY NOT REGEXP '[aeiou]$'

Hi it's in MySQL And I have written your code still showing error

@@Aabjjjjmja What error are you getting? Send screenshot of the error message, your code.

@@nbktechworld hi its working now may be some technical glitch on that day thank you so much for replying

Try removing it and see what happens! When you are unsure what something does, try removing it and see the effect before / after.

yea I saw it didn't work without it but i didn't understand why. It also seems wrong to me that it let you perform that on a string variable@@nbktechworld

Follow same pattern and add more columns. Add IF with NewAlias, then do MIN(NewAlias) in outer SELECT.

Please copy and paste the specific error message. Or send a screenshot.