Tables Create table Item nbs Bookmark Tables Create table It

Tables:

Create table Item(    &nbs...

Bookmark

Tables:

Create table Item(

                ItemId                 char(5) constraint itmid_unique primary key,

                Decription           varchar2(30),

                Unitcost               number(7,2));

Create table Customer(

                custID                   char(5) constraint cid.unique primary key,

                custName          varchar2(20),

                address                                varchar2(50));

Create table Orderdata(

orderID                char(5) constraint oid_uniq primary key,

                orderdate           date,

                shipdate              date,

                ItemId                  char(5) references Item.ItemId,

                No_of_items     number(4),

                Unitcost               number(7,2),

                Order_total        number(7,2),

                custID                   char(5) references customer.custID);

Insert Into Item values(‘A123’,’Pencil’,2.5);

Insert Into Item values(‘B123’,’Pen’,15);

Insert Into Customer(‘C123’,’ABC Gen stores’,’Sanfransico’);

Insert Into Customer(‘C132’,’XYZ stores’,’California’);

Insert into Orderdata(‘o123’,’12-aug-2016’,’12-aug-2016’,’A123’,5,2.5,12.5,’c123’);

Insert into Orderdata(‘o124’,’14-aug-2016’,’14-aug-2016’,’B123’,5,15,75,’c132’);

_____________________________________________________________

Enhance your Module 5 CT database table structures, via your selected RDBMS, as you wish.

Then, using SQL and an SQL script file, add at least five more rows of data to each of your tables.

Then, using SQL and an SQL script file, create and execute advanced queries of your choice that demonstrate each of the following:

The use of a GROUP BY clause, with a HAVING clause, and one or more group functions

The use of UNION operator

The use of a subquery

The use of an outer join

Then create and execute at least one SQL UPDATE and at least one SQL DELETE query.

Finally, create and use an SQL view and in a SELECT query.

Submit the following outputs of your SQL script file processing, in this order and appropriately labeled, in a single Word file:

The SQL and results of your INSERT statements to further populate your tables

The SQL and results of your 4 advanced SELECT queries

The SQL and results of your UPDATE and DELETE queries

The SQL and results of your view creation and its use in a SELECT query

You must show all of your SQL as it executed in Management Studio or other development environments. This includes the results returned by your selected RDBMS.

(((((((((((Note)))))))))))))))): you must populate any other tables and show the execution of your SQL statements as required.

Use a SELECT statement using the UNION operator.

add at least 5 more rows to each of your tables

create a view and use it in a SELECT query...

Do not answer if you dont know.

Solution

1.using SQL and an SQL script file, add at least five more rows of data to each of your tables.

Insert Into Item values(‘D123’,’Ink’,40);
Insert Into Item values(‘E123’,’Paper’,10);
Insert Into Item values(‘F123’,’Notebook’,25);
Insert Into Item values(‘G123’,’Paint Brush’,35);
Insert Into Item values(‘H123’,’Ruber’,5);

Insert Into Customer(‘I123’,’II Gen stores’,’Sanfransico’);
Insert Into Customer(‘J123’,’JJ stores’,’Las Vegas’);
Insert Into Customer(‘K123’,’KK Gen stores’,’Los Angeles’);
Insert Into Customer(‘L123’,’LL Gen stores’,’Sanfransico’);
Insert Into Customer(‘M123’,’MM Gen stores’,’Los Angeles’);

Insert into Orderdata(‘N123’,’14-aug-2016’,’17-aug-2016’,’A123’,5,2.5,12.5,’I123’);
Insert into Orderdata(‘P123’,’16-aug-2016’,’19-aug-2016’,’D123’,10,5,50,’J123’);
Insert into Orderdata(‘Q123’,’20-aug-2016’,’26-aug-2016’,’D123’,5,4,20,’K123’);
Insert into Orderdata(‘R123’,’23-aug-2016’,’29-aug-2016’,’F123’,10,3,30,’L123’);
Insert into Orderdata(‘S123’,’25-aug-2016’,’29-aug-2016’,’G123’,5,4,20,’M123’);

2. The use of a GROUP BY clause, with a HAVING clause, and one or more group functions

Select count(orderID) from Orderdata group by No_of_items having Order_total>13;

3. The use of UNION operator
Select ItemId, Unitcost from Item
Union
Select custID, address from Customer;

4. The use of a subquery
Select orderId from Orderdata where custId IN(Select custID from Customer where custName=\'K123\');

5. The use of an outer join
Select Customer.custId,Customer.custName, Orderdata.ItemId from Customer FULL OUTER JOIN Orderdata where Customer.custId=Orderdata.orderID order by Customer.custId;

6.Update
UPDATE Customer
SET custName=\'John Store\'
WHERE CustomerID=\'J123\';

7. Delete
DELETE FROM Customer
WHERE custId=\'K123\';

8.create view
CREATE VIEW [subquery] AS
Select orderId from Orderdata where custId IN(Select custID from Customer where custName=\'K123\');