— 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
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
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.
Identified 2-3 entities with 5-10 fields each
Database normalized appropriately
Data fields assigned valid names
Identify a primary key for each table and foreign keys as needed 10 points
Create an EER diagram
The sole goal of the procedure is to limit future street mishap event and seriousness once the plan has been constructed and the street comes into utilization. The recognizable proof of potential security risks on new street venture at the suitable Type, with the goal that they can be wiped out or generally treated to alleviate their unfriendly impact at least cost .To diminish the pace of mishaps at a specific area. The distinguishing proof of potential security perils highlights of a current street so they can be disposed of or generally treated before they become clumsy area. Street wellbeing review is formal methodology for surveying mishap potential and security execution in the arrangement of new street conspires, the improvement and recovery of existing street and in support of streets. The job of examiner is to give autonomous exhortation as composed proposals. The fashioner or customer at that point considers the exhortation and formal choice is made by them on whether to receive every one of the prescribed wellbeing changes. The essential job of review group is to distinguish the potential issues of a parkway venture by directing the site investigation and gathering information from different offices. 1 INTRODUCTION Street Safety Audit (RSA) is a conventional method for surveying mishap potential and wellbeing execution in the arrangement of new street plans and plans for the improvement and support of existing streets. Street security review methodology were created in 1989 by British traffic builds and advanced from an apparatus utilized by railroad specialists to look at wellbeing issues on rail lines. RSA's were before long embraced by Australia, New Zealand, Denmark, and numerous other created nations in the mid 1990s (FHWA 2009a). The improvement of the street security review strategies was refined before appropriation by the American transportation network. In 1996, the FHWA supported a voyage through Australia and New Zealand to ponder their street security review projects to learn techniques on the most proficient method to execute street wellbeing reviews in the United States. From the exercises learned, FHWA supported a street wellbeing review workshop in St. Louis to create methodology to be utilized in the street security review experimental run program. The principal test case program included thirteen states and gave a premise to utilization of street wellbeing reviews in the United States (Wilson and Lipinski 2004). As street wellbeing reviews have picked up notoriety in the United States they have additionally picked up acknowledgment and acknowledgment in different pieces of the world. The Asian Development Bank, as a team with United Nations Economic Commission for Europe and the World Bank, has as of late supported the utilization of street security reviews and have a distributed their own toolbox to be utilized in directing a street wellbeing review (ADB 2003). Nations around world are beginning to understand the minimal effort apparatus of sparing lives. There are two distinctive RSA forms that can be utilized. The first is the customary RSA that takes a gander at ventures before they are fabricated or operational, Pietrucha et al. (2001) depicted a street wellbeing review as a procedure where a group of specialists endeavors to 9 distinguish highlights of the roadway working condition as possibly hazardous and work to wipe out or change those highlights in various parts of the structure procedure. The other RSA process utilized and the procedure that is utilized all through this examination is called Road Safety Audit Reviews (RSARs) and can be characterized as "an assessment of a current roadway area by a free group, centering exclusively upon wellbeing issues" (Wilson and Lipinski 2004). Most states DOTs have receptive wellbeing programs that attention on highcrash areas or have dark spot treatment programs. RSARs are diverse in that they are proactive in nature and use crash information when accessible however are not subject to it. RSARs concentrates more on wellbeing issues related with the roadway, all street clients, working under every single ecological condition, and to recognize the security issues related with the current office (Wilson and Lipinski 2004). 1.1. General A street security review is the persistent observing of the wellbeing element of new and existing interstate and traffic the board plot, which include improvement of existing design. The essential objective of street security review is to guarantee that there is less future issues on interstate. A mishaps are happens because of impact of at least two vehicles, cyclist and vehicle, person on foot and vehicle, fixed article and vehicle, upset vehicle close to open street, and so on in our standard life as the transportation is expanding it will likewise build wellbeing issue around there of broad research and work. In creating nations like India, the street mishaps expanding step by step. So it is important to diminish street mishap and work towards the street wellbeing. To apply the street security review, all things considered, the principal thing is to know the geometric highlights of expressways. This mishaps will impact of individuals for quite a while. The Gujarat State Road Transport Corporation gives office of transportation to around 24 lakh individuals consistently. Street wellbeing review gives to survey the street mishap and will give the preferred sa over fix". For improving street wellbeing review will the work street on new streets , security, Existing street sand in this manner the support of the current street. Street wellbeing review contained the inner piece of structuring, arranging, development upkeep of street, this will end up being the necessary principle for recently street. In past, there is no need to street security review however now a days India additionally began having faith in the significance of street wellbeing review .on account of this Ministry of Road Transport and Highway supported the undertaking Under this venture, Manual for Road Safety Audit has been readied. First Road Safety Audit was conveyed again by CRRI IN 2000 on Indore Bypass. 1.2 Scope of Road wellbeing review India has the second biggest street organize on the planet with more than 3 million kms of streets of which 60% are cleared. These streets make a fundamental commitment to India's economy. All in all, be that as it may, the offices for the street clients fall a long ways behind adequate standard, prompting an enormous loss of life coming about because of street mishaps. As of late, there has been a developing worry over the street wellbeing issue. The Road Safety issue in India requests a multidimensional methodology. Street Safety Audit is just a single significant part: Subcontracts India is doing notable work in the accompanying territories: 1.3 Road wellbeing review Phases a. Fruition of primer structure, ideally preceding the accommodation for arranging authorization b. Finish of point by point plan, for the most part before the delicate reports have been submitted c. Finish of development preceding opening (or fruition if on a 'live' parkway) d. Impact observing (a year and three years subsequent to opening of the expressway plot) 1.4 Types of Road wellbeing review There are four distinct Types of a Road Safety Audit each framing their very own autonomous report however allude to one another and these are nitty gritty underneath:- Type 1 Type 1 Road Safety Audits are embraced toward the culmination of fundamental structure and where conceivable, before arranging assent is allowed. This is the last event at which land necessities might be expanded and it is accordingly basic to consider completely any street wellbeing issues which may have a direction upon land take before arranging assent is allowed. At the Road Safety Audit Type 1 all colleagues will visit together and look at the current interstate format or includes and where the new parkway improvement plot integrates with the current expressway. Type 1 – Completion of Preliminary Design • Will the new street channel satisfactorily • Can all gets to be utilized securely • Are flat and vertical arrangements predictable with required perceivability • Is arrangement for right turning vehicles required • Have person on foot and cycle courses been given where required • Are lighting segments situated at new intersections and where connecting existing streets • Are any street markings proposed at this Type proper Type 2 Type 2 Road Safety Audits are attempted at culmination of the nitty gritty plan Type of the works. The Audit Team will have the option to think about the design of intersections, position of signs, carriageway markings, lighting arrangement and different issues. At the Road Safety Audit Type 2 all colleagues will visit together and look at the current roadway design or includes and where the new parkway improvement conspire integrates with the current interstate. Type 2 – Completion of Detailed Design • General fundamental structure principals • Local arrangement • Visibility • Junctions design and perceivability • Non mechanized client arrangement • Road signs, carriageway markings and lighting Type 3 The Type 3 Road Safety Audit ought to be attempted when the Highway Improvement Scheme is generously finished and ideally before the works are available to street clients. The Audit Team will inspect the plan site during light and during the long periods of dimness, so perils specific to night activity can likewise be recognized. The Audit Team Leader will welcome delegates of the Police, the Local Authority and Maintaining Agent to go with the Audit Team to offer their perspectives for the Type 3 Audit. Type 3 – Completion of Construction • The Audit Team ought to think about whether the plan has been appropriately converted into the plan as developed and that no inalienable street security deformity has been consolidated into the works. • Particular consideration ought to be paid to configuration changes which have happened during development. • Design standards • Local Alignment • Visibility • Junction formats • Non mechanized client arrangement • Road signs, carriageway markings and lighting Type 4 – (Monitoring) During the primary year a Highway Imp>GET ANSWER