Using the Data Definition Language of SQL, complete the following
tasks:
- Create a table called Customers that has the following columns:
- cid --- integer of 6 digits, primary key
- name --- string
- address --- string
- Create another table called accounts that has the following columns:
- aid --- integer of 10 digits, primary key
- openAt -- date
- balance --- number up to 1 billion, with 2 digits after decimal point
- owner --- integer of 6 digits, references Customer table
- Alter table Customers to add another column called email (string).
Then, using SQL update query, complete the following tasks:
- Insert some sample data into table Customers and table Accounts;
- Insert all the employees from the HR database into table Customers,
using their employee number as their cid, full name as customer's
name, their email as customer's email, and their working
department's location address (street address, city and state
concatenated) as their customer's address;
- Delete an account with a given account id (aid);
- Delete all the accounts owned by a customer (or customers)
with the last name "King";
- Add $50 to the balance of each account whose account balance
is below $500.
In the end of the lab, drop both Customers and Accounts tables.