Write SQL statements to create the following tables with the given constraints. i) account account_no balance...

 

Transcribed Image Text:

Write SQL statements to create the following tables with the given constraints. i) account account_no balance char(5) primary key number Not null and cannot be less than 0 ii) customer customer_no char(5) primary key customer_name varchar2(20) Not null customer_city varchar2(10) iii) depositor account_no char(5) customer_no char(5) primary key (account_no, customer_no) Lab Task #02 (Schema Modification): After executing each of these SQL statements execute the command-dese <table_name> to confirm the changes. i. Write SQL statement to add a new attribute 'date of birth' (date type) in customer table. ii. Write SQL statement to drop the attribute 'date of birth' from customer table. iii. Write SQL statement to rename the attribute account_no, customer no from depositor table to a no and e_no, respectively. iv. Write SQL statements to add two foreign key constraints 'depositor_fkl' and 'depositor_fk2' which identifies a_no and c_no as a foreign key. Lab Task # 03 (Inserting Records into Tables): Write appropriate SQL statements to insert the records as shown below. Account Customer ACCOUNT NO BALANCE CUSTOMER NO CUSTOMER_NAME CUSTOMER_CITY A-101 12000 C-101 Alice Dhaka Depositor ANO CNO A-101 C-101 A-102 6000 C-102 Annie Dhaka A-103 C-102 A-103 2500 C-103 Bob Chittagong A-103 C-104 C-104 Charlie Khulna A-102 C-103 Lab Task #04 (Queries): i. Display customer name and customer city only. ii. Display the unique customer city. No repetitions are allowed. iii. Find account numbers with balance more than 7000. iv. Find customer number and customer name who live in Khulna. v. Find customer number and customer name who do not live in Dhaka. vi. Find customer name and customer city who have accounts with balance more than 7000. vii. Find customer name and customer city who have accounts with balance more than 7000 and do not live in Khulna. viii. Find account number and balance for those accounts which belong to a customer with id 'C-102'. ix. Find all account number and balance for those accounts which belong to customers of Dhaka and Khulna city. x. Find the customer who have no accounts. [Result of this query will be empty for this dataset. However, you must write the correct SQL] Write SQL statements to create the following tables with the given constraints. i) account account_no balance char(5) primary key number Not null and cannot be less than 0 ii) customer customer_no char(5) primary key customer_name varchar2(20) Not null customer_city varchar2(10) iii) depositor account_no char(5) customer_no char(5) primary key (account_no, customer_no) Lab Task #02 (Schema Modification): After executing each of these SQL statements execute the command-dese <table_name> to confirm the changes. i. Write SQL statement to add a new attribute 'date of birth' (date type) in customer table. ii. Write SQL statement to drop the attribute 'date of birth' from customer table. iii. Write SQL statement to rename the attribute account_no, customer no from depositor table to a no and e_no, respectively. iv. Write SQL statements to add two foreign key constraints 'depositor_fkl' and 'depositor_fk2' which identifies a_no and c_no as a foreign key. Lab Task # 03 (Inserting Records into Tables): Write appropriate SQL statements to insert the records as shown below. Account Customer ACCOUNT NO BALANCE CUSTOMER NO CUSTOMER_NAME CUSTOMER_CITY A-101 12000 C-101 Alice Dhaka Depositor ANO CNO A-101 C-101 A-102 6000 C-102 Annie Dhaka A-103 C-102 A-103 2500 C-103 Bob Chittagong A-103 C-104 C-104 Charlie Khulna A-102 C-103 Lab Task #04 (Queries): i. Display customer name and customer city only. ii. Display the unique customer city. No repetitions are allowed. iii. Find account numbers with balance more than 7000. iv. Find customer number and customer name who live in Khulna. v. Find customer number and customer name who do not live in Dhaka. vi. Find customer name and customer city who have accounts with balance more than 7000. vii. Find customer name and customer city who have accounts with balance more than 7000 and do not live in Khulna. viii. Find account number and balance for those accounts which belong to a customer with id 'C-102'. ix. Find all account number and balance for those accounts which belong to customers of Dhaka and Khulna city. x. Find the customer who have no accounts. [Result of this query will be empty for this dataset. However, you must write the correct SQL]

Answer rating: 100% (QA)

Lab Task 01 Schema Definition Create account Table CREATE TABLE account accountno CHAR5 PRIMARY KEY balance NUMBER NOT NULL CHECK balance 0 This query ... View the full answer