LargeCo is planning a new promotion in Alabama (AL) and wants to know about the largest purchases made by customers in that state. Write a query to display the customer code, customer first name, last name, full address, invoice date, and invoice total of the largest purchase made by each customer in Alabama. Be certain to include any customers in Alabama who have never made a purchase; their invoice dates should be NULL and the invoice totals should display as 0.
It is used to retrieve information from the table or database. The syntax for the “SELECT” statement is given below:
SELECT * FROM table_Name;
The “inner join” keyword is to select the matching records from two tables. The syntax of “inner join” is as follows:
SELECT column_name FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;
“WHERE” statement is used limit the number of rows. For example: Consider a table “FTable” that has two columns named “FruitName” and “Color”. “WHERE” clause is used when there is a need to display the entire FruitName whose color is Red.
SELECT * FROM FTable WHERE color = 'red';
When the above statement is executed, red colored fruits get displayed.
This function is used to get the maximum value from the column.
SELECT MAX(column_Name) FROM table_Name;
UNION set operator:
The UNION set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains unique values.
Syntax: QUERY UNION QUERY;
ORDER BY Clause:
SQL contains “ORDER BY” clause in order to sort rows. The values get sorted in ascending as well as descending order. The keyword used to sort values in ascending order is “ASC” and for descending order is “DESC”. By default, it sorts values by ascending order.
SELECT column_Name1, column_Name2 FROM table_Name ORDER BY column_Name2;
The SQL code to find out largest purchases of customer in AL is given below:
SELECT c.cust_code, c.cust_fname, c.cust_lname, c.cust_street, c.cust_city, c.cust_state, c.cust_zip, inv_date, inv_total AS "Largest Invoice" FROM lgcustomer C INNER JOIN lginvoice i ON c.cust_code = i.cust_code WHERE cust_state = 'AL' AND inv_total = (SELECT Max (inv_total) FROM lginvoice i2 WHERE i2.cust_code = c.cust_code)
SELECT cust_code, cust_fname, cust_lname, cust_street, cust_city, cust_state, cust_zip, NULL, 0 FROM lgcustomer WHERE cust_state = 'AL' AND cust_code NOT IN (SELECT cust_code FROM lginvoice) ORDER BY cust_lname, cust_fname;
Bartleby provides explanations to thousands of textbook problems written by our experts, many with advanced degrees!Get Started
Fundamentals of Information Systems
Fundamentals of Information Systems
Principles of Information Security (MindTap Course List)
A Guide to SQL
Database Systems: Design, Implementation, & Management
Fundamentals of Geotechnical Engineering (MindTap Course List)
Precision Machining Technology (MindTap Course List)
Principles of Information Systems (MindTap Course List)
Cornerstones of Financial Accounting
Automotive Technology: A Systems Approach (MindTap Course List)
Principles of Geotechnical Engineering (MindTap Course List)
Engineering Fundamentals: An Introduction to Engineering (MindTap Course List)
Mechanics of Materials (MindTap Course List)
Management Of Information Security
Systems Analysis and Design (Shelly Cashman Series) (MindTap Course List)
Fundamentals of Chemical Engineering Thermodynamics (MindTap Course List)
Solid Waste Engineering
International Edition---engineering Mechanics: Statics, 4th Edition
EBK ELECTRICAL WIRING RESIDENTIAL
Electric Motor Control
Steel Design (Activate Learning with these NEW titles from Engineering!)
A+ Guide to Hardware (Standalone Book) (MindTap Course List)
Enhanced Discovering Computers 2017 (Shelly Cashman Series) (MindTap Course List)
Welding: Principles and Applications (MindTap Course List)
Network+ Guide to Networks (MindTap Course List)