[CS] SQL Schema Design & Grammer Day-78

Create user table

CREATE TABLE `user` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) not NULL,
  `email` varchar(255) not NULL
);

CREATE content table

CREATE TABLE `content` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `title` varchar(255) not NULL,
  `body` varchar(255) not NULL,
  `created_at` timestamp not NULL DEFAULT CURRENT_TIMESTAMP,
  `userId` int,
  FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
);

Create category table

CREATE TABLE `category` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) not NULL
);

Create content_category table

CREATE TABLE `content_category` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `contentId` int not NULL,
  `categoryId` int not NULL,
  FOREIGN KEY (`contentId`) REFERENCES `content` (`id`),
  FOREIGN KEY (`categoryId`) REFERENCES `category` (`id`)
);

Create role table

CREATE TABLE `role` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) not NULL
);

Show database all tables

SHOW TABLES;

Show user Columns

SHOW COLUMNS FROM user;

Show content Columns

SHOW COLUMNS FROM content;

Check all data from user table

SELECT * FROM user;

Check name data only from user table

SELECT name FROM user;

Insert data(name, email) in user table

INSER INTO user(name, email) VALUES ("KJ", "cptkuk91@gmail.com");

Check specific data(name is "KJ") from user table

SELECT * FROM user WHERE name="KJ";

Check title column from content

SELECT title FROM content;

Find the title of the content and the name of the user who wrote the content

SELECT content.title, user.name FROM content, user WHERE content.userId = user.id;

Modify content data (chane content.title)

UPDATE content SET body="New Data" WHERE title="Origin Data";

Insert new Data(title, body, userId) in content table

INSERT INTO content (title, body, userId) VALUES ("New", "data", 1);

Check category table structure

DESC category;

Check content_category table structure

DESC content_category;

Check role table structure

DESC role;

Check user table structure

DESC user;

Check id and name from category table

SELECT id, name FROM category;

Find the user's name and email and the role name (column name: roleName) to which the user belongs

SELECT user.name, user.email, role.name as roleName FROM user LEFTJOIN role ON user.roleId = role.id;

Check roleId is Null from user

SELECT * FROM user WHERE roleId IS NULL;

Check content_category all Columns

SELECT * FROM content_category;

Find the category name of content written by KJ

SELECT category.name FROM category INNER JOIN content_category ON category.id = content_category.categoryId INNER JOIN content ON content_categort.contentId = content.id WHERE content.userId = 1;

Find title, body, created_at of content whose category name is soccer

SELECT content.title, content.body, content.created_at FROM content INNER JOIN content_category ON content.id = content_category.contentId INNER JOIN category ON content_category.categoryId = category.id WHERE category.name = "soccer";

Displaying the number of articles (column name: ContentCount) written by KJ

SELECT COUNT(content.userId) AS ContentCount FROM content INNER JOIN user ON contentId.userId = user.id WHERE user.name = "KJ";

Displaying the number of articles (column name: ContentCount) written by each user (column name: name)

SELECT COUNT(content.userId) AS ContentCount, user.name FROM user LEFT JOIN content ON content.userId = user.id GROUP BY name;