-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathLab5.txt
169 lines (148 loc) · 5.71 KB
/
Lab5.txt
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
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
mysql> source Lab5.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
--------------
show tables
--------------
+-----------------+
| Tables_in_books |
+-----------------+
| au_orders |
| authors |
| dups |
| employees |
| empsales |
| hier |
| publishers |
| roadtrip |
| royalties |
| telephones |
| temps |
| time_series |
| title_authors |
| titles |
+-----------------+
14 rows in set (0.00 sec)
--------------
select * from authors
--------------
+-------+-----------+-------------+--------------+----------------------+---------------+-------+-------+
| au_id | au_fname | au_lname | phone | address | city | state | zip |
+-------+-----------+-------------+--------------+----------------------+---------------+-------+-------+
| A01 | Sarah | Buchman | 718-496-7223 | 75 West 205 St | Bronx | NY | 10468 |
| A02 | Wendy | Heydemark | 303-986-7020 | 2922 Baseline Rd | Boulder | CO | 80303 |
| A03 | Hallie | Hull | 415-549-4278 | 3800 Waldo Ave, #14F | San Francisco | CA | 94123 |
| A04 | Klee | Hull | 415-549-4278 | 3800 Waldo Ave, #14F | San Francisco | CA | 94123 |
| A05 | Christian | Kells | 212-771-4680 | 114 Horatio St | New York | NY | 10014 |
| A06 | | Kellsey | 650-836-7128 | 390 Serra Mall | Palo Alto | CA | 94305 |
| A07 | Paddy | O'Furniture | 941-925-0752 | 1442 Main St | Sarasota | FL | 34236 |
+-------+-----------+-------------+--------------+----------------------+---------------+-------+-------+
7 rows in set (0.00 sec)
--------------
select au_id, au_fname from authors
--------------
+-------+-----------+
| au_id | au_fname |
+-------+-----------+
| A01 | Sarah |
| A02 | Wendy |
| A03 | Hallie |
| A04 | Klee |
| A05 | Christian |
| A06 | |
| A07 | Paddy |
+-------+-----------+
7 rows in set (0.00 sec)
--------------
select distinct state from authors
--------------
+-------+
| state |
+-------+
| NY |
| CO |
| CA |
| FL |
+-------+
4 rows in set (0.00 sec)
--------------
select title_id, title_name, price from titles order by price
--------------
+----------+-------------------------------------+-------+
| title_id | title_name | price |
+----------+-------------------------------------+-------+
| T10 | Not Without My Faberge Egg | NULL |
| T05 | Exchange of Platitudes | 6.95 |
| T11 | Perhaps It's a Glandular Problem | 7.99 |
| T08 | Just Wait Until After School | 10.00 |
| T12 | Spontaneous, Not Annoying | 12.99 |
| T04 | But I Did It Unconsciously | 12.99 |
| T09 | Kiss My Boo-Boo | 13.95 |
| T06 | How About Never? | 19.95 |
| T02 | 200 Years of German Humor | 19.95 |
| T01 | 1977! | 21.99 |
| T07 | I Blame My Mother | 23.95 |
| T13 | What Are The Civilian Applications? | 29.99 |
| T03 | Ask Your System Administrator | 39.95 |
+----------+-------------------------------------+-------+
13 rows in set (0.00 sec)
--------------
select title_id, title_name from titles where type = 'children'
--------------
+----------+------------------------------+
| title_id | title_name |
+----------+------------------------------+
| T08 | Just Wait Until After School |
| T09 | Kiss My Boo-Boo |
+----------+------------------------------+
2 rows in set (0.00 sec)
--------------
select title_id, title_name from titles where type not in ('history', 'biography')
--------------
+----------+----------------------------------+
| title_id | title_name |
+----------+----------------------------------+
| T03 | Ask Your System Administrator |
| T04 | But I Did It Unconsciously |
| T05 | Exchange of Platitudes |
| T08 | Just Wait Until After School |
| T09 | Kiss My Boo-Boo |
| T11 | Perhaps It's a Glandular Problem |
+----------+----------------------------------+
6 rows in set (0.00 sec)
--------------
select au_id, au_fname, au_lname, phone from authors where phone like '%549%'
--------------
+-------+----------+----------+--------------+
| au_id | au_fname | au_lname | phone |
+-------+----------+----------+--------------+
| A03 | Hallie | Hull | 415-549-4278 |
| A04 | Klee | Hull | 415-549-4278 |
+-------+----------+----------+--------------+
2 rows in set (0.00 sec)
--------------
select au_id, au_fname, au_lname, zip from authors where zip between '90000' and '99999'
--------------
+-------+----------+----------+-------+
| au_id | au_fname | au_lname | zip |
+-------+----------+----------+-------+
| A03 | Hallie | Hull | 94123 |
| A04 | Klee | Hull | 94123 |
| A06 | | Kellsey | 94305 |
+-------+----------+----------+-------+
3 rows in set (0.00 sec)
--------------
select au_id, au_fname, au_lname, state from authors where state = 'NY' or state = 'CA'
--------------
+-------+-----------+----------+-------+
| au_id | au_fname | au_lname | state |
+-------+-----------+----------+-------+
| A01 | Sarah | Buchman | NY |
| A03 | Hallie | Hull | CA |
| A04 | Klee | Hull | CA |
| A05 | Christian | Kells | NY |
| A06 | | Kellsey | CA |
+-------+-----------+----------+-------+
5 rows in set (0.00 sec)
mysql> exit