Once a table is created using Data Definition Language (DDL), the data can be inserted, viewed,...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Once a table is created using Data Definition Language (DDL), the data can be inserted, viewed, updated and deleted using Data Manipulation Language (DML) as part of SQL. The way to retrieve the records from a table is through the use of the SELECT statement, referred to as a query. This statement allows you choose the columns of interest and the rows based on a various criteria. But first, you must be able to add data to the tables using an INSERT statement. Records in a table may need to be modified or removed. The UPDATE statement is used to change the values in records; the DELETE statement is used to remove records. It is important to note that the WHERE clause plays an important role in these statements. Like the SELECT statement, the WHERE clause filters the rows to which the statement applies. Without the WHERE clause, the UPDATE or DELETE applies to all rows. Please note, referential integrity is maintained by these statements. For example, if there are sales records that relate to a customer, the customer record cannot be deleted. The related sales records must be deleted before the customer recorded can be deleted. Course Learning Outcomes Evaluated in this Assignment Demonstrate the use of the SELECT statement using SQL's Data Manipulation Language (DML) to query data in a database. Demonstrate the use of the INSERT statement using DML to add data to a database. Demonstrate the use of the DELETE statement using DML to delete data from a database. Demonstrate the use of the UPDATE statement using DML to change the data in a database. Instructions You are writing an application that keeps track of a membership list. You need to record the member's first name, last name, phone number, email address, city and date of birth. Each member is assigned a unique ID. 1. Write a single script to: a. Create a database called Membership b. Create a table that meets the requirements above 2. Write a script to add 10 members to the table. When creating this test list, make sure of the following: a. At least 2 members live in the same city b. At least 2 members have an email address in the same domain (the part after the @ sign). For example, at least 2 members with @gmail.com in their email address) The ID, first name, last name and date of birth are required. c. 3. Write a script containing queries to get: a. All rows and all columns b. All rows showing last name, first name and date of birth, in that column order. All rows showing city, last name and first name, in that column order, and sorted by city. All rows showing date of birth, last name, first name and email address, in that column order, and sorted by date of birth in descending order. e. All rows showing city, last name and first name where the city name is the one that you chose to be duplicated (item 2a above). f. All rows showing the last name, first name and email address where the email domain was duplicated (item 2b above). g. All rows showing the last name, city, email address and date of birth, in that order, sorted by date of birth, and where the city is the one where the city is duplicated (item 2a above) C. d. 4. Write a script that will: a. Change the phone number of one specific member (your choice) b. Change the city name of those members where the city is the same (item 2a above) C. Remove the email address of those members where the domain is duplicated (item 2b above) d. Delete the record of a specific member (your choice) e. Delete the records where the last name of the members end with 'son', even if you have no records that meet this criteria. 5. In all of the above queries, assume that you may have more data in the database, not just the sample data you provided. 6. Submit all four scripts. You may choose the name of the scripts, but use something descriptive. Once a table is created using Data Definition Language (DDL), the data can be inserted, viewed, updated and deleted using Data Manipulation Language (DML) as part of SQL. The way to retrieve the records from a table is through the use of the SELECT statement, referred to as a query. This statement allows you choose the columns of interest and the rows based on a various criteria. But first, you must be able to add data to the tables using an INSERT statement. Records in a table may need to be modified or removed. The UPDATE statement is used to change the values in records; the DELETE statement is used to remove records. It is important to note that the WHERE clause plays an important role in these statements. Like the SELECT statement, the WHERE clause filters the rows to which the statement applies. Without the WHERE clause, the UPDATE or DELETE applies to all rows. Please note, referential integrity is maintained by these statements. For example, if there are sales records that relate to a customer, the customer record cannot be deleted. The related sales records must be deleted before the customer recorded can be deleted. Course Learning Outcomes Evaluated in this Assignment Demonstrate the use of the SELECT statement using SQL's Data Manipulation Language (DML) to query data in a database. Demonstrate the use of the INSERT statement using DML to add data to a database. Demonstrate the use of the DELETE statement using DML to delete data from a database. Demonstrate the use of the UPDATE statement using DML to change the data in a database. Instructions You are writing an application that keeps track of a membership list. You need to record the member's first name, last name, phone number, email address, city and date of birth. Each member is assigned a unique ID. 1. Write a single script to: a. Create a database called Membership b. Create a table that meets the requirements above 2. Write a script to add 10 members to the table. When creating this test list, make sure of the following: a. At least 2 members live in the same city b. At least 2 members have an email address in the same domain (the part after the @ sign). For example, at least 2 members with @gmail.com in their email address) The ID, first name, last name and date of birth are required. c. 3. Write a script containing queries to get: a. All rows and all columns b. All rows showing last name, first name and date of birth, in that column order. All rows showing city, last name and first name, in that column order, and sorted by city. All rows showing date of birth, last name, first name and email address, in that column order, and sorted by date of birth in descending order. e. All rows showing city, last name and first name where the city name is the one that you chose to be duplicated (item 2a above). f. All rows showing the last name, first name and email address where the email domain was duplicated (item 2b above). g. All rows showing the last name, city, email address and date of birth, in that order, sorted by date of birth, and where the city is the one where the city is duplicated (item 2a above) C. d. 4. Write a script that will: a. Change the phone number of one specific member (your choice) b. Change the city name of those members where the city is the same (item 2a above) C. Remove the email address of those members where the domain is duplicated (item 2b above) d. Delete the record of a specific member (your choice) e. Delete the records where the last name of the members end with 'son', even if you have no records that meet this criteria. 5. In all of the above queries, assume that you may have more data in the database, not just the sample data you provided. 6. Submit all four scripts. You may choose the name of the scripts, but use something descriptive.
Expert Answer:
Related Book For
Data Modeling and Database Design
ISBN: 978-1285085258
2nd edition
Authors: Narayan S. Umanath, Richard W. Scammel
Posted Date:
Students also viewed these databases questions
-
NASA astronomers are planning on sending a space probe to orbit around an exoplanet and then eventually return to earth. The space probe will orbit the exoplanet 3 0 0 miles above its surface. The...
-
This data set represents the web server hit logs for a single website for a single day. You're going to do some analysis of the data, but first you need to normalize it properly -- which requires...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Raheem & Co. purchased a fixed asset on 1.4.2018 for Rs.2,50,000. Depreciation is to be provided @10% annually according to the Straight-line method. The books are closed on 31st March every year....
-
A firm reported the following income statement (amounts in millions): Net sales ......................................................... $496 Cost of goods sold...
-
The differential equations of a control system are as follows: For what value of " \(a\) " the system is stable? 1) \(a2) \(a>-2\) 3) \(-2 4) \(1 xi(t) = axi(t) +xz(t)+u(t) x2(t) = -2x1(t) +x2(t)...
-
Convert 17.03347 to fraction form.
-
To facilitate a move toward JIT production, AB Company is considering a change in its plant layout. The plant controller, Anita Bentley, has been asked to evaluate the costs and benefits of the...
-
Jamie Lee Jackson, age 26, is in her last semester of college and is waiting for graduation day, just around the corner! It is the time of year again when Jamie Lee must file her annual income taxes....
-
PART-4 PART-5 PART-6 Jaguar Plastics Company has been operating for three years. At December 31 of last year, the accounting records reflected the following: Cash Investments (short-term) Accounts...
-
2. Consider HCI gas. Find its molar entropy using ideal gas and Van der Waals partition functions. Compare these values against experimental results. You may need to look up values for the moment of...
-
Discuss the role of HR departments in managing employer-employee relations. How do HR professionals contribute to maintaining a harmonious work environment?
-
Lisa is considering upgrading the breakfast service to attract more business and increase prices. This will cost an additional $14 for food costs per person per night. She feels she can increase the...
-
Assessment Task 2: Case Study The resources you will need for this assessment are: 1. Access to the internet2. Your Learning Guide3. PC, Word Processor, Printer4. Relevant legislation, regulations,...
-
Grand Garden is a hotel with 150 suites. Its regular suite price is $250 per night per suite. The hotel's total cost per night is $140 per suite and consists of the following. Variable cost Fixed...
-
Discuss the role of linear programming in procurement and supplier selection decisions to achieve cost savings and improve supply chain performance.
-
What is the current price of a telecommunication company's stock if the current dividend is 0 . 8 0 $ , the expected constant growth rate in dividends is 6 % and the required return is 9 % ?
-
Following is the current balance sheet for a local partnership of doctors: The following questions represent independent situations: a. E is going to invest enough money in this partnership to...
-
Consider the relation schema STUDENT (Sid, Shoe_size, Marital_status) F: fd1: Sid Shoe_size;...................fd2: Sid Marital_status a. Does STUDENT possess a multi-valued dependency? Why or why...
-
What is the difference between a loss-join decomposition and a lossless-join decomposition?
-
How do cardinality constraints and participation constraints relate to the notions of total and partial participation?
-
Home prices have been on the rise since 2000, driven by low interest rates that make mortgages affordable.
-
On January 1, 2016, Safetyway Group,a LO1 U.S. company, formed a Swiss subsidiary, TEurope AG. The subsidiary issued all of its currently outstanding common stock on that date. Selected accounts from...
-
Your neighbors are thinking of selling their two-bedroom home in the suburbs and moving to a two-bedroom condominium. Help them make this decision.
Study smarter with the SolutionInn App