CareerCup

Interview Question Software Engineer / Developers

• 

  0

  of 0 votes
  1
  Answer

  create a database schema for...
  
  an application that tracks a bunch of users. the application has a list of races and users can sign up to run for races.
  the application also keeps track of if the user has completed their race
  
  give me queries for the following use cases:
  1. all the users in the app
  2. all the races that a user has signed up for
  3. all the races that a user has finished racing
  4. all the races that a has signed up for but not raced (meaning they signed up for a race but the race is over and they didn't run in it)
  5. find the races that any two users share

  - zkolhz on March 06, 2012 in United States Edit | Report Duplicate | Flag
  Software Engineer / Developer Database

create table user(
userid int, 
username varchar(200) 
)

create table race(
raceid int, 
racename varchar(200)
)


create table user_race(
userid int, 
raceid int, 
iscompleted varchar(2)
)
/*1*/
select * from user; 

/*2*/
select r.* from race r where exists 
(select 1 from user_race ur where ur.raceid = r.raceid and ur.userid = '::USERID::'); 

/*3*/
select r.* from race r where
exists 
(select 1 from user_race ur 
 where ur.raceid = r.raceid 
   and ur.iscompleted = '1'
   and ur.userid = '::USERID::')


/*4*/
select r.* from race r where
exists 
(select 1 from user_race ur 
  where ur.raceid = r.raceid 
    and ur.iscompleted = '0'
    and ur.userid = ':::USERID:')

/*5*/
select r.raceid
from user u, user_race ur, race r
where 
u.userid = ur.userid and ur.raceid = r.raceid
group by r.raceid
having COUNT(ur.userid) = 2