ZuoTi.Pro
Question

Please provide SQL command to provide the product names that customer number C0954327 ordered in January...

Please provide SQL command to provide the product names that customer number C0954327 ordered in January 2013 here is the database:

CREATE DATABASE OrderEntryBMIS325
GO
USE OrderEntryBMIS325
GO

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

INSERT INTO Customer (CustNo, CustFirstName, CustLastName, CustStreet, CustCity, CustState, CustZip, CustBal) VALUES
( 'C0954327','Sheri','Gordon','336 Hill St.','Littleton','CO','80129-5543',$230.00 ),
( 'C1010398','Jim','Glussman','1432 E. Ravenna','Denver','CO','80111-0033',$200.00 ),
( 'C2388597','Beth','Taylor','2396 Rafter Rd','Seattle','WA','98103-1121',$500.00 ),
( 'C3340959','Betty','Wise','4334 153rd NW','Seattle','WA','98178-3311',$200.00 ),
( 'C3499503','Bob','Mann','1190 Lorraine Cir.','Monroe','WA','98013-1095',$0.00 ),
( 'C8543321','Ron','Thompson','789 122nd St.','Renton','WA','98666-1289',$85.00 ),
( 'C8574932','Wally','Jones','411 Webber Ave.','Seattle','WA','98105-1093',$1500.00 ),
( 'C8654390','Candy','Kendall','456 Pine St.','Seattle','WA','98105-3345',$50.00 ),
( 'C9128574','Jerry','Wyatt','16212 123rd Ct.','Denver','CO','80222-0022',$100.00 ),
( 'C9403348','Mike','Boren','642 Crest Ave.','Englewood','CO','80113-5431',$0.00 ),
( 'C9432910','Larry','Styles','9825 S. Crest Lane','Bellevue','WA','98104-2211',$250.00 ),
( 'C9543029','Sharon','Johnson','1223 Meyer Way','Fife','WA','98222-1123',$856.00 ),
( 'C9549302','Todd','Hayes','1400 NW 88th','Lynnwood','WA','98036-2244',$0.00 ),
( 'C9857432','Homer','Wells','123 Main St.','Seattle','WA','98105-4322',$500.00 ),
( 'C9865874','Mary','Hill','206 McCaffrey','Littleton','CO','80129-5543',$150.00 ),
( 'C9943201','Harry','Sanders','1280 S. Hill Rd.','Fife','WA','98222-2258',$1000.00 );

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

INSERT INTO Employee (EmpNo, EmpFirstName, EmpLastName, EmpPhone, EmpEMail, SupEmpNo, EmpCommRate) VALUES
( 'E1329594','Landi','Santos','(303) 789-1234','[email protected]','E8843211',0.02 ),
( 'E8544399','Joe','Jenkins','(303) 221-9875','[email protected]','E8843211',0.02 ),
( 'E8843211','Amy','Tang','(303) 556-4321','[email protected]','E9884325',0.04 ),
( 'E9345771','Colin','White','(303) 221-4453','[email protected]','E9884325',0.04 ),
( 'E9884325','Thomas','Johnson','(303) 556-9987','[email protected]',NULL,0.05 ),
( 'E9954302','Mary','Hill','(303) 556-9871','[email protected]','E8843211',0.02 ),
( 'E9973110','Theresa','Beck','(720) 320-2234','[email protected]','E9884325',NULL );

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

