- 2, query the order number, order the customer name, and dealing with the employee's name,
- 3, check the product ID number, the name of the supply of the product suppliers, as well as the wholesale price of the product,
- 4, query the product name, the name of the supply of the product suppliers, as well as the wholesale price of the product
- 5, query the product id for 8 product sales
- 6, query the product ID number, and total sales of each product,
- 7, query the names of the products, and total sales of each product,
-- 8, query the names of the total sales of more than 20000 products and sales,
- 9, query the type name for each product categories, and each type of product sales,
-- 10, query the order number and each order number order how many kinds of products -
- 11, query the ordered products species more than five of the order number and order product species,
- 12, query the order products for more than five order number and the total order,
- 13, the query out each employee ID number and sales per employee, 14 query each employee ID number, the name of the employee, as well as the total sales of each employee's
- 15, query employees and customers live in the same city city name, the name of the customer and the employee's name,
CodePudding user response:
O bosses reply agghh ahhhCodePudding user response:
Too much content, help you top it, in fact, looking for the introduction of the basic SQL book to have a look, can be done
CodePudding user response:
- 1 of the product ID number, the name of the product, the retail price of products, product type name,The select p.p roductnumber, p.p roductname, p.r etailprice, p.q uantityonhand, Arthur c. ategorydescription from products p, categories where c p.c ategoryid=Arthur c. ategoryid;
- 2, query the order number, order the customer name, and dealing with the employee's name,
The select o.o rdernumber, Arthur c. ustfirstname, Arthur c. ustlastname, e.e mpfirstname, e.e mplastname from the orders, o customers c, where employees e o.e mployeeid=e.e mployeeid and o. chua ustomerid=Arthur c. ustomerid;
- 3, check the product ID number, the name of the supply of the product suppliers, as well as the wholesale price of the product,
The select p.p roductnumber, v.v endname, pv wholesaleprice from products p, product_vendors pv, where vendors v p.p roductnumber=pv. Productnumber and pv. Vendorid=v.v endorid;
- 4, query the product name, the name of the supply of the product suppliers, as well as the wholesale price of the product
The select p.p roductname, v.v endname, pv wholesaleprice from products p, product_vendors pv, where vendors v p.p roductnumber=pv. Productnumber and pv. Vendorid=v.v endorid;
- 5, query the product id for 8 product sales
Select sum (od. Quotedprice * od. Quantityordered) from order_details od the where od. Productnumber=8;
- 6, query the product ID number, and total sales of each product,
Select od ordernumber, sum (od. Quotedprice * od. Quantityordered) from order_details od group by od. The ordernumber.
- 7, query the names of the products, and total sales of each product,
The select p.p roductname, sum (od. Quotedprice * od. Quantityordered) from products p, order_details od the where p.p roductnumber=od. Productnumber group by p.p roductname;
-- 8, query the names of the total sales of more than 20000 products and sales,
The select p.p roductname, sum (od. Quotedprice * od. Quantityordered) from products p, order_details od the where p.p roductnumber=od. Productnumber group by p.p roductname having sum (od. Quotedprice * od. Quantityordered) & gt; 20000;
- 9, query the type name for each product categories, and each type of product sales,
The select Arthur c. ategorydescription, sum (od. Quotedprice * od. Quantityordered) from products p, categories, c order_details od the where p.c ategoryid=Arthur c. ategoryid and p.p roductnumber=od. The ordernumber group by Arthur c. ategorydescription;
-- 10, query the order number, and each order number how many kinds of products ordered
Select od ordernumber, count (p.c ategoryid) from order_details od, products p where od. Productnumber=p.p roductnumber group by od. The ordernumber.
- 11, query the ordered products species more than five of the order number and order product species,
Select od ordernumber, count (p.c ategoryid) from order_details od, products p where od. Productnumber=p.p roductnumber group by od. The ordernumber having count (p.c ategoryid) & gt; 5;
- 12, query the order products for more than five order number and the total order,
Select od ordernumber, count (p.c ategoryid), sum (od. Quotedprice * od. Quantityordered) from order_details od, products p where od. Productnumber=p.p roductnumber group by od. The ordernumber having count (p.c ategoryid) & gt; 5;
- 13, the query out each employee ID number and sales per employee
The select o.e mployeeid, sum (od. Quotedprice * od. Quantityordered) from the orders, o order_details od the where o.o rdernumber=od. The ordernumber group by o.e mployeeid;
- 14 query each employee ID number, the name of the employee, as well as the total sales of each employee's
Select o.e mployeeid, e.e mpfirstname | | "| | e.e mplastname, sum (od. Quotedprice * od. Quantityordered) from the orders, o order_details od, where employees e o.o rdernumber=od. The ordernumber and o.e mployeeid=e.e mployeeid group by o.e mployeeid, e.e mpfirstname, e.e mplastname;
- 15, query employees and customers live in the same city city name, the name of the customer and the employee's name,
Select Arthur c. ustcity, Arthur c. ustfirstname | | "| | Arthur c. ustlastname, e.e mpfirstname | |" | | e.e mplastname from customers, c employees e where Arthur c. ustcity=e.e mpcity;
For reference only