Create a script like the CreateCompanyPG.sql file to create 3 or more tables and

Question

Create a script like the CreateCompanyPG.sql file to create 3 or more tables and populate the tables. You may find it more convenient to break this up into a creation script to create the tables and a population script to fill the tables with data.

Insert data -- insert records for each table. Considering the queries below, make sure to enter enough data to check if your queries are correct. Your data should be from the website when available, but in some cases (like customers or orders), you may need to make up data.

QUERIES -- one basic, one using aggregate operations (AVG, MIN, COUNT, …) , one using at least one subquery (either nested or set operation, like EXCEPT, WHERE … IN, WHERE … =). The queries should be in a separate file to make it easier to execute them separately from the creation/population statements.

Explanation / Answer

->> CREATION OF TABLES ::

Table 1 : SAILORS TABLE

create table Sailors
(
sid number(2),
sname varchar2(25),
rating number(2),
age number(4,2),
constraint Sailors_pk primary key(sid)
);

Table created.

Table 2 : BOAT TABLE

create table boat
(
bid number(3),
bname varchar2(10),
color varchar2(8),
constaint boat_pk primary key(bid)
);

Table created.

Table 3 : RESERVE TABLE

create table reserves
(
sid number(2),
bid number(3),
day date,
constraint reserves_pk primarykey((sid,bid,day),foreign key(sid) references sailors,foreign key(bid) references boat);

Table created.

-->> INSERTION OF DATA ::

Insertion of data into Table 1 :

insert into sailors values(22,'Smith',7,45.0);
insert into sailors values(29,'Samrat',1,33.0);
insert into sailors values(31,'John',8,55.5);
insert into sailors values(85,'Snoopy',3,22.5);
insert into sailors values(22,'Smith',7,45.0);
insert into sailors values(95,'Jerry',3,63.5);

5 rows are inserted.

Insertion of data into Table 2 :

insert into boat values(101,'Inter lake','Blue');
insert into boat values(102,'Inter lake','Red');
insert into boat values(103,'Chipper','Green');
insert into boat values(104,'Marine','Blue');

4 rows are inserted.

Insert data into Table 3 :

insert into boat reserves(22,101,'10-OCT-98');
insert into boat reserves(22,102,'10-OCT-98');
insert into boat reserves(22,103,'10-AUG-98');
insert into boat reserves(64,102,'10-OCT-98');
insert into boat reserves(74,103,'09-AUG-98');

5 rows are inserted.

-->> QUERIES ::

BASIC QUERY : Display sailors table
  
select * from sailors;

USING AGGREGATE OPERATIONS : Find the average age from the sailors table

select AVG(age) from sailors;

USING SUB-QUERY : Find the names of the sailors who reserved boat number 101

select sailors.sname from sailors where sailors.sid in (select reserves.sid from reserves where reserves.bid=101);

///*** Thank You ***///

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.