Topics: Creating tables with primary keys Creating related tables with appropria
ID: 3704167 • Letter: T
Question
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 John’s name to Jon Smith
Change Jane’s 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
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
Explanation / Answer
Answer is as follows:
According to Chegg Guidelines, I will provide the Answer of first three Exercices, So please repost others.
Exercixe 1 : create and insert statments of given data
/* Create a table called NAMES */
CREATE TABLE patients(FirstName text, Lastname Text, Gender text, Dob date);
/* Create few records in this table */
INSERT INTO patients VALUES('John', 'Smith', 'Male', '2011-11-11') ;
INSERT INTO patients VALUES('Jane', 'Smith', 'Feale', '2010-10-10') ;
INSERT INTO patients VALUES('Jim', 'Shaw', 'Male', '2011-11-11') ;
Exercixe 2 selectr queries
a) SELECT * FROM patients;
b) SELECT * FROM patients WHERE Gender = 'Male';
c) SELECT * FROM patients WHERE Gender = 'Female';
d) SELECT * FROM patients WHERE Dob < '2011-1-11';
e) SELECT * FROM patients WHERE Dob > '2010-1-09';
Exercixe 3 : Update queries
a) UPDATE patients SET Firstname = 'Jon' Where Firstname = 'John' AND Lastname = 'Smith' ;
b) UPDATE patients SET Lastname = 'Doe' Where Firstname = 'Jane' AND Lastname = 'Smith' ;
if there is any query please ask in comments....