Where am i wrong in this 'apartment' project in SQL

Question

SELECT * FROM apartments WHERE city in ('Las Vegas', 'Marlboro', 'Great') AND
COUNT(CASE WHEN AVG(price) < price THEN 1 ELSE 0 END) AS not_rented
ORDER BY price;

Answer 1

SELECT *
FROM Apartments
WHERE Price > ( SELECT AVG ( Price ) FROM Apartments )
AND status = 'Not rented'
ORDER BY Price

This is correct👍😍

Answer 2

SELECT * FROM apartments
WHERE price > (SELECT AVG (price) FROM apartments )
ORDER BY price ASC;

Answer 3

Arvind Kumar Well no need to check city in because you need all cities in which appartments are not rented and Price has great than or equal to average. So you need a subquery also.

Check this

SELECT * FROM Apartments WHERE Price >= (SELECT avg(Price) FROM Apartments) AND status = Not rented ORDER BY Price;

Answer 4

Arvind Kumar

It will work. You just have to put Not rented in single quotes.

Not rented is a string so you must know in SQL we have to put string in single quotes.

Answer 5

This is working. Finally, I got it. I'm really tired.

SELECT * FROM apartments
WHERE price > (SELECT AVG(price) FROM apartments)
ORDER BY price

Answer 6

SELECT *
FROM Apartments
WHERE Price > ( SELECT AVG ( Price ) FROM Apartments )
AND status = 'Not rented'
ORDER BY Price

i applied the same but it still says not correct!!

Answer 7

I Am Groot ! Sorry this not working

Answer 8

It is not working

Answer 9

I tried in every possible way it doesn't work,, The only error it always shows me is lowercase & uppercase,,I even changed all the starting letters of string to uppercase but in 'Not rented' Only N should be in uppercase other all should be in lowercase,,but once I change the starting letters of the string to uppercase i get 'Not Rented'!!! In Rented R should be in lowercase,,but i can't change it!!
Do you guys know any way to uppercase only frst letter 'N' but not 'R'?????

Answer 10

MATHAVY UMASUTHASARMA and ssm could you please explain why it works? There is no query to get 'not rented' apartments.

Answer 11

SELECT * FROM apartments
WHERE price > (SELECT AVG(price) FROM apartments)
ORDER BY price

this is the answer

Answer 12

SELECT * FROM Apartments
WHERE price >= (SELECT AVG(price) FROM Apartments )
ORDER BY price;

This is correct 💯

Answer 13

I forgot about the nested query🙈 I wrote like this. This request passed the test😎

SELECT * FROM Apartments WHERE (price >= 750 AND status='Not rented') ORDER BY price

Answer 14

ssm I tried something similar, but it didn't work!...

Answer 15

Try this 👇🏻
SELECT * FROM apartments
WHERE price > (SELECT AVG(price) FROM apartments)
ORDER BY price

Answer 16

SELECT * FROM apartments
WHERE price > (SELECT AVG(price) FROM apartments)
ORDER BY price

Answer 17

actually i got it now, the issue is with the app. once i closed the app and reipened it again and tried the same query it worked!

Answer 18

SELECT * FROM apartments where price >( SELECT AVG (price)FROM apartments ) and status='Not rented'
ORDER by price;

Answer 19

SELECT *
FROM Apartments
WHERE Price > ( SELECT AVG ( Price ) FROM Apartments )
AND status = 'Not rented'
ORDER BY Price

this is right

Answer 20

SELECT * FROM APARTMENTS
WHERE PRICE > (SELECT AVG(PRICE) FROM APARTMENTS)
ORDER BY PRICE;

This will work