famtastic-fritz / code-examples Goto Github PK

##code-examples

This is exercise meant to be a validation of fundamental knowledge of basic concepts. Unfortunately, too many candidates who claim to have expert level knowledge of web application development fail to grasp the basics. This should be trivially easy for those with actual skills.

1. Fork this repo.
2. Provide answers to tasks in the form of new code & comments.
3. Commit your code
4. If you want to keep your submission in a private repo, add thajosh to the repo as a member with view access.

Explain what the code in admin.php does, line by line, in plain, concise english. Identify as many bugs as you can and why they are bugs.

Context: A page called login.php asks for a username and password. If the password is correct, it redirects the user to admin.php.

Task 1 Answer: commit updated admin.php with your comments

Given the table below, improve the structure of this database (adjust fields, data format, tables, order, field types, etc).

Hint: you should have more than one table.

CREATE TABLE employee_info (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64),
	email1 VARCHAR(64),
	email2 VARCHAR(128),
	password VARCHAR(14),
	company_name VARCHAR(128),
	company_phone CHAR(11),
	company_address VARCHAR(128),
	job_title CHAR(256),
	salary VARCHAR(10),
	permission_level VARCHAR(32),
	is_permission_level_active VARCHAR(10),
	start_date VARCHAR(11)
);

Answer should be a new "CREATE TABLE" script.

SQL for creating table does not have to include data, just structure.

Sample output from employee_info table:

Employees
id, username, email1, email2, password, company_name, company_phone, company_address, job_title, salary, permission_level, is_permission_level_active, start_date
37, JohnSmith, [email protected], [email protected], 764efa883dda1e11db47671c4a3bbd9e, ACME Inc, 555-2323, 123 Acme Way, Director, $100,000, admin, true, 2012-02-23
39, JaneSmith, [email protected], NULL, 153c2c618d157c4a3ba08e23a06b70d7, ACME INC, 555-2323, 123 ACME WAY, VP, $120,000, user, true, 99-03-19
40, ChrisSmith, [email protected], NULL, 34921ae7e3079e44e0ee2f96d8ee6e87, Example Dot Com, 555-1234, 456 Example Lane, Manager, $60,000, user, false, 2010-8-4
42, KateSmith, [email protected], NULL, dbe39d9d56f98d5f9cb22df15b285791, Example Dot Com, 555-1234, 456 Example Lane, Sr Manager, $80,000, admin, false, 2004-12-24

Task 2 Answer: commit a new file db.sql with your SQL statement(s)

After improving the database, write a raw sql statement that gets the username, first email, start_date, salary, and company_name for all active admins from your new database format.

This should be written in SQL, not PHP.

Task 3 Answer: commit a new file fetch.sql with your SQL statement(s)