![]() The ORDER BY clause sorts the result by the count of orders in descending order so customers with the most orders come first. The GROUP BY clause groups the results by both cust_id and cust_name to provide a count for each customer. For each customer, it counts the number of associated orders ( order_num entries). ORDER BY order_count DESC This query works by joining the Customers and Orders tables together where the cust_id fields match. LEFT JOIN Orders O ON C.cust_id = O.cust_id ![]() Here's an example of how you might do this: SELECT C.cust_id, C.cust_name, COUNT(O.order_num) AS order_count This was my next prompt: Return a list of customers with a count of orders for each.ĬhatGPT worked for a few seconds and returned this: To return a list of customers along with a count of their orders, you could perform a SQL query that JOINs the Customers and Orders tables on the cust_id field, and then groups by the customer name or ID while counting the number of orders. Now to see how well ChatGPT generates SQL. It helps that the column name conventions I used were consistent, but still, wow! I didn’t define any of them, it worked it which columns in which tables were related to others. It worked out how Orders relate to Customers, and Products to OrderItems, and so on. Seriously, that is mind-boggling! Not only did ChatGPT understand the tables I enumerated, but it also figured out all the table relationships. Take a deep breath, and … read that response. Please let me know what specific query or operation you would like to perform on this database structure. It also has the quantity of each product and the item_price in the order. * OrderItems table: This table details the items in each order, identified by order_num and prod_id. Each product is linked to a vendor through vend_id. Each product has a unique prod_id, a prod_name, and a prod_price. * Products table: It has product information. * Vendors table: It holds vendor information, each identified by a unique vend_id and a vend_name. * Orders table: It records individual orders, each associated with a unique order_num, order_date, and linked to a customer through cust_id. * Customers table: It has customer information, each identified by a unique cust_id and associated with a cust_name. These tables are Customers, Orders, Vendors, Products, and OrderItems. So, what did ChatGPT do with this info? This is how it responded: It appears you are providing the structure of multiple database tables in a relational database, possibly SQL. (There are actually more columns in my tables for brevity’s sake I opted to just list the ones needed here). OrderItems table contains columns order_num, prod_id, quantity, item_price.Īs you can see, I simply told ChatGPT the table names and the columns in each. Products table contains columns prod_id, vend_id, prod_name, prod_price. Vendors table contains columns vend_id, vend_name. ![]() Orders table contains columns order_num, order_date, cust_id. Here’s the prompt I used: Customers table contains columns cust_id, cust_name. The first thing you need to do is tell ChatGPT about your tables. Obviously, you’ll need to log in to ChatGPT (creating an account if you’ve not yet done so). And so, in this post I’ll demonstrate how to take advantage of this magical capability using tables (well, a subset of the tables) and examples from my Sams Teach Yourself SQL in 10 Minutes. ![]() One of ChatGPT’s many neat tricks is its ability to generate SQL statements you simply tell ChatGPT about your tables, verify that it understands the structure properly, and then ask for what you’d like returned, all in simple English. Yep, it’s a rabbit hole, and one we’re all getting a kick out of. Feeding it prompts and watching it spit out quality articles, recipes, travel itineraries, code examples, and more is fun and addictive. ChatGPT is all the rage right now, and understandably so.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |