Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Use SQL commands to Create a table, insert data into that table and Select data from that table in SQLite. You will need to

Use SQL commands to Create a table, insert data into that table and Select data from that table in SQLite. EmployeelD 100 200 300 INFO222 WEEK 3 Assignment SSN fName 111-11-0607 John 333-22-0607 John 444-44-0607 INFO321 WEEK 3 Assignment Common SQL problems A) Table and field names are case sensitive, and should not

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.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

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 ... blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Concepts of Database Management

Authors: Philip J. Pratt, Mary Z. Last

8th edition

1285427106, 978-1285427102

More Books

Students also viewed these Databases questions

Question

Define the term user view as it applies to database design.

Answered: 1 week ago