Practice Problems Instructions: When working with a database, it is important to know what tables it contains. It is also essential to have information about the structure of each table, including column names, data types, constraints, etc. to retrieve data. This kind of information (and much more!) can be found in the data dictionary, which is a collection of read-only tables that contain metadata, or data about the database. See more on thedata dictionary here. Use the following commands to obtain information about the tables in the database: select table_name from user_tables; /* to display a list of tables */ select * from all_tab_columns where table_name='table_name'; /* to display the structure of a table */ select * from table_name; /* to display the content of a table */ In the commands above, substitute table_name with the actual name of the table. If 'table_name' is enclosed in single quotation marks, the name of the table must be entered in capital letters. Instructions:For each problemcomplete the following Write and execute an SQL query in Oracle Live SQL or SQL*Plus Execute the following command: select sysdate, 'your name' from dual; where your_name is substituted with your name Take a screenshot that includes both SQL statements and all results Copy and paste the screenshot into a MS Word file containing your solutions Practice Problems: Display all data contained in one of the tables in the JustLee Books database. Retrieve two columns from one of the tables in the JustLee Books database. Retrieve several columns from one of the tables in the JustLee Books database. Use an alias for one of the columns. Retrieve several columns from one of the tables in the JustLee Books database. Use an alias that contains blank space(s) for one of the columns. Find a table in the JustLe Books database that contains duplicate values in one of the columns. Retrieve that column but list each value only once. Retrieve data from two character columns from one of the tables in the JustLee Books database and display the result in a single column with a descriptive heading. Values in the output should be separated by a comma and a blank space. Find a table in the JustLe Books database that contains numerical values in one of the columns. Retrieve that column and perform a basic arithmetic operation on data. Find a table in the JustLe Books database that contains numerical values in one of the columns. Retrieve that column and increase each value by 10%. Display the original value and the new value with descriptive headings in the output. Create a mailing list of all customers stored in the CUSTOMERS table. Create a mailing list from the CUSTOMERS table. The mailing list should display the name, address, city, state, and zip code for each customer. Each customer’s name should be listed in order of last name followed by first name, separated with a comma, and have the column header “Name.” The city and state should be listed as one column of output, with the values separated by a comma and the column header “Location.” Calculate a list of the percentage of profit generated by each book in the BOOKS table. To determine the percentage of profit for a particular item, subtract the item’s cost from the retail price to calculate the dollar amount of profit, and then divide the profit by the item’s cost. The solution is then multiplied by 100 to determine the profit percentage for each book. Use a SELECT statement to display each book’s title and percentage of profit. For the column displaying the percentage markup, use “Profit %” as the column heading. Assignment SubmissionSave all your solution screenshots in a Word file, type your name and course number in that file, name it M4_Practice_Problems.

Practice Problems Instructions:When working with a database, it is important to know what tables it contains. It is also essential to have information about the structure of each table, including...

Practice Problems Instructions:

When working with a database, it is important to know what tables it contains. It is also essential to have information about the structure of each table, including column names, data types, constraints, etc. to retrieve data. This kind of information (and much more!) can be found in the data dictionary, which is a collection of read-only tables that contain metadata, or data about the database.

See more on thedata dictionary here.

Use the following commands to obtain information about the tables in the database:

select table_name from user_tables; /* to display a list of tables */

select * from all_tab_columns where table_name='table_name'; /* to display the structure of a table */

select * from table_name; /* to display the content of a table */

In the commands above, substitute table_name with the actual name of the table. If 'table_name' is enclosed in single quotation marks, the name of the table must be entered in capital letters.

Instructions:For each problemcomplete the following

Write and execute an SQL query in Oracle Live SQL or SQL*Plus

Execute the following command: select sysdate, 'your name' from dual; where your_name is substituted with your name

Take a screenshot that includes both SQL statements and all results

Copy and paste the screenshot into a MS Word file containing your solutions

Practice Problems:

1. Display all data contained in one of the tables in the JustLee Books database.
2. Retrieve two columns from one of the tables in the JustLee Books database.
3. Retrieve several columns from one of the tables in the JustLee Books database. Use an alias for one of the columns.
4. Retrieve several columns from one of the tables in the JustLee Books database. Use an alias that contains blank space(s) for one of the columns.
5. Find a table in the JustLe Books database that contains duplicate values in one of the columns. Retrieve that column but list each value only once.
6. Retrieve data from two character columns from one of the tables in the JustLee Books database and display the result in a single column with a descriptive heading. Values in the output should be separated by a comma and a blank space.
7. Find a table in the JustLe Books database that contains numerical values in one of the columns. Retrieve that column and perform a basic arithmetic operation on data.
8. Find a table in the JustLe Books database that contains numerical values in one of the columns. Retrieve that column and increase each value by 10%. Display the original value and the new value with descriptive headings in the output.
9. Create a mailing list of all customers stored in the CUSTOMERS table. Create a mailing list from the CUSTOMERS table. The mailing list should display the name, address, city, state, and zip code for each customer. Each customer’s name should be listed in order of last name followed by first name, separated with a comma, and have the column header “Name.” The city and state should be listed as one column of output, with the values separated by a comma and the column header “Location.”
10. Calculate a list of the percentage of profit generated by each book in the BOOKS table. To determine the percentage of profit for a particular item, subtract the item’s cost from the retail price to calculate the dollar amount of profit, and then divide the profit by the item’s cost. The solution is then multiplied by 100 to determine the profit percentage for each book. Use a SELECT statement to display each book’s title and percentage of profit. For the column displaying the percentage markup, use “Profit %” as the column heading.

Assignment Submission

Save all your solution screenshots in a Word file, type your name and course number in that file, name it M4_Practice_Problems.

assignment-description-11375-prz1aa50-42vx13vt.docx 117375-k35yda2s-qzkfu0q1.zip live-sql-oracle-tvxhhovg-4qphir4r.docx 117375-sql-qd1mocfl-svbvlwuq.zip jldbdrop-cidb4of5-qgysjj5r.sql

Answered 18 days AfterMar 03, 2023

Answer To: Practice Problems Instructions:When working with a database, it is important to know what tables...

Sanskar answered on Mar 07 2023

41 Votes

SOLUTION.PDF

Practice Problems Instructions:When working with a database, it is important to know what tables it contains. It is also essential to have information about the structure of each table, including...

Assignment Submission

Answer To: Practice Problems Instructions:When working with a database, it is important to know what tables...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment