Skip to main content

Engineering Computer Science Oracle 12c: SQL

To write a query to display the list of all books customers which are purchased by Jake Lucas. Required SQL query: SELECT DISTINCT c.firstname||’’||c.lastname "NAME", b.title FROM customers c INNER JOIN orders o ON c.customer# = 0.customer# INNER JOIN orderitems oi ON oi.order# = o.order# LEFT JOIN books b ON b.ISBN = oi.ISBN WHERE (c.firstname||’’||c.lastname) = 'JAKE LUCAS’; Explanation: SELECT keyword will allow the user to retrieve the required values. By providing the table name, FROM can be used to search the database and return the desired data. WHERE is used to specify a condition based on which the data is to be retrieved. AND is used to specify that both conditions must be satisfied. ON clause is used in joining the columns who are having different names. The JOIN Clause is a special keyword in SQL (structured Query Language), which is used to combine the rows from two or more tables in the database. INNER JOIN selects records that have the same(matching) values which are present in both tables. LEFT JOIN selects all records from the table listed on the left side of the join, even when no match is found with the table (another one) in the join operation. In the above SQL query, it selects the Customer name and the title. The distinct keyword is used to avoid duplicate records. Perform the search using the customer name instead of the customer#. Sample output: CUSTOMER NAME TITLE JAKE LUCAS PAINLESS CHILD-REARING JAKE LUCAS HOW TO MANAGE THE MANAGER

To write a query to display the list of all books customers which are purchased by Jake Lucas. Required SQL query: SELECT DISTINCT c.firstname||’’||c.lastname "NAME", b.title FROM customers c INNER JOIN orders o ON c.customer# = 0.customer# INNER JOIN orderitems oi ON oi.order# = o.order# LEFT JOIN books b ON b.ISBN = oi.ISBN WHERE (c.firstname||’’||c.lastname) = 'JAKE LUCAS’; Explanation: SELECT keyword will allow the user to retrieve the required values. By providing the table name, FROM can be used to search the database and return the desired data. WHERE is used to specify a condition based on which the data is to be retrieved. AND is used to specify that both conditions must be satisfied. ON clause is used in joining the columns who are having different names. The JOIN Clause is a special keyword in SQL (structured Query Language), which is used to combine the rows from two or more tables in the database. INNER JOIN selects records that have the same(matching) values which are present in both tables. LEFT JOIN selects all records from the table listed on the left side of the join, even when no match is found with the table (another one) in the join operation. In the above SQL query, it selects the Customer name and the title. The distinct keyword is used to avoid duplicate records. Perform the search using the customer name instead of the customer#. Sample output: CUSTOMER NAME TITLE JAKE LUCAS PAINLESS CHILD-REARING JAKE LUCAS HOW TO MANAGE THE MANAGER

Oracle 12c: SQL

Oracle 12c: SQL

3rd Edition

ISBN: 9781305251038

Author: Joan Casteel

Publisher: Cengage Learning

See similar textbooks

Concept explainers

Basic SQL Queries

Structured Query Language is the full form of SQL. It is a high-level language used to communicate with a relational database. It is used for creating, storing and accessing contents of a relational database. SQL is used to write the queries which are ex…

Expert Solution & Answer

Want to see the full answer?

Check out a sample textbook solution

Blurred answer

Chapter 9, Problem 3HOA

Chapter 9, Problem 5HOA

Students have asked these similar questions

The query should list all employees whose Position begins with the word greenhouse and whose weekly hours are greater than or equal to 20. Hint: Include a wildcard character in the criterion for the Position field. in ACCESS

WEEK 9 ASSIGNMENT#6 Part II: Complete the following exercise: Using a subquery, select student first name where the student is in the student course table. Using a subquery, select the course id where the average GPA is 2. Using a subquery, select the course id where the average GPA is greater than 2. Using a subquery, select student first name where the student is not in the student course t

4. Determine which books customer Jake Lucas has purchased. Perform the search using thecustomer name, not the customer number. If he has purchased multiple copies of the samebook, unduplicate the results.

Chapter 9 Solutions

Oracle 12c: SQL

Ch. 9 - Prob. 1HOA Ch. 9 - Prob. 2HOA Ch. 9 - Prob. 3HOA Ch. 9 - Prob. 4HOA Ch. 9 - Prob. 5HOA Ch. 9 - Prob. 6HOA Ch. 9 - Prob. 7HOA Ch. 9 - Prob. 8HOA Ch. 9 - Prob. 9HOA Ch. 9 - An EMPLOYEES table was added to the JustLee Books...

Knowledge Booster

Background pattern image

Computer Science

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions

SEE MORE QUESTIONS

Recommended textbooks for you

Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr

Text book image

Oracle 12c: SQL

Computer Science

ISBN:9781305251038

Author:Joan Casteel

Publisher:Cengage Learning

Text book image

Computer Science

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Course Technology Ptr

SEE MORE TEXTBOOKS