INSERT INTO OrderTbl (OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity, OrdState, OrdZip) VALUES
( 'O1116324','1-23-2013','C0954327','E8544399','Sheri Gordon','336 Hill St.','Littleton','CO','80129-5543' ),
( 'O1231231','1-23-2013','C9432910','E9954302','Larry Styles','9825 S. Crest Lane','Bellevue','WA','98104-2211' ),
( 'O1241518','2-10-2013','C9549302',NULL,'Todd Hayes','1400 NW 88th','Lynnwood','WA','98036-2244' ),
( 'O1455122','1-9-2013','C8574932','E9345771','Wally Jones','411 Webber Ave.','Seattle','WA','98105-1093' ),
( 'O1579999','1-5-2013','C9543029','E8544399','Tom Johnson','1632 Ocean Dr.','Des Moines','WA','98222-1123' ),
( 'O1615141','1-23-2013','C8654390','E8544399','Candy Kendall','456 Pine St.','Seattle','WA','98105-3345' ),
( 'O1656777','2-11-2013','C8543321',NULL,'Ron Thompson','789 122nd St.','Renton','WA','98666-1289' ),
( 'O2233457','1-12-2013','C2388597','E9884325','Beth Taylor','2396 Rafter Rd','Seattle','WA','98103-1121' ),
( 'O2334661','1-14-2013','C0954327','E1329594','Mrs. Ruth Gordon','233 S. 166th','Seattle','WA','98011' ),
( 'O3252629','1-23-2013','C9403348','E9954302','Mike Boren','642 Crest Ave.','Englewood','CO','80113-5431' ),
( 'O3331222','1-13-2013','C1010398',NULL,'Jim Glussman','1432 E. Ravenna','Denver','CO','80111-0033' ),
( 'O3377543','1-15-2013','C9128574','E8843211','Jerry Wyatt','16212 123rd Ct.','Denver','CO','80222-0022' ),
( 'O4714645','1-11-2013','C2388597','E1329594','Beth Taylor','2396 Rafter Rd','Seattle','WA','98103-1121' ),
( 'O5511365','1-22-2013','C3340959','E9884325','Betty White','4334 153rd NW','Seattle','WA','98178-3311' ),
( 'O6565656','1-20-2013','C9865874','E8843211','Mr. Jack Sibley','166 E. 344th','Renton','WA','98006-5543' ),
( 'O7847172','1-23-2013','C9943201',NULL,'Harry Sanders','1280 S. Hill Rd.','Fife','WA','98222-2258' ),
( 'O7959898','2-19-2013','C8543321','E8544399','Ron Thompson','789 122nd St.','Renton','WA','98666-1289' ),
( 'O7989497','1-16-2013','C3499503','E9345771','Bob Mann','1190 Lorraine Cir.','Monroe','WA','98013-1095' ),
( 'O8979495','1-23-2013','C9865874',NULL,'HelenSibley','206 McCaffrey','Renton','WA','98006-5543' ),
( 'O9919699','2-11-2013','C9857432','E9954302','Homer Wells','123 Main St.','Seattle','WA','98105-4322' );

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

INSERT INTO Product (ProdNo, ProdName, ProdMfg, SuppNo, ProdQOH, ProdPrice, ProdNextShipDate) VALUES
( 'P0036566','17 inch Color Monitor','ColorMeg Inc.','S2029929',12,$169.00,'2-20-2013' ),
( 'P0036577','19 inch Color Monitor','ColorMeg Inc.','S2029929',10,$319.00,'2-20-2013' ),
( 'P1114590','R3000 Color Laser Printer','Connex','S3399214',5,$699.00,'1-22-2013' ),
( 'P1412138','10 Foot Printer Cable','Ethlite','S4290202',100,$12.00, NULL ),
( 'P1445671','8-Outlet Surge Protector','Intersafe','S4298800',33,$14.99, NULL ),
( 'P1556678','CVP Ink Jet Color Printer','Connex','S3399214',8,$99.00,'1-22-2013' ),
( 'P3455443','Color Ink Jet Cartridge','Connex','S3399214',24,$38.00,'1-22-2013' ),
( 'P4200344','36-Bit Color Scanner','UV Components','S4420948',16,$199.99,'1-29-2013' ),
( 'P6677900','Black Ink Jet Cartridge','Connex','S3399214',44,$25.69, NULL ),
( 'P9995676','Battery Back-up System','Cybercx','S5095332',12,$89.00,'2-1-2013' );

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 )

