Create a SQL query based on tblProducts and tblTransactionDetails that will display all the fields from tblProducts and the TransactionID and Quantity fields from tblTransactionDetails. Use a join to include all products, regardless of whether or not they have been purchased. Sort in ascending order by Brand. Save your query as qryTransactionDetails_initialLastn ame.
TransactionID | ProductID | Quantity |
---|---|---|
1 | VZH159 | 2 |
2 | EPP101 | 2 |
3 | EOL672 | 3 |
4 | VZH159 | 2 |
5 | ZLE580 | 2 |
TransactionID | CustomerID | EmployeeID | TransactionDate | MethodOfPayment |
---|---|---|---|---|
1 | 11 | 00078528 | 1/1/2015 | Check |
2 | 10 | 00078116 | 1/2/2015 | Credit |
3 | 6 | 00078116 | 1/3/2015 | Cash |
4 | 22 | 0007135 | 1/3/2015 | Cash |
5 | 9 | 00078116 | 1/4/2015 | Credit |
6 | 21 | 00016812 | 1/5/2015 | Cash |
7 | 18 | 00010099 | 1/6/2015 | Cash |
8 | 16 | 00016812 | 1/7/2015 | Cash |
9 | 2 | 00016356 | 1/8/2015 | Cash |
10 | 4 | 00078528 | 1/8/2015 | Cash |
11 | 10 | 00036096 | 1/8/2015 | Check |
12 | 13 | 00016812 | 1/9/2015 | Credit |
13 | 3 | 00066224 | 1/10/2015 | Credit |
14 | 2 | 00074823 | 1/11/2015 | Check |
15 | 26 | 00032877 | 1/12/2015 | Cash |
16 | 24 | 00016812 | 1/14/2015 | Check |
17 | 3 | 00078528 | 1/14/2015 | Credit |
18 | 7 | 00016812 | 1/14/2015 | Check |
19 | 6 | 00016812 | 1/14/2015 | Cash |
20 | 9 | 00066224 | 1/15/2015 | Credit |
21 | 16 | 00016812 | 1/16/2015 | Check |
22 | 21 | 00078528 | 1/16/2015 | Check |
23 | 5 | 0007135 | 1/17/2015 | Cash |
Query => select p.*, tT.TransactionID,tT.Quantity from tblProducts t LEFT JOIN tblTransactionDetails tT ON t.ProductID = tT.ProductID order by p.brand asc
explanation : Left join is instrumental in finding all the rows from the first table in Join irrespective of whether any connecting row is there in 2nd table.
Create a SQL query based on tblProducts and tblTransactionDetails that will display all the fields from...
Display all customers. If a customer has placed any orders, also display the highest..... Tables: CREATE TABLE Sales.Customers ( CustomerId INT NOT NULL IDENTITY, CustomerName NVARCHAR(50) NOT NULL, StreetAddress NVARCHAR(50) NULL, City NVARCHAR(20) NULL, [State] NVARCHAR(20) NULL, PostalCode NVARCHAR(10) NULL, Country NVARCHAR(20) NULL, Contact NVARCHAR(50) NULL, Email NVARCHAR(50) NULL, CONSTRAINT PK_Customers PRIMARY KEY(CustomerId) ); CREATE TABLE HR.Employees ( EmployeeId INT NOT NULL IDENTITY, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, BirthDate DATE NOT NULL, HireDate DATE NOT NULL, HomeAddress...
Still working on SQL, but now trying to run some commands to pull data from the databases. The last 3 statements Im getting errors but I am not sure how to fix them. What do I need to do differently to those statements to make it work? create table Employee( EmpID char(10), Name varchar2(50), Salery char(10), Address varchar2(50), HireDate date, Phone char(10), Primary key(EmpID)) ; create table Inventory( ProductID char(10), ProductName varchar2(50), UnitPrice char(10), CurrentInventory char(10), MonthlySales char(10), PrecentOfPrice char(10),...
(IN SQL) /* 6. Create a list of all the products (prod_id and name) if all the following are true (7 records) Supplierid = 2, 5, 16, 8, or 9 Categoryid = 1, 2, or 4 Unitprice > 15.00 */ /* 7. Create a list of all the products (prod_id and name) if all the following are true (11 records) Supplierid = 2, 5, 16, 8, or 9 AND Categoryid...
Create a query in microsoft access based on tblTransactions and tblProducts that calculates total sales volume for each ProductType during the first quarter of 2018. The query should calculate the total quantity of items sold by applying the Sum aggregate function to the Quantity field. Group the results by ProductType. tblTransactions 8/6/2019 ProductID TransID CustID RepID TransactionDate Quantity 12 Nguyen 13 Nguyen 14 1/1/2018 Jenkins Johnson 1/1/2018 17 3 20 Branson Jenkins 1/6/2018 7 9 1/6/2018 21 Peters Corez 12 Johnson...
I need help in SQL management studio please. Question: Create the same query as query 5, but only include artists if they have 'Pop' Genre. Previous question: Create a list of all composers and artists in your database. Include two columns in your record set (the names of the returned columns are in brackets): 'Composer/Artist Name' and 'Type' (which will indicate whether your results are from the composer or artist table). Order by 'Composer/Artist Name'. This is the answer for...
I need to create a C++ program to simulate a Round Robin Tournament. For example: if a user enters 4, a 4 team tournament would output: 1 2 3 4 2 1 4 3 3 4 1 2 4 3 2 1 My goal is to create this program using a two dimentional array, however I am unsure how to go about doing initializing everything. How do I write a constructor for this? The following is the class declaration I...
SQL – Microsoft Access -------------------------------------------------------------------------- Display the average trip distance for each pilot and use GROUP BY ------------------------------------------------------------------------------ .82714 .6 9 7 4 1 3 6 5 2 54 200 49 3 5 0 3 2 3 24100。40 2 9 9 。 6 6 1 33 4 10 12 2 1 1 3 13 14 14 14 4 15 16 16 16 17 10 1 2 3 4 5 1 11
Which orders have been placed since October 20, 2010? I need SQL query OrderlD, OrderDate, CustomerlD 1001 21/Oct/10,1 1002, 21/Oct/10,8 1003, '22/Oct/10, 15 1004, '22/Oct/10, 5 1005, 24/Oct/10, 3 1006, '24/Oct/10, 2 1007, '27/Oct/10,11 1008, 30/Oct/10, 12 1009, '05/Nov/10,4 1010, '0S/Nov/10 1 End Table, Cherry,175, 1 2 Coffee Table, Natural Ash: 200,2 3 Computer Desk, Natural Ash 3752 Natural Ash. 375 Entertainment Center Natural Maple,650, 3 Desk, 'white 6, '8-Drawer Ash, 750,2 7. Dining Tabla Natural Ash,800, 2 'Computer Desk,...
Please access to TUN SQL/Assistant using the default database = db_pvfc12_std 1.- Run a query to retrieve all the data field from db_pvfc12_std in table CUSTOMER_T 2.- Run a query to retrieve record --> CustomerID = 11, How many records did you get and why? 3.- Run a query to retrieve all the data field from table Order_T Once retrieved: 3.1.- Please name the fields showing: Primary Key, Foreign Key and other fields 3.2.- What type of...
Create sql query command for the following. Modify your query and produce the following results. Customer #000000001 1 1428873.61 9 Customer #000000002 2 1156504.92 10 Customer #000000004 4 4134567.39 31 Customer#000000005 5 1084042.74 9 Customer #000000007 7 3922020.98 24 Column 1 is the customer Name, Column 2, is the customer key, column 3 is the total of the customer orders ($$) and column 4 is the number of orders.