PLEASE WRITE AN ANSWER IN MYSQL WORKBENCH Use the IN keyword with a subquery to

Question

PLEASE WRITE AN ANSWER IN MYSQL WORKBENCH

Use the IN keyword with a subquery to list the customer_id, first_name, and last_name of all customers whose shipping address is different than their billing address. Hint: use a grouping procedure in the subquery and only count customers who have more than 1 address in the addresses table. OR write a subquery that generates the list by comparing shipping_address _id to the billing_address_id in the customers table.

addresses table:

Columns:

customers table :

Columns:

int(11)

address_id int(11) AI PK customer_id int(11) line1 varchar(60) line2 varchar(60) city varchar(40) state varchar(2) zip_code varchar(10) phone varchar(12) disabled tinyint(1)

Explanation / Answer

Ans :- select customer_id,fname,lname from customer where customer_id in (select customer_id from customer where shipping_id != billing_id);

explanation :- In the above query we are selecting the customer id,first name and last name and we are adding a subquery in the where condition in which we are selecting the sutomer id which has both shipping and billing address as different ones.The subquery will return number of customer ids which satisfy the subquery the in clause in the main query will select the customer ids which are present in the result of the subquery.

the sql file contents for creating the database is given below copy paste the contents and save the sql file as mark3.sql:=

Note - if you found this answer useful please give a thumbsup it would really help me :p

--
-- Database: `mark3`
--

-- --------------------------------------------------------

--
-- Table structure for table `address`
--

CREATE TABLE `address` (
`address_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`line1` varchar(60) NOT NULL,
`line2` varchar(60) NOT NULL,
`city` varchar(40) NOT NULL,
`state` varchar(20) NOT NULL,
`zip` varchar(10) NOT NULL,
`phone` varchar(12) NOT NULL,
`disabled` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `address`
--

INSERT INTO `address` (`address_id`, `customer_id`, `line1`, `line2`, `city`, `state`, `zip`, `phone`, `disabled`) VALUES
(101, 1, 'mt-2 c507', 'iit madras chennai', 'chennai', 'TN', '1234567', '5678910', 0),
(102, 2, 'mt-2 c508', 'iit madras chennai', 'chennai', 'MH', '1456789', '67890123', 0),
(103, 3, 'mt-2 c567', 'iitb mumbai', 'mumbai', 'MH', '400234', '5412986', 0);

-- --------------------------------------------------------

--
-- Table structure for table `customer`
--

CREATE TABLE `customer` (
`customer_id` int(11) NOT NULL,
`email_id` varchar(255) NOT NULL,
`password` varchar(60) NOT NULL,
`fname` varchar(60) NOT NULL,
`lname` varchar(60) NOT NULL,
`shipping_id` int(11) NOT NULL,
`billing_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `customer`
--

INSERT INTO `customer` (`customer_id`, `email_id`, `password`, `fname`, `lname`, `shipping_id`, `billing_id`) VALUES
(1, 'thetalwar@my.com', '123456', 'alex', 'pane', 101, 102),
(2, 'biju@spell.com', '548796', 'mike', 'specter', 103, 103);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `address`
--
ALTER TABLE `address`
ADD PRIMARY KEY (`address_id`);

--
-- Indexes for table `customer`
--
ALTER TABLE `customer`
ADD PRIMARY KEY (`customer_id`);

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.