SQL DUMP for example tables used for left join query

student6

id	name	class	sex	house_id
1	John Deo	Four	female	2
2	Max Ruin	Three	male	1
3	Arnold	Three	male	1
4	Krish Star	Four	female	3
5	John Mike	Four	female	2

student_mark

mark_id	student_id	exam_dt	exam	mark
1	2	2017-04-05	Sem1	80
2	2	2017-04-19	Sem2	75
3	1	2017-04-05	Sem1	84
4	1	2017-04-19	Sem2	76
5	3	2017-04-05	Sem1	71
6	3	2017-04-19	Sem2	72
7	1	2017-05-10	Sem1	93
8	1	2017-05-24	Sem2	90
9	2	2017-05-10	Sem1	85
10	2	2017-05-24	Sem2	86
11	3	2017-05-10	Sem1	63
12	3	2017-05-24	Sem2	80
13	1	2017-06-07	Sem1	80
14	1	2017-06-21	Sem2	75
15	2	2017-06-07	Sem1	50
16	2	2017-06-21	Sem2	87
17	3	2017-06-07	Sem1	59
18	3	2017-06-21	Sem2	61
19	4	2017-04-05	Sem1	84
20	4	2017-04-19	Sem2	82
21	5	2017-04-05	Sem1	63
22	5	2017-04-19	Sem2	95
23	4	2017-05-10	Sem1	59
24	4	2017-05-24	Sem2	85
25	5	2017-05-10	Sem1	89
26	5	2017-05-24	Sem2	84
27	4	2017-06-07	Sem1	84
28	4	2017-06-21	Sem2	87
29	5	2017-06-07	Sem1	95
30	5	2017-06-21	Sem2	87

student_house

house_id	h_name
1	King
2	Queen
3	Jack

student_fee

fee_id	id	dt	amount
1	1	2013-01-08	200
2	1	2013-01-10	100
3	2	2013-01-24	120
4	3	2013-02-02	211
5	2	2013-02-07	150
6	3	2013-02-06	135
7	4	2013-02-14	100

CREATE TABLE IF NOT EXISTS `student6` (
  `id` int(2) NOT NULL DEFAULT '0',
  `name` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `class` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `sex` varchar(6) CHARACTER SET utf8 NOT NULL DEFAULT 'male',
  `house_id` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `student6`
--

INSERT INTO `student6` (`id`, `name`, `class`, `sex`, `house_id`) VALUES
(1, 'John Deo', 'Four', 'female', 2),
(2, 'Max Ruin', 'Three', 'male', 1),
(3, 'Arnold', 'Three', 'male', 1),
(4, 'Krish Star', 'Four', 'female', 3),
(5, 'John Mike', 'Four', 'female', 2);

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

--
-- Table structure for table `student_fee`
--

CREATE TABLE IF NOT EXISTS `student_fee` (
  `fee_id` int(2) NOT NULL AUTO_INCREMENT,
  `id` int(2) NOT NULL,
  `dt` date NOT NULL,
  `amount` int(5) NOT NULL,
  PRIMARY KEY (`fee_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

--
-- Dumping data for table `student_fee`
--

INSERT INTO `student_fee` (`fee_id`, `id`, `dt`, `amount`) VALUES
(1, 1, '2013-01-08', 200),
(2, 1, '2013-01-10', 100),
(3, 2, '2013-01-24', 120),
(4, 3, '2013-02-02', 211),
(5, 2, '2013-02-07', 150),
(6, 3, '2013-02-06', 135),
(7, 4, '2013-02-14', 100);

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

--
-- Table structure for table `student_house`
--

CREATE TABLE IF NOT EXISTS `student_house` (
  `house_id` int(2) NOT NULL AUTO_INCREMENT,
  `h_name` varchar(10) NOT NULL,
  PRIMARY KEY (`house_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `student_house`
--

INSERT INTO `student_house` (`house_id`, `h_name`) VALUES
(1, 'King'),
(2, 'Queen'),
(3, 'Jack');

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

--
-- Table structure for table `student_mark`
--

CREATE TABLE IF NOT EXISTS `student_mark` (
  `mark_id` int(3) NOT NULL AUTO_INCREMENT,
  `student_id` int(3) NOT NULL,
  `exam_dt` date NOT NULL,
  `exam` varchar(10) NOT NULL,
  `mark` int(3) NOT NULL,
  PRIMARY KEY (`mark_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=31 ;

--
-- Dumping data for table `student_mark`
--

INSERT INTO `student_mark` (`mark_id`, `student_id`, `exam_dt`, `exam`, `mark`) VALUES
(1, 2, '2017-04-05', 'Sem1', 80),
(2, 2, '2017-04-19', 'Sem2', 75),
(3, 1, '2017-04-05', 'Sem1', 84),
(4, 1, '2017-04-19', 'Sem2', 76),
(5, 3, '2017-04-05', 'Sem1', 71),
(6, 3, '2017-04-19', 'Sem2', 72),
(7, 1, '2017-05-10', 'Sem1', 93),
(8, 1, '2017-05-24', 'Sem2', 90),
(9, 2, '2017-05-10', 'Sem1', 85),
(10, 2, '2017-05-24', 'Sem2', 86),
(11, 3, '2017-05-10', 'Sem1', 63),
(12, 3, '2017-05-24', 'Sem2', 80),
(13, 1, '2017-06-07', 'Sem1', 80),
(14, 1, '2017-06-21', 'Sem2', 75),
(15, 2, '2017-06-07', 'Sem1', 50),
(16, 2, '2017-06-21', 'Sem2', 87),
(17, 3, '2017-06-07', 'Sem1', 59),
(18, 3, '2017-06-21', 'Sem2', 61),
(19, 4, '2017-04-05', 'Sem1', 84),
(20, 4, '2017-04-19', 'Sem2', 82),
(21, 5, '2017-04-05', 'Sem1', 63),
(22, 5, '2017-04-19', 'Sem2', 95),
(23, 4, '2017-05-10', 'Sem1', 59),
(24, 4, '2017-05-24', 'Sem2', 85),
(25, 5, '2017-05-10', 'Sem1', 89),
(26, 5, '2017-05-24', 'Sem2', 84),
(27, 4, '2017-06-07', 'Sem1', 84),
(28, 4, '2017-06-21', 'Sem2', 87),
(29, 5, '2017-06-07', 'Sem1', 95),
(30, 5, '2017-06-21', 'Sem2', 87);

LEFT JOIN ( Basic Query) LEFT JOIN using Multiple Tables

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

Zubair	28-03-2018
I am new on SQL i have database which have daily customer balances to takeout potential customer i require query to retrieve data customer account #, maximum balance on which date. Please help Thanks

SQL DUMP of Three tables used for LEFT JOIN query

student6

student_mark

student_house

student_fee

Subhendu Mohapatra