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:
![1. Write SQL statements to create all tables in the final normalized ERD Use appropriate data types and](https://dsd5zvtm8ll6.cloudfront.net/si.experts.images/questions/2023/09/651283573f43f_1695712087140.jpg)
![4. Write and run an SQL statement to update the default value of the newly added column to a different value](https://dsd5zvtm8ll6.cloudfront.net/si.experts.images/questions/2023/09/6512835f61142_1695712095487.jpg)
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...
-
Create a physical database design for the logical design of the Dream Home case study (described in Chapter 16) based on the DBMS that you have access to.
-
What role does indexing play in enhancing database performance, and how do various indexing techniques, such as B-trees and hash indexes, differ in terms of their effectiveness and applicability in...
-
M/s. XYZ Co. has a single process. Work-in-progress (opening) = 8,000 units Rs Cost: Materials 29,600 Wages 6,600 Overheads 5,800 During the period, the input was 32,000 units. Additional cost data...
-
Using the following data and the straight-line method of depreciation, compute depreciation expense and make the necessary journal entry to record depreciation expense for the first year. Cost of...
-
The Gold Standard was established to create confidence in paper money and make international trade viable. Select one: True False
-
Their 1.5 inch, 90-degree copper elbow (see below) is a very popular item in the plumbing line that comes packaged in 10 elbows per box. The box costs about $65 to Western, and Western sells it at...
-
Which of the following is an idea set forth by W. Edwards Deming? Multiple Choice Mistakes and defects are a natural part of production and cannot be avoided. The organizational environment should...
-
3. Given the continuous beam shown below, which span or spans should be loaded with a uniform distributed load to produce a maximum moment at support B? (5 points) SPAN 1 SPAN 2 SPAN 3 A B D 20 ft...
-
Complete the following writing assignment: Analyze the attached 10_pages. Write_about them, summarize what you read, and connect it to personal experiences. CHAPTER 8 Anxiety Disorders DAVID P....
-
As a manager of an airline company you want to learn the average weight of luggages checked in on a flight. From a sample of 1 6 luggages, you find the average to be 2 6 kg and the standard deviation...
-
What is the Manufacturing Cycle Efficiency? 11. Use High-Low to find the fixed and variable costs. Machine Month Costs Hours 12345678 $1,730,890 15,820 $1,753,860 13,980 $1,562,890 11,550 4...
-
Jimmy Padilla purchased a gravel pit in the current year for $944,232 and estimates that there will be a residual value in the land of $36,404 once resource extraction is complete. He estimates that...
-
Table B shows a balance sheet for a certain company. All quantities shown are in millions of dollars. What is the company's quick ratio for 2020? Note: Express your answers in strictly numerical...
-
A number of years ago the United Food and Commercial Workers Union organized 800 workers of the 1035 employees at one of the Wilson Brothers food operations in Toronto, Ontario. The employees include...
-
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...
-
If the one-year discount factor is 0.8333, what is the discount rate per year? a. 10% b. 20% c. 30% d. None of the above
-
What is the FV of $2,500 invested for three years at an interest rate of 3% per annum compounded semi-annually? a. $5,783 b. $4,429 c. $4,419 d. $2,734
-
Hilary has a 20-year mortgage with a fixed monthly payment. Which of the following statements regarding Hilarys mortgage is most correct? a. The monthly payments will decline over time. b. The...
![Mobile App Logo](https://dsd5zvtm8ll6.cloudfront.net/includes/images/mobile/finalLogo.png)
Study smarter with the SolutionInn App