Skip to content

Instantly share code, notes, and snippets.

@maheshmnj

maheshmnj/mysql.md

Last active March 2, 2023 20:30
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save maheshmnj/d80f614ce1a4c3071f4433aef26c3c2c to your computer and use it in GitHub Desktop.
Save maheshmnj/d80f614ce1a4c3071f4433aef26c3c2c to your computer and use it in GitHub Desktop.
Download ZIP
my sql workbench quick notes
Raw
mysql.md

MYSQL Workbench (2.0.31) docs.

Contents

1. Configuration

2. Chore Queries

Modify column Type

ALTER Table events
MODIFY COLUMN description VARCHAR(100)

Choose a existing database

USE <database name>;

3. Schema Creation

  1. MySQL SERVER must be installed and running before starting my sqlworkbench
  2. Create a ER Diagram.
  3. Generate SQL script using forward engineering
  4. Under Home -> Go to mysql conections to open sql editor and run the queries to check if the physical schema is created.

4. Data Populate

-- Populate users
INSERT INTO user (user_id, email, avatar_url, isAdmin, username, created_at)
VALUES
('1', 'johnsmith@example.com', 'http://example.com/avatar1.jpg', 1, 'johnsmith', NOW()),
('2', 'janedoe@example.com', 'http://example.com/avatar2.jpg', 0, 'janedoe', NOW()),
('3', 'davidsanchez@example.com', 'http://example.com/avatar3.jpg', 0, 'davidsanchez', NOW()),
('4', 'mariaperez@example.com', 'http://example.com/avatar4.jpg', 0, 'mariaperez', NOW()),
('5', 'markjohnson@example.com', 'http://example.com/avatar5.jpg', 0, 'markjohnson', NOW()),
('6', 'sarahlee@example.com', 'http://example.com/avatar6.jpg', 0, 'sarahlee', NOW()),
('7', 'brianharris@example.com', 'http://example.com/avatar7.jpg', 0, 'brianharris', NOW()),
('8', 'jessicawang@example.com', 'http://example.com/avatar8.jpg', 0, 'jessicawang', NOW()),
('9', 'andrewnguyen@example.com', 'http://example.com/avatar9.jpg', 0, 'andrewnguyen', NOW()),
('10', 'emilytaylor@example.com', 'http://example.com/avatar10.jpg', 0, 'emilytaylor', NOW()),
('11', 'jamesbrown@example.com', 'https://i.pravatar.cc/150?img=11', 0, 'jamesbrown', NOW()),
('12', 'ashleywilson@example.com', 'https://i.pravatar.cc/150?img=12', 0, 'ashleywilson', NOW()),
('13', 'samueljackson@example.com', 'https://i.pravatar.cc/150?img=13', 0, 'samueljackson', NOW()),
('14', 'lauradavis@example.com', 'https://i.pravatar.cc/150?img=14', 0, 'lauradavis', NOW()),
('15', 'robertmorris@example.com', 'https://i.pravatar.cc/150?img=15', 0, 'robertmorris', NOW()),
('16', 'dianeparker@example.com', 'https://i.pravatar.cc/150?img=16', 0, 'dianeparker', NOW()),
('17', 'kevinsmith@example.com', 'https://i.pravatar.cc/150?img=17', 0, 'kevinsmith', NOW()),
('18', 'kellygreen@example.com', 'https://i.pravatar.cc/150?img=18', 0, 'kellygreen', NOW()),
('19', 'williamturner@example.com', 'https://i.pravatar.cc/150?img=19', 0, 'williamturner', NOW()),
('20', 'jessicamiller@example.com', 'https://i.pravatar.cc/150?img=20', 0, 'jessicamiller', NOW());

