System_Functions_Lab
docx
keyboard_arrow_up
School
Merritt College *
*We aren’t endorsed by this school
Course
3A
Subject
Information Systems
Date
Feb 20, 2024
Type
docx
Pages
14
Uploaded by ElderEnergyGiraffe3
*System Functions*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
1.
Create 2 statement using each of the following functions.
a.
LEN
b.
LEFT
Page 1
*System Functions*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
c.
RIGHT
d.
SUBSTRING
Page 2
*System Functions*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
e.
CHARINDEX
f.
LTRIM
Page 3
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
*System Functions*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
g.
RTRIM
h.
DATEDIFF
Page 4
*System Functions*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
i.
DATEPART
j.
DATEADD
Page 5
*System Functions*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
k.
CAST AND CONVERT
l.
ISDATE
Page 6
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
*System Functions*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
m. ISNULL
n.
ISNUMERIC
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.
SELECT DISTINCT()
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?
JOIN
4.
What statement would you use to remove all rows from a table?
DELETE
5.
What statement would you use to delete the entire table (remove all data, triggers, indexes)?
6.
I want to add 2 months to today’s date?
TRUNCATE
7.
I have two dates (3/25/2007 and 4/1/2009) how can I get the number of months between the two dates?
SELECT DATEDIFF(MM,’3/25/2007’,’4/1/2009’)
Run the Following Script and answer the Questions Below
Page 7
*System Functions*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
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
Page 8
*System Functions*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
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)
Page 9
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
*System Functions*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
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.
Page 10
*System Functions*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
11.
Find out how many days old each Loan is?
12.
Find the State with the highest Avg UPB.
Page 11
*System Functions*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
13.
Each Loan has a length of 30 years. Retrieve the LoanNumber, Attorney Name and the anticipated Finish Date of the Loan.
Page 12
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
*System Functions*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
14.
15.
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.
Page 13
*System Functions*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
Page 14