Answered step by step
Verified Expert Solution
Question
1 Approved Answer
CREATE TABLE person ( ID NUMBER, Name VARCHAR2 (20), Home VARCHAR2 (30), Age NUMBER (2), Occupation VARCHAR2 (45), Gender VARCHAR2 (6), Salary NUMBER, CONSTRAINTS PK_person
CREATE TABLE person ( ID NUMBER, Name VARCHAR2 (20), Home VARCHAR2 (30), Age NUMBER (2), Occupation VARCHAR2 (45), Gender VARCHAR2 (6), Salary NUMBER, CONSTRAINTS PK_person PRIMARY KEY (ID) CREATE TABLE Phone ( ID number, brand varchar2 (40), model varchar2 (49), screensize number); INSERT INTO person VALUES (1, aisha', 'Dhaka', 25, Teacher', 'Female', 50000); INSERT INTO person VALUES (2, 'Saad', 'Dhaka', 56, 'Service', 'Male', 60000); INSERT INTO person VALUES (3, 'Rakeen', 'ctg', 71, 'Retired', 'Male', 10000); INSERT INTO person VALUES (6, 'Ilma', 'Gazipur', 54, 'Doctor', 'Female', 55000); INSERT INTO person VALUES (7, 'Rajib', 'Gazipur', 65, 'Musician', 'Male', 5000); INSERT INTO person VALUES (8, 'Raisa', 'Dhaka', 56, 'Engineer', 'Female', 60000); INSERT INTO person VALUES (9, 'Sakib', 'ctg', 23, 'Student', 'Male', 1000); INSERT INTO person VALUES (10, 'Mosaddek', 'Comilla', 32, 'Teacher', 'Male', 45000); INSERT INTO person VALUES (11, 'Jarin', 'Comilla', 51, 'Farmer', 'Female', 20000); INSERT INTO person VALUES (12, 'Rudaba', 'Khulna', 15, 'Student', 'Female', 1500); INSERT INTO person VALUES (13, 'Sami', Ctg', 25, 'Business', 'Male', 100000); INSERT INTO person VALUES (14, 'Nihal', 'Comilla', 52, 'Doctor', 'Male', 70000); INSERT INTO person VALUES (15, 'Rafid', 'Gazipur', 53, 'Teacher', 'Male', 50000); INSERT INTO person VALUES (16, 'Medha', 'Dhaka', 35, 'Musician', 'Female', 50000); INSERT INTO person VALUES (17, 'Sakib', 'Khulna', 43, 'Service', 'Male', 50000); INSERT INTO person VALUES (18, 'Zobaeir', 'Khulna', 34, 'Service', 'Male', 45000); INSERT INTO person VALUES (19, 'Shahriyar', 'ctg', 16, 'Student', 'Male', 500); INSERT INTO person VALUES (20, 'Mahir', 'Comilla', 32, 'Business', 'Male', 120000); INSERT INTO person VALUES (21, 'Mushfiqur', 'ctg', 25, 'Musician', 'Male', 100000); INSERT INTO person VALUES (22, 'Najish', 'Gazipur', 14, 'Student', 'Male', 400); INSERT INTO person VALUES (23, 'Bandhan', 'Dhaka', 25, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (24, 'Shadman', 'ctg', 28, 'Teacher', 'Male', 80000); INSERT INTO person VALUES (25, 'Faria', 'Dhaka', 25, 'Engineer', 'Female', 50000); INSERT INTO person VALUES (26, 'Taki', 'Dhaka', 26, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (27, 'Tanzir', 'Dhaka', 45, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (28, 'Alvi', 'ctg', 23, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (29, 'Nasib', 'Dhaka', 21, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (30, 'Mim', 'Dhaka', 75, 'Teacher', 'Female', 40000); INSERT INTO person VALUES (31, 'Ornob', 'Dhaka', 43, Student', 'Male', 50000); INSERT INTO person VALUES (32, 'Shuvo', 'Dhaka', 2, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (33, 'Anika', 'Dhaka', 56, 'Engineer', 'Female', 50000); INSERT INTO person VALUES (34, 'Ikra', 'Ctg', 78, 'Teacher', 'Female', 50000); INSERT INTO person VALUES (35, 'ojhor', 'Dhaka', 12, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (36, 'Saadman', 'Dhaka', 32, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (37, 'Nafiz', 'Dhaka', 15, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (38, 'Arman', 'Dhaka', 55, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (39, 'Faysal', 'Dhaka', 23, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (40, 'Syed', 'Dhaka', 22, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (41, 'Abidure', 'Dhaka', 21, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (42, 'Nafisa', 'Dhaka', 24, 'Engineer', 'Female', 50000); INSERT INTO person VALUES (44, 'Abu', 'ctg', 25, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (45, 'Maruf', 'Dhaka', 26, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (46, 'Omar', 'Dhaka', 27, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (47, 'Mahdia', 'Dhaka', 28, 'Engineer', 'Female', 50000); INSERT INTO person VALUES (48, 'Nazneen', 'Dhaka', 29, 'Engineer', 'Female', 50000); INSERT INTO person VALUES (49, 'Ahikul', 'Dhaka', 30, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (50, 'Asif', 'Dhaka', 31, 'Musician', 'Male', 50000); INSERT INTO person VALUES (51, 'Abir', 'ctg', 32, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (52, 'Afrin', 'Dhaka', 22, 'Engineer', 'Female', 50000); INSERT INTO person VALUES (53, 'Evan', 'Dhaka', 78, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (55, 'Muhib', 'ctg', 66, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (56, 'Zizan', 'Dhaka', 90, 'Musician', 'Male', 40000); INSERT INTO person VALUES (57, 'Tansi', 'Dhaka', 44, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (58, 'Mumu', 'ctg', 87, 'Engineer', 'Female', 1000); INSERT INTO person VALUES (59, 'Elma', 'Dhaka', 25, 'Teacher', 'Female', 9000); INSERT INTO person VALUES (60, 'Emon', 'Dhaka', 33, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (61, 'Tanzil', 'Dhaka', 89, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (62, 'Nafi', 'Dhaka', 75, 'Engineer', 'Male', 50000); INSERT INTO person VALUES (63, 'Azraf', 'ctg', 54, 'Engineer', 'Male', 50000); Execute the the command given in "person.sql" file The sql file contains the necessary DDL statements to create and store values inside the table. The table is as follows: PERSON (ID, name, hometown, age, occupation, gender, salary) Phone (ID, brand, model, screensize) Now, execute the following queries: 7. What will be the age of all the persons after 5 years? (Result shows limited rows, there are more). NAME AGE_IN_FUTURE 30 61 76 59 Maisha Saad Rakeen Ilma Rajib Raisa Sakib Mosaddek Jarin Rudaba Sami Nihal Rafid Medha Sakib Zobaeir Shahriyar Mahir 70 61 28 37 56 20 30 57 58 40 48 39 21 37 8. Make a list of people which will show how many people belong to which occupation? (hint: use group by) OCCUPATION COUNT Doctor Engineer Student Business Retired Farmer teacher Musician Teacher Service 2 51 4 2 1 1 1 5 8 3 9. Categorize the average salary of female persons based on occupation where the salary is at least 10000. (hint: having clause) OCCUPATION AVERAGE Doctor Engineer Farmer Musician Teacher 55000 42600 20000 50000 37250 10. Count the number of people who don't live in Dhaka and have average salary more than 30000. Show their home district and count. COUNT HOME ctg Comilla Jashore Khulna 14 5 1 3 11. List the name of the persons with their id, occupation and age whose name starts with 'M'. ID NAME OCCUPATION AGE 1 Maisha 10 Mosaddek 16 Medha 20 Mahir 21 Mushfiqur 30 Mim 45 Maruf 47 Mahdia 55 Muhib 58 Mumu 74 Muhtasim Teacher Teacher Musician Business Musician Teacher Engineer Engineer Engineer Engineer Engineer 25 32 35 32 25 75 26 28 66 87 39 12. List the name of the persons who have at least 7 characters in their name. ID NAME OCCUPATION 10 Mosaddek 18 Zobaeir 19 Shahriyar 21 Mushfiqur 23 Bandhan 24 Shadman 36 Saadman 41 Abidure 48 Nazneen 64 Istiaque 66 Rubaiat 69 Abdullah 74 Muhtasim 80 Shams Shuvo 83 Ibrahim Teacher Service Student Musician Engineer Teacher Engineer Engineer Engineer Engineer Engineer Engineer Engineer Engineer Engineer 13. Which brand and model of phone does ID 1 use? Show like the following. BRAND ID NAME 1 Maisha MODEL galaxy note 8 Samsung 14. How many people living in Ctg have a phone screen size bigger than 6 inch? COUNT_IN_CTG 6 15. Add an attribute to the 'person' named 'data_of_birth' with appropriate datatype
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started