-- Populate events
INSERT INTO `events` (`id`, `created_at`, `name`, `description`, `starts_at`, `ends_at`, `cover_image`, `address`, `private`, `deleted`, `user_id`)
VALUES
  ('11', NOW(), 'TED2023 Conference', 'TED is a nonprofit devoted to spreading ideas, usually in the form of short, powerful talks.', NOW() + INTERVAL 1 MONTH, NOW() + INTERVAL 1 MONTH + INTERVAL 3 DAY, 'https://ted.com/talks/cover.jpg', 'Vancouver Convention Centre, Vancouver, Canada', 0, 0, '2'),
  ('12', NOW(), 'Web Summit 2023', 'Web Summit brings together the people and companies redefining the global tech industry.', NOW() + INTERVAL 2 MONTH, NOW() + INTERVAL 2 MONTH + INTERVAL 4 DAY, 'https://websummit.com/cover.jpg', 'Altice Arena, Lisbon, Portugal', 0, 0, '3'),
  ('13', NOW(), 'Comic Con 2023', 'Comic Con is a multi-genre entertainment and comic convention.', NOW() + INTERVAL 3 MONTH, NOW() + INTERVAL 3 MONTH + INTERVAL 3 DAY, 'https://comiccon.com/cover.jpg', 'San Diego Convention Center, San Diego, USA', 0, 0, '14'),
  ('14', NOW(), 'New York Fashion Week', 'New York Fashion Week is a fashion event held twice a year in New York City.', NOW() + INTERVAL 4 MONTH, NOW() + INTERVAL 4 MONTH + INTERVAL 5 DAY, 'https://nyfw.com/cover.jpg', 'Skylight Clarkson Sq, New York City, USA', 0, 0, '15'),
  ('15', NOW(), 'Cannes Film Festival 2023', 'Cannes Film Festival is an annual film festival held in Cannes, France.', NOW() + INTERVAL 5 MONTH, NOW() + INTERVAL 5 MONTH + INTERVAL 7 DAY, 'https://cannesfilmfestival.com/cover.jpg', 'Palais des Festivals et des Congrès, Cannes, France', 0, 0, '16'),
  ('16', NOW(), 'Coachella 2023', 'Coachella is an annual music and arts festival held in California.', NOW() + INTERVAL 6 MONTH, NOW() + INTERVAL 6 MONTH + INTERVAL 4 DAY, 'https://coachella.com/cover.jpg', 'Empire Polo Club, Indio, California, USA', 0, 0, '17'),
  ('17', NOW(), 'Art Basel 2023', 'Art Basel is an international art fair held annually in Basel, Switzerland.', NOW() + INTERVAL 7 MONTH, NOW() + INTERVAL 7 MONTH + INTERVAL 6 DAY, 'https://artbasel.com/cover.jpg', 'Messe Basel, Basel, Switzerland', 0, 0, '18'),
  ('18', NOW(), 'South by Southwest (SXSW) 2023', 'SXSW is an annual conglomerate of film, interactive media, and music festivals.', NOW() + INTERVAL 8 MONTH, NOW() + INTERVAL 8 MONTH + INTERVAL 7 DAY, 'https://sxsw.com/cover.jpg', 'Austin Convention Center, Austin, Texas, USA', 0, 0, '19'),
  ('19', NOW(), 'Paris Fashion Week 2023', 'Paris Fashion Week is a fashion event held biannually in Paris, France.', NOW() + INTERVAL 9 MONTH, NOW() +  INTERVAL 9 MONTH + INTERVAL 8 DAY, 'https://sxsw.com/cover.jpg', 'Texas Convention Center, Texas, USA', 0, 0, '1'); 

-- Populate attendees
INSERT INTO attendees (id, event_id, user_id)
VALUES 
(1, 11, 1),
(2, 11, 2),
(3, 11, 3),
(11, 12, 2),
(16, 12, 10),
(17, 13, 11),
(18, 13, 12),
(19, 13, 13),
(20, 13, 14),
(21, 14, 15),
(22, 14, 16),
(23, 14, 17),
(24, 15, 18),
(25, 15, 19),
(26, 16, 20),
(27, 16, 19),
(28, 16, 18),
(29, 16, 17),
(30, 16, 16);

-- Populate tags
INSERT INTO tags (tag_id, tag_name)
VALUES 
    (1, 'Technology'),
    (2, 'Innovation'),
    (3, 'Science'),
    (4, 'Art'),
    (5, 'Design'),
    (6, 'Fashion'),
    (7, 'Music'),
    (8, 'Film'),
    (9, 'Entertainment');

-- Populate event_tag_map 
INSERT INTO `event_tag_map` (`event_id`, `tag_id`, `map_id`)
VALUES
  ('11', '1', UUID()),
  ('11', '2', UUID()),
  ('13', '4', UUID()),
  ('14', '5', UUID()),
  ('15', '4', UUID()),
  ('15', '7', UUID()),
  ('16', '5', UUID()),
  ('16', '6', UUID()),
  ('17', '7', UUID()),
  ('17', '8', UUID()),
  ('18', '8', UUID()),
  ('18', '9', UUID()),
  ('19', '4', UUID()),
  ('19', '7', UUID()),
  ('19', '9', UUID());
  
