mysql-notes.md

1. create a copy from a table:
   suppose we have already a table named: orders and we want to make a copy from this table which name will be order_archived.\

   CREATE TABLE order_archived AS 
       SELECT * FROM orders

   but please note: this way does not copy some column attributes like primary key.

   2. داشتم فیلم های mosh hamedani رو میدیدم. یک تریک خیلی جالب ازش یاد گرفتم. ببین این کوئری کار نمیکنه:

select 
    invoice_id,
    invoice_total,
    AVG(invoice_total)
FROM invoices;

و این خطا رو میده که AVG رو باید با GROUP BY استفاده کنیم. حالا ماش این شکلی مساله رو حل کرده:

select 
	invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total) FROM invoices) as invoice_average
FROM invoices;

دقت کن => در بخش SELECT یک subquery زده.

3. نکته مهم بعدی. میگه ==> we can not use column alias in an expression

یعنی این کوئری کار نمیکنه:

select 
	invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total) FROM invoices) as invoice_average,
    invoice_total - invoice_average
FROM invoices;

به همین دلیل مشکل رو این شکلی حل کرد:

select 
	invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total) FROM invoices) as invoice_average,
    invoice_total - (SELECT invoice_average)
FROM invoices;

4. when we use subquery in FROM clause, then defining an alias to this subquery is required. (source: session6 from mastery SQl Mosh - part10 -> subqueries in the FROM clause).

5. show list of all indexes for specific table:\

e.g. (table: users) show indexes from users