|
Crima
Starting Member
USA
17 Posts |
 Posted - 11/14/2012 : 20:16:56
|
This is my homework can someone look it over? any and all help would be great thanks !: )
--2. Based on the design above, answer the following questions. All the answers have to be in
--the form of a .sql script.
--a. Create a new database named Membership.
--b. Write the CREATE TABLE statements needed to implement the design from 1 in
--the Membership database. Include reference constraints. Define IndividualID and
--GroupID with the IDENTITY keyword. Decide which columns should allow null
--values, if any, and explain your decisions.
--A.
CREATE DATABASE Members
--B.
CREATE TABLE Individuals
(
Individual_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
Phone varchar(255)
)
CREATE TABLE GGroup
(
Group_ID int,
GroupName varchar(255)
)
--3.
USE [AP]
GO
ALTER TABLE [dbo].[Invoices] WITH NOCHECK ADD CONSTRAINT [AllowNonNullPaymentDate] CHECK (([PaymentDate] IS NOT NULL AND [PaymentTotal]>(0)))
GO
ALTER TABLE [dbo].[Invoices] CHECK CONSTRAINT [AllowNonNullPaymentDate]
GO
ALTER TABLE [dbo].[Invoices] WITH NOCHECK ADD CONSTRAINT [AllowNullPaymentDate] CHECK (([PaymentDate] IS NULL AND [PaymentTotal]=(0)))
GO
ALTER TABLE [dbo].[Invoices] CHECK CONSTRAINT [AllowNullPaymentDate]
GO
ALTER TABLE
--4.Write a CREATE VIEW statement that defines a view named InvoiceBasic that returns
--three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT
--statement that returns all of the columns in the view, sorted by VendorName, where the
--first letter of the vendor name is N, O, or P.
CREATE VIEW InvoiceBasic
AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
FROM Invoices JOIN Vendors ON Invoices.InvoiceID = Vendors.VendorID
ALTER VIEW Invoicebasic
AS
SELECT*
FROM Invoices JOIN Vendors ON Invoices.InvoiceID = Vendors.VendorID
WHERE VendorName '
--5.
CREATE View Top10PaidInvoices as
Select Top 10 VendorName, Max(InvoiceDate) as LastInvoice,
Sum(InvoiceTotal) as SumOfInvoices
From Vendors Join Invoices
On Vendors.VendorID = Invoices.VendorID
Where (InvoiceTotal - PaymentTotal - CreditTotal) > 0
Group By VendorName
Order By SumOfInvoices DESC
CREATE VIEW TOP10PaidInvoices
AS
SELECT TOP 10 Vendors.VendorName, Invoices.LastInvoice AS InvoiceDate,
Invoices.SumOfInvoice AS InvoiceTotal,
FROM Invoices INNER JOIN Vendors ON Invoices.InvoiceID = Vendors.VendorID
ORDER BY SumOfInvoices ASC
------------------------------------------------------------------------------------
--EXTRA CREDIT--
------------------------------------------------------------------------------------
--1.Modify your design from problem 1 to keep track of the role served by each individual in
--each group. Each individual can only serve one role in each group. Each group has a
--unique set of roles that members can fulfill. Create additional tables and columns, if
--necessary. Explain your design decisions.
--2.Write an ALTER TABLE statement that adds two new check constraints to the Invoices
--table of the AP database. The first should allow (1) PaymentDate to be null only if
--PaymentTotal is zero and (2) PaymentDate to be not null only if PaymentTotal is greater
--than zero. The second constraint should prevent the sum of PaymentTotal and
--CreditTotal from being greater than InvoiceTotal.
ALTER TABLE Invoices WITH CHECK
ADD check (
(PaymentTotal = 0 AND PaymentDate is NULL)
OR
(PaymentTotal > 0 AND PaymentDate is NOT NULL)
)
go
ALTER TABLE Invoices WITH CHECK
ADD CHECK (
(PaymentTotal + CreditTotal) <= InvoiceTotal
)
go
--3.Create an updatable view named VendorAddress that returns the VendorID, both address
--columns, and the city, state, and zip code columns for each vendor. Then, write a
--SELECT query to examine the result set where VendorID=4. Next, write an UPDATE
--statement that changes the address so that the suite number (Ste 260) is stored in
--VendorAddress2 rather than in VendorAddress1. To verify the change, rerun your
--SELECT query.
CREATE VIEW VendorAddress
return VendorID, VendorAddress1, VendorAddress2, VendorState, VendorZip, VendorCity
SELECT VendorID = 4
UPDATE
--4. Write a SELECT statement that selects all of the columns from the catalog view that
--returns information about foreign keys. How many foreign keys are defined in the AP
--database?
SELECT * CatalogView
WHERE
|
|
Can someone look this over? thanks :)
