SQL query about database

Question

I have some trouble with SQL. I have database structure like below I should write a query that return: All moviestar's name and address, where they are woman(G='F') and address="Berlin" + all studio's name and address, where address="Berlin" too. Ordered by address.

My suggestion is:

SELECT * FROM
   (SELECT name, address 
   FROM MovieStar 
   WHERE gender = 'F'
   UNION 
   SELECT name, address FROM Studio) T 
WHERE T.address LIKE '%Berlin%' ORDER BY T.address;

Answer 1

You can just do this

SELECT name, address from MovieStar WHERE gender='F' AND address LIKE '%Berlin%'
UNION 
SELECT name, address from Studio WHERE address LIKE '%Berlin'

and if you want use subquery like you do in your example, I think you have to add "as" keyword for sub query

SELECT * FROM
   (SELECT name, address 
   FROM MovieStar 
   WHERE gender = 'F'
   UNION 
   SELECT name, address FROM Studio) AS T 
WHERE T.address LIKE '%Berlin%' ORDER BY T.address;

but I'm not sure, that's a good deal for performance since you will have a memory table created and then projected

Answer 2

This should work for you.

SELECT name, address 
   FROM MovieStar 
   WHERE gender = 'F' AND address LIKE '%Berlin%'
   UNION 
   SELECT name, address FROM Studio WHERE address LIKE '%Berlin%' 
ORDER BY address;