lab-7-and-lab-8

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