INSERT INTO OrdLine (OrdNo, ProdNo, Qty) VALUES
( 'O1116324','P1445671',1 ),
( 'O1231231','P0036566',1 ),
( 'O1231231','P1445671',1 ),
( 'O1241518','P0036577',1 ),
( 'O1455122','P4200344',1 ),
( 'O1579999','P1556678',1 ),
( 'O1579999','P6677900',1 ),
( 'O1579999','P9995676',1 ),
( 'O1615141','P0036566',1 ),
( 'O1615141','P1445671',1 ),
( 'O1615141','P4200344',1 ),
( 'O1656777','P1445671',1 ),
( 'O1656777','P1556678',1 ),
( 'O2233457','P0036577',1 ),
( 'O2233457','P1445671',1 ),
( 'O2334661','P0036566',1 ),
( 'O2334661','P1412138',1 ),
( 'O2334661','P1556678',1 ),
( 'O3252629','P4200344',1 ),
( 'O3252629','P9995676',1 ),
( 'O3331222','P1412138',1 ),
( 'O3331222','P1556678',1 ),
( 'O3331222','P3455443',1 ),
( 'O3377543','P1445671',1 ),
( 'O3377543','P9995676',1 ),
( 'O4714645','P0036566',1 ),
( 'O4714645','P9995676',1 ),
( 'O5511365','P1412138',1 ),
( 'O5511365','P1445671',1 ),
( 'O5511365','P1556678',1 ),
( 'O5511365','P3455443',1 ),
( 'O5511365','P6677900',1 ),
( 'O6565656','P0036566',10 ),
( 'O7847172','P1556678',1 ),
( 'O7847172','P6677900',1 ),
( 'O7959898','P1412138',5 ),
( 'O7959898','P1556678',5 ),
( 'O7959898','P3455443',5 ),
( 'O7959898','P6677900',5 ),
( 'O7989497','P1114590',2 ),
( 'O7989497','P1412138',2 ),
( 'O7989497','P1445671',3 ),
( 'O8979495','P1114590',1 ),
( 'O8979495','P1412138',1 ),
( 'O8979495','P1445671',1 ),
( 'O9919699','P0036577',1 ),
( 'O9919699','P1114590',1 ),
( 'O9919699','P4200344',1 );

