SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Can someone look this over? thanks :)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Crima
Starting Member

USA
17 Posts

Posted - 11/14/2012 :  20:16:56  Show Profile  Reply with Quote
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

India
534 Posts

Posted - 11/15/2012 :  01:16:43  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
what have tried till now?post it and someone can help if you are stuck anywhere.
Go to Top of Page

Crima
Starting Member

USA
17 Posts

Posted - 11/15/2012 :  15:08:36  Show Profile  Reply with Quote
I have fixed and completed everything but the last question (4)
I'm not sure how to select catalog view.

A witty student
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/15/2012 :  23:43:38  Show Profile  Reply with Quote
make use sys.foreign_keys catalog view

http://technet.microsoft.com/en-us/library/ms189807.aspx


or use sp_foreignkeys procedure

http://visakhm.blogspot.in/2010/11/spfkeys-vs-spforeignkeys.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17428 Posts

Posted - 11/16/2012 :  00:10:33  Show Profile  Reply with Quote
use the INFORMATION_SCHEMA view.
refer to http://msdn.microsoft.com/en-us/library/ms186778.aspx


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000