Assignment Chapter 8 and 9
All the questions are using AP database that is created by running script: create_db_ap.sql
Copyright By PowCoder代写 加微信 powcoder
Each question is worth 8 points, 2 points for completion of all problems.
1. Write a SELECT that returns all vendors from Vendor table, that have addresses as P.O. boxes.
· Use string functions from Chapter 9 to get records with every possible format of P.O box address from vendor_address1 column
· Statement should return:
· first and last name concatenated in 1 column
· phone number in the following format: 1-310-092-0134
· PO Box column (without P.O. Box words in it) so P.O. Box 123 should show as 123
It should return records that originally had all different formats:
P O Box 7126
P O Box 7247-7844
P.O. Box 1140
P.O. Box 860070
PO Box 1091
2. Using Invoices table write select statement that will return data that has invoice_date older the 6 years and 7 months from this date.
This should return the following columns
· Invoice_id
· Invoice_date year column in format: 2020
· Invoice_date month column in format: January
· Invoice_date: day of the week
· Invoice_date: day of the month
· Invoice_date: quarter of the year
· How many days passed from invoice_date until now
· How many days passed from invoice_date to invoice_due_date
Answer should include uses of all of these functions to get full credit: Date_Format, Extract, Quarter
3. Select Vendors from the vendors table from the following states: ‘DC’, ‘CT’, ‘CA’, ‘AZ’
This should return the following columns
· Vendor_contact_last_name
· Vendor_phone, in case phone is Null, return “no phone number”
· Vendor_state – replace state with full state name, so CA shows up as California. Do this using Case statement and only for the 4 states above (do not do it for 50 states)
4. Write a select statement that gets
· Invoice_Id CAST to Char,
· Payment_Total Format to decimal with 1 digit to the right
· Payment_Total CONVERT to Int
· Invoice_Total CAST to UNSIGNED integer
· Invoice_date Get the year
· Payment_date CAST to string
· Show next day date that is after the Payment_date
5.1 Create new Database with name: DataType_Test
Create table Customers
Make the following columns
first_name: VARCHAR (20)
last_name: VARCHAR (20)
middle_name: CHAR(1)
customer_type: ENUM with values: External, Internal, Undefined
preferred_vendor_types: SET with values B2C, B2G, B2B
secret_code: VARCHAR(40)
create_date: DATETIME
modified_date: DATETIME
internal_id: UNSIGNED int
picture: Blob
5.2 Insert records
first_name: Anna
last_name: Choir
middle_name: L
customer_type: Internal
preferred_vendor_types: B2C, B2B
secret_code: asdsa123kwa
create_date: current date
modified_date: current date
internal_id: 123455
picture: null
This should succeed for Id:1
first_name: Julie
last_name: Morgan
middle_name: K
customer_type: External
preferred_vendor_types: B2B
secret_code: as3md93f
create_date: current date
modified_date: current date
internal_id: -123455
picture: null
This should succeed for Fail, explain why it fails in your file submitted
5.3 make another 2 examples on putting invalid data in the different columns
6. Create table Points with columns
· X: Double
· Y: Double
Insert 2 rows with ids 1 and 2, using RAND function to populate each X and Y values to be from 1 to 100 inclusive.
Make select statement that calculates distance between (X,Y) in the first row to (X,Y) in the second row
Hints: Use subqueries to bring all data into the same column
Distance is calculated by
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com