MY SQL

2.       If a table has a list of multiple foreclosure codes, and you don’t know how much unique codes are in the table, how could you get a list of unique codes within the table.
3.      What function/process could you use to combine two datasets/tables together (assume the tables have the same number of columns and data types?
4.      What statement would you use to remove all rows from a table?
5.      What statement would you use to delete the entire table (remove all data, triggers, indexes)?
6.      I want to add 2 months to todays date?
7.      I have two dates (3/25/2007 and 4/1/2009) how can I get the number of months between the two dates?
Run the Following Script and answer the Questions Below
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Loan]') AND type in (N'U'))
DROP TABLE [dbo].[Loan]

CREATE TABLE [dbo].[Loan](
      [LoanNumber] [int] IDENTITY(1000,1) NOT NULL,
      [CustomerFname] [varchar](50) NULL,
      [CustomerLname] [varchar](50) NULL,
      [PropertyAddress] [varchar](150) NULL,
      [City] [varchar](150) NULL,
      [State] [varchar](50) NULL,
      [BankruptcyAttorneyName] [varchar](50) NULL,
      [UPB] MONEY NULL,
      [LoanDate] [Datetime] NULL
 CONSTRAINT [PK_Loan] PRIMARY KEY CLUSTERED
(
      [LoanNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

TRUNCATE TABLE dbo.Loan

INSERT INTO [dbo].[Loan]
           ([CustomerFname]
           ,[CustomerLname]
           ,[PropertyAddress]
           ,[City]
           ,[State]
           ,[BankruptcyAttorneyName]
               ,[UPB]
               ,[LoanDate])
SELECT      'Mr. Anand','Dasari','1212 Main St.','Plano','TX','Jerry',85000,'1/1/2012' UNION
SELECT      'Mr. John','Nasari','1215 Joseph St.','Garland','TX','Jerry',95000,'4/2/2012' UNION
SELECT      'Dr. Ali','Muwwakkil','2375 True True St.','Atlanta','GA','Diesel',115000,'5/3/2008' UNION
SELECT      'Mr. John','Brown','11532 Chain St.','SanFrancisco','CA','Mora',350000,'6/13/2004' UNION
SELECT      'Dr. Kishan','Johnson','4625 Miller Rd.','Atlanta','GA','Diesel',225000,'8/9/2002' UNION
SELECT      'Mr. John','Jackson','972 Flower Rd.','Dallas','TX','Jerry',150000,'3/1/2012' UNION
SELECT      'Sr. Ralph','Jenkins','1518 Mission Ridge St.','SanFrancisco','CA','Mora',650000,'12/15/2011' UNION
SELECT      'Dr. John','Howard','102 Washington','Dallas','TX','Jerry',450000,'4/5/2010' UNION
SELECT      'Mrs. Marsha','Tamrie','1301 Solana','SanFrancisco','CA','Mora',750000,'7/1/2000' UNION
SELECT      'Mrs. Alexis','Gibson','1111 Phillips Rd.','Atlanta','GA','Diesel',99000,'6/1/2012'
       
SELECT * FROM [dbo].[Loan]


8.      Write a SQL query to retrieve loan number, state, city, UPB and todays date for loans in the state of TX that have a UPB greater than $100,000 or loans that are in the state of CA or FL that have a UPB greater than or equal to $500,000
9.      Write a SQL query to retrieve loan number, customer first name, customer last name, property address, and bankruptcy attorney name.  I want all the records that have the same attorney name to be together, then the customer last name in order from Z-A (ex.Customer last name of Wyatt comes before customer last name of Anderson)
10.      Write a sql query to retrieve the loan number, state and city, customer first name for loans that are in the states of CA,TX,FL,NV,NM but exclude the following cities (Dallas, SanFrancisco, Oakland) and only return loans where customer first name begins with John.
11.      Find out how many days old each Loan is?
12.      Find the State with the highest Avg UPB.
13.      Each Loan has a length of 30 years.  Retrieve the LoanNumber, Attorney Name and the anticipated Finish Date of the Loan.
14.      The Title of the Customer is Located in the CustomerFname Column.  Separate the title into its own column and also retrieve CustomerFname, CustomerLname, City, State and LoanDate of Loans that are more than 1 yr old.
Find another function not used above.  Explain what it does. Create a Statement using the new function and post it to the discussions.  Take a screenshot of the posted article, paste it to a Word Doc and submit it with this assignment.
System-Functions-Lab.docx