Write SQL statements to create the following tables with the given constraints. i) account account_no balance...
Fantastic news! We've Found the answer you've been seeking!
Question:
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]
Expert Answer:
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
Related Book For
Concepts of Database Management
ISBN: 978-1285427102
8th edition
Authors: Philip J. Pratt, Mary Z. Last
Posted Date:
Students also viewed these programming questions
-
In preparing its December 31, Year 4, financial statements, Irene Corp. must determine the proper accounting treatment of a $180,000 loss carryforward available to offset future taxable income. There...
-
ConAir (CA) commercial airlines company requires you experience in database design and have approached you to assist in the design of their new airline database system. They urgently need to have...
-
There is an XYZ Company, which purchases some parts from vendors to produce some products. It has several departments, marketing sites, and parts supply vendors in the company. 1) For each...
-
What is the output of the following application? A. 5,LONG B. 6,LONG C. 5,null D. 6,null E. The code does not compile. F. The code compiles but throws an exception at runtime. package fly; public...
-
1. Suppose that the sportswriter in Exercise 36 simply puts the 12 team names in a hat and draws 3 completely at random. Does this increase or decrease the writer's chance of picking at least one...
-
Chicago Supply Company manufactures paper clips and other office products. Although inexpensive, paper clips have provided the firm with a high margin of profitability. Sample size is 200. Results...
-
Understanding whether agreed-upon procedures relate to a specified element, account, or item of a financial statement or to a nonfinancial statement assertion is important because the professional...
-
College Supply Company (CSC) makes three types of drinking glasses: short, medium, and tall. It presently applies overhead using a predetermined rate based on direct labor-hours. A group of company...
-
Jack Sparrow, Will Turner, and James Norrington each want Davy Jones' heart for their own ends. The dead man's heart is hidden in a chest buried on Isla Cruces, but one needs the key to unlock the...
-
WW Development Inc. (WW), a real estate developer, was incorporated in Canada in 1975 by two brothers, Peter and David Wang. In the 1980s and 1990s, WW was quite successful. In early 2002, Peter and...
-
1) The Handmaids Tale has recently exploded in popularity and been turned into a television series. Why are people today connecting with this text based on the current social climate? What real-world...
-
Describe the process for developing routine business messages.
-
Analyze presentation audiences in terms of message benefits, learning styles, and communicator styles.
-
Describe how delivering routine messages impacts credibility.
-
Describe how planning your presentations leads to credibility.
-
Explain how the tone and style of persuasive messages impact their influence.
-
Use the formula r(t) (9)- B'(t)- N(t) Ir'(t)| to find the torsion at the given value of t. r(t) = (sin(t), 6t, cos(t)), t= -7/22 EN
-
Write a paper about medication error system 2016.
-
Why is increased efficiency an advantage in a distributed database?
-
You have a table that contains the following fields: Member Last Name, Member First Name, Street, City, State, Postal Code, and Membership Fee. There are 75,000 records in the table. What indexes...
-
What are the SQL built-in functions? How do you use them in an SQL query?
-
A bank offers customers the option of receiving interest compounded quarterly, semi-annually, or annually. If the rate of interest is the same, which is the best option for the customer?
-
What is a stock market index? What is an index designed to accomplish?
-
What are an LLC and an LLP? Why have they become more popular business entities over the past ten years? What concerns should an investor investigate before forming an LLC or LLP?
Study smarter with the SolutionInn App