Write an SQL statement to show which customers boughtwhich items, and include any items that...

Write an SQL statement to show which customers bought which items, and include any items that have not been sold. Include LastName, FirstName, InvoiceNumber, InvoiceDate,ItemNumber, ItemDescription, ArtistLastName, and ArtistFirstName. Use a join using JOIN ON syntax, and sort the results by ArtistLastName and ArtistFirstName in ascending order (Hint: you have to use a RIGHT JOIN on the last step).

Solutions

Expert Solution

I have assumed that "CustomerData" contains the data related to
Customer i.e. CustomerData(FirstName,LastName,InvoiceNumber,ItemNumber)
and "ArtistData" contains the data related to Artist i.e.
ArtistData(ItemNumber,ItemDescription,ArtistLastName,ArtistFirstName)

NOTE: Please update the table name if it is different.

SQL QUERY

SELECT c.FirstName,c.LastName,c.InvoiceNumber,a.ItemNumber,
a.ItemDescription,a.ArtistLastName,a.ArtistFirstName
FROM CustomerData c
RIGHT JOIN
ArtistData a
ON a.ItemNumber = c.ItemNumber
ORDER BY a.ArtistFirstName ASC

venereology answered 2 years ago

Next > < Previous

Related Solutions

Write a SQL statement which joins the parts table with the supplier table and lists the...

Write a SQL statement which joins the parts table with the supplier table and lists the part_name, supplier_name for all parts in the part table. The supplier_id column in the suppliers table is the primary key in the suppliers table, and this key has been exported to the parts table where it is a foreign key. You should use an inner join for this query. Write a SQL statement which joins the parts table with the suppliers table and lists...

1. Write a SQL statement which joins the rider_student table with the rider_major table and lists...

1. Write a SQL statement which joins the rider_student table with the rider_major table and lists the rider student name and the name of the major (major_name) and the description of the major for which they are currently assigned. (You may use the SQL 'join' subclause, or simply express the join as part of the 'where' clause by indicating that you only want records where the primary key of the child table, rider_major, equals the corresponding foreign key of the...

Write the following SQL queries and show the corresponding output of the DBMS: 1) Write an...

Write the following SQL queries and show the corresponding output of the DBMS: 1) Write an SQL statement to display all the information of all Nobel Laureate winners. 2) Write an SQL statement to display the string "Hello, World!". 3) Write an SQL query to display the result of the following expression: 2 * 14 +76. 4) Write an SQL statement to display the winner and category of all Laureate winners. 5) Write an SQL query to find the winner(s)...

PL/SQL Write a PL/SQL block, using a Case Statement that prints a student’s letter grade...

PL/SQL Write a PL/SQL block, using a Case Statement that prints a student’s letter grade based on the value stored in a variable called grade. Use the ACC grading system described in the course syllabus to create the block and set the initial value of grade as 95. Use only one print statement and no logical operators in your code. Assume a grade can exceed 100, but it can’t be negative. Grade Scale: Grade Scale:...

I need to show this using SQL: Credit card numbers should include asterisks in place of...

I need to show this using SQL: Credit card numbers should include asterisks in place of all digits preceding the last four digits, which will be left visible, regardless of credit card length

Write and run SQL statements to complete the following tasks Show the details of the employees...

Write and run SQL statements to complete the following tasks Show the details of the employees who are located in area code 901 and their manager employee number is 108. Show the details of the employees who are also mangers. Show the details of the customers whose balance is greater than 220 but less than 500. Show the details of the customers whose balance is highest. Show customer 10014’s name and the product’s descriptions which he/she purchased and the number...

Write a SQL script to add another table to your database. Include these fields in your...

Write a SQL script to add another table to your database. Include these fields in your Product table: Field Name Description Data Type Sample Value ProductID Product ID integer 5 ProductName Product Name varchar(50) candle Description Product Description varchar(255) Bee’s wax candle picUrl Filename of the product’s picture varchar(50) candle.gif Price Product Price decimal 10.99 ProductID should be the Primary Key. It is an auto-increment field. The Price field stores prices to 7 significant digits and to 2...

In SQL we are working with functions 1. Write a SELECT statement that returns these columns...

In SQL we are working with functions 1. Write a SELECT statement that returns these columns from the Instructors table: a. The AnnualSalary column b. A column named MonthlySalary that is the result of dividing the AnnualSalary column by 12 c. A column named MonthlySalaryRounded that calculates the monthly salary and then uses the ROUND function to round the result to 2 decimal places 2. Write a SELECT statement that returns these columns from the Students table: a. The EnrollmentDate...

Financial Statement Items Identify the financial statement (or statements) in which each of the following items...

Financial Statement Items Identify the financial statement (or statements) in which each of the following items would appear: income statement (IS), statement of stockholders’ equity (SSC), balance sheet (BS), or statement of cash flow (SCF). Assets Revenue Cashflow from investing activities Stockholders’ equity Expenses Net change in cash Net Income Liabilities

Please implement this in Oracle sql 2.) Write a SELECT statement that answers this question: What...

Please implement this in Oracle sql 2.) Write a SELECT statement that answers this question: What is the total amount ordered for each product? Return these columns: The product name from the Products table The total amount for each product in the Order_Items (Hint: You can calculate the total amount by subtracting the discount amount from the item price and then multiplying it by the quantity) Use the ROLLUP operator to include a row that gives the grand total.

Subjects