-
Notifications
You must be signed in to change notification settings - Fork 0
/
q14
148 lines (117 loc) · 4.57 KB
/
q14
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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
omickeyee@omickeyee-HP-Pavilion-Laptop-15-cc1xx:~$ sudo mysql
[sudo] password for omickeyee:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 8.0.29-0ubuntu0.22.04.2 (Ubuntu)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database q14;
Query OK, 1 row affected (0.00 sec)
mysql> use q14;
Database changed
mysql> create table supplier(sid int NOT NULL ,sname varchar (20) ,address varchar(20) NOT NULL , primary key(sid));
Query OK, 0 rows affected (0.02 sec)
mysql> create table parts(pid int NOT NULL ,pname varchar (20) ,color varchar(20
) NOT NULL , primary key(pid));
Query OK, 0 rows affected (0.03 sec)
mysql> create table catalog(sid int NOT NULL ,pid int NOT NULL ,cost int NOT NULL );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into supplier(sid ,sname ,address) values (101,'s1','pune');
Query OK, 1 row affected (0.01 sec)
mysql> insert into supplier(sid ,sname ,address) values (102,'s2','mumbai');
Query OK, 1 row affected (0.00 sec)
mysql> insert into supplier(sid ,sname ,address) values (103,'s3','delhi');
Query OK, 1 row affected (0.01 sec)
mysql> insert into supplier(sid ,sname ,address) values (104,'s4','banglore');
Query OK, 1 row affected (0.01 sec)
mysql> insert into parts(pid ,pname ,color) values (205,'handle','red');
Query OK, 1 row affected (0.00 sec)
mysql> insert into parts(pid ,pname ,color) values (206,'brake','green');
Query OK, 1 row affected (0.00 sec)
mysql> insert into parts(pid ,pname ,color) values (207,'mirror','green');
Query OK, 1 row affected (0.01 sec)
mysql> insert into parts(pid ,pname ,color) values (208,'tyre','red');
Query OK, 1 row affected (0.00 sec)
mysql> insert into parts(pid ,pname ,color) values (209,'light','blue');
Query OK, 1 row affected (0.00 sec)
mysql> insert into catalog(sid ,pid ,cost) values (101,205,25);
Query OK, 1 row affected (0.00 sec)
mysql> insert into catalog(sid ,pid ,cost) values (102,206,30);
Query OK, 1 row affected (0.01 sec)
mysql> insert into catalog(sid ,pid ,cost) values (103,207,15);
Query OK, 1 row affected (0.00 sec)
mysql> insert into catalog(sid ,pid ,cost) values (104,208,35);
Query OK, 1 row affected (0.01 sec)
mysql> insert into catalog(sid ,pid ,cost) values (105,209,40);
Query OK, 1 row affected (0.01 sec)
mysql> select * from supplier;
+-----+-------+----------+
| sid | sname | address |
+-----+-------+----------+
| 101 | s1 | pune |
| 102 | s2 | mumbai |
| 103 | s3 | delhi |
| 104 | s4 | banglore |
+-----+-------+----------+
4 rows in set (0.00 sec)
mysql> select * from parts;
+-----+--------+-------+
| pid | pname | color |
+-----+--------+-------+
| 205 | handle | red |
| 206 | brake | green |
| 207 | mirror | green |
| 208 | tyre | red |
| 209 | light | blue |
+-----+--------+-------+
5 rows in set (0.00 sec)
mysql> select * from catalog;
+-----+-----+------+
| sid | pid | cost |
+-----+-----+------+
| 101 | 205 | 25 |
| 102 | 206 | 30 |
| 103 | 207 | 15 |
| 104 | 208 | 35 |
| 105 | 209 | 40 |
+-----+-----+------+
5 rows in set (0.00 sec)
mysql> select supplier.sid ,supplier.sname from catalog inner join supplier on supplier.sid=catalog.sid inner join parts on catalog.pid=parts.pid where color='red';
+-----+-------+
| sid | sname |
+-----+-------+
| 101 | s1 |
| 104 | s4 |
+-----+-------+
2 rows in set (0.00 sec)
mysql> select parts.pid ,parts.pname from catalog inner join supplier on supplier.sid=catalog.sid inner join parts on catalog.pid=parts.pid wh
ere cost>25;
+-----+-------+
| pid | pname |
+-----+-------+
| 206 | brake |
| 208 | tyre |
+-----+-------+
2 rows in set (0.00 sec)
mysql> select parts.pid ,parts.pname from catalog inner join supplier on supplier.sid=catalog.sid inner join parts on catalog.pid=parts.pid where color='green';
+-----+--------+
| pid | pname |
+-----+--------+
| 206 | brake |
| 207 | mirror |
+-----+--------+
2 rows in set (0.00 sec)
mysql> select supplier.sname ,parts.pname,parts.color,catalog.cost from catalog inner join supplier on supplier.sid=catalog.sid inner join parts on catalog.pid=parts.pid ;
+-------+--------+-------+------+
| sname | pname | color | cost |
+-------+--------+-------+------+
| s1 | handle | red | 25 |
| s2 | brake | green | 30 |
| s3 | mirror | green | 15 |
| s4 | tyre | red | 35 |
+-------+--------+-------+------+
4 rows in set (0.00 sec)
mysql>