Một mẫu sql để thực hành

Ví dụ: Tạo database tên là: registration

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
CREATE TABLE `categories` (
  `cat_id` int(11) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `cat_name` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `position` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `categories` (`cat_id`, `user_id`, `cat_name`, `position`) VALUES
(1, 0, '', 0),
(2, 1, 'Cate 1', 1),
(3, 1, 'Cate 2', 1),
(4, 1, 'Cate 3', 2),
(5, 1, 'Cate 4', 3),
(6, 2, 'Cate 5', 1),
(7, 2, 'Cate 6', 2),
(8, 2, 'Cate 7', 3),
(9, 3, 'Cate 8', 2);
CREATE TABLE `comments` (
  `comment_id` int(11) UNSIGNED NOT NULL,
  `page_id` int(11) UNSIGNED NOT NULL,
  `author` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `comment` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `comment_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `comments` (`comment_id`, `page_id`, `author`, `email`, `comment`, `comment_date`) VALUES
(1, 1, 'Lionel 1', '[e-mail]', 'Content Lionel 1', '2019-06-01 00:00:00'),
(2, 2, 'Lionel 1', '[e-mail]', 'Content Lionel 2', '2019-05-08 00:00:00'),
(3, 2, 'Lionel 2', '[e-mail]', 'Content Lionel 3', '2019-05-06 00:00:00'),
(4, 2, 'Lionel 2', '[e-mail]', 'Content Lionel 4', '2019-03-04 00:00:00'),
(5, 3, 'Lionel 2', '[e-mail]', 'Content Lionel 5', '2019-04-08 00:00:00'),
(6, 3, 'Lionel 3', '[e-mail]', 'Content Lionel 6', '0000-00-00 00:00:00'),
(7, 3, 'Lionel 4', '[e-mail]', 'Content Lionel 7', '2019-06-02 00:00:00'),
(8, 3, 'Lionel 5', '[e-mail]', 'Content Lionel 8', '2019-05-01 00:00:00');
CREATE TABLE `pages` (
  `page_id` int(11) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `cat_id` int(11) UNSIGNED NOT NULL,
  `page_name` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `position` int(3) NOT NULL,
  `post_on` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `pages` (`page_id`, `user_id`, `cat_id`, `page_name`, `content`, `position`, `post_on`) VALUES
(1, 1, 1, 'Page 1', 'Page 1', 1, '2019-06-02 00:00:00'),
(2, 1, 1, 'Page 2', 'Page 2', 0, '2019-06-02 00:00:00'),
(3, 2, 2, 'Page 2', 'Page 2', 2, '2019-06-02 00:00:00'),
(4, 2, 1, 'Page 3', 'Page 3', 2, '2019-06-02 00:00:00'),
(5, 2, 1, 'Page 3', 'Content Page 2.3', 3, '2019-06-02 00:00:00'),
(6, 2, 3, 'Page 4', 'Content Page 2.4', 3, '2019-06-02 00:00:00'),
(7, 3, 3, 'Page 3', 'Content Page 2.5', 3, '2019-06-02 00:00:00'),
(8, 3, 4, 'Page 7', 'Content Page 7', 4, '2019-06-09 00:00:00');
CREATE TABLE `users` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `first_name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `pass` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `website` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `yahoo` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bio` text COLLATE utf8mb4_unicode_ci,
  `avatar` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_level` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  `active` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `registration_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `users` (`user_id`, `first_name`, `last_name`, `email`, `pass`, `website`, `yahoo`, `bio`, `avatar`, `user_level`, `active`, `registration_date`) VALUES
(1, 'Lionel Hoang 1', 'Pham 1', '[e-mail]', '123456', 'www.lionel1.com', '[e-mail]', 'Content Lionel Hoang 1', '1.jpg', 1, NULL, '2019-06-02 00:00:00'),
(2, 'Lionel Hoang 2', 'Pham 2', '[e-mail]', '123456', 'www.lionel2.com', '[e-mail]', 'Content Lionel Hoang 2', '2.jpg', 1, NULL, '2019-06-01 00:00:00'),
(3, 'Lionel Hoang 3', 'Pham 3', '[e-mail]', '123456', 'www.lionel3.com', '[e-mail]', 'Content Lionel Hoang 3', NULL, 1, NULL, '2019-06-02 00:00:00'),
(4, 'Lionel Hoang 4', 'Pham 4', '[e-mail]', '123456', 'www.lionel4com', '[e-mail]', 'Content Lionel Hoang 4', NULL, 1, NULL, '2019-06-02 00:00:00');
ALTER TABLE `categories`
  ADD PRIMARY KEY (`cat_id`),
  ADD KEY `user_id` (`user_id`);
ALTER TABLE `comments`
  ADD PRIMARY KEY (`comment_id`);
ALTER TABLE `pages`
  ADD PRIMARY KEY (`page_id`),
  ADD KEY `user_id` (`user_id`);
ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `login` (`email`,`pass`);
ALTER TABLE `categories`
  MODIFY `cat_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
ALTER TABLE `comments`
  MODIFY `comment_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
ALTER TABLE `pages`
  MODIFY `page_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
ALTER TABLE `users`
  MODIFY `user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;

Thực hành với INNER JOIN
SELECT p.page_name, p.content, date_format(p.post_on,'%b %d %Y') as date, CONCAT_WS(' ',u.first_name, u.last_name) as name, count(c.comment_id) as count FROM pages AS p INNER JOIN users as u USING (user_id) INNER JOIN comments as c using(page_id) WHERE p.page_id = 1

Bài 1:
Nếu users có 4 cột và pages có 8 cột khi đó:
SELECT p.page_name FROM pages AS p INNER JOIN users AS u sẽ cho ta 32 kết qủa
Sử dụng
SELECT p.page_name, u.email FROM pages AS p INNER JOIN users AS u USING(user_id) sẽ cho ta kết qủa là 7

Dịch đoạn mã sau:
SELECT p.page_name, u.email, c.author FROM users AS u INNER JOIN pages AS p USING(user_id) INNER JOIN comments AS c USING (page_id) WHERE p.page_id = 1 AND c.author = 'Lionel 1'
Lấy số người sử dụng comment trên page có page_id = 1 và người dùng author = 'Lionel 1'