Brain Teasers Database Design

SQL Queries and Updates

Here are some sample queries and updates on my database.

This query selects the hard teasers (rating>9).

SQL> select id, title, rating from teaser where rating>9 order by rating desc;

        ID TITLE                                                                
---------- ----------------------------------------------------------------     
    RATING                                                                      
----------                                                                      
         4 Body Parts                                                           
   9.60921

This selects all riddles.

 
SQL> select id, title from teaser where category = 'Riddle';

        ID TITLE                                                                
---------- ----------------------------------------------------------------     
         1 I Never Was                                                          
         2 Once in a Minute                                                     
         3 Food and Water

Selects the highest rating teaser.

SQL> select id, title, rating from teaser T1 where not exists ( select * from teaser T2 where T1.rating < T2.rating);

        ID TITLE                                                                
---------- ----------------------------------------------------------------     
    RATING                                                                      
----------                                                                      
         4 Body Parts                                                           
   9.60921

Selects users ranked by number of submitted teasers.

SQL> select submitted.username,count(*) from account,submitted where submitted.username = account.username group by submitted.username order by count(*) desc;

USERNAME           COUNT(*)                                                     
---------------- ----------                                                     
jolefsky                  2                                                     
JohnC                     1                                                     
superF                    1                                                     
turkeyboy                 1                                                     
babyfishmouth             1

Selects all teasers not yet approved.

SQL> select teaser.id, title from teaser,submitted where teaser.id = submitted.id and approveddate < submitdate;

        ID TITLE                                                                
---------- ----------------------------------------------------------------     
         6 Birthdays

Finds good teasers that got lots of views when featured on the web page

SQL> select id, title, rating from teaser where rating >9 AND id in (select id from featured where numviews > 1000);

        ID TITLE                                                                
---------- ----------------------------------------------------------------     
    RATING                                                                      
----------                                                                      
         4 Body Parts                                                           
   9.60921

Finds the most successful users

SQL> select username, count(*) from answered where correctP=1 group by username;

USERNAME           COUNT(*)                                                     
---------------- ----------                                                     
Brainmaster               1                                                     
jolefsky                  1

Adds a user.

SQL> insert into account values ('shannonc', 'tulip', 1, DATE '2000-02-14', 'shannon@hotmail.com', 'Shannon Conroy');

1 row created.

Inserts a request for an answer to teaser #2.

SQL> insert into wantsanswer values (2, 'klin');

1 row created.

Updates an account when the user logs in.

SQL> update account set numlogins=numlogins+1, lastlogin = sysdate where username = 'turkeyboy';

1 row updated.

This is the update statement that I would run to approve a teaser.

SQL> update submitted set approved = 'yes', approveddate = sysdate where id = 5;

1 row updated.

Features the highest ranking teaser not already featured.

 
SQL> insert into featured select sysdate, 0, id from teaser where id not in (select id from featured) AND rating >= ALL (select rating from teaser where id not in (select id from featured));

1 row created.

Insert into 'submitted' all teasers not already there and set them to submitted by unknown.

SQL> insert into submitted select id, 'unknown', DATE '1990-01-01', DATE '1990-01-01', 'yes' from teaser where id not in (select id from submitted);

0 rows created.

Deletes old users

SQL> delete from account where lastlogin < sysdate - 60;

5 rows deleted.

Deletes teasers that are not approved

SQL> delete from teaser where id in (select id from submitted where approved = 'no');

1 row deleted.

Back to Table of Contents

by Jake Olefsky - June 2000