mysql> create table beers (
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
mysql> create table breweries(
-> id int autoincrement primary key,
-> name varchar(20),
-> city varchar(20),
-> state char(2),
-> founded int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'primary key,
name varchar(20),
city varchar(20),
state char(2),
founded int)' at line 2
mysql> create table breweries( id int auto increment primary key, name varchar(20), city varchar(20), state char(2), founded int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'increment primary key, name varchar(20), city varchar(20), state char(2), founde' at line 1
mysql> create table breweries( id int auto_increment primary key, name varchar(20), city varchar(20), state char(2), founded int);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into breweries values('Firestone','Paso Robles','CA', 1995);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into breweries(name, city, state, founded) values('Firestone','Paso Robles','CA', 1995);
Query OK, 1 row affected (0.00 sec)
mysql> select * from breweries;
+----+-----------+-------------+-------+---------+
| id | name | city | state | founded |
+----+-----------+-------------+-------+---------+
| 1 | Firestone | Paso Robles | CA | 1995 |
+----+-----------+-------------+-------+---------+
1 row in set (0.00 sec)
mysql> insert into breweries(name, city, state, founded) values('Sierra Nevada','Chico','CA', 1970), ('New Belgium', 'Boulder','CO', 1982);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from breweries;
+----+---------------+-------------+-------+---------+
| id | name | city | state | founded |
+----+---------------+-------------+-------+---------+
| 1 | Firestone | Paso Robles | CA | 1995 |
| 2 | Sierra Nevada | Chico | CA | 1970 |
| 3 | New Belgium | Boulder | CO | 1982 |
+----+---------------+-------------+-------+---------+
3 rows in set (0.00 sec)
mysql> create table beers (
-> id auto_increment primary key,
-> name varchar(20),
-> brewery int,
-> type varchar(20),
-> alcohol float);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'auto_increment primary key,
name varchar(20),
brewery int,
type varchar(20),
alc' at line 2
mysql> create table beers ( id int auto_increment primary key, name varchar(20), brewery int, type varchar(20), alcohol float, foreign key(brewery) references breweries(id));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into beers(name,brewery,type,alcohol) values ('DBA', 1, 'Brown Ale', 5.8);
Query OK, 1 row affected (0.01 sec)
mysql> insert into beers(name,brewery,type,alcohol) values ('805', 1, 'Ale', 5.5), ('Pale Ale', 2, 'Pale Ale', 5.7), ('Fat Tire', 3,'Ale', 6);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from beers;
+----+----------+---------+-----------+---------+
| id | name | brewery | type | alcohol |
+----+----------+---------+-----------+---------+
| 1 | DBA | 1 | Brown Ale | 5.8 |
| 2 | 805 | 1 | Ale | 5.5 |
| 3 | Pale Ale | 2 | Pale Ale | 5.7 |
| 4 | Fat Tire | 3 | Ale | 6 |
+----+----------+---------+-----------+---------+
4 rows in set (0.00 sec)
mysql> select * from breweries;
+----+---------------+-------------+-------+---------+
| id | name | city | state | founded |
+----+---------------+-------------+-------+---------+
| 1 | Firestone | Paso Robles | CA | 1995 |
| 2 | Sierra Nevada | Chico | CA | 1970 |
| 3 | New Belgium | Boulder | CO | 1982 |
+----+---------------+-------------+-------+---------+
3 rows in set (0.00 sec)
mysql> SELECT *
-> FROM breweries
-> WHERE state =
-> 'CA';
+----+---------------+-------------+-------+---------+
| id | name | city | state | founded |
+----+---------------+-------------+-------+---------+
| 1 | Firestone | Paso Robles | CA | 1995 |
| 2 | Sierra Nevada | Chico | CA | 1970 |
+----+---------------+-------------+-------+---------+
2 rows in set (0.02 sec)
mysql> SELECT * FROM breweries WHERE state = 'CO';
+----+-------------+---------+-------+---------+
| id | name | city | state | founded |
+----+-------------+---------+-------+---------+
| 3 | New Belgium | Boulder | CO | 1982 |
+----+-------------+---------+-------+---------+
1 row in set (0.00 sec)
mysql> SELECT * FROM breweries WHERE state = 'AZ';
Empty set (0.01 sec)
mysql> select * from beers;
+----+----------+---------+-----------+---------+
| id | name | brewery | type | alcohol |
+----+----------+---------+-----------+---------+
| 1 | DBA | 1 | Brown Ale | 5.8 |
| 2 | 805 | 1 | Ale | 5.5 |
| 3 | Pale Ale | 2 | Pale Ale | 5.7 |
| 4 | Fat Tire | 3 | Ale | 6 |
+----+----------+---------+-----------+---------+
4 rows in set (0.00 sec)
mysql> SELECT *
-> FROM beers
-> WHERE type = 'Ale' AND alcohol >= 6;
+----+----------+---------+------+---------+
| id | name | brewery | type | alcohol |
+----+----------+---------+------+---------+
| 4 | Fat Tire | 3 | Ale | 6 |
+----+----------+---------+------+---------+
1 row in set (0.00 sec)
mysql> SELECT * FROM beers WHERE type = 'Ale' AND alcohol < 6;
+----+------+---------+------+---------+
| id | name | brewery | type | alcohol |
+----+------+---------+------+---------+
| 2 | 805 | 1 | Ale | 5.5 |
+----+------+---------+------+---------+
1 row in set (0.00 sec)
mysql> SELECT * FROM beers WHERE type = 'Ale' AND alcohol < 5;
Empty set (0.00 sec)
mysql> select * from beers;
+----+----------+---------+-----------+---------+
| id | name | brewery | type | alcohol |
+----+----------+---------+-----------+---------+
| 1 | DBA | 1 | Brown Ale | 5.8 |
| 2 | 805 | 1 | Ale | 5.5 |
| 3 | Pale Ale | 2 | Pale Ale | 5.7 |
| 4 | Fat Tire | 3 | Ale | 6 |
+----+----------+---------+-----------+---------+
4 rows in set (0.00 sec)
mysql> select * from breweries;
+----+---------------+-------------+-------+---------+
| id | name | city | state | founded |
+----+---------------+-------------+-------+---------+
| 1 | Firestone | Paso Robles | CA | 1995 |
| 2 | Sierra Nevada | Chico | CA | 1970 |
| 3 | New Belgium | Boulder | CO | 1982 |
+----+---------------+-------------+-------+---------+
3 rows in set (0.00 sec)
mysql> SELECT city, state
-> FROM breweries;
+-------------+-------+
| city | state |
+-------------+-------+
| Paso Robles | CA |
| Chico | CA |
| Boulder | CO |
+-------------+-------+
3 rows in set (0.00 sec)
mysql> SELECT state FROM breweries;
+-------+
| state |
+-------+
| CA |
| CA |
| CO |
+-------+
3 rows in set (0.01 sec)
mysql> SELECT DISTINCT state FROM breweries;
+-------+
| state |
+-------+
| CA |
| CO |
+-------+
2 rows in set (0.00 sec)
mysql> select * from beers;
+----+----------+---------+-----------+---------+
| id | name | brewery | type | alcohol |
+----+----------+---------+-----------+---------+
| 1 | DBA | 1 | Brown Ale | 5.8 |
| 2 | 805 | 1 | Ale | 5.5 |
| 3 | Pale Ale | 2 | Pale Ale | 5.7 |
| 4 | Fat Tire | 3 | Ale | 6 |
+----+----------+---------+-----------+---------+
4 rows in set (0.00 sec)
mysql> select type from beers;
+-----------+
| type |
+-----------+
| Brown Ale |
| Ale |
| Pale Ale |
| Ale |
+-----------+
4 rows in set (0.02 sec)
mysql> select distinct type from beers;
+-----------+
| type |
+-----------+
| Brown Ale |
| Ale |
| Pale Ale |
+-----------+
3 rows in set (0.00 sec)
mysql> select *
-> from beers, breweries;
+----+----------+---------+-----------+---------+----+---------------+-------------+-------+---------+
| id | name | brewery | type | alcohol | id | name | city | state | founded |
+----+----------+---------+-----------+---------+----+---------------+-------------+-------+---------+
| 1 | DBA | 1 | Brown Ale | 5.8 | 1 | Firestone | Paso Robles | CA | 1995 |
| 1 | DBA | 1 | Brown Ale | 5.8 | 2 | Sierra Nevada | Chico | CA | 1970 |
| 1 | DBA | 1 | Brown Ale | 5.8 | 3 | New Belgium | Boulder | CO | 1982 |
| 2 | 805 | 1 | Ale | 5.5 | 1 | Firestone | Paso Robles | CA | 1995 |
| 2 | 805 | 1 | Ale | 5.5 | 2 | Sierra Nevada | Chico | CA | 1970 |
| 2 | 805 | 1 | Ale | 5.5 | 3 | New Belgium | Boulder | CO | 1982 |
| 3 | Pale Ale | 2 | Pale Ale | 5.7 | 1 | Firestone | Paso Robles | CA | 1995 |
| 3 | Pale Ale | 2 | Pale Ale | 5.7 | 2 | Sierra Nevada | Chico | CA | 1970 |
| 3 | Pale Ale | 2 | Pale Ale | 5.7 | 3 | New Belgium | Boulder | CO | 1982 |
| 4 | Fat Tire | 3 | Ale | 6 | 1 | Firestone | Paso Robles | CA | 1995 |
| 4 | Fat Tire | 3 | Ale | 6 | 2 | Sierra Nevada | Chico | CA | 1970 |
| 4 | Fat Tire | 3 | Ale | 6 | 3 | New Belgium | Boulder | CO | 1982 |
+----+----------+---------+-----------+---------+----+---------------+-------------+-------+---------+
12 rows in set (0.01 sec)
mysql> select * from beers, breweries;
+----+----------+---------+-----------+---------+----+---------------+-------------+-------+---------+
| id | name | brewery | type | alcohol | id | name | city | state | founded |
+----+----------+---------+-----------+---------+----+---------------+-------------+-------+---------+
| 1 | DBA | 1 | Brown Ale | 5.8 | 1 | Firestone | Paso Robles | CA | 1995 |
| 1 | DBA | 1 | Brown Ale | 5.8 | 2 | Sierra Nevada | Chico | CA | 1970 |
| 1 | DBA | 1 | Brown Ale | 5.8 | 3 | New Belgium | Boulder | CO | 1982 |
| 2 | 805 | 1 | Ale | 5.5 | 1 | Firestone | Paso Robles | CA | 1995 |
| 2 | 805 | 1 | Ale | 5.5 | 2 | Sierra Nevada | Chico | CA | 1970 |
| 2 | 805 | 1 | Ale | 5.5 | 3 | New Belgium | Boulder | CO | 1982 |
| 3 | Pale Ale | 2 | Pale Ale | 5.7 | 1 | Firestone | Paso Robles | CA | 1995 |
| 3 | Pale Ale | 2 | Pale Ale | 5.7 | 2 | Sierra Nevada | Chico | CA | 1970 |
| 3 | Pale Ale | 2 | Pale Ale | 5.7 | 3 | New Belgium | Boulder | CO | 1982 |
| 4 | Fat Tire | 3 | Ale | 6 | 1 | Firestone | Paso Robles | CA | 1995 |
| 4 | Fat Tire | 3 | Ale | 6 | 2 | Sierra Nevada | Chico | CA | 1970 |
| 4 | Fat Tire | 3 | Ale | 6 | 3 | New Belgium | Boulder | CO | 1982 |
+----+----------+---------+-----------+---------+----+---------------+-------------+-------+---------+
12 rows in set (0.00 sec)
mysql> SELECT * from beers
-> ORDER by Alcohol;
+----+----------+---------+-----------+---------+
| id | name | brewery | type | alcohol |
+----+----------+---------+-----------+---------+
| 2 | 805 | 1 | Ale | 5.5 |
| 3 | Pale Ale | 2 | Pale Ale | 5.7 |
| 1 | DBA | 1 | Brown Ale | 5.8 |
| 4 | Fat Tire | 3 | Ale | 6 |
+----+----------+---------+-----------+---------+
4 rows in set (0.00 sec)
mysql> SELECT * from beers;
+----+----------+---------+-----------+---------+
| id | name | brewery | type | alcohol |
+----+----------+---------+-----------+---------+
| 1 | DBA | 1 | Brown Ale | 5.8 |
| 2 | 805 | 1 | Ale | 5.5 |
| 3 | Pale Ale | 2 | Pale Ale | 5.7 |
| 4 | Fat Tire | 3 | Ale | 6 |
+----+----------+---------+-----------+---------+
4 rows in set (0.00 sec)
mysql> SELECT * from beers ORDER by Alcohol DESC;
+----+----------+---------+-----------+---------+
| id | name | brewery | type | alcohol |
+----+----------+---------+-----------+---------+
| 4 | Fat Tire | 3 | Ale | 6 |
| 1 | DBA | 1 | Brown Ale | 5.8 |
| 3 | Pale Ale | 2 | Pale Ale | 5.7 |
| 2 | 805 | 1 | Ale | 5.5 |
+----+----------+---------+-----------+---------+
4 rows in set (0.01 sec)
mysql> SELECT * from beers ORDER by Type;
+----+----------+---------+-----------+---------+
| id | name | brewery | type | alcohol |
+----+----------+---------+-----------+---------+
| 2 | 805 | 1 | Ale | 5.5 |
| 4 | Fat Tire | 3 | Ale | 6 |
| 1 | DBA | 1 | Brown Ale | 5.8 |
| 3 | Pale Ale | 2 | Pale Ale | 5.7 |
+----+----------+---------+-----------+---------+
4 rows in set (0.00 sec)
mysql> SELECT * from beers ORDER by Type, Alcohol;
+----+----------+---------+-----------+---------+
| id | name | brewery | type | alcohol |
+----+----------+---------+-----------+---------+
| 2 | 805 | 1 | Ale | 5.5 |
| 4 | Fat Tire | 3 | Ale | 6 |
| 1 | DBA | 1 | Brown Ale | 5.8 |
| 3 | Pale Ale | 2 | Pale Ale | 5.7 |
+----+----------+---------+-----------+---------+
4 rows in set (0.00 sec)
mysql> SELECT * from beers ORDER by Type, Alcohol DESC;
+----+----------+---------+-----------+---------+
| id | name | brewery | type | alcohol |
+----+----------+---------+-----------+---------+
| 4 | Fat Tire | 3 | Ale | 6 |
| 2 | 805 | 1 | Ale | 5.5 |
| 1 | DBA | 1 | Brown Ale | 5.8 |
| 3 | Pale Ale | 2 | Pale Ale | 5.7 |
+----+----------+---------+-----------+---------+
4 rows in set (0.00 sec)
mysql> CREATE TABLE foo as (SELECT name, type from beers where alcohol > 5.7);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT name, type from beers where alcohol > 5.7;
+----------+-----------+
| name | type |
+----------+-----------+
| DBA | Brown Ale |
| Fat Tire | Ale |
+----------+-----------+
2 rows in set (0.00 sec)
mysql> select * from foo;
+----------+-----------+
| name | type |
+----------+-----------+
| DBA | Brown Ale |
| Fat Tire | Ale |
+----------+-----------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE foo1 as (SELECT name, type from beers where alcohol > 5.7 order by alcohol);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> ls
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ls' at line 1
mysql> select * from foo1;
+----------+-----------+
| name | type |
+----------+-----------+
| DBA | Brown Ale |
| Fat Tire | Ale |
+----------+-----------+
2 rows in set (0.00 sec)
mysql> show create table foo;
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| foo | CREATE TABLE `foo` (
`name` varchar(20) DEFAULT NULL,
`type` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT beers.name, beers.type, breweries.name
-> FROM beers, breweries
-> WHERE beers.brewery = breweries.Id AND
-> breweries.state = 'CA'
-> ;
+----------+-----------+---------------+
| name | type | name |
+----------+-----------+---------------+
| DBA | Brown Ale | Firestone |
| 805 | Ale | Firestone |
| Pale Ale | Pale Ale | Sierra Nevada |
+----------+-----------+---------------+
3 rows in set (0.00 sec)
mysql>
mysql> SELECT beers.name, beers.type, breweries.name
-> FROM beers, breweries
-> WHERE beers.brewery = breweries.Id AND
-> breweries.state <> 'CA'
-> ;
+----------+------+-------------+
| name | type | name |
+----------+------+-------------+
| Fat Tire | Ale | New Belgium |
+----------+------+-------------+
1 row in set (0.00 sec)
mysql>