[SOLVED] lab 7 and Lab 8

I’m studying for my MySQL class and don’t understand how to answer this. Can you help me study?

Struggling to find relevant content or pressed for time? – Don’t worry, we have a team of professionals to help you on
[SOLVED] lab 7 and Lab 8
Get a 15% Discount on this Paper
Order Now

LAB 7:It is also attatched below:

Data Types and Functions.

— 1.Write a SELECT statement that returns InvoiceId and Total from the Invoice table. Use the CONCAT function to insert a ‘$’ in the front of the total value

— 2.Write a SELECT statement that returns CustomerId and average Total from the Invoice table grouped by customerId. Use the CONCAT function to insert a ‘$’ in the front of the average total and the FORMAT function to to display it with commas and 2 decimal places.

— 3. Use the CHAR and CONCAT functions (for the first and last names), to format the the address for each customer in the customer table – the formatted output should look like this

— Gary Hernandez

— 3829 Broadway Ave

— New York, NY 10012

— note ***** the output will not look like this in the workbench the CHAR functions will place columns in each row of the results *****

— 4. Display the first initial, last name, and email address of each customer in the customer table.

— 5. Return these columns from the customer table, customerid, email and Phone. Use the substr and lpad functions to render the phone number like this: ************9999

— 6. use the concat_ws function to render to display the customer lastname and firstname in this manner: Lackey, Toby. Also display the customer’s email address but only list the customers whose email addresses have more than 20 characters

— 7.Display the customerId and the phone for all customers who live in the USA, format the phone numbers so that they look like this 123-456-7899

— 8. For every track in the track table, return the track name and the album title. The album title should be in all uppercase letters.

— 9. For each track, display the unitprice, unitprice rounded to 1 decimal place, unitprice truncated to 1 decimal place, smallest integer >= unitprice, largest integer <= unitprice, and a random number between 1 and 10 using the trackId as a seed.

— 10. Use now and curtime functions to display these values on your system */

— 11. Count the number of invoices grouped by the day of the week using the Invoice date.

— 12. Display all columns and rows from the invoice table where the invoicedate is in the second quarter of 2012. Hint you will need to use two functions in the WHERE clause, YEAR and QUARTER*/

— 13. Use the extract function to return the InvoiceId, InvoiceDate and Total for all invoices that were placed on the first day of any month or year.

— 14. Use a case function to display the following information from the track table. The TrackId, name, the UnitPrice and the word “MPEG”, if the the MediaTypeId is 1 or 3, and “AAC” for all others. Name this column ‘Media Type’

— 15. Display the InvoiceId and InvoiceDate for all invoices. Use the date format function to dformate the date as MM/DD/YY. Use a column alias of your choice for the formatted date

— 16. Use an IF statement to display the InvoiceId, BillingCity, BillingState, BillingCountry and the word “Yes” if the BillingCountry is “USA”, “Mexico” or “Canada” or the word “No” for all other BillingCountries. The title for the added column should be ‘North America’ and

Lab 8

  • Creation and Design of MySQL Schema Objects
  • Design and create normalized databases
  • Create and modify tables using appropriate data types and indexing
  • Describe and create table constraints enforcing data integrity

REQUIREMENTS:

  • Identify a real world application that would require a database. This could be a business, an organization or a specific area within a business or organization (i.e. human resources, payables and receivables, etc.)
  • Identify 2-3 major entities involved in the application. For example, if you were doing a college scheduling application, the entities would be students, classes and instructors.
  • For each entity, identify the information about the entities (data fields) that would be needed. You should have about 5-10 fields for each entity.
  • Using the identified entities and data fields, go through the data normalization process to identify the tables that would be needed for a relational database
  • Assign valid SQL fieldnames to each field
  • Identify a primary key for each table.
  • Identify the foreign keys that would be needed
  • Use the data modeling utilities in the workbench to create a new EER diagram for your database and submit that.
  • Click on the View/Complete link at the bottom of this assignment. Attach the text file to the assignment and Submit.

Grading Rubric:

CriteriaI Points
Identified 2-3 entities with 5-10 fields each

15 points

Database normalized appropriately

30 points

Data fields assigned valid names

10 points

Identify a primary key for each table and foreign keys as needed 10 points
Create an EER diagram 15 points

Total

80 points

Calculate the price
Make an order in advance and get the best price
Pages (550 words)
$0.00
*Price with a welcome 15% discount applied.
Pro tip: If you want to save more money and pay the lowest price, you need to set a more extended deadline.
We know how difficult it is to be a student these days. That's why our prices are one of the most affordable on the market, and there are no hidden fees.

Instead, we offer bonuses, discounts, and free services to make your experience outstanding.
Sign up, place your order, and leave the rest to our professional paper writers in less than 2 minutes.
step 1
Upload assignment instructions
Fill out the order form and provide paper details. You can even attach screenshots or add additional instructions later. If something is not clear or missing, the writer will contact you for clarification.
s
Get personalized services with MyCoursebay
One writer for all your papers
You can select one writer for all your papers. This option enhances the consistency in the quality of your assignments. Select your preferred writer from the list of writers who have handledf your previous assignments
Same paper from different writers
Are you ordering the same assignment for a friend? You can get the same paper from different writers. The goal is to produce 100% unique and original papers
Copy of sources used
Our homework writers will provide you with copies of sources used on your request. Just add the option when plaing your order
What our partners say about us
We appreciate every review and are always looking for ways to grow. See what other students think about our do my paper service.
Technology
My paper was sent back after my due date time
Customer 452901, November 12th, 2022
Business and administrative studies
Thanks
Customer 452701, August 16th, 2022
Nursing
Fantastic work on this project as usual.
Customer 452707, July 5th, 2022
Other
GREAT
Customer 452813, June 20th, 2022
Social Work and Human Services
Excellent Work
Customer 452587, November 22nd, 2021
Technology
Great job on the paper!
Customer 452885, December 14th, 2022
Other
AWESOME
Customer 452813, July 5th, 2022
Nursing
Thank you MyCoursebay team for your support. With your help I got an A for my past courses
Customer 452635, May 7th, 2022
Social Work and Human Services
Excellent Work!
Customer 452587, August 24th, 2021
Criminal Justice
always great!
Customer 452465, February 23rd, 2021
Ethics
thank you
Customer 452493, March 15th, 2021
Nursing
Paid for the paper to be completed 5 days prior than the day I received the paper.
Customer 452693, July 13th, 2022
OUR GIFT TO YOU
15% OFF your first order
Use a coupon FIRST15 and enjoy expert help with any task at the most affordable price.
Claim my 15% OFF Order in Chat

Get top-notch homework help now. 20% off first 10 orders!

NEW

Thank you for choosing MyCoursebay. Your presence is a motivation to us. All papers are written from scratch. Plagiarism is not tolerated. Order now for a 15% discount

Order Now