Can someone look this over? thanks :)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can someone look this over? thanks :)

Author

Topic

Crima
Starting Member

17 Posts

Posted - 2012-11-14 : 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

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-11-15 : 01:16:43

what have tried till now?post it and someone can help if you are stuck anywhere.