SQL queries Assessment
Query 1
ALTER TABLE transactions ADD COLUMN transaction_type VARCHAR(225);
DESC transactions;
Query 2
CREATE TABLE customers (
customer_id VARCHAR(20) PRIMARY KEY,
customer_name VARCHAR(225),
phone_no INT,
city VARCHAR(200),
pin_code INT);
DESC customers;
Query 3
select * from product where seller like '______n';
Query 4
select count(order_id) as orders_delivered from orders where est_delivery_date >= '2021-12-01' and est_delivery_date < '2021-12-31';
Query 5
select * from product where mod(item_id,2) != 0;
create table giftlist like product;
insert into giftlist select * from product;
select table_name, column_name, data_type from information_schema.columns
where table_name = 'giftlist';
Query 7
select * from product limit 3,6;
Query 8
select distinct price as maximum_price
from product order by price desc
limit 1,1 ;
Query 9
select * from product
order by price desc
limit 6,1;
Query 10
SELECT * FROM product where item_id <= (SELECT COUNT(item_id)/3 FROM product);
select seller, count(product_id) as nop from product group by seller;
Query 12
select distinct(price) as price from product order by price limit 5;
Query 13
select distinct(price) as price from product order by price desc limit 5;
Query 14
select (select count(*) from product
where inStock='Y' and YouSave>4000)*100/(select count(*) from product
where inStock='Y') as percentage;
Query 15
select * from product where item_id>20;
select product_id, seller, count(*) from product
group by product_id , seller
having count(*)>1;
Query 17
SELECT DISTINCT p.product_id,p.p_name,p.inStock,p.seller,q.countSame
FROM product p
INNER JOIN(SELECT product_id,p_name,inStock,COUNT(*) as countSame
FROM product
GROUP BY product_id,p_name,inStock
HAViNG COUNT(*)>1
and inStock="Y") q
ON p.product_id=q.product_id
AND p.p_name=q.p_name
AND p.inStock=q.inStock
ORDER BY p.product_id,p.p_name,p.inStock,p.seller;
Query 18
SELECT AVG(TOTAL)
FROM
( SELECT product_id ,SUM(youSave) AS TOTAL FROM product
GROUP BY product_id) AS TOTALS
WHERE product_id IN(SELECT product_id FROM product
WHERE seller='Maple store'
OR
seller= 'Kukreja Telecom Store');
Query 19
SELECT product_id,p_name,MIN(price),(AVG(youSave)*100)/SUM(youSave) AS avgP
FROM product
GROUP BY product_id,p_name
HAVING COUNT(*)>1;
Query 20
select DISTINCT p.p_name, o.customer_id, c.customer_name from product p
join orders o on p.product_id = o.product_id
join customer c on o.customer_id = c.customer_id;
Query 21
SELECT product_id, COUNT(product_id)
FROM orders
GROUP BY product_id;
Query 22
select transaction_id from transactions;
Query 23
select distinct p.p_name, o.payment_date, o.est_delivery_date from product p
join orders o on p.product_id = o.product_id
order by o.est_delivery_date ;
Query 24
select o.order_id, o.product_id,o.customer_id, t.transaction_id from orders o
join transactions t on o.transaction_id = t.transaction_id
where t.transaction_status = 'not completed';
Query 25
select distinct p.p_name, o.payment_date, o.est_delivery_date, t.transaction_status from orders o
join product p on o.product_id = p.product_id
join transactions t on o.transaction_id = t.transaction_id
order by o.payment_date;
Query 26
select customer_name, order_id from customer c
join orders o
on c.customer_id = o.customer_id
where customer_name like '%d%';
Query 27
SELECT c.customer_id, SUM(o.checkout_price) AS totalpayment
FROM customer c LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;
Query 28
create table cloneListpro like product;
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'cloneListpro'
ORDER BY column_name;