-- Populate bookmarks 
INSERT INTO `bookmarks` (`id`, `created_at`, `event_id`, `user_id`)
VALUES 
    (UUID(), NOW(), '12', '16'),
    (UUID(), NOW(), '14', '15'),
    (UUID(), NOW(), '16', '14'),
    (UUID(), NOW(), '19', '3'),
    (UUID(), NOW(), '19', '1'),
    (UUID(), NOW(), '13', '12'),
    (UUID(), NOW(), '12', '2');

5. Procedures

1. Add a new user

CREATE PROCEDURE `insert_user` (
    IN p_user_id VARCHAR(128),
    IN p_email VARCHAR(128),
    IN p_avatar_url VARCHAR(256),
    IN p_isAdmin BOOLEAN,
    IN p_username VARCHAR(128)
)
BEGIN
    INSERT INTO user (user_id, email, avatar_url, isAdmin, username, created_at)
    VALUES (p_user_id, p_email, p_avatar_url, p_isAdmin, p_username, NOW());
END

2. Load dummy data

CREATE PROCEDURE `loadData`()
BEGIN

-- Insert users
INSERT INTO user (user_id, email, avatar_url, isAdmin, username, created_at)
VALUES
  ('1', 'johnsmith@example.com', 'http://example.com/avatar1.jpg', 1, 'johnsmith', NOW()),
  ('2', 'janedoe@example.com', 'http://example.com/avatar2.jpg', 0, 'janedoe', NOW()),
  ('3', 'davidsanchez@example.com', 'http://example.com/avatar3.jpg', 0, 'davidsanchez', NOW()),
  ('4', 'mariaperez@example.com', 'http://example.com/avatar4.jpg', 0, 'mariaperez', NOW()),
  ('5', 'markjohnson@example.com', 'http://example.com/avatar5.jpg', 0, 'markjohnson', NOW()),
  ('6', 'sarahlee@example.com', 'http://example.com/avatar6.jpg', 0, 'sarahlee', NOW()),
  ('7', 'brianharris@example.com', 'http://example.com/avatar7.jpg', 0, 'brianharris', NOW()),
  ('8', 'jessicawang@example.com', 'http://example.com/avatar8.jpg', 0, 'jessicawang', NOW()),
  ('9', 'andrewnguyen@example.com', 'http://example.com/avatar9.jpg', 0, 'andrewnguyen', NOW()),
  ('10', 'emilytaylor@example.com', 'http://example.com/avatar10.jpg', 0, 'emilytaylor', NOW()),
  ('11', 'jamesbrown@example.com', 'https://i.pravatar.cc/150?img=11', 0, 'jamesbrown', NOW()),
  ('12', 'ashleywilson@example.com', 'https://i.pravatar.cc/150?img=12', 0, 'ashleywilson', NOW()),
  ('13', 'samueljackson@example.com', 'https://i.pravatar.cc/150?img=13', 0, 'samueljackson', NOW()),
  ('14', 'lauradavis@example.com', 'https://i.pravatar.cc/150?img=14', 0, 'lauradavis', NOW()),
  ('15', 'robertmorris@example.com', 'https://i.pravatar.cc/150?img=15', 0, 'robertmorris', NOW()),
  ('16', 'dianeparker@example.com', 'https://i.pravatar.cc/150?img=16', 0, 'dianeparker', NOW()),
  ('17', 'kevinsmith@example.com', 'https://i.pravatar.cc/150?img=17', 0, 'kevinsmith', NOW()),
  ('18', 'kellygreen@example.com', 'https://i.pravatar.cc/150?img=18', 0, 'kellygreen', NOW()),
  ('19', 'williamturner@example.com', 'https://i.pravatar.cc/150?img=19', 0, 'williamturner', NOW()),
  ('20', 'jessicamiller@example.com', 'https://i.pravatar.cc/150?img=20', 0, 'jessicamiller', NOW());
  
-- Insert events
INSERT INTO `events` (`id`, `created_at`, `name`, `description`, `starts_at`, `ends_at`, `cover_image`, `address`, `private`, `deleted`, `user_id`)
VALUES
  ('11', NOW(), 'TED2023 Conference', 'TED is a nonprofit devoted to spreading ideas, usually in the form of short, powerful talks.', NOW() + INTERVAL 1 MONTH, NOW() + INTERVAL 1 MONTH + INTERVAL 3 DAY, 'https://ted.com/talks/cover.jpg', 'Vancouver Convention Centre, Vancouver, Canada', 0, 0, '2'),
  ('12', NOW(), 'Web Summit 2023', 'Web Summit brings together the people and companies redefining the global tech industry.', NOW() + INTERVAL 2 MONTH, NOW() + INTERVAL 2 MONTH + INTERVAL 4 DAY, 'https://websummit.com/cover.jpg', 'Altice Arena, Lisbon, Portugal', 0, 0, '3'),
  ('13', NOW(), 'Comic Con 2023', 'Comic Con is a multi-genre entertainment and comic convention.', NOW() + INTERVAL 3 MONTH, NOW() + INTERVAL 3 MONTH + INTERVAL 3 DAY, 'https://comiccon.com/cover.jpg', 'San Diego Convention Center, San Diego, USA', 0, 0, '14'),
  ('14', NOW(), 'New York Fashion Week', 'New York Fashion Week is a fashion event held twice a year in New York City.', NOW() + INTERVAL 4 MONTH, NOW() + INTERVAL 4 MONTH + INTERVAL 5 DAY, 'https://nyfw.com/cover.jpg', 'Skylight Clarkson Sq, New York City, USA', 0, 0, '15'),
  ('15', NOW(), 'Cannes Film Festival 2023', 'Cannes Film Festival is an annual film festival held in Cannes, France.', NOW() + INTERVAL 5 MONTH, NOW() + INTERVAL 5 MONTH + INTERVAL 7 DAY, 'https://cannesfilmfestival.com/cover.jpg', 'Palais des Festivals et des Congrès, Cannes, France', 0, 0, '16'),
  ('16', NOW(), 'Coachella 2023', 'Coachella is an annual music and arts festival held in California.', NOW() + INTERVAL 6 MONTH, NOW() + INTERVAL 6 MONTH + INTERVAL 4 DAY, 'https://coachella.com/cover.jpg', 'Empire Polo Club, Indio, California, USA', 0, 0, '17'),
  ('17', NOW(), 'Art Basel 2023', 'Art Basel is an international art fair held annually in Basel, Switzerland.', NOW() + INTERVAL 7 MONTH, NOW() + INTERVAL 7 MONTH + INTERVAL 6 DAY, 'https://artbasel.com/cover.jpg', 'Messe Basel, Basel, Switzerland', 0, 0, '18'),
  ('18', NOW(), 'South by Southwest (SXSW) 2023', 'SXSW is an annual conglomerate of film, interactive media, and music festivals.', NOW() + INTERVAL 8 MONTH, NOW() + INTERVAL 8 MONTH + INTERVAL 7 DAY, 'https://sxsw.com/cover.jpg', 'Austin Convention Center, Austin, Texas, USA', 0, 0, '19'),
  ('19', NOW(), 'Paris Fashion Week 2023', 'Paris Fashion Week is a fashion event held biannually in Paris, France.', NOW() + INTERVAL 9 MONTH, NOW() +  INTERVAL 9 MONTH + INTERVAL 8 DAY, 'https://sxsw.com/cover.jpg', 'Texas Convention Center, Texas, USA', 0, 0, '1'); 

-- Insert attendees
INSERT INTO attendees (id, event_id, user_id)
VALUES 
(1, 11, 1),
(2, 11, 2),
(3, 11, 3),
(11, 12, 2),
(16, 12, 10),
(17, 13, 11),
(18, 13, 12),
(19, 13, 13),
(20, 13, 14),
(21, 14, 15),
(22, 14, 16),
(23, 14, 17),
(24, 15, 18),
(25, 15, 19),
(26, 16, 20),
(27, 16, 19),
(28, 16, 18),
(29, 16, 17),
(30, 16, 16);

-- Insert tags
INSERT INTO tags (tag_id, tag_name)
VALUES 
    (1, 'Technology'),
    (2, 'Innovation'),
    (3, 'Science'),
    (4, 'Art'),
    (5, 'Design'),
    (6, 'Fashion'),
    (7, 'Music'),
    (8, 'Film'),
    (9, 'Entertainment');
    
