Suppose you have a database with two tables, employee and department (see below for exact schema). Write a SQL statement to answer the following questions:

1. Show a list of all employees that includes both the employee name and the department name.
2. Show a list of departments and include the number of employees in each department.
3. Show a list of all departments with no employees.
4. Show a list of departments with more than 2 employees
5. Show a list of departments that includes average and maximum salary in that department. Sort the list by the average salary.

Employee Table
---------------------
id
first_name
last_name
salary
department

Department Table
-----------------------
id
name

Describe a schema (or classes) to power an online restaurant reservation system. Describe the tables and the relationships beween the tables.

This system should allow a user to reserve a table at a restaurant for a certain time period and a certain party size.

It should allow the restauranteur to configure the system to describe their restaurant and manage reservations.

Think http://opentable.com

What is a database index? When would you use one? What's the downside of using an index?

What's the difference between a primary key and a unique key? What's a foreign key? What's a composite key?

When should you consider denormalizing data? What are the advantages of doing this? What are the disadvantages?

How can you optimize the performance of a poorly tuned query? How can you tell how the database will execute your query?

What is an artificial (derived) primary key? When should it be used? How would you compare this to using a natural primary key?

Explain the difference between dropping a table, truncating a table and deleting all the rows in a table.

Explain each of these types of joins:

• Inner Join
• Outer Join
• Left outer join
• Right outer join
• Full outer join
• Cross join

Explain the difference between the SQL "where" and "having" clauses.

Explain how you would implement the following database relationships when designing tables:

• one to one
• one to many
• many to many

Explain what a database transaction is. When would you use one? What problems could you case by using one?

What is CAP theorem? Describe how weakening consistency constraints can yield highly available distributed systems (e.g. databases). Give an example.

Describe consistent hashing and the advantages that it has over traditional hashing techniques. How can this technique help while scaling distributed systems, for example, distributed hash tables?

Imagine a scenario where your website's database can only handle 1,000 queries every second, and your traffic has grown enough to product 1,500 queries per second at peak times.

What can you do to deal with this problem in the short term? What's a long term solution to deal with ongoing growth in traffic?

Contrast and compare the features of Google's BigTable and Amazon's Dynamo databases

Explain the properties, advantages and drawbacks of these different types of NoSQL databases. Give examples of each type of database e.g. CouchDB is an example of a document Oriented Database.

• Document Oriented Database
• Ordered Key/Value Store
• Eventually consistent Key/Value Store
• Graph Database
• Object Database

Describe a schema (or classes) to power an online car rental system. Describe the tables and the relationships beween the tables.

Design the schema or the classes to implement a library for building card games with. This library should make it easy to build games like Rummy, Poker, Hearts, Whist etc.

Outline the implementation for two of the most important methods of the Deck class.