-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathJoseOrtiz System Functions lab.sql
137 lines (111 loc) · 4.35 KB
/
JoseOrtiz System Functions lab.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
/*
Name: Jose Ortiz
Lesson: System Functions
Date: 03/24/2023
*/
--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*/
SELECT
LoanNumber
,State
,City
,UPB
,GETDATE() TodayDate
FROM [dbo].[Loan]
WHERE State='TX' AND UPB>100000 OR State IN ('CA','FL') AND UPB>500000
/*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)*/
SELECT
LoanNumber
,CustomerFname
,CustomerLname
,PropertyAddress
,BankruptcyAttorneyName
FROM [dbo].[Loan]
ORDER BY 5, 3 DESC
/* 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.*/
SELECT
LoanNumber
,State
,City
,CustomerFname
FROM [dbo].[Loan]
WHERE State IN ('CA','TX','FL','NV','NM')
AND City NOT IN ('Dallas', 'SanFrancisco', 'Oakland')
AND CustomerFname LIKE '%John%'
--11. Find out how many days old each Loan is?
SELECT
[LoanNumber]
,DATEDIFF(DD,[LoanDate],GETDATE()) AS LoanTime_Days
FROM [dbo].[Loan]
--12. Find the State with the highest Avg UPB.
SELECT TOP 1
State
,AVG(UPB) AS AvgUPB
FROM [dbo].[Loan]
GROUP BY State
/*13. Each Loan has a length of 30 years. Retrieve the LoanNumber, Attorney Name
and the anticipated Finish Date of the Loan.*/
SELECT
[LoanNumber]
,[BankruptcyAttorneyName]
,DATEADD(DD,30,[LoanDate]) Loan_FinishDate
FROM [dbo].[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.*/
SELECT
LEFT([CustomerFname],CHARINDEX(' ',[CustomerFname]))
AS Title
,RIGHT([CustomerFname],(LEN([CustomerFname])-CHARINDEX(' ',[CustomerFname])))
AS FirstName
,[CustomerLname]
,[City]
,[State]
,[LoanDate]
FROM [dbo].[Loan]
WHERE DATEDIFF(DD,[LoanDate],GETDATE())>365