0

Create a new table that contains all items that cost more than the average.

My previous queries:

CREATE VIEW AveragePrice AS SELECT * FROM Item
WHERE price > (SELECT AVG(price) FROM Item);

SELECT * FROM item
WHERE price > (SELECT AVG(price) FROM item);

Now I want to create table and insert same data as above to the new table in one query..mimer sql

Boohz12
  • 19
  • 5
  • Mimer SQL has no such functionality. You have to do it in two steps. (First `CREATE TABLE`, then `INSERT INTO table SELECT ...`.) But why store same values twice?!? I'd keep the VIEW solution - and avoid data redundancy, and also prevent data inconsistency (which is always the risk when storing data dependent on other table data, such as AVG(price).) – jarlh Jan 13 '19 at 17:11

1 Answers1

-1
 create table newTab as 
 SELECT * FROM item
 WHERE price > (SELECT AVG(price) FROM item);
Joe Love
  • 5,594
  • 2
  • 20
  • 32
  • Thank you for your answer but it gives me error: 1: create table AveragePrice2 as SELECT * FROM item WHERE price > (SELECT AVG(price) FROM item) ^ Mimer SQL error -12104 in function PREPARE Invalid construction – Boohz12 Jan 11 '19 at 19:28