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...
How many customers placed orders between 1-Apr-03 and 31-May-03? COUNT 12 a.    SELECT COUNT(DISTINCT order_no)...
How many customers placed orders between 1-Apr-03 and 31-May-03? COUNT 12 a.    SELECT COUNT(DISTINCT order_no) AS count       FROM Orders       WHERE order_date BETWEEN '1-APR-03' AND '31-May-03'; b.    SELECT COUNT(DISTINCT order_no) AS count       FROM Orders       WHERE order_date BETWEEN '4/1/03' AND '5/31/03'; c.    a and b are correct d.    a and b are incorrect Question 2 (2 points) . Find product no. , brand and unit price of the products whose unit price is greater than the unit...
1. Write a SELECT statement to count SKUs for each department, i.e., a table of two...
1. Write a SELECT statement to count SKUs for each department, i.e., a table of two columns, departments and their SKU counts. use cape_codd; select department, count(*) from sku_data group by department ; 2. List buyers who is responsible for at least two SKUs, in three columns, department, buyer, sku count. Sort buyers by their department names in ascending order. select department, buyer, count(sku) from sku_data group by buyer ; 3. Write a SQL statement which returns all the orders....
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...
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...
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...
Assignment Instructions (MY WORK IN MYSQLBENCH)- Follow instructions in class to download and build a database...
Assignment Instructions (MY WORK IN MYSQLBENCH)- Follow instructions in class to download and build a database in your DBMS. Write and demonstrate queries (in-class) that do the following: Download the appropriate attached SQL file for your DBMS. Load the file into your database design tool and run it to build the Pine Valley Furniture Company database. This is the database that is used in the textbook examples for both Chapters 5 and 6. Write a query to show the price...
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...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT