Query Formulation with SQL in Software

Creation Denso QR Bar Code in Software Query Formulation with SQL

Qty 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 10 1 1 5 5 5 5 2 2 3 1 1 1 1 1 1

CREATE TABLE Customer ( CustNo CHAR(8), VARCHAR2(20) CONSTRAINT CustFirstNameRequired NOT NULL, CustFirstName VARCHAR2(30) CONSTRAINT CustLastNameRequired NOT NULL, CustLastName CustStreet VARCHAR2(50), CustCity VARCHAR2(30), CHAR(2), CustState CustZip CHAR(10), CustBal DECIMAL(12,2) DEFAULT 0, CONSTRAINT PKCustomer PRIMARY KEY (CustNo) )

CREATE TABLE OrderTbl ( OrdNo CHAR(8), DATE CONSTRAINT OrdDateRequired NOT NULL, OrdDate CHAR(8) CONSTRAINT CustNoRequired NOT NULL, CustNo CHAR(8), EmpNo OrdName VARCHAR2(50), OrdStreet VARCHAR2(50), VARCHAR2(30), OrdCity CHAR(2), OrdState CHAR(10), OrdZip CONSTRAINT PKOrderTbl PRIMARY KEY (OrdNo), CONSTRAINT FKCustNo FOREIGN KEY (CustNo) REFERENCES Customer, CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee )

CREATE TABLE OrdLine ( OrdNo CHAR(8), ProdNo CHAR(8), Qty INTEGER DEFAULT 1, CONSTRAINT PKOrdLine PRIMARY KEY (OrdNo, ProdNo), CONSTRAINT FKOrdNo FOREIGN KEY (OrdNo) REFERENCES OrderTbl ON DELETE CASCADE, CONSTRAINT FKProdNo FOREIGN KEY (ProdNo) REFERENCES Product)

CREATE TABLE Employee ( EmpNo CHAR(8), VARCHAR2(20) CONSTRAINT EmpFirstNameRequired NOT NULL, EmpFirstName EmpLastName VARCHAR2(30) CONSTRAINT EmpLastNameRequired NOT NULL, EmpPhone CHAR(15), VARCHAR(50) CONSTRAINT EmpEMailRequired NOT NULL, EmpEMail SupEmpNo CHAR(8), DECIMAL(3,3), EmpCommRate CONSTRAINT PKEmployee PRIMARY KEY (EmpNo), CONSTRAINT UNIQUEEMail UNIQUE(EmpEMail), CONSTRAINT FKSupEmpNo FOREIGN KEY (SupEmpNo) REFERENCES Employee )

4

CREATE TABLE Product ( ProdNo CHAR(8), ProdName VARCHAR2(50) CONSTRAINT ProdNameRequired NOT NULL, ProdMfg VARCHAR2(20) CONSTRAINT ProdMfgRequired NOT NULL, ProdQOH INTEGER DEFAULT 0, ProdPrice DECIMAL(12,2) DEFAULT 0, DATE, ProdNextShipDate CONSTRAINT PKProduct PRIMARY KEY (ProdNo) )

1 List the customer number, the name (first and last), and the balance of customers 2 List the customer number, the name (first and last), and the balance of customers who reside in Colorado (CustState is CO) 3 List all columns of the Product table for products costing more than $50 Order the result by product manufacturer (ProdMfg) and product name 4 List the order number, order date, and shipping name (OrdName) of orders sent to addresses in Denver or Englewood 5 List the customer number, the name (first and last), the city, and the balance of customers who reside in Denver with a balance greater than $ 150 or who reside in Seattle with a balance greater than $300 6 List the cities and states where orders have been placed Remove duplicates from the result 7 List all columns of the OrderTbl table for Internet orders placed in January 2007 An Internet order does not have an associated employee 8 List all columns of the OrderTbl table for phone orders placed in February 2007 A phone order has an associated employee 9 List all columns of the Product table that contain the words Ink Jet in the product name 10 List the order number, order date, and customer number of orders placed after January 23, 2007, shipped to Washington recipients 11 List the order number, order date, customer number, and customer name (first and last) of orders placed in January 2007 sent to Colorado recipients 12 List the order number, order date, customer number, and customer name (first and last) of orders placed in January 2007 placed by Colorado customers (CustState) but sent to Washington recip ients (OrdState) 13 List the customer number, name (first and last), and balance of Washington customers who have placed one or more orders in February 2007 Remove duplicate rows from the result 14 List the order number, order date, customer number, customer name (first and last), employee number, and employee name (first and last) of January 2007 orders placed by Colorado customers 15 List the employee number, name (first and last), and phone of employees who have taken orders in January 2007 from customers with balances greater than $300 Remove duplicate rows in the result 16 List the product number, name, and price of products ordered by customer number C0954327 in January 2007 Remove duplicate products in the result 17 List the customer number, name (first and last), order number, order date, employee number, employee name (first and last), product number, product name, and order cost (OrdLineQty * ProdPrice) for products ordered on January 23, 2007, in which the order cost exceeds $150 18 List the average balance of customers by city Include only customers residing in Washington State (WA)