Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

Write SQL statements to create the following tables with the given constraints. i) account account_no balance Lab Task # 03 (Inserting Records into Tables): Write appropriate SQL statements to insert the records as

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 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]

Step by Step Solution

3.49 Rating (149 Votes )

There are 3 Steps involved in it

Step: 1

Lab Task 01 Schema Definition Create account Table CREATE TABLE account accountno CHAR5 PRIMARY KEY balance NUMBER NOT NULL CHECK balance 0 This query ... blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Concepts of Database Management

Authors: Philip J. Pratt, Mary Z. Last

8th edition

1285427106, 978-1285427102

Students also viewed these Programming questions

Question

Why is increased efficiency an advantage in a distributed database?

Answered: 1 week ago