1. Write SQL statements to create all tables in the final normalized ERD Use appropriate data...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
1. Write SQL statements to create all tables in the final normalized ERD Use appropriate data types and required constraints for attributes. Ensure all relationships and relationship constraints are maintained. Note that the order of table creation can be important when tables have Foreign Keys. Provide SQL statements to create all tables in your report. 2. Insert at least FIVE records in each table using SQL. Provide all SQL statements used to insert data and screenshots of results of 'SELECT * FROM ;' for each table in your report. 3. Write and run an SQL statement to add one additional column (attribute) in any one of the existing tables with a default value. First, provide the following information: Name of table you want to add a new column Name, data type and default value of the new column you want to add in the table Then write an SQL statement to do it. Your SQL statement must do what you said you want to do in the first part of this question. When you run the SQL, it populates the default value in the newly added column for all existing records in the table. Provide your SQL statement and a screenshot of records in the table using 'SELECT * FROM to show that existing records have default values for the newly added column in your report. 4. Write and run an SQL statement to update the default value of the newly added column to a different value for certain rows based on a condition using any other column. NOTE: You are NOT changing the values of the new column for all records; you are ONLY changing for the records that match your condition. First, provide the following information: What is the condition you want to use to filter the rows? You can use any comparison operator. What is the new value of the newly added column you want to set for those selected rows? Then, write an SQL statement to do it. Your SQL command must do what you said you want to do in the first part of this question. When you run the SQL, it changes the value of the newly added column for the records matching the condition from the default value to the new value. FOM Provide your SQL statement and a screenshot of records in the table using 'SELECT ;' to show that the value of newly added column is successfully updated for records matching the condition in your report. 5. In this section, you are required to write SQL queries to interact with the database you implemented. Answer each SQL question in this section with the following: First you provide what you want to do Provide an SQL statement to do what you want to do and provide a screenshot of results after successful execution of the SQL command a) Write an SQL query to demonstrate the use of SELECT with INNER JOIN and ORDER BY. b) Write an SQL query to demonstrate the use of SELECT with WHERE and IN. c) Write an SQL query to demonstrate the use of at least one DATE function. d) Write an SQL statement to create a VIEW using a SELECT statement with a JOIN. Provide the statement to create the VIEW you want and demonstrate the output of the VIEW using 'SELECT * FROM ;'. 1. Write SQL statements to create all tables in the final normalized ERD Use appropriate data types and required constraints for attributes. Ensure all relationships and relationship constraints are maintained. Note that the order of table creation can be important when tables have Foreign Keys. Provide SQL statements to create all tables in your report. 2. Insert at least FIVE records in each table using SQL. Provide all SQL statements used to insert data and screenshots of results of 'SELECT * FROM ;' for each table in your report. 3. Write and run an SQL statement to add one additional column (attribute) in any one of the existing tables with a default value. First, provide the following information: Name of table you want to add a new column Name, data type and default value of the new column you want to add in the table Then write an SQL statement to do it. Your SQL statement must do what you said you want to do in the first part of this question. When you run the SQL, it populates the default value in the newly added column for all existing records in the table. Provide your SQL statement and a screenshot of records in the table using 'SELECT * FROM to show that existing records have default values for the newly added column in your report. 4. Write and run an SQL statement to update the default value of the newly added column to a different value for certain rows based on a condition using any other column. NOTE: You are NOT changing the values of the new column for all records; you are ONLY changing for the records that match your condition. First, provide the following information: What is the condition you want to use to filter the rows? You can use any comparison operator. What is the new value of the newly added column you want to set for those selected rows? Then, write an SQL statement to do it. Your SQL command must do what you said you want to do in the first part of this question. When you run the SQL, it changes the value of the newly added column for the records matching the condition from the default value to the new value. FOM Provide your SQL statement and a screenshot of records in the table using 'SELECT ;' to show that the value of newly added column is successfully updated for records matching the condition in your report. 5. In this section, you are required to write SQL queries to interact with the database you implemented. Answer each SQL question in this section with the following: First you provide what you want to do Provide an SQL statement to do what you want to do and provide a screenshot of results after successful execution of the SQL command a) Write an SQL query to demonstrate the use of SELECT with INNER JOIN and ORDER BY. b) Write an SQL query to demonstrate the use of SELECT with WHERE and IN. c) Write an SQL query to demonstrate the use of at least one DATE function. d) Write an SQL statement to create a VIEW using a SELECT statement with a JOIN. Provide the statement to create the VIEW you want and demonstrate the output of the VIEW using 'SELECT * FROM ;'.
Expert Answer:
Answer rating: 100% (QA)
Task 1 Creation of all table in mysql create database Homework Help use Homework Help create table CUSTOMER clientid int clientname varchar255 clientaddress varchar255 clientphone varchar255 clientema... View the full answer
Related Book For
Modern Database Management
ISBN: 978-0133544619
12th edition
Authors: Jeff Hoffer, Ramesh Venkataraman, Heikki Topi
Posted Date:
Students also viewed these databases questions
-
Differentiate between the following terms. a. Depreciation and depletion. b. Revenue and capital expenditure
-
ConAir (CA) commercial airlines company requires you experience in database design and have approached you to assist in the design of their new airline database system. They urgently need to have...
-
There is an XYZ Company, which purchases some parts from vendors to produce some products. It has several departments, marketing sites, and parts supply vendors in the company. 1) For each...
-
Listing 8.4 checks whether a solution is valid by checking whether every number is valid in the board. Rewrite the program by checking whether every row, every column, and every small box has the...
-
The insurance company where you work is planning to raise all premiums for health-care coverage. Your boss has asked you to read a draft of her letter to customers, announcing the new, higher rates....
-
Hugh, an investment analyst in Brisbane, is analyzing three separate convertible bonds issued by Woolworths Limited, the Commonwealth Bank of Australia, and BHP Billiton. For each of them, calculate...
-
Question: Eric mails glossy brochures to 25,000 people, offering to sell them a one-month time-share in a stylish apartment in Las Vegas. The brochure depicts an imposing building, an opulent...
-
The Cutting Department of Hong Manufacturing has the following production and cost data for July. Instructions(a) Determine the equivalent units of production for (1) Materials and (2) Conversion...
-
A firm owns six identical restaurants. Each restaurant has a debt-equity ratio of 32 percent and makes interest payments of $44,000 at the end of each year. The cost of the firm's levered equity is...
-
An oil company produces three brands of oils: Regular, Multi grade, and Supreme. Each brand of oil is composed of one or more of four crude stocks, each having a different viscosity index. The...
-
Sunland Sports Equipment Company just reported the following financial information. Cash Assets Accounts receivable Inventories Total current assets Sunland Sports Equipment Company $848,000...
-
1. I have mentioned in class numerous times that in doing criminal defense work the role of the criminal defense investigator is to "maintain the integrity of the system". What do I mean by that?...
-
32. Please write a short paragraph that Applies the principles of biopsychology to Describe how the structure of neurons and of neural impulses generated and neural transmission at the synapse during...
-
1. What are your perceptions of the private investigations industry now as compared to when you came into the program? 2. Looking back on the introduction to private investigations class, can you...
-
Comparative advertising comprises a sizable percentage of advertising in the United States. What is your opinion about ads that compare the features and benefits of competitive products and services?...
-
https://ethicsunwrapped.utexas.edu/video/penn-state-scandal 1. Joe Paterno became a central figure in this case because he was a beloved and successful football coach. Testimonies have revealed that...
-
Rhea is celebrating her birthday and wants to start saving for anticipated retirement. She has the following years to retirement and retirement spending goals: Years until retirement Amount to...
-
1. Firms may hold financial assets to earn returns. How the firm would classify financial assets? What treatment will such financial assets get in the financial statements in accordance with US GAAP...
-
Which tutors, by name, are available to tutor? Write the SQL command.
-
Describe some field-level and record-level data trans-formations that often occur during the ETL process for loading a data warehouse.
-
What do you need to do if a tutor signs up and wants to tutor in both reading and math? Draw the new ERD, create new relations, and write any SQL statements that would be needed to handle this...
-
Identify and explain factors that should keep the quality of audits high even though advertising and tender ing are allowed.
-
Why do you think the rules of conduct of the profes sional accounting bodies which pertain to public accounting ban contingent fees for audits?
-
Each of the following scenarios involves a possible violation of the rules of conduct discussed in the chapter. Indicate whether each is a violation and explain why if you think it is. a. John Brown...
Study smarter with the SolutionInn App