CREATE TABLE Supplier
(       SuppNo          CHAR(8),
        SuppName        VARCHAR(30) CONSTRAINT SuppNameRequired NOT NULL,
        SuppEMail       VARCHAR(50),
        SuppPhone       CHAR(14),
        SuppURL         VARCHAR(100), 
        SuppDiscount    DECIMAL(3,3),
 CONSTRAINT PKSupplier PRIMARY KEY (SuppNo) );

 INSERT INTO supplier
        (SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
        VALUES('S2029929','ColorMeg, Inc.','[email protected]','(720)444-1231','www.colormeg.com',0.10);

 INSERT INTO supplier
        (SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
        VALUES('S3399214','Connex','[email protected]','(206)432-1142','www.connex.com',0.12);

 INSERT INTO supplier
        (SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
        VALUES('S4290202','Ethlite','[email protected]','(303)213-2234','www.ethlite.com',0.05);

 INSERT INTO supplier
        (SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
        VALUES('S4298800','Intersafe','[email protected]','(512)443-2215','www.intersafe.com',0.10);

 INSERT INTO supplier
        (SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
        VALUES('S4420948','UV Components','[email protected]','(303)321-0432','www.uvcomponents.com',0.08);

 INSERT INTO supplier
        (SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
        VALUES('S5095332','Cybercx','[email protected]','(212)324-5683','www.cybercx.com',0.00);

CREATE TABLE Purchase
(       PurchNo         CHAR(8),
        PurchDate       DATE CONSTRAINT PurchDateRequired NOT NULL,
        SuppNo          CHAR(8) CONSTRAINT SuppNo2Required NOT NULL,
        PurchPayMethod  CHAR(6) DEFAULT 'PO', 
        PurchDelDate    DATE,
 CONSTRAINT PKPurchase PRIMARY KEY (PurchNo),
 CONSTRAINT SuppNoFK2 FOREIGN KEY (SuppNo) REFERENCES Supplier );
 
INSERT INTO purchase
        (PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)
        VALUES('P2224040','3-Feb-2013','S2029929','Credit','8-Feb-2013');
        
INSERT INTO purchase
        (PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)
        VALUES('P2345877','3-Feb-2013','S5095332','PO','11-Feb-2013');

INSERT INTO purchase
        (PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)
        VALUES('P3249952','4-Feb-2013','S3399214','PO','9-Feb-2013');

INSERT INTO purchase
        (PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)
        VALUES('P3854432','3-Feb-2013','S4290202','PO','8-Feb-2013');

INSERT INTO purchase
        (PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)
        VALUES('P9855443','7-Feb-2013','S4420948','PO','15-Feb-2013');

CREATE TABLE PurchLine
(       PurchNo                 CHAR(8),
        ProdNo                  CHAR(8),
        PurchQty                INTEGER DEFAULT 1 CONSTRAINT PurchQtyRequired NOT NULL,
        PurchUnitCost           DECIMAL(12,2),
CONSTRAINT PKPurchLine PRIMARY KEY (PurchNo, ProdNo), 
CONSTRAINT FKPurchNo FOREIGN KEY (PurchNo) REFERENCES Purchase 
    ON DELETE CASCADE, 
CONSTRAINT FKProdNo2 FOREIGN KEY (ProdNo) REFERENCES Product );

INSERT INTO purchline
        (PurchNo, ProdNo, PurchQty, PurchUnitCost)
        VALUES('P2224040','P0036566',10,100.00);
        
INSERT INTO purchline
        (PurchNo, ProdNo, PurchQty, PurchUnitCost)
        VALUES('P2224040','P0036577',10,200.00);

INSERT INTO purchline
        (PurchNo, ProdNo, PurchQty, PurchUnitCost)
        VALUES('P2345877','P9995676',10,45.00);

INSERT INTO purchline
        (PurchNo, ProdNo, PurchQty, PurchUnitCost)
        VALUES('P3249952','P1114590',15,450.00);

INSERT INTO purchline
        (PurchNo, ProdNo, PurchQty, PurchUnitCost)
        VALUES('P9855443','P4200344',15,99.00);
engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

Please find the corresponding SQL query below.

QUERY

SELECT p.ProdName FROM OrderTbl o JOIN OrdLine ol ON ol.OrdNo = o.OrdNo JOIN Product p ON ol.ProdNo = pProdNo WHERE o.CustNo = 'C0954327';

0 0
Add a comment
Know the answer?
Add Answer to:
Please provide SQL command to provide the product names that customer number C0954327 ordered in January...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • I need the SQL code to accomplish the following: Provide a list of orders that were...

    I need the SQL code to accomplish the following: Provide a list of orders that were created in January 2013.   Be sure to show the order number, date, employee name who took the order, and customer name who created the order. For all of the orders for 17” monitors in 2013, provide a list that shows the order number, date, and the quantity. here is the database CREATE DATABASE OrderEntryBMIS325 GO USE OrderEntryBMIS325 GO CREATE TABLE Customer ( CustNo CHAR(8),...

  • MICROSOFT SQL SERVER - Create the following views 1.List the employee assigned to the most projects...

    MICROSOFT SQL SERVER - Create the following views 1.List the employee assigned to the most projects 2.List the project with the most hours SCRIPT TO BUILD DATABASE create table department ( dept_ID int not null , dept_name char(50) NOT NULL, manager_ID int not null, manager_start_date date not null, constraint Dept_PK primary key (dept_ID), constraint D_Name_AK unique (dept_name) ); insert into department values(1,'abc',1,'2019-01-08'); insert into department values(2,'abc2',2,'2019-01-08'); insert into department values(3,'abc3',2,'2019-01-08'); insert into department values(4,'abc4',2,'2019-01-08'); /*project table done*/ create table project...

  • SQL Command - Create a view called S LIST that lists the Cardholder Number, last name,...

    SQL Command - Create a view called S LIST that lists the Cardholder Number, last name, first name and due date for all cardholders who have not returned a book. Use the CREATE OR REPLACE VIEW AS ... command. TABLE CARD HOLDERS Cardholder Numberint NOT NULL CONSTRAINT CH PK PRIMARY KEY, First_Name varchar(10) NULL, LastName varchar(15) NULL, Address varchar(20) NULL, varchar(15) NULL, State char(2) NULL, Zip_Code char(5) NULL) City TABLE BOOKS CHECKED OUT Cardholder_Numberint NOT NULL, Book Numberint NOT NULL,...

  • Database HW help!!!!!!! Provide the SQL statements for the following queries. (1) Provide the names and...

    Database HW help!!!!!!! Provide the SQL statements for the following queries. (1) Provide the names and phones of all swimmers currently in level (of id) 3. +-------+---------+---------------------------+ | FName | LName   | EMail                     | +-------+---------+---------------------------+ | Bobby | Khan    | theBKhan1 | | Clara | Johnson | ClaraJohnson_11 | +-------+---------+---------------------------+ 2 rows in set (0.00 sec) (2) Provide the names of swimmers who have signed up to participate in the event '100M Butterfly' of Meet id 1. +-------+---------+ | FName...

  • Query 1: Retrieve names of all the projects as well as First and Last name of...

    Query 1: Retrieve names of all the projects as well as First and Last name of managers if they are working on any of these projects. Database: //STEP #1: CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), Super_ssn char(9), dno char(4), primary key (ssn)); CREATE TABLE department ( dname varchar(25) not null, dnumber char(4), Mgr_ssn char(9) not null, Mgr_start_date date, primary key (dnumber)); CREATE...

  • Query #2:       List the name of the project and total number of hours worked on by...

    Query #2:       List the name of the project and total number of hours worked on by all the employees on this project, also report the number of employees working on each project. Database: //STEP #1: CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), Super_ssn char(9), dno char(4), primary key (ssn)); CREATE TABLE department ( dname varchar(25) not null, dnumber char(4), Mgr_ssn char(9) not null,...

  • I need help with the following SQL query for a company database (script given below). The...

    I need help with the following SQL query for a company database (script given below). The name of the Department. The number of employees working in that department. The number of different projects controlled by this department. The name of the project controlled by this department that has the maximum number of employees of the company working on it. The number of the above project. The cumulative sum of the number of employees of the company working on the projects...

  • Write an SQL query to return the users who posted the most number of jokes on...

    Write an SQL query to return the users who posted the most number of jokes on 1/1/2019. Database: create table User( userid varchar(30) not null, password varchar(30), firstname varchar(30), lastname varchar(50), email varchar(50), gender char(1), age integer, banned boolean, primary key(userid), unique(email)); create table MyFriends( userid varchar(30), friendid varchar(30), primary key(userid,friendid), foreign key(userid) references User, foreign key(friendid) references User(userid)); Create table Jokes( Jokeid integer, title varchar(100), description varchar(255), authorid varchar(30) not null, primary key(jokeid), posttime Datetime, foreign key(authorid) references User(userid));...

  • NEED HELP IN INSERT STATEMENTS FOR THE TABLES CAN YOU PLEASE ADD SOME INSERT STATEMENTS FOR...

    NEED HELP IN INSERT STATEMENTS FOR THE TABLES CAN YOU PLEASE ADD SOME INSERT STATEMENTS FOR A COMPANY SCHEMA SCRIPT. PLEASE ADN THANK YOU DROP TABLE dependent; DROP TABLE works_on; DROP TABLE project; DROP TABLE dept_locations; DROP TABLE department; DROP TABLE employee; CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary numeric(10,2), superssn char(9), dno numeric, primary key (ssn), foreign key (superssn) references employee(ssn) ); CREATE...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT