Question

CREATE TABLE GLAccounts
(
   AccountNo INT,
   AccountDescription VARCHAR(50),
   --
   CONSTRAINT GLAccounts_PK PRIMARY KEY(AccountNo),
   --
   CONSTRAINT AccountDescription_NULL CHECK(AccountDescription IS NOT NULL),
   --
   CONSTRAINT AccountDescription_Zero_LEN CHECK(LEN(AccountDescription)>0)
);

GO

-----------------------------------------------------------------------------

CREATE TABLE Terms
(
   TermsID INT,
   TermsDescription VARCHAR(50),
   TermsDueDays SMALLINT,
   --
   CONSTRAINT PK_Terms PRIMARY KEY(TermsID),
   --
   CONSTRAINT TermsDescription_NULL    CHECK(TermsDescription IS NOT NULL),
   CONSTRAINT TermsDueDays_NULL        CHECK(TermsDueDays IS NOT NULL),
   --
   CONSTRAINT TermsDescription_zero_LEN
       CHECK( LEN(TermsDescription)>0)
);
ALTER TABLE Terms NOCHECK CONSTRAINT TermsDueDays_NULL;
GO

-----------------------------------------------------

CREATE TABLE Vendors
(
   VendorID INT,
   VendorName VARCHAR(50),
   VendorAddress1 VARCHAR(50),
   VendorAddress2 VARCHAR(50),
   VendorCity VARCHAR(50),
   VendorState CHAR(2),
   VendorZipCode VARCHAR(20),
   VendorPhone VARCHAR(50),
   VendorContactLName VARCHAR(50),
   VendorContactFName VARCHAR(50),
   DefaultTermsID INT,
   DefaultAccountNo INT,
   --
   CONSTRAINT PK_Vendors PRIMARY KEY(VendorID),
   CONSTRAINT FK_def_accnt_no FOREIGN KEY(DefaultAccountNo)
       REFERENCES GLAccounts(accountNo),
   --
   CONSTRAINT VendorName_NULL            CHECK(VendorName IS NOT NULL),
   CONSTRAINT VendorAddress1_NULL        CHECK(VendorAddress1 IS NOT NULL),
   CONSTRAINT VendorAddress2_NULL        CHECK(VendorAddress2 IS NOT NULL),
   CONSTRAINT VendorCity_NULL            CHECK(VendorCity IS NOT NULL),
   CONSTRAINT VendorState_NULL        CHECK(VendorState IS NOT NULL),
   CONSTRAINT VendorZipCode_NULL        CHECK(VendorZipCode IS NOT NULL),
   CONSTRAINT VendorPhone_NULL        CHECK(VendorPhone IS NOT NULL),
   CONSTRAINT VendorContactLName_NULL    CHECK(VendorContactLName IS NOT NULL),
   CONSTRAINT VendorContactFName_NULL    CHECK(VendorContactFName IS NOT NULL),
   CONSTRAINT DefaultTermsID_NULL        CHECK(DefaultTermsID IS NOT NULL),
   CONSTRAINT DefaultAccountNo_NULL    CHECK(DefaultAccountNo IS NOT NULL),
   --
   CONSTRAINT VendorName_zero_LEN           CHECK( LEN(VendorName)>0),
   CONSTRAINT VendorAddress1_zero_LEN       CHECK( LEN(VendorAddress1)>0),
   CONSTRAINT VendorAddress2_zero_LEN       CHECK( LEN(VendorAddress2)>0),
   CONSTRAINT VendorCity_zero_LEN           CHECK( LEN(VendorCity)>0),
   CONSTRAINT VendorState_valid_LEN       CHECK( LEN(VendorState)=2),
   CONSTRAINT VendorZipCode_valid_LEN       CHECK( LEN(VendorZipCode)>=5),
   CONSTRAINT VendorContactLName_zero_LEN   CHECK( LEN(VendorContactLName)>0),
   CONSTRAINT VendorContactFName_zero_LEN   CHECK( LEN(VendorContactFName)>0)
);

-- Turn the constraints I do not want to use off
ALTER TABLE Vendors NOCHECK CONSTRAINT VendorAddress1_NULL;
ALTER TABLE Vendors NOCHECK CONSTRAINT VendorAddress2_NULL;
ALTER TABLE Vendors NOCHECK CONSTRAINT VendorPhone_NULL;
ALTER TABLE Vendors NOCHECK CONSTRAINT VendorZipCode_NULL;
ALTER TABLE Vendors NOCHECK CONSTRAINT VendorContactLName_NULL;
ALTER TABLE Vendors NOCHECK CONSTRAINT VendorContactFName_NULL;
GO

--------------------------------------------------------------

CREATE TABLE Invoices
(
   InvoiceID       INT,
   VendorID       INT,
   InvoiceNumber   VARCHAR(50),
   InvoiceDate       SMALLDATETIME,
   InvoiceTotal   MONEY,
   PaymentTotal   MONEY,
   CreditTotal       MONEY,
   TermsID           INT,
   InvoiceDueDate   SMALLDATETIME,
   PaymentDate       SMALLDATETIME,
   --
   CONSTRAINT PK_Invoices PRIMARY KEY(InvoiceID),
   --
   -- Constrain all NULLs
   CONSTRAINT VendorID_NULL        CHECK(VendorID IS NOT NULL),
   CONSTRAINT InvoiceNumber_NULL    CHECK(InvoiceNumber IS NOT NULL),
   --CONSTRAINT InvoiceTotal_NULL    CHECK(InvoiceTotal IS NOT NULL),
   CONSTRAINT PaymentTotal_NULL    CHECK(PaymentTotal IS NOT NULL),
   CONSTRAINT CreditTotal_NULL    CHECK(CreditTotal IS NOT NULL),
   CONSTRAINT TermsID_NULL           CHECK(TermsID IS NOT NULL),
   CONSTRAINT InvoiceDueDate_NULL   CHECK(InvoiceDueDate IS NOT NULL),
   CONSTRAINT PaymentDate_NULL       CHECK(PaymentDate IS NOT NULL),
   --
   CONSTRAINT InvoiceNumber_zero_LEN   CHECK( LEN(InvoiceNumber)>0),
   --
   CONSTRAINT Invoices_Vendors_FK   FOREIGN KEY(VendorID)
       REFERENCES Vendors(VendorID),
   CONSTRAINT Invoices_Terms_FK   FOREIGN KEY(TermsID)
       REFERENCES Terms(TermsID)
);

-- Turn the constraints I do not want to use off
ALTER TABLE Invoices NOCHECK CONSTRAINT PaymentDate_NULL;
ALTER TABLE Invoices NOCHECK CONSTRAINT TermsID_NULL;
ALTER TABLE Invoices NOCHECK CONSTRAINT InvoiceDueDate_NULL;
ALTER TABLE Invoices NOCHECK CONSTRAINT Invoices_Vendors_FK;
GO
GO

-----------------------------------------------------------------------------

CREATE TABLE InvoiceLineItems
(
   InvoiceID INT,
   InvoiceSequence SMALLINT,
   AccountNo INT,
   InvoiceLineItemAmount MONEY,
   InvoiceLineItemDescription VARCHAR(100),
   --
   CONSTRAINT InvoiceLineItems_PK
       PRIMARY KEY(InvoiceID, InvoiceSequence),
------------------------------------------------------------------
   CONSTRAINT FK_Inv_Line FOREIGN KEY(invoiceID)
       REFERENCES invoices(invoiceID),
   CONSTRAINT FK_glAccounts FOREIGN KEY(accountNo)
       REFERENCES glAccounts(accountNo),
---------------------------------------------------------------
   --
   CONSTRAINT AccountNo_NULL CHECK(AccountNo IS NOT NULL),
   --
   CONSTRAINT InvoiceLineItemAmount_NULL CHECK(InvoiceLineItemAmount IS NOT NULL),
   --
   CONSTRAINT InvoiceLineItemDescription_NULL CHECK(InvoiceLineItemDescription IS NOT NULL),
   --
   CONSTRAINT InvoiceLineItemDescription_zero_LEN
       CHECK( LEN(InvoiceLineItemDescription)>0)
);
ALTER TABLE invoiceLineItems NOCHECK CONSTRAINT FK_Inv_Line;

