To begin with, let's connect to Manticore.

mysql -h0 -P9306

Next, create a table named products. This table will store product information such as name, details, and price: create table products (name string, info text, price integer) morphology = 'stem_en';

Proceed to insert multiple records into the products table. Each record represents a different product with a unique ID, name, description, and price: insert into products (id, name, info, price) values (1, 'Product 1', 'First demo product, small', 10), (2, 'Product 2', 'Second demo product', 15), (3, 'Product 3', 'Third demo product', 22), (4, 'Product 4', 'Fourth small demo product', 8), (10, 'Product 5', 'Fifth demo product', 99);

To review the structure of the products table, use the following command: desc products;

To view all entries in the products table, execute this command: select * from products;

The id field in products serves as a product code. We'll use it for linking with other tables. Now, let’s create another table named basket. This table will simulate a shopping cart where each entry includes a user ID, the product code (ID from the products table), and the quantity of the product: create table basket (user_id integer, product_code integer, count integer);

Insert sample data into the basket table to represent different user orders: insert into basket (user_id, product_code, count) values (1, 3, 1), (1, 2, 2), (1, 5, 4), (2, 1, 3), (2, 4, 2);

To display the contents of the basket for user ID 1, use the following SQL query: select * from basket where user_id = 1;

Not quite clear what it returns, right? For a more readable output, join the basket and products tables. This join links each product code in the basket with the corresponding product in the products table: select *, products.name name, products.price as price from basket inner join products on basket.product_code = products.id where user_id = 1\G

Explore using different types of joins. A left join operation will show all records from the 'basket' table along with matching records from the 'products' table. If there is no match, the result is NULL for the columns of 'products': select id, name, price, basket.user_id from products left join basket on basket.product_code = products.id;

Changing to inner join will display only those entries that have matching product codes in both 'basket' and 'products' tables: select id, name, price, basket.user_id, basket.count from products inner join basket on basket.product_code = products.id;

Improve the sorting of the output to make it easier for users to read by sorting by user ID: select name, info, price, basket.count, basket.user_id from products left join basket on basket.product_code = products.id where basket.count > 1 order by basket.user_id asc;

Sort the results by price for a clearer financial perspective: select id, name, info, price, basket.user_id from products left join basket on basket.product_code = products.id order by price asc;

Filter the entries to display only those products that contain the word small in their description and have a count greater than one, sorted by price: select name, info, price, basket.count from products left join basket on basket.product_code = products.id where basket.count > 1 and match ('small') order by price asc;

Using "match" in compound queries

Create a new table customers to store customer data including their city location: create table customers (user_id integer, name text, city text);

Populate the customers table with sample data: insert into customers (user_id, name, city) values (1, 'John', 'New York'), (2, 'David', 'Los Angeles'), (3, 'Joe', 'Washington'), (4, 'Anna', 'New York');

Execute a query to find customers from a specific city using the match() function on the main table: select * from customers left join basket on basket.user_id = customers.user_id where match('new');

If the structure of the query is reversed, and the match() needs to be applied to a joined table, specify the table within the match() function: select * from basket inner join customers on basket.user_id = customers.user_id where match ('new york', customers);

Finally, showcase the number of purchases made by each customer by using the FACET function, which aggregates data based on a specified field: select name, price, basket.count, basket.user_id from products left join basket on basket.product_code = products.id order by basket.user_id asc facet basket.user_id;

This will output two tables: the first containing the results of the SELECT query with a list of products, and the second displaying the outcome of the FACET calculation, which shows the count of product names for each customer.