-- Insert event_tag_map
INSERT INTO `event_tag_map` (`event_id`, `tag_id`, `map_id`)
VALUES
  ('11', '1', UUID()),
  ('11', '2', UUID()),
  ('3', '4', UUID()),
  ('4', '5', UUID()),
  ('5', '4', UUID()),
  ('5', '7', UUID()),
  ('6', '5', UUID()),
  ('6', '6', UUID()),
  ('7', '7', UUID()),
  ('7', '8', UUID()),
  ('8', '8', UUID()),
  ('8', '9', UUID()),
  ('9', '4', UUID()),
  ('9', '7', UUID()),
  ('9', '9', UUID());

INSERT INTO `bookmarks` (`id`, `created_at`, `event_id`, `user_id`)
VALUES
    (UUID(), NOW(), '12', '16'),
    (UUID(), NOW(), '14', '15'),
    (UUID(), NOW(), '16', '14'),
    (UUID(), NOW(), '19', '3'),
    (UUID(), NOW(), '19', '1'),
    (UUID(), NOW(), '13', '12'),
    (UUID(), NOW(), '12', '2');
END

3. Delete all data

CREATE PROCEDURE `delete_all_data` ()
BEGIN
	SET FOREIGN_KEY_CHECKS=0;
	DELETE FROM events;
	DELETE FROM user;
	DELETE FROM attendees;
	DELETE FROM bookmarks;
	DELETE FROM leaderboard;
	DELETE FROM tags;
	DELETE FROM event_tag_map;
	DELETE FROM feedback;
	SET FOREIGN_KEY_CHECKS=1;
END

4. Get event attendees by event_id

CREATE PROCEDURE `get_event_attendees`(IN event_id varchar(128))
BEGIN
    SELECT `user`.*
    FROM attendees
    INNER JOIN events ON attendees.event_id = events.id
    INNER JOIN `user` ON attendees.user_id = `user`.user_id
    WHERE events.id = event_id;
END

Invoke using

CALL get_event_attendees(12);

5. Create a event

CREATE PROCEDURE create_event (
  IN id varchar(128),
  IN name VARCHAR(255),
  IN description TEXT,
  IN starts_at DATETIME,
  IN ends_at DATETIME,
  IN cover_image VARCHAR(255),
  IN address varchar(100),
  IN private tinyint(1),
  IN user_id varchar(128)
)
BEGIN
  INSERT INTO events (id, created_at, name, description, starts_at, ends_at, cover_image, address, private, deleted, user_id)
  VALUES (id, now(),name, description, starts_at, ends_at, cover_image, address, private, '0',user_id);
END

Invoke using

CALL create_event(21, 'abc', 'abc', now(), now(), 'aosbboa', 'New Bedford', 0, 18);

6. Register an attendee for a event.

CREATE PROCEDURE register_for_event (
  IN id	varchar(128),
  IN event_id varchar(128),
  IN user_id varchar(128)
)
 BEGIN
  INSERT INTO attendees (id, event_id, user_id) VALUES (id, event_id, user_id);
END

Invoke using

CALL get_event_attendees(12);

6. Triggers

1. Trigger for Event Deletion

DELIMITER $$
CREATE TRIGGER delete_attendees_on_cancel
AFTER UPDATE ON events
FOR EACH ROW
BEGIN
  IF NEW.deleted = 1 AND OLD.deleted = 0 THEN
    DELETE FROM attendees WHERE event_id = OLD.id;
  END IF;
END$$
DELIMITER ;
@maheshmnj
Copy link
Author

maheshmnj commented Feb 27, 2023 •
edited

POSTGRES equivalence Schema creation and data population

postgres.zip

@dhyeydoshi
Copy link

dhyeydoshi commented Feb 27, 2023 •
edited by maheshmnj

Trigger for Event Deletion

DELIMITER $$
CREATE TRIGGER delete_attendees_on_cancel
AFTER UPDATE ON events
FOR EACH ROW
BEGIN
  IF NEW.deleted = 1 AND OLD.deleted = 0 THEN
    DELETE FROM attendees WHERE event_id = OLD.id;
  END IF;
END$$
DELIMITER ;

@maheshmnj
Copy link
Author

@dhyeydoshi
We should also add two more triggers

  • Inserting a user in leaderboard with a default to 0 Points, when a user is created in the user table
  • Update the leaderboard when an attendee is added/deleted from the attendees table ( add = +10, delete = -10 points)

@dhyeydoshi
Copy link

dhyeydoshi commented Feb 28, 2023 •
edited

