Missing right parenthesis in a Create sentence

Question

I get this missing right parenthesis but I cant find it

CREATE TABLE PUBLICACION AS 
(SELECT id AS id_revista,
 nombre AS nombre_revista, 
 COUNT(C.dni) AS cuantos_contratados, 
 COUNT(A.dni) AS cuantos_freelance
 FROM REVISTA, 
 PERIODISTA_CONTRATADO AS C, 
 PERIODISTA_FREELANCE AS A );

Answer 1

In Oracle, table aliases don't accept the AS keyword. You commented that - when you fixed that - you got the column ambiguously defined error. Must be one, or both, columns i SELECT that don't have table aliases and column(s) with the same name exist in two or all three tables. So, use table alias

What is missing, is the group by clause as all non-aggregated columns must be specified in that clause.

Therefore:

SQL> create table publicacion as
  2    select r.id           as id_revista,
  3           r.nombre       as nombre_revista,
  4           count(c.dni) as cuantos_contratados,
  5           count(a.dni) as cuantos_freelance
  6    from revista r,
  7         periodista_contratado c,
  8         periodista_freelance a
  9    group by id, nombre;

Table created.

SQL>

However, that's most probably wrong because you produced cross-join of all 3 tables from the FROM clause. As you decided to separate tables with commas, you'd now have to join tables in the WHERE clause, but that's not a good idea - explicitly JOIN tables, and leave the WHERE clause for possible conditions.