1
JASMINE WILSON
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 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
Practice Problems: Joining Data from Multiple Tables
Before 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.
1. Write a SQL code to join the tables using the WHERE statement. Explain what the query is
intended to do in a complete, coherent sentence with no SQL terminology.
AIT524
This study source was downloaded by 100000757387545 from CourseHero.com on 03-02-2023 23:27:49 GMT -06:00
https://www.coursehero.com/file/162073570/M6-1-Practice-Problems-AIT524-JASMINE-WILSON-1docx/
2
JASMINE WILSON
List the Title, ISBM, PUBID, and AUTHORID from the books category joined with the table
bookauthor where the ISBN from both tables was ‘0132149871’
2. Repeat Problem 1 above but remove the WHERE statement. What happened? Why?
When I removed the where statement, it listed the Title, ISBN, PUBID, AUTHORID, and
CATEGORY of all books.
AIT524
This study source was downloaded by 100000757387545 from CourseHero.com on 03-02-2023 23:27:49 GMT -06:00
https://www.coursehero.com/file/162073570/M6-1-Practice-Problems-AIT524-JASMINE-WILSON-1docx/
3
JASMINE WILSON
3. 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.
List the TITLE, ISBN, PUBID, AUTHORID and CATEGORY of all books where the AUTHORID
from the BOOKAUTHOR table is equal to the AUTHORID from the AUTHOR table and the
ISBN is equal to 8843172113.
4. 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.
List the TITLE, ISBN, PUBID, CATEGORY and COST from books that cost greater than 50
dollaes abd where the customer number ends in 05.
AIT524
This study source was downloaded by 100000757387545 from CourseHero.com on 03-02-2023 23:27:49 GMT -06:00
https://www.coursehero.com/file/162073570/M6-1-Practice-Problems-AIT524-JASMINE-WILSON-1docx/
4
JASMINE WILSON
5. Write an SQL query to join two tables using JOIN…ON and JOIN…USING (use SELECT *). Did
you receive the same output? Why?
I chose to use a SQL query that listed all data from the table “orderitems’ joined with the table
where the order# from the orderitems table was equal to the order# from the orders table. I got
the same result using both Join…On and Join using as both queries produce the same results.
JOIN…ON
AIT524
This study source was downloaded by 100000757387545 from CourseHero.com on 03-02-2023 23:27:49 GMT -06:00
https://www.coursehero.com/file/162073570/M6-1-Practice-Problems-AIT524-JASMINE-WILSON-1docx/
5
JASMINE WILSON
JOIN USING
AIT524
This study source was downloaded by 100000757387545 from CourseHero.com on 03-02-2023 23:27:49 GMT -06:00
https://www.coursehero.com/file/162073570/M6-1-Practice-Problems-AIT524-JASMINE-WILSON-1docx/
6
JASMINE WILSON
AIT524
This study source was downloaded by 100000757387545 from CourseHero.com on 03-02-2023 23:27:49 GMT -06:00
https://www.coursehero.com/file/162073570/M6-1-Practice-Problems-AIT524-JASMINE-WILSON-1docx/
7
JASMINE WILSON
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.
AIT524
This study source was downloaded by 100000757387545 from CourseHero.com on 03-02-2023 23:27:49 GMT -06:00
https://www.coursehero.com/file/162073570/M6-1-Practice-Problems-AIT524-JASMINE-WILSON-1docx/
Powered by TCPDF (www.tcpdf.org)
