Create Tables using SQL DDL
The following questions are based on the Bicycle Rental ERD as shown below:
![167_Write and execute SQL definition commands.png](https://secure.tutorsglobe.com/CMSImages/167_Write%20and%20execute%20SQL%20definition%20commands.png)
Tasks:
- Write the SQL DDL to create the database that contains each of the relations shown in the above ERD. You will need to provide:
- Your DDL code for each table that you create; (20 marks)
- A screenshot showing each table that is created. (10 marks)
- Write and execute SQL definition commands for each of the following queries:
- Add the attributes Email, Comments and Send Newsletter to the Renters table. Give those attributes an appropriate size and data type (3 marks).
- Change the attribute Renters.renterDOB from type Varchar(15) to type DATE. (2 marks)
- Write and execute SQL commands for the following:
- Add the following customers to the Renters table. (3 marks)
- Write a command that will remove the Jones that lives in Orange from the Renters table (2 marks)
id_r
|
r_lname
|
rent_fname
|
r_address
|
r_town
|
r_state
|
r_pcode
|
r_phone
|
r_dob
|
12345
|
Jones
|
George
|
123 Bentinck St
|
Bathurst
|
NSW
|
2795
|
0212345678
|
01/05/1964
|
23456
|
Jones
|
Pauline
|
48 Summer St
|
Orange
|
NSW
|
2800
|
0223456789
|
11/09/1986
|
28765
|
Wilson
|
Paul
|
23 Long St
|
Blayney
|
NSW
|
2799
|
0298745621
|
22/12/1977
|
You are required to submit:
- The appropriate SQL command which should be copied from your source code in MySQL and pasted into your submission file; and
- The resultant tables, which must be screenshots to show the change due to the execution of the SQL commands.
Typing or manually drawing the results will NOT be accepted.