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...
-
A mass-loaded piston/cylinder containing air is at 300 kPa, 17C with a volume of 0.25 m3, while at the stops V 1 m3. An air line, 500 kPa, 600 K, is connected by a valve that...
-
Gear-A has 12 teeth and Gear-B has 36 teeth. Knowing that a larger gear will always travel slower than a smaller gear, if Gear-A is traveling at 8 revolutions per minute, how fast is Gear B traveling
-
Why is the range value affected by outliers?
-
a. Calculate and display the first 50 autocorrelations for the four data series in the accompanying table, labeled A, B, C, and D; consider each of the four data series to be a quarterly time series....
-
Below are extracted information for XYZ Limited for its first - year account ending 3 1 December 2 0 2 1 : Equipment ( NCA ) 2 0 , 0 0 0 Utilities Expense 2 , 1 0 0 Cash 5 , 0 0 0 Accounts Receivable...
-
The activities needed to develop a new software application are listed in the following table with the activity times and the order in which the activities need to be performed: Activity Duration...
-
Find the number of vertices in the hypercube Q n ., where n 0.
-
LCD Corporation purchased residential real property for \(\$ 385,000\) (exclusive of land cost) and placed it in service on August 16, 2018. Compute MACRS depreciation on the property for 2018 and...
-
Discuss the pros and cons of the following on the performance of service systems: a. Increasing self-service b. Allowing employees to exercise judgment in meeting customer needs c. Introducing an...
-
Sinco Company had \(\$ 300,000\) of income from business operations and \(\$ 700,000\) of allowable expenses. It also received \(\$ 20,000\) in dividends from a domestic corporation in which it owns...
-
What are examples of times you would need to solve for the cash flow in an annuity?
-
Kendall Jones is in the process of opening a new pet store. The store will sell pet food, pet supplies, and a select variety of pets (excluding dogs and cats). It will also offer grooming services...
-
SQL: _____ operator is used for appending two strings. a) & b) % c) || d) _
-
Write a function that reads a Float24_t value: Float24_t float24_read(void) A legitimate float24 value string is of the form: "mantissabexponent" where the mantissa (m) and the exponent (e) may have...
-
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...
-
A flow of nitrogen is discharged from a duct with \(M_{2}=0.85, T_{2}=500^{\circ} \mathrm{R}\), and \(p_{2}=28\) psia. The temperature at the inlet is \(560^{\circ} \mathrm{R}\). Compute the pressure...
-
Consider the flow of a perfect gas along a Fanno line. Show that the pressure at the * reference state is given by the relation 1/2 m 2RT, P Ayge(y+1)
-
Air enters a duct with a mass flow rate of \(35 \mathrm{lbm} / \mathrm{sec}\) at \(T_{1}=520^{\circ} \mathrm{R}\) and \(p_{1}=20 \mathrm{psia}\). The duct is square and has an area of \(0.64...
Study smarter with the SolutionInn App