Home

M6.1 Practice Problems
Please select the link above to submit this week’s assignment.Instructions
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 the data 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.Important Note:For the practice problems below, follow examples shown in the PowerPoint presentations and textbooks but do not copy their solutions unless it is the only possible answer to the problem.General Instructions:The practice problems are arranged in order of increasing difficulty – the last several problems might present the most challenge. Students are expected to work out and submit the solutions to at least 5 problems. If you have previous experience in SQL you can select the 5 most challenging problems though it is still recommended to complete all problems. This will help you be better prepared for the Midterm and Final Exams. Challenge yourself to complete all problems!Instructions: For each problemWrite 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 Word file containing your solutions
Practice Problems: Joining Data from Multiple TablesBefore starting these problems, update the JustLee Books database by executing the JLDB_Build_9.sql script, otherwise, your solutions might not work!Note: In this module, it is assumed that students already have JLDB installed so they need to run JLDB_Build_9.sql to add the three tables to the existing DB. If you do not have it installed, you need to run JLDB_Build_8.sql first and then JLDB_Build_9.sql.Write a SQL code to join two tables using the WHERE statement. Explain what the query is intended to do in a complete, coherent sentence with no SQL terminology.
Repeat Problem 1 above but remove the WHERE statement. What happened? Why?
Write an SQL query to join three related tables using JOIN … ON in the JustLee Books. Select four-five columns that you would like to display in the output (do not use SELECT *). Include one condition with a special comparison operator. Explain what the query is intended to do in a complete, coherent sentence with no SQL terminology.
Write an SQL query to join three related tables using JOIN … USING in the JustLee Books. Select four-five columns that you would like to display in the output (do not use SELECT *). Include one condition with a special comparison operator and a logical operator. Do not use the same set of tables as before. Explain what the query is intended to do in a complete, coherent sentence with no SQL terminology.
Write an SQL query to join two tables using JOIN…ON and JOIN…USING (use SELECT *). Did you receive the same output? Why?
Write an SQL query that lists customer’s last name and first name if he/she does not have the same address and shipping street and whose orders include books that have the letter “a“ in the title. In addition, consider only the following authors: Peterson, Adams, and Austin. Explain why some customers’ names appear multiple times.
Write an SQL query to create a list of employees and their managers. Use columns’ aliases to clearly identify mangers and employees in the output.
Modify Problem 7 above to list all customers along with those who referred them (if any).
Write an SQL query to display a list of books in the BOOKS table. If a book has been ordered by a customer, also list the corresponding order number and the state in which the customer resides.
Use set operators UNION, INION ALL, INTERSECT, and MINUS to combine the results of two queries. Explain what queries are intended to do in complete, coherent sentences with no SQL terminology.
Assignment Submission
Save all your solution screenshots in a Word file, type your name and course number in that file, name it M6_1_Practice_Problems.Due Date
This assignment is due by Sunday, 11:59 pm ET.
M6.1 Assignment – Joining Data from Multiple Tables
Please select the link above to submit this week’s assignment.Instructions
For each problem: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 Word file containing your solutions
Assignment:For the database you designed in the previous assignments, complete the following problems:Write a SQL code to join two tables using the WHERE statement. Explain what the query is intended to do.
Repeat problem 1 using the JOIN … USING keywords.
Repeat problem 1 using the JOIN … ON keywords.
Repeat problem 1 but add at least two conditions in the WHERE statement and use an arithmetic and a special operator. Use logical operators to combine multiple conditions. Explain what the query is intended to do.
Repeat problem 2 but add two more conditions in the WHERE statement and use an arithmetic and a special operator that are different from the ones you used in the previous query. Use logical operators to combine multiple conditions. Explain what the query is intended to do.
-9 Repeat problems 1-4 above to join three tables.
10. Write a SQL query that joins four tables using any type of join and uses both an arithmetic and special operator. Explain what the query is intended to do.
Assignment Submission
Paste the solutions in a Word file, type your name and course number in that file, name it M6_1_Assignment_Joining_Data. Due Date
This assignment is due by Sunday, 11:59 pm ET.
M6.2 Practice Problems
Please select the link above to submit this week’s assignment.Instructions
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 the data 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.Important Note:For the practice problems below, follow examples shown in the PowerPoint presentations and textbooks but do not copy their solutions unless it is the only possible answer to the problem.General Instructions:The practice problems are arranged in order of increasing difficulty – the last several problems in each section might present the most challenge. Students are expected to work out and submit the solutions to at least 5 problems in each section. If you have previous experience in SQL you can select the 5 most challenging problems in each section though it is still recommended to complete all problems. This will help you be better prepared for the Midterm and Final Exams. Challenge yourself to complete all problems!Instructions: For each problemWrite 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 Word file containing your solutions
Practice Problems: Selecting Single-Row Functions
Before starting these problems, update the JustLee Books database by executing the JLDB_Build_10.sql script, otherwise your solutions might not work! The JLDB_Build_10.sql script will add new tables to the existing JustLee Books Database. This script does not re-create the original tables. If the JustLee Books Database does not exist in your schema, you also need to run the JLDB_Build_8.sql script to create it.Write an SQL query to display a text string ‘your_first_name your_last_name’ that represents your full name. Then, use the INSTR function to determine the position of the single blank space in the text string ‘your_first_name your_last_name’. Use the DUAL table.
Use the SUBSTR function to extract a substring that represents your last name from the text string ‘your_first_name your_last_name’. Assume you know the number of characters you need to extract.
Use the INSTR and SUBSTR functions to extract a substring that represents your first name from the text string ‘your_first_name your_last_name’. Assume you do not know the number of characters to extract.
Use the INSTR, SUBSTR, and LENGTH functions to extract a substring that represents your last name from the text string ‘your_first_name your_last_name’. Assume you do not know the number of characters you need to extract.
Retrieve a list of all book titles along with authors’ names displayed as a single string ‘Last name, First name’ using the CONCAT function. In addition, also display the percentage of markup for each book as a whole number followed by a percent sign (for example, .2793 = 28%). The percentage of markup should reflect the difference between the retail and cost amount as a percent of the cost.
Using today’s date, determine the age (in months) of each book that JustLee sells. Make sure only whole months are displayed; ignore any portions of months. Display the book title, publication date, current date, and age.
Create a list of all customer numbers along with text indicating whether the customer has been referred by another customer. Display the text “NOT REFERRED” if the customer wasn’t referred to JustLee Books by another customer or “REFERRED” if the customer was referred.
Determine the amount of total profit generated by the book purchased on order 1002. Display the book title and profit. The profit should be formatted to display a dollar sign and two decimal places. Take into account that the customer might not pay the full retail price, and each item ordered can involve multiple copies.
Management is proposing to increase the price of each book. The amount of the increase will be based on each book’s category, according to the following scale: Computer books, 10%; Fitness books, 15%; Self-Help books, 25%; all other categories, 3%. Create a list that displays each book’s title, category, current retail price, and revised retail price. The prices should be displayed with two decimal places. The column headings for the output should be as follows: Title, Category, Current Price, and Revised Price. Sort the results by category. If there’s more than one book in a category, a secondary sort should be performed on the book’s title.
Write a complex SQL query that is based on three or more tables and includes multiple nested single-row functions. Explain in a complete, coherent sentence what they query is intended to do.
Practice Problems: Group Functions
Before starting these problems, update the JustLee Books database by executing the JLDB_Build_11.sql script, otherwise your solutions might not work! The JLDB_Build_11.sql script will add new tables to the existing JustLee Books Database. This script does not re-create the original tables. If the JustLee Books Database does not exist in your schema, you also need to run the JLDB_Build_8.sql script to create it.What’s the retail price of the most expensive book written by Sam Smith?
Determine the total profit generated by sales per customer. Note: Quantity should be reflected in the total profit calculation.
Modify problem 2 to include only those customers who live in FL.
Modify problem 3 to display the results with profit greater than $50.
Determine the average retail price of books by publisher name and category. Include only the categories Children and Computer and the groups with an average retail price greater than $50.
Assignment Submission
Save all your solution screenshots in a Word file, type your name and course number in that file, name it M6_2_Practice_Problems.Due Date
This assignment is due by Sunday, 11:59 pm ET.
M6.2 Assignment – Selected Single-Row Functions, Group Functions
Please select the link above to submit this week’s assignment.Instructions
For each problem: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 Word file containing your solutions
Assignment:For the database you designed in the previous assignments, complete the following problems:1 – 4. Write four SQL queries to join two or more tables where each query contains multiple nested single-row functions. Make sure to use different functions in each query. Explain what each query is intended to do.5. Write a query which is based on a single table and includes the GROUP B statement. Explain what the query is supposed to do.6. Repeat problem 5 but add the WHERE statement. Explain what the query is supposed to do.7. Repeat problem 6 but add the HAVING statement. Explain what the query is supposed to do.8. Write a query which is based on three tables and includes the GROUP BY, WHERE and HAVING statements. Explain what the query is supposed to do.9. Write an SQL query that uses the GROUP BY ROLL statement. Explain what the query is supposed to do.10. Write an SQL query that uses the GROUP BY CUBE statement. Explain what the query is supposed to do..Note: Include your ERD/EERD from your previous assignment.Assignment Submission
Due Date
This assignment is due by Sunday, 11:59 pm ET.