-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathAdvancedScripting.sql
More file actions
62 lines (43 loc) · 1.87 KB
/
Copy pathAdvancedScripting.sql
File metadata and controls
62 lines (43 loc) · 1.87 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
show databases;
use pinevalley;
show tables;
#Equijoin - you use primary key and foreign key as comparison
desc order_line_t;
desc order_t;
select customer_t.customer_name, customer_t.customer_address, order_t.order_date
from customer_t, order_t
where (customer_t.customer_id = order_t.customer_id);
select product_description, product_t.product_id, order_line_t.order_id
from order_line_t, product_t
where (product_t.product_id = order_line_t.product_id);
select customer_name, customer_address, product_description, ordered_quantity
from customer_t, order_line_t, order_t, product_t
where (order_line_t.order_id = order_t.order_id) and (order_t.customer_id = customer_t.customer_id)
and (order_line_t.product_id = product_t.product_id);
#non equijoins - no comparisons of primary & foreign keys
#we need to match column values from different tables using comparison operators
select product_description, product_finish, standard_price
from product_t, price_t
where standard_price between low and high
order by standard_price;
#outer joins - to see records from a table that do not satisfy join condition or must satisfy certain condition
#left join and right join
select customer_name, customer_address, order_date
from order_t left join customer_t
on (customer_t.customer_id = order_t.customer_id);
select customer_name, customer_address, order_date
from order_t right join customer_t
on (customer_t.customer_id = order_t.customer_id);
#practices
select * from customer_t
left join order_t
on (customer_t.customer_id = order_t.customer_id);
select customer_name, product_description, standard_price, ordered_quantity
from customer_t, order_t, order_line_t, product_t
where (customer_t.customer_id = order_t.customer_id)
and (order_t.order_id = order_line_t.order_id)
and (product_t.product_id = order_line_t.product_id);
desc customer_t;
desc order_t;
desc order_line_t;
desc product_t;