Logo

Go on. Explore!

lvl 0 • 0 xp

lvl 1

HomeMarketDecksInterests
SQL

SQL

memorize.aimemorize.ai (lvl 286)
Star 0%
Star 0%
Star 0%
Star 0%
Star 0%
(0)
(1)
(0)
23 cards
()
Section 1

Preview this deck

Find those customers who ordered any "Tofu" product (i.e., product name contains word "Tofu"). You need to list both customerid and customer name.

Front

1 / 23

SQL
Star 0%
Star 0%
Star 0%
Star 0%
Star 0%

0.0

0 reviews

5
0
4
0
3
0
2
0
1
0

Active users

0

All-time users

1

Favorites

0

Last updated

6 years ago

Date created

Mar 14, 2020

Cards (23)

Section 1

(23 cards)

Find those customers who ordered any "Tofu" product (i.e., product name contains word "Tofu"). You need to list both customerid and customer name.

Front

SELECT customerid, customername FROM customers where customerid in (SELECT customerid FROM [Orders] where orderid in (SELECT orderid FROM [OrderDetails] where productid in (SELECT productid FROM products where productname like '%Tofu%')))

Back

List customer names (and number of orders placed) that placed more than 5 orders. Sort the result by number of orders descending.

Front

SELECT customername, count(distinct orderid) as numofOrders FROM customers a inner join orders b on b.customerid=a.customerid GROUP BY customername HAVING numofOrders>5 ORDER BY numofOrders desc

Back

What is the number of unique customers that have placed at least one order?

Front

SELECT distinct customerid FROM [Orders]

Back

List the total sales amount for each employee, including those who have zero sale. Sort your results by total sales amount descending.

Front

SELECT firstname, lastname, sum(quantity*price) as sales FROM employees a left join orders b on b.employeeid=a.employeeid left join orderdetails c on c.orderid=b.orderid left join products d on d.productid=c.productid GROUP BY firstname, lastname ORDER BY sales desc

Back

Find all the employees that have a bachelor's degree and knows French, then sort the results by their last names descending.

Front

SELECT * FROM [Employees] where notes like '%French%' and (notes like '%BS%' OR notes like '% BA%') ORDER BY lastname desc

Back

List top 3 suppliers by number of different products they supplied.

Front

SELECT supplierid, suppliername FROM [Suppliers] where supplierid in (SELECT supplierid FROM [Products] group by supplierid order by count(distinct productid) desc limit 3)

Back

What is the number of unique customers that have placed at least one order? Inner Join

Front

SELECT distinct customername FROM [Customers] a inner join orders b on a.customerid=b.customerid

Back

Find all the customers from USA

Front

SELECT * FROM [Customers] where country='USA'

Back

Find all foreign supplier(s) that the second letter of their contact names is a or h.

Front

SELECT * From [Suppliers] where country!='USA' and (contactname like '_a%' or contactname like '_h%')

Back

What is the number of unique orders that contain product Aniseed Syrup?

Front

SELECT distinct orderid FROM [OrderDetails] where productid=(SELECT productid FROM [Products] where productname='Aniseed Syrup')

Back

Find the top 10 customers by their total purchase amount. Sort your results by total purchase amount descending. A customer's total purchase amount is calculated by the total invoice amount of orders he or she placed.

Front

SELECT customername, sum(quantity*price) as purchaseAmount from Customers a inner join orders b on b.customerid=a.customerid inner join orderdetails c on c.orderid=b.orderid inner join products d on d.productid=c.productid GROUP BY customername ORDER BY purchaseAmount desc limit 10

Back

What are unique first names of employees who have processed at least 10 orders? Please sort the first names alphabetically.

Front

SELECT distinct firstname FROM [Employees] where employeeid in (SELECT employeeid FROM [Orders] group by employeeid having count(orderid)>=10) ORDER BY firstname

Back

Find customer name, contact name, and city information for all the customers not from the UK.

Front

SELECT CustomerName, ContactName, City FROM [Customers] where country!='UK'

Back

What is the number of unique customers whose orders are processed by employee Nancy Davolio and shipped by United Package?

Front

SELECT distinct customerid FROM [Orders] where employeeid=(SELECT employeeid from [Employees] where firstname='Nancy') and shipperid=(SELECT shipperid from [Shippers] where shippername='United Package')

Back

List the top 5 customer names (and number of orders placed) that placed the most orders. Sort the result by number of orders descending.

Front

SELECT customername, count(distinct orderid) as numofOrders FROM customers a inner join orders b on b.customerid=a.customerid GROUP BY customername ORDER BY numofOrders desc limit 5

Back

List the first name and last name of employees who have served at least 10 customers (processed orders of at least 10 customers). Please sort the last names alphabetically. Inner Join

Front

SELECT firstname, lastname, count(orderid) as numofOrders FROM [Employees] a inner join orders b on a.employeeid=b.employeeid GROUP BY firstname having count(orderid)>=10 ORDER BY lastname

Back

Find the name and price information for those products supplied by USA and with a price higher than 20.

Front

SELECT * FROM [Products] where supplierid in (SELECT supplierid FROM suppliers where country='USA') and price>=20

Back

Find those customers who ordered any "Tofu" product (i.e., product name contains word "Tofu"). You need to list both customerid and customer name. Inner Join

Front

SELECT distinct a.customerid, customername FROM customers a inner join orders b on b.customerid=a.customerid inner join orderdetails c on c.orderid=b.orderid inner join products d on d.productid=c.productid where productname like '%Tofu%'

Back

Find all products in Beverages category with a unit price between 30 and 50, inclusive.

Front

SELECT * FROM [Products] where categoryid=(SELECT categoryid from [Categories] where categoryname='Beverages') and price between 30 and 50.

Back

Your boss gives you an orderid (10248) and asks you to check how many unique products and what is the total quantity of items associated with the orderid.

Front

SELECT orderid, count(distinct productid) as numofProducts, sum(quantity) as totalQuantity FROM [OrderDetails] where orderid=10248.

Back

List the number of products for each category name. Sort your results by the number of products.

Front

SELECT categoryname, count(*) as numofProducts FROM categories a left join products b on b.categoryid=a.categoryid GROUP BY categoryname ORDER BY count(*) desc

Back

What is the number of unique customers whose orders are processed by employee Nancy Davolio and shipped by United Package? Inner Join

Front

SELECT distinct customerid FROM [Orders] where employeeid=2 and shipperid=1 SELECT distinct customerid FROM [Employees] a inner join orders b on a.employeeid=b.employeeid inner join shippers c on c.shipperid=b.shipperid where firstname='Nancy' and lastname='Davolio' and shippername='United Package'

Back

List the first name and last name of employees who have served at least 10 customers (processed orders of at least 10 customers). Please sort the last names alphabetically.

Front

SELECT firstname, lastname FROM [Employees] where employeeid in (SELECT employeeid FROM [Orders] group by employeeid having count(distinct customerid)>=10) ORDER BY lastname

Back