CREATE TABLE InvoiceArchive
(
   InvoiceID        INT,
   VendorID        INT,
   InvoiceNumber    VARCHAR(50),
   InvoiceDate    SMALLDATETIME,
   InvoiceTotal   MONEY,
   PaymentTotal   MONEY,
   CreditTotal       MONEY,
   TermsID           INT,
   InvoiceDueDate   SMALLDATETIME,
   PaymentDate       SMALLDATETIME,
   --
   CONSTRAINT arcInvoiceID_NULL        CHECK(InvoiceID        IS NOT NULL),
   CONSTRAINT arcVendorID_NULL        CHECK(VendorID            IS NOT NULL),
   CONSTRAINT arcInvoiceNumber_NULL    CHECK(InvoiceNumber    IS NOT NULL),
   CONSTRAINT arcInvoiceDate_NULL        CHECK(InvoiceDate        IS NOT NULL),
   CONSTRAINT arcInvoiceTotal_NULL    CHECK(InvoiceTotal        IS NOT NULL),
   CONSTRAINT arcPaymentTotal_NULL    CHECK(PaymentTotal        IS NOT NULL),
   CONSTRAINT arcCreditTotal_NULL        CHECK(CreditTotal        IS NOT NULL),
   CONSTRAINT arcTermsID_NULL            CHECK(TermsID            IS NOT NULL),
   CONSTRAINT arcInvoiceDueDate_NULL    CHECK(InvoiceDueDate    IS NOT NULL),
   CONSTRAINT arcPaymentDate_NULL        CHECK(PaymentDate        IS NOT NULL),
   --
   CONSTRAINT arcInvoiceNumber_zero_LEN   CHECK( LEN(InvoiceNumber)>0),
   --
   CONSTRAINT arcInvoices_PK   PRIMARY KEY(InvoiceID)
);
ALTER TABLE InvoiceArchive NOCHECK CONSTRAINT arcPaymentDate_NULL;
GO
INSERT dbo.InvoiceArchive (InvoiceID, VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal, PaymentTotal, CreditTotal, TermsID, InvoiceDueDate, PaymentDate) VALUES (300, 123, N'963007234', CAST(0x9FB80000 AS SmallDateTime), 138.7500, 138.7500, 0.0000, 3, CAST(0x9FD70000 AS SmallDateTime), CAST(0x9FD30000 AS SmallDateTime))
INSERT dbo.InvoiceArchive (InvoiceID, VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal, PaymentTotal, CreditTotal, TermsID, InvoiceDueDate, PaymentDate) VALUES (400, 123, N'2-000-2556', CAST(0x9FBB0000 AS SmallDateTime), 144.7000, 144.7000, 0.0000, 3, CAST(0x9FDA0000 AS SmallDateTime), CAST(0x9FD60000 AS SmallDateTime))

HERE IS THE DATA AND I NEED HELP WITH A PROBLEM RELATED TO THIS DATA

3) We are trying to enable the FOREIGN KEY constraint in the invoiceLineItems table; however, we have data in the child table that are inconsistent with the similarly named field in the invoices table. Return all data on the row or rows. (Hint: SELECT tableName.* ... one row returned)

4) While we're at it, let's check the contactUpdates table for bogus data. The odd thing about that table is that somebody set up the FOREIGN KEY referencing the vendors table in contactUpdates as also being the PRIMARY KEY! Write an SQL query that will find any vendorID in contactUpdates that does not appear in the vendors; return all data. (There is one.)

5) List the vendorID and vendorName for all vendors who have no corresponding rows in the invoices table. (I see 88 of them.)

Answer 1

In above question you need data from both parent and child tables. Here parent table is "Invoices" and child is "invoiceLineItems".

Following is the query to fetch data from both tables:

SELECT t1.InvoiceID, t1.VendorID, t1.InvoiceNumber, t1.InvoiceDate, t1.InvoiceTotal, t1.PaymentTotal, t1.CreditTotal, t1.TermsID, t1.InvoiceDueDate, t1.PaymentDate,

t2.InvoiceSequence, t2.AccountNo, t2.InvoiceLineItemAmount, t2.InvoiceLineItemDescription

FROM Invoices t1, invoiceLineItems t2

WHERE t1.InvoiceID = t2.InvoiceID;