Question

In the northwind database and orders table, select the ShipCountry, ShipVia, and EmployeeID as a group...

In the northwind database and orders table, select the ShipCountry, ShipVia, and EmployeeID as a group and compute the count as Total. The group can only include where the ShipVia is less than the EmployeeID and either ShipCountry does not end in "y" or "%%l" or EmployeeID = 4. The grouping total must be greater than 7 and sort by total descending.

Engineering   Computer-Science   
0 0
Add a commentTranscribed image text
Next >

Homework Answers

Answer #1 
USE northwind; #Selected the database northwind

SELECT COUNT(*) AS 'Total' #Calculating the count and giving it the tab Total

FROM orders #From the table orders

WHERE (ShipVia < EmployeeID) AND (SUBSTRING(ShipCountry, -1) != 'y' OR SUBSTRING(ShipCountry, -3) != '%%l' OR EmployeeID = 4) #The conditions as mentioned in the question

GROUP BY ShipCountry, ShipVia, EmployeeID #Grouped by on the columns mentioned in the question

HAVING Total > 7 #Applying the clause where the count is required to be greater than 7

ORDER BY Total DESC; #Ordered the results by Total column that we created in descending order

There isn't much to explain over here. This entire question is a combination of the basic tools in SQL nothing more than that. If you have any specific doubts do let me know I will add it to the answer. Regards.

0 0
Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
In the movies database and actor table, label all values in the last_name field that start...
In the movies database and actor table, label all values in the last_name field that start with a, b or c as "A,B,C start" or "Other" and name it LastName, and do the same with all the values in the first_name field and name it FirstName. Group by FirstName and LastName, and return the count as total but only return grouping where the FirstName and the Lastname are equal. Order by total desc.
Write a SELECT statement that returns one row for each general ledger account number that contains...
Write a SELECT statement that returns one row for each general ledger account number that contains three columns: The account_description column from the General_Ledger_Accounts table The count of the items in the Invoice_Line_Items table that have the same account_number. The sum of the line_item_amount columns in the Invoice_Line_Items table that have the same account_number Return only those rows where the count of line items is greater than 1. This should return 10 rows. Group the result set by the account_description...
Save your select statements as a script. Place the semicolon at the end of each SQL...
Save your select statements as a script. Place the semicolon at the end of each SQL statement. Please number your select statements from 1 to 8 in your script and comment out each number. Include your name and student number as a comment at the top of your script. The name of the script has to be Assignment1_JohnSmith. Instead of JohnSmith use your First Name and Last Name. Upload your script trough Blackboard. Use SQL Developer to create the My...
Class-In Assignment 3: Chapters 4&5 How to retrieve data from two or more tables Exercises 1....
Class-In Assignment 3: Chapters 4&5 How to retrieve data from two or more tables Exercises 1. Write a SELECT statement that returns all columns from the Vendors table inner-joined with all columns from the Invoices table. 2. Write a SELECT statement that returns four columns: vendor_name vendor_name from the Vendors table invoice_number invoice_number from the Invoices table invoice_date invoice_date from the Invoices table balance_due invoice_total minus payment_total minus credit_total from the Invoices table The result set should have one row...
Which query lists all products with a total quantity sold less than the average quantity sold?...
Which query lists all products with a total quantity sold less than the average quantity sold? Select one: a. SELECT P_CODE, SUM(LINE_UNITS) AS TOTALUNITS FROM LINE GROUP BY P_CODE HAVING SUM(LINE_UNITS) < AVG(LINE_UNITS); b. SELECT P_CODE, AVG(LINE_UNITS) AS TOTALUNITS FROM LINE GROUP BY P_CODE HAVING AVG(LINE_UNITS) < (SELECT AVG(LINE_UNITS) FROM LINE); c. SELECT P_CODE, SUM(LINE_UNITS) AS TOTALUNITS FROM LINE GROUP BY P_CODE HAVING SUM(LINE_UNITS) < (SELECT AVG(LINE_UNITS) FROM LINE); d. SELECT P_CODE, SUM(LINE_UNITS) AS TOTALUNITS FROM LINE GROUP BY P_CODE...
Inventory . Orders Warranty Types You own a small Computer Retailing company that sells computer systems....
Inventory . Orders Warranty Types You own a small Computer Retailing company that sells computer systems. You have created a spreadsheet that stores basic sales information. Inventory: Lists the computer & upgrade costs for each system identified by product number and product name. Order: Lists basic customer information such as, their name, computer purchased, and warranty cost (if chosen) by the customer. Warranty Types: Lists the warranty costs. The cost of the warranty is based on the type of warranty...
The relational schema for the Academics database is as follows: DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode)...
The relational schema for the Academics database is as follows: DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode) ACADEMIC(acnum, deptnum*, famname, givename, initials, title) PAPER(panum, title) AUTHOR(panum*, acnum*) FIELD(fieldnum, id, title) INTEREST(fieldnum*, acnum*, descrip) Some notes on the Academics database: An academic department belongs to one institution (instname) and often has many academics. An academic only works for one department. Research papers (PAPER) are often authored by several academics, and of course an academic often writes several papers (AUTHOR). A research field...
Problem 1 The table in worksheet "Problem 1" is a list of orders from a company...
Problem 1 The table in worksheet "Problem 1" is a list of orders from a company that sells sports products. Each row is an order. Only one type of product is sold in each order, but the quantity and price of each unit vary across orders. Information about each order includes location, customer, segment, product sold, order date, the list price per unit, the price each unit was actually sold in the order (unit price), quantity of units sold, revenue...
Fixed costs include: Select one: a. variable labor expenses. b. output-related energy costs. c. output-related raw...
Fixed costs include: Select one: a. variable labor expenses. b. output-related energy costs. c. output-related raw material costs. d. variable interest costs for borrowed capital. If a total product curve exhibits increasing returns to a variable input, the cost elasticity is: Select one: a. equal to one. b. greater than one. c. unknown, without further information. d. less than one. f the productivity of variable factors is decreasing in the short-run: Select one: a. marginal cost must increase as output...
Complete a Java program named ARMgr that maintains customer accounts receivable in a database. The code...
Complete a Java program named ARMgr that maintains customer accounts receivable in a database. The code to initialize the CustomerAccountsDB database table and add a set of customer accounts is provided. Finish the code in these 3 methods in CustomerAccountDB.java to update or query the database: -purchase(double amountOfPurchase) -payment(double amountOfPayment) -getCustomerName() Hint: For getCustomerName(), look at the getAccountBalance() method to see an example of querying data from the database. For the purchase() and payment() methods, look at the addCustomerAccount() method...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT