MySQL
Week 4 70 pts
This week assignment has a little of everything: Some CREATE TABLE statements with FOREIGN KEYS, ALTER TABLE, string functions (CONCAT, SUBSTR), UPDATES, copy tables, DISTINCT, WHERE, ORDER BY, and JOINS. It just gets more fun!
- Write 3 create table statements to create the tables below. Include datatypes as listed and appropriate foreign and primary key syntax. Run your statements to make sure they work. The order you create your tables will make a difference. (10)
• Yellow Key = Primary Key
• Red filled Diamond = Foreign Key
• Blue Filled Diamond = required field or not null
• Blue unfilled Diamond = optional field or null
- Determine which categories are represented in the current book inventory. List
each category only once. (5)
- List all customers who have been referred to the bookstore by
another customer. List each customer’s firstname, lastname, and
the number of the customer who made the referral. Do not use
‘Where Referred is > 0’ (5)
- Determine the length of data stored in the lastname field of the customers
table. Make sure each different length value is displayed only once. Sort data
in ASCENDING order(5)
- Copy the books table. Call the new table New_books. Put all the books from the original books
table into the new_books table. (5)
- Add 10% to the cost of all books in the new_books table from the category Family Life. Actually,
change the values in the database, not just select and display. (5)
- Alter the new-books table to include a column called Cat_isbn with data type char. (5)
- Using string functions, write an update statement that add the
following values to Cat_isbn column created in previous problem.
CAT_ISBN should contain the first two characters from the category
followed by the last 3 digits of the ISBN. Include a dash between the
two values. (see picture below). (5)
- Produce a list of all customers’ names in which the first letter of the first and last names is in uppercase and the rest are in lower case. You will need to nest string functions to make this work. MySQL doesn’t have a Proper string function like some languages. You will need to grab the last characters and make them lowercase and attach them to the first character which is already upper case. (5)
- What books did author James Austin write? (Show isbn, title, author first and last name concatenated together into one column). (5)
- Determine which orders haven‘t yet shipped and the name of the customer who placed the order. Sort the results by the date on which the order was placed. Sort data by orderdate with most current date at the top. (5)
- Write a query to show the isbn, title, pubdate and publisher name for all books in the family life category. (5)
- Show me the customers from Florida and all the books they ordered. Display the columns as shown below and order the data by last name.. (5)
Custompaper.help is an academic writing platform that provides students and professionals with quality services to ease their assignment challenges. With a wide range of services, the platform has become one of the leading academic writing service providers in the market due to its reliability and affordability. Below, we shall discuss some of the services which Custompaper.help provides:
Essay Writing Service
The essay writing service provides customers with quality essays on any subject or topic they requested within a given deadline. The writers at custompaper are experts in their respective fields, and they ensure customer satisfaction by producing original content with thorough research involved backed up with strong evidence from reliable sources available online or offline. All essays come with free plagiarism reports as well as revisions if needed so that customers can trust that they are getting high-quality work for their money’s worth.
Research Paper Writing Service
This is another popular service offered by custom paper help wherein customers are assisted in conducting research papers according to their needs and requirements within specified deadlines. Writers at custom paper help have vast knowledge about various topics belonging to different disciplines such as Management, Arts & Humanities, History etc., allowing them to craft impeccable research papers every time regardless of the complexity or urgency level requested by customers. Additionally, all our research papers come along with proper citation formats which ensures customer’s safety when it comes to plagiarism issues while also helping them save time on formatting procedures thus improving overall efficiency even further!
Dissertation Writing Service
CustomPaperHelp also offers dissertation writing assistance for those who need it most – students preparing for graduation or professionals wanting to further refine their skills through higher education levels such as Master/PhD degrees etc, Our team of writers consists of experienced individuals having Master’s/PhD degrees themselves so you can rest assured that your dissertation will be handled expertly from the start till the end! Furthermore, we offer unlimited revisions until your complete satisfaction is achieved, along with regular checkups being done throughout the process just so everything goes according to course without any hiccups!
Editing And Proofreading Services
If you already have written an essay but want professional feedback before submitting it, then CustomPaperHelp also offers editing & proofreading services which helps in identifying & rectifying errors present in assignments like grammar mistakes, spelling mistakes, punctuation problems etc., thereby ensuring top-notch quality work, before submission takes place! We understand how important accuracy & consistency are when it comes to grading systems used at universities nowadays, so taking extra care during this phase becomes absolutely essential if good grades are desired afterward!
Coursework Help
For those who find coursework difficult due to complications of being quite technical in nature; CustomPaperHelp extends its support here too, by providing quality solutions every single time – starting from understanding concepts properly up until composing flawless answers following university guidelines closely enough making sure no loopholes remain whatsoever! Whether it’s related math problem-solving algorithms or lengthy home assignments – our expert panel has got you covered from head-to-toe leaving no stone unturned during this very crucial stage since final grades depend largely upon the success rate achieved here successfully!
Assignment Help
Assignments form an integral part of getting better scores & ultimately, a degree itself (in case pursuing a masters/PhD) – something only possible if precision is made a priority right away; otherwise, chances of failure increase exponentially over a time period detrimentally affecting performance evaluation systems later down road drastically hence stressing timely importance completion prior commencement other operations associated same project altogether consistently meeting high standards expected each situation respectively! Here too, CustomPaperHelp presents comprehensive solution package exceeding expectations beyond point offering optimal values therein ensuring maximum satisfaction reached under circumstances prevailing accordingly without fail…proving once more why people trust us implicitly without second thought whatsoever!!
