queries1

Display the orders having a total amount of order less than 2000

Ans: select orderid,sum(unitprice*quantity) from orderdetails group by orderid
having sum(unitprice*quantity) >2000

Display the firstname and lastname as a single name in a column(Name it as "Name")along with the orderid in which he belongs
(leave a space b/w the names)

Ans: select e.firstname||' '||e.lastname as Name, o.orderid from employees e join orders o on e.employeeid=o.employeeid

Display the current age of employees along with first and last name

Ans: select trunc(months_between(sysdate,birthdate)/12) "AGE" from employees

More Queries

Find Region name which has most customers.

Ans: select region from (select count(customerid) "count1",region from customers where region is not null
group by region order by "count1" desc) where rownum=1;

Find the best sales month in northwind?

Ans: select "BestMonth" from (select count(orderid) "totalvalue",to_char(orderdate,'month') "BestMonth"
from orders group by to_char(orderdate,'month')order by "totalvalue" desc) where rownum=1;

Create customer report(customer-id - customer name-address-total order value)

Ans: select c.customerid,c.companyname,c.address,sum( orderdetails.unitprice * quantity) as totalvalue from customers c join orders on c.customerid=orders.customerid
join orderdetails on orders.orderid=orderdetails.orderid
group by c.customerid,c.companyname,c.address

Employees list with(total order values) from high to low.

Ans: select e.firstname,sum(quantity* unitprice) "total" from employees e,orders,orderdetails
where orders.employeeid=e.employeeid and orderdetails.orderid =orders.orderid group by firstname order by "total" desc

Northwind Database Exercise

The Northwind database is a sample database used by Microsoft to demonstrate the features of some of its products, including SQL Server and Microsoft Access. The database contains the sales data for Northwind Traders, a fictitious specialty foods export-import company.
Although the code taught in this class is not specific to Microsoft products, we use the Northwind database for many of our examples because many people are already familiar with it and because there are many resources for related learning that make use of the same database.
The diagram below shows the table structure of the Northwind database.
The Northwind database has additional tables, but we will only be using the ones shown above. In this lesson, we will explore some of these tables



Northwind Database Exercise Soluations
Create a report that shows the product name and supplier id for all products supplied by Exotic Liquids, Grandma Kelly's Homestead, and Tokyo Traders.

Ans: select s.supplierid,s.companyname,p.productid,p.productname from suppliers s join products p on(s.supplierid= p.supplierid) and s.companyname IN('Grandma Kelly''s Homestead','Tokyo Traders','Exotic Liquids')

Create a report that shows all products by name that are in the Seafood category.

Ans: select p.productid,p.productname,c.categoryid,c.categoryname from products p join categories c on (p.categoryid=c.categoryid)and c.categoryname like '%Seafood%'

Create a report that shows all companies by name that sell products in CategoryID8.

Ans: select count(companyname),p.categoryid,s.companyname from products p join suppliers s on (p.supplierid = s.supplierid) and categoryid=8 group by s.companyname,p.categoryid

Create a report that shows all companies by name that sell products in the Seafood category.

Ans: select s.companyname from suppliers s where supplierid IN(select p.supplierid from products p join categories c on (c.categoryid=p.categoryid)and c.categoryname= 'Seafood')

Create a report showing the Order ID, the name of the company that placed the order,
and the first and last name of the associated employee.
Only show orders placed after January 1, 1998 that shipped after they were required.
Sort by Company Name.

Ans: select o.orderid,c.customerid,c.companyname from orders o join customers c on (c.customerid=o.customerid)
where o.orderdate > '1-jan-1998' and o.shippeddate >'1-jan-1998' and o.requireddate >'1-jan-1998'

Which products are provided by which suppliers.

Ans: select p.productname,s.companyname from products p join suppliers s on (p.supplierid=s.supplierid)

Create a report that shows the order ids and the associated employee names for orders that shipped after the required date.

Ans: select e.employeeid,e.firstname,o.orderid from employees e join orders o on(o.employeeid=e.employeeid)where requireddate

Create a report that shows the total quantity of products (from the Order_Details table) ordered. Only show records for products for which the quantity ordered is fewer than 200.

Ans: select p.productname,sum(o.quantity) from products p join orderdetails o on(p.productid=o.productid) group by p.productname having sum(o.quantity)

Create a report that shows the total number of orders by Customer since December 31, 1996. The report should only return rows for which the NumOrders is greater than 15. The report should return the following 5 rows.

Ans: select c.companyname,count(o.orderid) from orders o join customers c on(o.customerid= c.customerid) where o.orderdate >= '31-dec-1996'
group by c.companyname having count(o.orderid)>15 order by count(o.orderid)

Create a report that shows the company name, order id, and total price of all products of which Northwind has sold more than $10,000 worth. There is no need for a GROUP BY clause in this report.

Ans: select c.companyname,o.orderid,(((unitprice)-(unitprice*discount))*(quantity))As total from customers c join orders o on(o.customerid=c.customerid) join orderdetails_1 ord on(ord.orderid=o.orderid)
where(((unitprice)-(unitprice*discount))*(quantity)) > 10000

Create a report that shows the number of employees and customers from each city that
has employees in it.

Ans: select count(distinct(e.employeeid))as Numemp,count(distinct(c.customerid)) as Numcus,e.city,c.city from employees e join customers c on(e.city=c.city)
group by e.city,c.city order by Numemp

Create a report that shows the company names and faxes for all customers. If the customer doesn't have a fax, the report should show "No Fax" in that field

Ans: select customerid,companyname,
( CASE
when fax= ' ' Then 'No Fax'
End )fax
from customers