SQL Queries

use sql_store;

select *

from order_items as o

join products as p

using(product_id);

select p.name,o.order_id,o.quantity,o.unit_price

from order_items as o

join products as p

using(product_id);

select p.name,o.order_id,o.quantity,o.unit_price

from order_items as o

join products as p

using(product_id)

where quantity regexp '1$|2|3';

select *

from order_items as o

join sql_inventory.products as p

using(product_id);

select *

from order_items as o

join sql_inventory.products as p

using(product_id)

where quantity=10;

use sql_hr;

select *

from employees;

select *

from employees as e

join employees as m

on e.reports_to=m.employee_id;

select e.employee_id,e.first_name,m.first_name as manager

from employees as e

join employees as m

on e.reports_to=m.employee_id;

use sql_store;

select *

from customers as c

join orders as o1

using(customer_id)

join order_statuses as o2

on o2.order_status_id=o1.status;

select o1.order_id,o1.order_date,c.first_name,c.last_name,o2.name

from customers as c

join orders as o1

using(customer_id)

join order_statuses as o2

on o2.order_status_id=o1.status;

select o1.order_id,o1.order_date,c.first_name,c.last_name,o2.name

from customers as c

join orders as o1

using(customer_id)

join order_statuses as o2

on o2.order_status_id=o1.status

order by order_date;

use sql_invoicing;

select *

from payments as p1

join payment_methods as p2

on p1.payment_method=p2.payment_method_id;

select *

from payments as p1

join payment_methods as p2

on p1.payment_method=p2.payment_method_id

join clients as c

on c.client_id=p1.client_id;

select p1.date,p1.invoice_id,p1.date,c.name,p2.name

from payments as p1

join payment_methods as p2

on p1.payment_method=p2.payment_method_id

join clients as c

on c.client_id=p1.client_id;

select p1.date,p1.invoice_id,p1.date,c.name,p2.name

from payments as p1

join payment_methods as p2

on p1.payment_method=p2.payment_method_id

join clients as c

on c.client_id=p1.client_id

where p2.name='Cash';