Comments:

Add deleted columns for attendee table
Auto Increment Primary key for attendee

# PROCEDURE get_event_attendees

DELIMITER $$
CREATE PROCEDURE `get_event_attendees`(IN event_id varchar(128))
BEGIN
    SELECT `user`.*
    FROM attendees
    INNER JOIN events ON attendees.event_id = events.id
    INNER JOIN `user` ON attendees.user_id = `user`.user_id
    WHERE events.id = event_id;
END$$
DELIMITER ;

call get_event_attendees(12);

# Procedure register_for_event

DELIMITER $$
CREATE PROCEDURE register_for_event (
  IN id	varchar(128),
  IN event_id varchar(128),
  IN user_id varchar(128)
)
 BEGIN
  INSERT INTO attendees (id, event_id, user_id) VALUES (id, event_id, user_id);
END$$
DELIMITER ;

call register_for_event(31, 17, 20);

# Procedure create_event

DELIMITER $$
CREATE PROCEDURE create_event (
  IN id varchar(128),
  IN name VARCHAR(255),
  IN description TEXT,
  IN starts_at DATETIME,
  IN ends_at DATETIME,
  IN cover_image VARCHAR(255),
  IN address varchar(100),
  IN private tinyint(1),
  IN user_id varchar(128)
)
BEGIN
  INSERT INTO events (id, created_at, name, description, starts_at, ends_at, cover_image, address, private, deleted, user_id)
  VALUES (id, now(),name, description, starts_at, ends_at, cover_image, address, private, '0',user_id);
END$$
DELIMITER ;

call create_event(21, 'abc', 'abc', now(), now(), 'aosbboa', 'New Bedford', 0, 18);

@dhyeydoshi
Copy link

Trigger Leaderboard Update

DELIMITER $$
create trigger update_leaderboard
after insert on csevents.user
for each row
begin
	insert into csevents.leaderboard(user_id, reputation)
    values(new.user_id, '0');
end$$
DELIMITER ;

@dhyeydoshi
Copy link

dhyeydoshi commented Mar 1, 2023 •
edited

@maheshmnj

DELIMITER $$

CREATE PROCEDURE `get_event_details` (
    IN eventId varchar(128),
    OUT eventName VARCHAR(50),
    OUT eventDate timestamp,
    OUT eventAddress VARCHAR(100),
    OUT userName VARCHAR(128)
)
BEGIN
    DECLARE errorMessage VARCHAR(4000);
    
    SELECT e.name, e.starts_at, e.address, u.username
    INTO eventName, eventDate, eventAddress, userName
    FROM events e
    JOIN `user` u on e.user_id = u.user_id
    WHERE e.id = eventId;
    
    
    IF eventName IS NULL THEN
        SET errorMessage = 'Event not found.';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = errorMessage;
    END IF;
END$$

DELIMITER ;

call get_event_details(12, @eventName, @eventDate, @eventAddress, @userName);

select  @eventName, @eventDate, @eventAddress, @userName;

@dhyeydoshi
Copy link

@maheshmnj

View for Popular Events

CREATE VIEW PopularEvents AS
SELECT events.id, events.Name, COUNT(attendees.id) AS AttendanceCount
FROM events
LEFT JOIN attendees ON events.id = attendees.event_id
GROUP BY events.id
ORDER BY AttendanceCount DESC
LIMIT 5;

select * from PopularEvents;

View for Most Recent Events

CREATE VIEW MostRecentEvents AS
SELECT e.name, e.starts_at, e.address, u.username
FROM events e
JOIN `user` u on e.user_id = u.user_id
ORDER BY starts_at DESC
limit 5;

select * from MostRecentEvents;

View for Attendees Per Event

CREATE VIEW AttendeesPerEvent AS
SELECT e.id, e.name, COUNT(a.id) AS AttendanceCount
FROM events e
JOIN attendees a ON e.id = a.event_id
GROUP BY e.id;

select * from AttendeesPerEvent;

@maheshmnj
Copy link
Author 

INSERT INTO user (user_id, email, avatar_url, isAdmin, username, created_at)
VALUES
('22', 'joerogan@example.com', 'http://example.com/avatar1.jpg', 1, 'joerogan', NOW());

@maheshmnj
Copy link
Author 

SET SQL_SAFE_UPDATES=0;
SET FOREIGN_KEY_CHECKS=0;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment