Question

create database shop;
use shop;

create table users(
   id int null auto_increment,
   firstname varchar(255),
   lastname varchar(255) not null,
   primary key (id)
);

create table products(
id int null auto_increment,
name varchar(255),
price float,
primary key (id)
);

create table orders(
   id_users int not null,
   id_items int not null,
   primary key (id_users,id_items),
   foreign key (id_users) references users(id),
   foreign key (id_items) references items(id)
);

write three SQL queries to answer the following questions

1.- Products in the shop catalog; product name.

2.- List of products in stock (not sold); product name, code, price.

3.- Products sold in the shop; product name, code, price and full name of buyer.


Concepts in practice: Relational Databases, SQL.

Answer 1

SQL Query 1 :

select name as 'Product Name' from products;

Description :This SQL query will get all the products from the shop that is from products table.

===========================

SQL Query 2 :

select name as 'Product Name',Products.id as 'Code',Price
from products
where id not in (select id_items from orders);

Description :

• This SQL query will list products that are not sold.
• Inner query will return all the products that are sold and
• Outer query will only list the product details that are not in the inner query
• This means these products are not sold they are in stock.

===========================

SQL Query 3 :

select name as 'Product Name',Products.id as 'Code',Price,
concat(firstname ,lastname) as 'Full Name of buyer'
from products,orders,users
where
Products.id=orders.id_items and
orders.id_users=users.id;

Description :

• This SQL query is using equi join to join the tables.
• This SQL query is joining three tables products,orders,users and will get the details.

===========================