Topics:

Creating tables with primary keys

Creating related tables with appropriate foreign keys (1:M, M:N relationships)

Inserting data

Updating data

Selecting data (using logical operators, date comparisons, date functions)

Solution file: sample1.sql

Exercise 1

Write the SQL statement to create a table to store the following information about patients (with appropriate primary key):

Write the SQL statements to insert following patient information

First name	Last name	Gender	Date of Birth
John	Smith	Male	11/11/2011
Jane	Smith	Female	10/10/2010
Jim	Shaw	Male	9/9/2010

Exercise 2

Write the SQL statements to list the following

List of all patients

List of all male patients

List of all female patients

List of all patients born before 11/1/2011

List of all male patients born after 9/1/2010

Exercise 3

Write the SQL statements to perform the following changes to the data

Change Johns name to Jon Smith

Change Janes name to Jane Doe

Exercise 4 Practice (to be done at home)

Write the SQL statement to create a table to store the following information about products (with appropriate primary key):

Write the SQL statements to insert following product information

Product Name	Product Description	Manufacture Date
TVR120	TV Remote	11/11/2011
FLEX	Fitbit Flex	10/10/2010
FORCE	Fitbit Force	9/9/2010

Exercise 5 Practice (to be done at home)

Write the SQL statements to list the following

List of all products

List of all products manufactured after 1/1/2011

Write the SQL statements to perform the following changes to the data:

Change the name of FLEX to FLEX-2

Change the description of TVR120 to Television remote control

Exercise 6

The Acme job search website allows applicants to search for jobs and apply to multiple jobs.

Write the SQL statements to create tables to store information about Job positions and applicants.

Each job position has the following attributes: Job id, Job title, Job description, posting start date, posting end date

Each applicant has the following attributes: Name, Address, Phone, email

Write the SQL statements to insert the following job positions:

DEV1001	Java Developer	Java developer to write code	11/11/2011	1/1/2012
ANL1001	Analyst	Business analyst to write requirements	11/11/2011	11/30/2011
MGR101	Manager	Manager to run projects	11/11/2011	11/30/2011
ANL1002	Business Analyst	Business analyst to develop use-cases	12/1/2011	6/1/2012
MGR102	Sr. Manager	Manager to manage jr managers	1/1/2012	5/1/2012

Write the SQL statements to insert the following applicants:

John Doe	123 Market St	301-301-3010	john@email.com
Jane Doe	123 Market St	301-301-3011	jane@email.com
Jim Smith	234 Water St	240-240-2401	jim@jim.com
Mike Smith	567 University Dr	571-324-1234	mike@mike.com
Sue Shaw	678 Bell St	703-234-5467	sue@bell.com

Exercise 7

Write the SQL statements to create the table to hold the following job application information and insert the information into the table.

Applicant	Positions applied for (date applied)
John Doe	Java Developer (11/15/2011) Analyst (11/20/2011) Manager (11/11/2011)
Jane Doe	Manager (11/30/2011)
Jim Smith	Analyst (11/25/2011) Manager(11/15/2011)
Mike Smith	Sr. Manager (3/1/2012) Business Analyst (4/1/2012)

Exercise 8

Write the SQL statements (select statements) to obtain the following information on the job applications

List of all applicants

List of all jobs

List of all jobs available after 11/30/2011

List of all jobs active as of 11/15/2011

List of all applicants to the Java Developer job

List of all jobs applied by Jane Doe

List of all applicants and the jobs they applied

Number of jobs that John Doe applied to

List of all applicants to Java Developer and Analyst jobs

Number of applicants who have applied for the Sr. manager job