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

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?

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?