System description You are required to design and implement a small database app
ID: 3686985 • Letter: S
Question
System description You are required to design and implement a small database application to manage any data you are interested in. Some examples are listed below for your reference: 1, if you have a collection of CDs, you can manage the data of albums, musicians, songs, companies etc. 2, if you are interested in NBA/NFL games, you can manage the data of players, teams, coaches, matches etc. 3, you can also manage your favorite movies by storing and manipulating the data of movies, actors/actresses, directors etc.
Explanation / Answer
1. First we have to create a structure of table. After that we will insert data into it.
CREATE TABLE Customer_detail
(
Cutomer_ID Primary Key NOT NULL VARCHAR2(5),
First_name NOT NULL VARCHAR2(15),
Last_name VARCHAR2(15),
Customer_address VARCHAR2(255),
Customer_country NOT NULL varchar2 (100),
Cutomer_mobile NOT NULL NUMBER(12),
Email_id VARCHAR2(20)
);
2. when we will run this command, it will look like this.
Describe Customer_detail;
Cutomer_ID First_name Last_name Customer_address Customer_country Cutomer_mobile Email_id
3. Now we will insert data into following table
INSERT INTO Customer_detail (Cutomer_ID, First_name, Last_name, Customer_address, Customer_country, Cutomer_mobile,Email_id) VALUES ('100', 'Stuart', 'Braud', 'abc street', 'England', '111111111', 'abc@hotmail.com');
This will look like when we command
SELECT * from Customer_detail;
Cutomer_ID First_name Last_name Customer_address Customer_country Cutomer_mobile Email_id
100 Stuart Braud abc street England 111111111 abc@hotmail.com
4. Now we will insert more than one rows with a single command
INSERT INTO Customer_detail (Cutomer_ID, First_name, Last_name, Customer_address, Customer_country, Cutomer_mobile,Email_id)
VALUES ('101', 'Michel', 'johnsan', 'ijk street', 'USA', '222222', 'ijk@hotmail.com'),
('102', 'Adam', 'Kelly', 'xyz street', 'Canada', '33333333', 'xyz@hotmail.com'),
('103', 'Naureen', 'Naaz', 'mnr street', 'India', '9897547', 'mnr@gmail.com');
This will result in more number of rows simulataniously
5. SELECT * from Customer_detail;
Cutomer_ID First_name Last_name Customer_address Customer_country Cutomer_mobile Email_id
100 Stuart Braud abc street England 111111111 abc@hotmail.com
101 Michel johnsan ijk street USA 222222 ijk@hotmail.com
102 Adam Kelly xyz street Canada 33333333 xyz@hotmail.com
103 Naureen Naaz mnr street India 9897547 mnr@gmail.com
6. We can insert data into specific column only like
INSERT INTO Customer_deatil (Customer_id, First_name, Customer_country, Cutomer_mobile) VALUES ('502', 'sachin', 'India', '9194652')
7. This will result in when we command like
SELECT * from Customer_detail;
Cutomer_ID First_name Last_name Customer_address Customer_country Cutomer_mobile Email_id
100 Stuart Braud abc street England 111111111 abc@hotmail.com
101 Michel johnsan ijk street USA 222222 ijk@hotmail.com
102 Adam Kelly xyz street Canada 33333333 xyz@hotmail.com
103 Naureen Naaz mnr street India 9897547 mnr@gmail.com
502 Sachin Null Null India 9897547 Null
8. We can reteive column particular like
SELECT Customer_Id from Customer_detail;
this will give
Customer_Id
100
101
102
103
502
Other columns can also be retrived same above.
9. If we want to retreive a particular row then
SELECT * from Customer_detail WHERE Customer_Id='100';
Cutomer_ID First_name Last_name Customer_address Customer_country Cutomer_mobile Email_id
100 Stuart Braud abc street England 111111111 abc@hotmail.com
if you change customer_id, and put aonther one,it will give u detail of another row.
NOTE:
1. we have to strictly insert the data in that column in which NOT NULL is applied. we can leave that column free in which no restriction is applied. (like 5th entry).
2. There must not by any duplicacy in the value in which primary key is applied.Like First column Customer_id, all the values must be unique in that column, in other column it is possilbe to duplcate the values e.g. two names may in two rows , two country names may be same.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.