SQLQuery Help

juannntrujillo 21 Reputation points
2020-10-11T00:51:16.73+00:00

Hi! Can anyone help me with the query to get this information from the database on the URL bellow?

  • Names of customers and the items they bought whose price is less than 600.
  • City of the client and name of the article of all the orders that were made.
    -Customer name and total sum of the number of orders.

https://www.db-fiddle.com/f/qcWXTy3cpjfgDSzW8GdwjA/0

Azure Database for MySQL
Azure Database for MySQL
An Azure managed MySQL database service for app development and deployment.
762 questions
Accepted answer
  1. Anurag Sharma 17,586 Reputation points
    2020-10-12T04:08:40.627+00:00

    Hi @juannntrujillo , it looks you need 3 separate queries. Please refer to below queries:

    1. Names of customers and the items they bought whose price is less than 600.
      select C.Name,C.LastName,C.City,
      I.name as Item, I.Price
      from Orders O
      inner join Clients C on O.ClientId = C.Id
      inner join Items I on O.Id = I.Id
      where I.Price < 600;
    2. City of the client and name of the article of all the orders that were made.
      select C.Name,C.LastName,C.City,
      I.name as Item, I.Price
      from Orders O
      inner join Clients C on O.ClientId = C.Id
      inner join Items I on O.Id = I.Id;
    3. Customer name and total sum of the number of orders. select C.Name,C.LastName,COUNT(I.name)
      from Orders O
      inner join Clients C on O.ClientId = C.Id
      inner join Items I on O.Id = I.Id
      GROUP BY C.Name,C.LastName,C.ID

    Please let us know if this helps, or else if you are looking for any other query just mention and we can proceed further.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. juannntrujillo 21 Reputation points
    2020-10-11T01:22:54.127+00:00

    Sorry, this is the complete data. If any tag is wrong just let me know, i will remove it.

    -- phpMyAdmin SQL Dump
    -- version 4.9.4

    -- https://www.phpmyadmin.net/

    -- Host: localhost:3306
    -- Generation Time: Oct 10, 2020 at 08:17 PM
    -- Server version: 10.3.24-MariaDB-log-cll-lve
    -- PHP Version: 7.3.6

    SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
    SET AUTOCOMMIT = 0;
    START TRANSACTION;
    SET time_zone = "+00:00";

    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
    /    !40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
    /    !40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
    /    !40101 SET NAMES utf8mb4 */;

    --
    -- Database: padewpid_consultas

    -- Table structure for table Clients

    --
    CREATE TABLE Clients (
    id int(11) NOT NULL,
    Name char(50) DEFAULT NULL,
    LastName char(50) DEFAULT NULL,
    City char(50) DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    --
    -- Dumping data for table Clients

    --
    INSERT INTO Clients (id, Name, LastName, City) VALUES
    (1, 'Courtnay', 'Slixby', 'Mongolia'),
    (2, 'Hayden', 'Petrulis', 'Greece'),
    (3, 'Opaline', 'Kelmere', 'Russia'),
    (4, 'Colan', 'Barme', 'Ethiopia'),
    (5, 'Stan', 'MacLaig', 'Indonesia'),
    (6, 'Stafani', 'Uttridge', 'Nigeria'),
    (7, 'Mike', 'Forsey', 'Russia'),
    (8, 'Fons', 'Cuthill', 'France'),
    (9, 'Jerome', 'Mynett', 'Indonesia'),
    (10, 'Sibby', 'Balam', 'Mongolia'),
    (11, 'Rivy', 'Pandie', 'Philippines'),
    (12, 'Ruthy', 'Kemmis', 'Japan'),
    (13, 'Zoe', 'Kolodziej', 'Colombia'),
    (14, 'Tammara', 'Capstack', 'France'),
    (15, 'Hermie', 'McKinie', 'China'),
    (16, 'Jeni', 'Fishwick', 'Sweden'),
    (17, 'Alissa', 'Annandale', 'Russia'),
    (18, 'Magda', 'Dunmore', 'Croatia'),
    (19, 'Winnie', 'Clemett', 'China'),
    (20, 'Fionnula', 'MacKeeg', 'Armenia'),
    (21, 'Brit', 'Diver', 'Kosovo'),
    (22, 'Foss', 'Moran', 'China'),
    (23, 'Bobette', 'Wharfe', 'China'),
    (24, 'Marlena', 'Crottagh', 'Bulgaria'),
    (25, 'Pearla', 'Underhill', 'Costa Rica'),
    (26, 'Portie', 'Kidby', 'Indonesia'),
    (27, 'Dorette', 'Silver', 'Benin'),
    (28, 'Melosa', 'Cattle', 'Philippines'),
    (29, 'Fawn', 'Berryann', 'Slovenia'),
    (30, 'Buckie', 'Klink', 'China');

    -- Table structure for table Items

    --
    CREATE TABLE Items (
    id int(11) NOT NULL,
    Name char(50) DEFAULT NULL,
    Quantity char(50) DEFAULT NULL,
    Price char(50) DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    --
    -- Dumping data for table Items

    --
    INSERT INTO Items (id, Name, Quantity, Price) VALUES
    (1, 'Chicken Breast Wing On', '69', '41'),
    (2, 'Squid - Breaded', '59', '1737'),
    (3, 'Rice - Basmati', '57', '591'),
    (4, 'Carbonated Water - Strawberry', '99', '731'),
    (5, 'Chicken - Bones', '72', '2743'),
    (6, 'Bagel - 12 Grain Preslice', '22', '2155'),
    (7, 'Oil - Sunflower', '92', '1072'),
    (8, 'Lamb Rack - Ontario', '31', '135'),
    (9, 'Turkey - Breast, Smoked', '47', '2197'),
    (10, 'Nougat - Paste / Cream', '87', '258'),
    (11, 'Pepper - Paprika, Spanish', '53', '1389'),
    (12, 'Chicken - Livers', '25', '1875'),
    (13, 'Rabbit - Whole', '81', '664'),
    (14, 'Rappini - Andy Boy', '32', '1986'),
    (15, 'Wine - Pinot Noir Stoneleigh', '96', '39'),
    (16, 'Juice - Apple, 500 Ml', '68', '299'),
    (17, 'Beer - Molson Excel', '35', '1350'),
    (18, 'Nori Sea Weed', '7', '958'),
    (19, 'Ecolab - Mikroklene 4/4 L', '31', '1771'),
    (20, 'Pate - Peppercorn', '50', '654'),
    (21, 'Wine - Jafflin Bourgongone', '46', '2518'),
    (22, 'Bread - Sticks, Thin, Plain', '42', '2102'),
    (23, 'Syrup - Monin, Swiss Choclate', '54', '2945'),
    (24, 'Beer - Sleemans Cream Ale', '3', '801'),
    (25, 'Bagelers - Cinn / Brown Sugar', '16', '296'),
    (26, 'Appetizer - Tarragon Chicken', '16', '363'),
    (27, 'Milk - Condensed', '30', '467'),
    (28, 'Pants Custom Dry Clean', '97', '2237'),
    (29, 'Kaffir Lime Leaves', '98', '2806'),
    (30, 'Crackers - Water', '24', '700');

    -- Table structure for table Orders

    --
    CREATE TABLE Orders (
    id int(11) NOT NULL,
    ClientID char(50) DEFAULT NULL,
    ItemID char(50) DEFAULT NULL,
    Quantity char(50) DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    --
    -- Dumping data for table Orders

    --
    INSERT INTO Orders (id, ClientID, ItemID, Quantity) VALUES
    (1, '5', '29', '88'),
    (2, '12', '21', '69'),
    (3, '10', '5', '52'),
    (4, '26', '14', '14'),
    (5, '9', '12', '34'),
    (6, '24', '2', '18'),
    (7, '13', '11', '31'),
    (8, '11', '30', '44'),
    (9, '26', '22', '37'),
    (10, '18', '14', '93'),
    (11, '10', '30', '16'),
    (12, '22', '16', '48'),
    (13, '24', '6', '64'),
    (14, '16', '9', '77'),
    (15, '27', '15', '66'),
    (16, '9', '17', '39'),
    (17, '4', '26', '54'),
    (18, '3', '29', '82'),
    (19, '4', '16', '43'),
    (20, '17', '24', '62'),
    (21, '21', '4', '69'),
    (22, '14', '12', '89'),
    (23, '21', '30', '59'),
    (24, '15', '1', '40'),
    (25, '9', '27', '63'),
    (26, '20', '2', '43'),
    (27, '25', '11', '87'),
    (28, '14', '13', '50'),
    (29, '14', '24', '17'),
    (30, '23', '25', '94');

    --
    -- Indexes for dumped tables

    -- Indexes for table Clients

    ALTER TABLE Clients
    ADD PRIMARY KEY (id);

    --

    -- Indexes for table Items

    ALTER TABLE Items
    ADD PRIMARY KEY (id);

    --

    -- Indexes for table Orders

    ALTER TABLE Orders
    ADD PRIMARY KEY (id),
    ADD KEY IDCliente (ClientID),
    ADD KEY IDArticulo (ItemID);
    COMMIT;

    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
    /    !40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
    /    !40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;