Starting from:
$30

$24

INFSCI 1022 Lab 3 – Introduction to SQL Solved

1.    Write SQL queries for each of the tasks using MySQL workbench.  
2.    You cannot use Workbench’s GUI to create database schemas and tables – you must write your own SQL statements.  
3.    Follow the underscore convention when naming your database schema, entities and attributes.

Consider the following two tables and their logical structures:

accounts

Field name    Primary Key    Data type    Is null?    Is auto-increment?
account_id    yes    INT    no    yes
balance    no    DOUBLE    no    no
type    no    VARCHAR(30)    no    no
date_opened    no    DATETIME    no    no
status    no    VARCHAR(30)    no    no

transactions

Field name    Primary Key    Data type    Is null?    Is auto-increment?
transaction_id    yes    INT    no    yes
date_time    no    DATETIME    no    no
amount    no    DOUBLE    no    no
remaining_balance    no    DOUBLE    no    no
account_id    no    INT    no    no

Task 1:  Create a database called bank using CREATE DATABASE statement.

Task 2: In database bank, create the following entity tables:
1.    account
2.    transactions
Each tables logical structure should correspond to the descriptions provided in this assignment.  Use CREATE TABLE statement.

Task 3: Use ALTER TABLE statement to create a foreign key connection account_id in transactions table to account_id in accounts table.

Task 4:  For each entity table, insert rows using INSERT statement:
1.    At least 3 accounts in the accounts table
2.    At least 5 transactions in the transactions table.  Make sure that at least one account has multiple transactions.
You can make up your own data for the INSERT statements.

Task 5:  Write a SELECT statement to display top 2 accounts sorted by date created in descending order.

Task 6:  Write a SELECT statement to display transactions for a particular account number (hard-code account number in the where clause).

Task 7:  Write an UPDATE statement to update all account statuses to ‘active’.

Task 8:  DELETE one row of your choice from transactions table.  Be careful – make sure to write a correct WHERE clause.

Save your SQL script as YourLastname_Assignment#.sql and submit through CourseWeb.  

More products