Use SQL commands to Create a table, insert data into that table and Select data from...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Use SQL commands to Create a table, insert data into that table and Select data from that table in SQLite. You will need to formulate the commands and complete the tasks below. SQL Commands (100%) Read all instructions before beginning It is recommended that you submit and execute one SQL command at a time. For each task below: a) Label each Task to match the instruction b) Construct the SQL command (include the text of each command in your formal submission) c) Paste the SQL command into SQLite and hit enter to execute the command d) Capture the entire response display as a screen shot and paste into the word document e) Each command must be proceeded by a comment /* Your name and date */ Task 1 - Use the SQL Create command to create a table within the Chinook database with the below specifications The table name should be your last name plus a unique number Use the following to name each field (column), with a matching data type EmployeeID 10 numeric digits (no decimal points) SSN up to 11 characters (SSN = social security number) fName up to 12 characters IName up to 12 characters Position up to 12 characters Salary 10 maximum numeric digits (including 2 decimal digits) PhoneNum 12 characters. Task 2 - Use the Insert command to insert the records / data below: Submit one record at a time (easier to correct any mistakes) EmployeelD 100 200 300 INFO222 WEEK 3 Assignment SSN fName 111-11-0607 John 333-22-0607 John 444-44-0607 Sally IName Position Salary PhoneNum Smith Manager 35000.75 800-350-0000 Associate 25000.00 202-999-0000 Jones Smith Manager 46500.00 303-999-0000 100, 111-11-0607, John, Smith, Manager, 35000.75, 800-350-0000 200, 333-22-0607, John, Jones, Associate, 25000.00, 202-999-0000 300, 444-44-0607, Sally, Smith, Manager, 46500.00, 303-999-0000 Insert one more record - you determine the content, EXCEPT it must use your name for the first name (fName) and last name IName), and a Salary of 36900. Task 3 Use the Select * command to list all fields for all records in the table. Task 4 Use a Select command to list the EmployeeID, fName, IName, Position, and Salary fields for all employees with a last name of Smith. Task 5 Use a Select command to list the EmployeeID, fName, IName, Position, Salary and PhoneNum fields for all employees with a salary greater than 20000. Task 6 Use a Select command to list the EmployeeID, fName, IName, Position and Salary fields for all Employees with a Salary greater than 30000 and less than 45000. Submit this part of the assignment in a word document. Name your document Last_Name_SQL(i.e. Smith_SQL). A description of the most common SQL problems can be found at the end of these instructions. Contact the instructor with any questions. INFO321 WEEK 3 Assignment Common SQL problems A) Table and field names are case sensitive, and should not include spaces. B) Another common error is "Invalid character used in command" - this means you have used an illegal character. Character fields should be enclosed in single quote marks. Sometimes an incorrect single quote is generated by Word (this seems to be associated with the keyboard used). You can determine the "value" of a character in Word by selecting it and then pressing the ALT + X (the letter x) keys. That will display the value of the character. It should have a value of 0027. If you have the wrong value-go to the SQL Web site an copy one of the single quotes from an "Example" and use that in your rd document C) Declaring a field to be numeric, and using single quotes in a Select statement (for the field's value) seems to generate a Something Went Wrong error message, or even worse it may blank the field. I suggest you double check whether you are using character or numeric fields - and ensure the Select statement field is consistent with the Insert statement. D) The Insert command does not display the contents of the record entered into the table. The site will return a "SQL Command executed" message. This is "not" an error Use Select * From YourTableName to display all records in the table. E) If the above does not localize the problem I suggest starting again: use the DROP TABLE command to delete your table. Then use the Create command to reestablish the table. Then use an INSERT command to load only the first field. If that works then add the second field name (and a value), and so on. Each INSERT will add a new record - which has values only in the specified fields. Once you master the Insert command, then DROP the table and use the INSERT command to load a record with all fields and values. Use SQL commands to Create a table, insert data into that table and Select data from that table in SQLite. You will need to formulate the commands and complete the tasks below. SQL Commands (100%) Read all instructions before beginning It is recommended that you submit and execute one SQL command at a time. For each task below: a) Label each Task to match the instruction b) Construct the SQL command (include the text of each command in your formal submission) c) Paste the SQL command into SQLite and hit enter to execute the command d) Capture the entire response display as a screen shot and paste into the word document e) Each command must be proceeded by a comment /* Your name and date */ Task 1 - Use the SQL Create command to create a table within the Chinook database with the below specifications The table name should be your last name plus a unique number Use the following to name each field (column), with a matching data type EmployeeID 10 numeric digits (no decimal points) SSN up to 11 characters (SSN = social security number) fName up to 12 characters IName up to 12 characters Position up to 12 characters Salary 10 maximum numeric digits (including 2 decimal digits) PhoneNum 12 characters. Task 2 - Use the Insert command to insert the records / data below: Submit one record at a time (easier to correct any mistakes) EmployeelD 100 200 300 INFO222 WEEK 3 Assignment SSN fName 111-11-0607 John 333-22-0607 John 444-44-0607 Sally IName Position Salary PhoneNum Smith Manager 35000.75 800-350-0000 Associate 25000.00 202-999-0000 Jones Smith Manager 46500.00 303-999-0000 100, 111-11-0607, John, Smith, Manager, 35000.75, 800-350-0000 200, 333-22-0607, John, Jones, Associate, 25000.00, 202-999-0000 300, 444-44-0607, Sally, Smith, Manager, 46500.00, 303-999-0000 Insert one more record - you determine the content, EXCEPT it must use your name for the first name (fName) and last name IName), and a Salary of 36900. Task 3 Use the Select * command to list all fields for all records in the table. Task 4 Use a Select command to list the EmployeeID, fName, IName, Position, and Salary fields for all employees with a last name of Smith. Task 5 Use a Select command to list the EmployeeID, fName, IName, Position, Salary and PhoneNum fields for all employees with a salary greater than 20000. Task 6 Use a Select command to list the EmployeeID, fName, IName, Position and Salary fields for all Employees with a Salary greater than 30000 and less than 45000. Submit this part of the assignment in a word document. Name your document Last_Name_SQL(i.e. Smith_SQL). A description of the most common SQL problems can be found at the end of these instructions. Contact the instructor with any questions. INFO321 WEEK 3 Assignment Common SQL problems A) Table and field names are case sensitive, and should not include spaces. B) Another common error is "Invalid character used in command" - this means you have used an illegal character. Character fields should be enclosed in single quote marks. Sometimes an incorrect single quote is generated by Word (this seems to be associated with the keyboard used). You can determine the "value" of a character in Word by selecting it and then pressing the ALT + X (the letter x) keys. That will display the value of the character. It should have a value of 0027. If you have the wrong value-go to the SQL Web site an copy one of the single quotes from an "Example" and use that in your rd document C) Declaring a field to be numeric, and using single quotes in a Select statement (for the field's value) seems to generate a Something Went Wrong error message, or even worse it may blank the field. I suggest you double check whether you are using character or numeric fields - and ensure the Select statement field is consistent with the Insert statement. D) The Insert command does not display the contents of the record entered into the table. The site will return a "SQL Command executed" message. This is "not" an error Use Select * From YourTableName to display all records in the table. E) If the above does not localize the problem I suggest starting again: use the DROP TABLE command to delete your table. Then use the Create command to reestablish the table. Then use an INSERT command to load only the first field. If that works then add the second field name (and a value), and so on. Each INSERT will add a new record - which has values only in the specified fields. Once you master the Insert command, then DROP the table and use the INSERT command to load a record with all fields and values.
Expert Answer:
Answer rating: 100% (QA)
Certainly lets go through the assignments step by step Task 1 involves creating a new table in the Chinook database Assuming the unique number is 123 ... View the full answer
Related Book For
Concepts of Database Management
ISBN: 978-1285427102
8th edition
Authors: Philip J. Pratt, Mary Z. Last
Posted Date:
Students also viewed these databases questions
-
Kingbird Inc. manufactures cycling equipment. Recently, the company's vice-president of operations has requested construction of a new plant to meet the increasing demand for the company's bikes....
-
You are required to write a Python program that will manage character (heroes and villain) information. Character (hero and villain) information will be stored in a text file that will be read in...
-
You have been employed to create program to assist the manager of a coffee shop. You decide to make a prototype application in Java to show what you can do, and to be able to ask the manager what...
-
Assume that self-esteem measurements are not affected by an interaction between subject self-esteem and target self-esteem. Is there sufficient evidence to support the claim that the category of the...
-
Consider the power plant as described in the previous problem. a. Determine the quality of the steam leaving the reactor. b. What is the power to the pump that feeds water to the reactor?
-
Carry out a ratio analysis of Safe and Sure plc, using the financial statements set out in Appendix I (at the end of this book) and applying the method of analysis set out in section13.6. Making a...
-
Consider a market with two securities, one of them risk free with rate of return \(R\), and three future states. Suppose that the risky security has a present value \(S_{1}\) and can take three...
-
Owner Shan Lo is considering franchising her Happy Wok restaurant concept. She believes people will pay $5 for a large bowl of noodles. Variable costs are $1.50 a bowl. Lo estimates monthly fixed...
-
Why is Microsoft Corp bond a good investment? The Bond has a maturity date of 6/1/2039 and a coupon rate of 5.2000% and is At the current price of 114.234 USD, this equates to a 4.16 percent annual...
-
Name the following alcohols according to the IUPAC nomenclature system. Indicate stereochemistry (if any) and label the hydroxy groups as primary, secondary, or tertiary. OH Br OH (a) CH;CH,CHCH; (b)...
-
Executive Chalk is financed solely by common stock and has outstanding 45 million shares with a market price of $50 a share. It now announces that it intends to issue $750 million of debt and to use...
-
Assume that the spot JPY/USD exchange rate is 120 Yen per 1 USD, and that the continuously compounded interest rate in the United States and Japan are \(1 \%\) and \(2 \%\), respectively. (a) What is...
-
What problem does the separate share rule alleviate?
-
Roy Rainer will pay his wife, Mae, alimony according to the following schedule: Roy and Mae were divorced on January 6, 2018 For the first three years of the agreement, what portions are excludable...
-
Beneficiaries of estates and complex trusts are subject to the tier system of taxation. Explain what is meant by a first- and second- tier beneficiary.
-
What three functions does distributable net income serve in fiduciary taxation?
-
The two wattmeter method is applicable for only single phase systems only star connected systems both star connected and delta connected systems only delta connected systems
-
On January 1, 2017, McIlroy, Inc., acquired a 60 percent interest in the common stock of Stinson, Inc., for $340,200. Stinson's book value on that date consisted of common stock of $100,000 and...
-
What is an un-normalized relation? Is it a relation according to the definition of the word relation?
-
What is a primary key? What is the primary key for each table in the Colonial Adventure Tours database shown in Figures 1-15 through 1-19 in Chapter 1? Figure 1-15 GuideNum LastName FirstName Address...
-
Define the term user view as it applies to database design.
-
A company expects to receive which of the following benefits when it uses its budgeting process? a. The planning required to develop the budget helps managers foresee and avoid potential problems...
-
A direct materials budget would be used in which of the following types of companies? a. Service b. Merchandising c. Manufacturing d. All of the above
-
A disadvantage to merchants from accepting debit and credit cards would be a. lost sales. b. transaction fees. c. bounced checks. d. longer time to receive cash.
Study smarter with the SolutionInn App