I need the SQL code to accomplish the following:
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);
1)
SELECT o.OrdNo, o.OrdDate, e.EmpFirstName, e.EmpLastName, c.CustFirstName, c.CustLastName FROM OrderTbl o, Customer c, Employee e WHERE o.CustNo = c.CustNo AND o.EmpNo = e.EmpNo AND month(OrdDate) = 1 and year(OrdDate) = 2013;
2)
SELECT o.OrdNo, o.OrdDate, ol.Qty FROM OrderTbl o, OrderLine ol, Product p WHERE o.OrdNo = ol.OrdNo AND ol.ProdNo = p.ProdNo AND p.ProdName = '17'' Monitor' AND year(o.OrdDate) = 2013;
I need the SQL code to accomplish the following: Provide a list of orders that were...
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...
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...
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...
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,...
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 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...
/* I am executing following SQL statement for the below code but I am getting error. Pls, upload screenshot after running it. SQL Statement: SELECT OfferNo, CourseNo FROM Offering WHERE OfferTerm = 'Sum' AND OfferYear = 2012 AND FacSSN IS NULL; */ CREATE TABLE STUDENT( StdSSN INT NOT NULL PRIMARY KEY, StdFName VARCHAR2(50), StdLName VARCHAR2(50), StdCity VARCHAR2(50), StdState VARCHAR2(2), StdZip VARCHAR2(10), StdMajor VARCHAR2(15), StdYear VARCHAR2(20) ); CREATE TABLE FACULTY( FacSSN INTEGER NOT NULL PRIMARY KEY, FacFName VARCHAR(50), FacLName VARCHAR(50), FacCity...
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...
SQL Query Question: I have a database with the tables below, data has also been aded into these tables, I have 2 tasks: 1. Add a column to a relational table POSITIONS to store information about the total number of skills needed by each advertised position. A name of the column is up to you. Assume that no more than 9 skills are needed for each position. Next, use a single UPDATE statement to set the values in the new...
write an SQL statement to list all the people in the Owner table, along with their pets' names if any. That is, for each pet an owner has, there will be a record containing the owner and the pet's name. For any owner contained in the Owner table, the output should always contain the owner's record even if no pets are associated with this owner. Hint: outer join/s may be needed. -- DROP TABLE PetAndOwner, Pet, PetType, Owner; CREATE TABLE...