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>