-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathmysql practice
172 lines (148 loc) · 6.9 KB
/
mysql practice
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
170
171
student@student-OptiPlex-3070:~$ sudo mysql -u root -p
[sudo] password for student:
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.33-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2021, 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 College;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+-----------------------+
| Database |
+-----------------------+
| information_schema |
| College |
| Student |
| mysql |
| ordermanagementsystem |
| performance_schema |
| qwe |
| sys |
+-----------------------+
8 rows in set (0.00 sec)
mysql> use College;
Database changed
mysql> create table Student_Info( ID int NOT NULL AUTO_INCREMENT, Name varchar(45) NOT NULL, Fav_Sports varchar(20), Age int NOT NULL, PRIMARY KEY (ID));
Query OK, 0 rows affected (0.22 sec)
mysql> show tables;
+-------------------+
| Tables_in_College |
+-------------------+
| Student_Info |
+-------------------+
1 row in set (0.00 sec)
mysql> describe tables;
ERROR 1146 (42S02): Table 'College.tables' doesn't exist
mysql> describe Student_Info;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(45) | NO | | NULL | |
| Fav_Sports | varchar(20) | YES | | NULL | |
| Age | int(11) | NO | | NULL | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> DROP TABLE Student_info;
ERROR 1051 (42S02): Unknown table 'College.Student_info'
mysql> DROP TABLE Student_Info;
Query OK, 0 rows affected (0.13 sec)
mysql> create table Student_Info( ID int NOT NULL AUTO_INCREMENT, Name varchar(45) NOT NULL, Fav_Sports varchar(20) NOT NULL, Age int NOT NULL, PRIMARY KEY (ID));
Query OK, 0 rows affected (0.23 sec)
mysql> show tables;
+-------------------+
| Tables_in_College |
+-------------------+
| Student_Info |
+-------------------+
1 row in set (0.01 sec)
mysql> describe Student_Info;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(45) | NO | | NULL | |
| Fav_Sports | varchar(20) | NO | | NULL | |
| Age | int(11) | NO | | NULL | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table Student_Info
-> add column Branch varchar(20) NOT NULL;
Query OK, 0 rows affected (0.39 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe Student_Info;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(45) | NO | | NULL | |
| Fav_Sports | varchar(20) | NO | | NULL | |
| Age | int(11) | NO | | NULL | |
| Branch | varchar(20) | NO | | NULL | |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> insert into Student_Info(Name,Fav_Sports,Age,Branch)values('Pratham','Vollyball',21),('Pratik','Cricket',20),('Prathamesh','chess',20);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into Student_Info(Name,Fav_Sports,Age,Branch)values('Pratham','Vollyball',21,'Comp'),('Pratik','Cricket',20,'Comp'),('Prathamesh','chess',20,'Comp');
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM Student_Info;
+----+------------+------------+-----+--------+
| ID | Name | Fav_Sports | Age | Branch |
+----+------------+------------+-----+--------+
| 1 | Pratham | Vollyball | 21 | Comp |
| 2 | Pratik | Cricket | 20 | Comp |
| 3 | Prathamesh | chess | 20 | Comp |
+----+------------+------------+-----+--------+
3 rows in set (0.00 sec)
mysql> create table Teachers_Info( ID int NOT NULL AUTO_INCREMENT, Name varchar(45) NOT NULL, Age int NOT NULL,Branch varchar(20) NOT NULL, PRIMARY KEY (ID));
Query OK, 0 rows affected (0.22 sec)
mysql> insert into Teachers_Info(Name,Age,Branch)values('ABC',50,'Comp'),('ACC',49,'Comp'),('BBC',56,'Mech'),('BBB',52,'Civil');
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> describe Teachers_Info;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(45) | NO | | NULL | |
| Age | int(11) | NO | | NULL | |
| Branch | varchar(20) | NO | | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM Teachers_Info;
+----+------+-----+--------+
| ID | Name | Age | Branch |
+----+------+-----+--------+
| 1 | ABC | 50 | Comp |
| 2 | ACC | 49 | Comp |
| 3 | BBC | 56 | Mech |
| 4 | BBB | 52 | Civil |
+----+------+-----+--------+
4 rows in set (0.00 sec)
mysql> select * from Student_Info group by ID;
+----+------------+------------+-----+--------+
| ID | Name | Fav_Sports | Age | Branch |
+----+------------+------------+-----+--------+
| 1 | Pratham | Vollyball | 21 | Comp |
| 2 | Pratik | Cricket | 20 | Comp |
| 3 | Prathamesh | chess | 20 | Comp |
+----+------------+------------+-----+--------+
3 rows in set (0.02 sec)
mysql> select * from Student_Info group by Age;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'College.Student_Info.ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select sum(Age) from Student_info;
ERROR 1146 (42S02): Table 'College.Student_info' doesn't exist
mysql> select sum(Age) from Student_Info;
+----------+
| sum(Age) |
+----------+
| 61 |
+----------+
1 row in set (0.00 sec)
mysql>