MySQL CRUD workshop solutions

From Knowledge Kitchen
Jump to navigation Jump to search


The following are some example table structures and queries that solve the problems outlined in the MySQL CRUD workshop


TWITTER WORKSHOP

Table name: tweets

  • tweet_id - INT AUTO_INCREMENT PRIMARY KEY
  • user - VARCHAR (10)
  • tweet - VARCHAR (140)
  • created - TIMESTAMP DEFAULT CURRENT_TIMESTAMP
#read the first 20 records from the table
SELECT * FROM tweets ORDER BY created DESC LIMIT 0,20;

#read the next 20 records from the table
SELECT * FROM tweets ORDER BY created DESC LIMIT 20,20;

#read the next 20 records from the table
SELECT * FROM tweets ORDER BY created DESC LIMIT 40,20;

#show only tweets from user "foobar"
SELECT * FROM tweets WHERE user="foobar" ORDER BY created DESC LIMIT 0,20;

#view tweets with hastag "#amazing"
SELECT * FROM tweets WHERE tweet LIKE "%#amazing%" ORDER BY created DESC LIMIT 0,20;

BLOG POSTS WORKSHOP

Table name: blog_posts

  • post_id - INT AUTO_INCREMENT PRIMARY KEY
  • category - VARCHAR (50)
  • post - TEXT
  • created - TIMESTAMP DEFAULT CURRENT_TIMESTAMP
#read the first 20 records from the table
SELECT * FROM blog_posts ORDER BY created DESC LIMIT 0,20;

#read the next 20 records from the table
SELECT * FROM blog_posts ORDER BY created DESC LIMIT 20,20;

#read the next 20 records from the table
SELECT * FROM blog_posts ORDER BY created DESC LIMIT 40,20;

#show only blog posts from category "foobar"
SELECT * FROM blog_posts WHERE category="foobar" ORDER BY created DESC LIMIT 0,20;

#view blog posts with keyword "amazing"
SELECT * FROM blog_posts WHERE post LIKE "%amazing%" ORDER BY created DESC LIMIT 0,20;


EMAIL CLIENT WORKSHOP

Table name: emails

  • email_id - INT AUTO_INCREMENT PRIMARY KEY
  • subject - VARCHAR (150)
  • content - TEXT
  • sender - VARCHAR (100)
  • read - BOOLEAN DEFAULT 0
  • folder - VARCHAR(100) DEFAULT "INBOX"
  • created - TIMESTAMP DEFAULT CURRENT_TIMESTAMP
#view emails from a particular sender
SELECT content, sender FROM emails WHERE sender="foo@bar.com" ORDER BY created DESC LIMIT 0,20;

#view emails with a particular keyword
SELECT * FROM emails WHERE content LIKE "%amazing%" ORDER BY created DESC LIMIT 0,20;

#view emails within a particular folder
SELECT * FROM emails WHERE folder="INBOX" ORDER BY created DESC LIMIT 0,20;

#modify a given email's folder setting
UPDATE emails SET folder="business" WHERE email_id=15;

#modify a bunch of emails' folder settings at once
UPDATE emails SET folder="business" WHERE sender="foo@bar.com";

#delete a given email
DELETE FROM emails WHERE email_id=15;

#delete a bunch of emails
DELETE FROM emails WHERE subject LIKE "%FREE CIALIS%";

#mark email as read
UPDATE emails SET read=1 WHERE email_id=15;



What links here