all_sql_bcp_a_roguska

Agnieszka Roguska A00188976

BCP

BIRTHDAY CAKE PRODUCTION

Birthday Cake Production-BCP

Higher Diploma in Computing

Subject: Database Applications

Oracle SQL Project

Student Account No: Student 123

12/01/2015

BIRTHDAY CAKE PRODUCTION BCP|AGNIESZKA ROGUSKA A00188976

BCP|AGNIESZKA ROGUSKA A00188976 1

Table of Contents

1. Introduction ....................................................................................................................... 2

2. Original Data ..................................................................................................................... 2

Modified for ERD .................................................................................................................. 4

2. ERD - Entity Relationship Diagram .................................................................................. 7

Relationship in Access ......................................................................................................... 7

Erd .......................................................................................................................................... 7

4. Create Table Statements ................................................................................................... 8

Drop All Tables ...................................................................................................................... 8

Insert new Tables .................................................................................................................. 9

Shop Table with Constraints ............................................................................................... 9

Client Table with Constraints ............................................................................................ 10

Baker Table with Constraints ............................................................................................ 11

Cake Table with Constraints ............................................................................................. 12

Cake Order Table with Constraints ................................................................................. 13

5. Tables .................................................................................................................................. 14

Shop Table .......................................................................................................................... 14

Client Table ......................................................................................................................... 15

Baker Table ......................................................................................................................... 16

Cake Table ......................................................................................................................... 17

Cake Order Table .............................................................................................................. 18

6. Insert Statements ............................................................................................................... 19

Shop Inserts ......................................................................................................................... 19

Clients Insert Records ........................................................................................................ 21

Bakers Insert Records ......................................................................................................... 23

Cake Insert Records .......................................................................................................... 25

Cake Orders Insert Records .............................................................................................. 27

7. Single Table Queries .......................................................................................................... 29

7. Queries involving Joins ...................................................................................................... 35

8. Summary Queries .............................................................................................................. 47

9. Subqueries .......................................................................................................................... 52

10. View Table Queries.......................................................................................................... 57

11. Data Manipulation Language(DML)Insert, Update, Delete ..................................... 70

12. Conclusion ........................................................................................................................ 74



1. Introduction

The aim of this project was to examine all the data in relation to Birthday Cake

Production and prepare and sort data into related tables in Excel, in order to build a

database using Oracle SQL.

The table and column names were supplied and therefore the initial step was to find

the relationships between each table and then populate the tables, these

relationships were modelled on an Entity Relationship Diagram (ERD). In addition I

have outlined the Primary Keys and Foreign Keys in each table.

I will cover every aspect of the SQL Command Set: Data Manipulation Language,

Data Description Language, Data Controlling Language and Select Statement.

2. Original Data

Bakery (employs specialist bakers). The bakery responds to the demands of private

individuals who want cakes baked for special occasions like: weddings, christenings,

family reunions, birthdays, retirements etc. This is a growing business and there is

potential for repeat business like: anniversaries. The bakery only bakes to order from

customers, so it doesn’t actually carry stock. Each type of cake made by the bakery

has been “invented” by one of its own bakers.

Shop (Shop_Id, Address, Town, County, Date_Opened, Telephone_No, Email, Web_Site,

SQ_Footage, Location (Poor, Good, Excellent), Annual_Sales)

Baker (Baker_Id, Firstname, Surname, Address, Town, County, Date_Of_Birth, Qualifications

(Master Baker, No Formal Training, Appreticeship), Specialism (Brown Bread, Scones, Tarts,

Loaves, Wedding Cakes, Sponge Cakes), Date_Hired, Hourly_Fee, Shop_Id)

Cake (Cake_Id, Type, (Brack, Sponge, Gateau, Chocolcate, Porter etc), Description,

Ingredients, Cooking_Time (Mins), Oven_Temp, Cooking_Instructions, Calories, Baker_Id

(the baker_Id here tells us is the “expert” who will bake the cake when a client orders)

Cake_Order (Cake_Id, Client_Id, Date_Ordered, Date_Baked, Cake_Size(Small, Medium,

Large), Quantity, Unit_Cost_Price, Unit_Selling_Price, Pay_Method (Cash, Cheque, Credit

Card))

Client (Client_Id, Name, Address, County, Telephone_No, Email_Address, Type (Private,

Corporate), Fav_Cake(Chocolate, Carror, Ginger, Fruit etc))



Modified Data (PK&FK)

Shop

Shop_Id(PK),Address,Town,County,Date_Opened,Telephone_No,Email,Web_Site,

SQ_Footage,Location,Annual_Sales

Client

Client_Id(PK), Name, Address, County, Telephone_No, Email_Address, Type , Fav_Cake

Baker

Baker_Id(PK), Firstname, Surname, Address, Town, County, Date_Of_Birth, Qualifications ,

Specialism, Date_Hired, Hourly_Fee, Shop_Id(FK)

Cake

Cake_Id(PK), Type, Description, Ingredients, Cooking_Time, Oven_Temp,

Cooking_Instructions, Calories, Baker_Id(FK)

Cake_Order

Cake_Id(Fk), Client_Id(FK)(CK), Date_Ordered, Date_Baked, Cake_Size, Quantity,

Unit_Cost_Price, Unit_Selling_Price,Pay_Method



Modified for ERD

Shop

Shop_Id

Address

Town

County

Date_Opened

Telephone_No

Email

Web_Site

SQ_Footage

Location

Annual_Sales)

Client

Client_Id

Name

Address

County

Telephone_No

Email_Address

Type

Fav_Cake



Baker

Baker_Id

Firstname

Surname

Address

Town

County

Date_Of_Birth

Qualifications

Specialism

Date_Hired

Hourly_Fee

Shop_Id

Cake

Cake_Id

Type

Description

Ingredients

Cooking_Time

Oven_Temp

Cooking_Instructions

Calories

Baker_Id



Cake_Order

Cake_Id

Client_Id

Date_Ordered

Date_Baked

Cake_Size

Quantity

Unit_Cost_Price

Unit_Selling_Price

Pay_Method



2. ERD - Entity Relationship Diagram

Relationship in Access

Erd



4. Create Table Statements

Drop All Tables

Drop Table Shop cascade constraints;

Drop Table Client cascade constraints;

Drop Table Cake_Order cascade constraints;

Drop Table Cake cascade constraints;

Drop Table Baker cascade constraints;

PURGE RECYCLEBIN;



Insert new Tables

Shop Table with Constraints



Client Table with Constraints



Baker Table with Constraints



Cake Table with Constraints



Cake Order Table with Constraints



5. Tables

Shop Table



Client Table



Baker Table



Cake Table



Cake Order Table `



6. Insert Statements

Shop Inserts Insert into Shop values (1,'4 Rodney Street','Grangemore','Longford','02-May-

87','35343562545','info@Earl_Russell.ie',null,50,'Poor',25633);

Insert into Shop values (2,'4804 Maronga Street','Collon','Longford','01-Jun-

10','35343562542','info@Edward_Stanley.ie',null,100,'Good',5425);

Insert into Shop values (3,'76 Cathcart Road','Galway City','Galway','01-Jul-

87','35343562546','info@George_Hamilton.ie','www.Georgordon.ie',60,'Excellent',6000

0);

Insert into Shop values (4,'90 Manesi ','Cloonboo','Galway','31-Jul-

87','35343562543','info@Henry_John_Temple.ie','www.Henemple.ie',90,'Poor',4000);

Insert into Shop values (5,'68 Njoli Street','Ballymore ','Sligo','30-Aug-

09','null','info@John_Stanley.ie','www.John_Stanley.ie',120,'Good',4005);

Insert into Shop values (6,'Summerstrand Village','Golden','Sligo','29-Sep-

87','35343562544','info@Henry_Temple.ie','www.Henry_Temple.ie',140,'Excellent',4001);

Insert into Shop values (7,'2 Church Street','Grangemore','Longford','29-Oct-

87','35343562548','info@Earl_Russell.ie','www.Earl_Russell.ie',30,'Poor',4130);

Insert into Shop values (8,'86 Market Street','Collon','Dublin','28-Nov-

87','35343562545','info@Edward_George.ie','www.Edwardanley.ie',40,'Good',8000);

Insert into Shop values (9,'84 Constitution Road','Galway City','Galway','28-Dec-

09','35343562549','info@Benjamin_Disraeli.ie','www.Benjamin_Disraeli.ie',40,'Excellent',2

560);

Insert into Shop values (10,'7 Chase Street','Cloonboo','Galway','27-Jan-

88','35343562546','[email protected]','www.Wiadstone.ie',90,'Poor',1365);

Insert into Shop values (11,'Uitenhage','Ballymore ','Sligo','27-Mar-

88',null,'help@Benjamin_Disraeli.ie','www.Benjamin_Disraeli.ie',30,'Good',170);

Insert into Shop values (12,'34 Graaff Reinet Road','Golden','Sligo','26-May-

88','34344523359','[email protected]','www.Williamladstone.ie',30,'Excellent',2562);

Insert into Shop values (13,'1st Floor Middestad','Grangemore','Longford','25-Jul-

88','34344523363','help@John_White.ie','www.John_White.ie',50,'Poor',2000);

Insert into Shop values (14,'Baysvillage Centre','Collon','Longford','23-Sep-

88',null,'[email protected]','www.Kate_adstone.ie',60,'Good',2563);

Insert into Shop values (15,'Hoek van Harts','Galway City','Galway','22-Nov-

88','34344523371','help@Robert_Arthurl.ie','www.Ro_Cecil.ie',50,'Excellent',2000);

Insert into Shop values (16,'1124 De Villiers Street','Cloonboo','Galway','21-Jan-

89','34344523375','help@William_Gladstone.ie','www.Willistone.ie',60,'Poor',2564);



Insert into Shop values (17,'Winkel 4','Ballymore ','Sligo','22-Mar-

89','34344523379','[email protected]','www.Archibmrose.ie',140,'Good',3250);

Insert into Shop values (18,'24137 Brits Road','Golden','Sligo','21-May-

89','34344523383','[email protected]','www.Talbotcil.ie',90,'Excellent',56655);

Insert into Shop values (19,'2 Library House','Golden','Sligo','20-Jul-

89','34344523387','help@Arthuour_.ie','www.Arthlfour_.ie',30,'Poor',7741);

Insert into Shop values (20,'22 B Harvey Road','Burtonport','Longford','18-Sep-

89','34344523391','help@Henry_Bannerman.ie','www.Henry_Bannerman.ie',50,'Good',

30000);



Clients Insert Records

Insert into Client values (21,'Earl Russell','7 Shoprite Centre',' Carlow

','35343456544','[email protected]','Private','Chocolate');

Insert into Client values (22,'Edward George Stanley','Leadwood House','

Dublin','35343456540','[email protected]','Corporate','Carrot');

Insert into Client values (23,'George Hamilton Gordon','Beacon Bay Park','

Fingal','35343456545','[email protected]','Private','Ginger');

Insert into Client values (24,'Henry John Temple','5 Berea Mall','

Limerick','35343456541','[email protected]','Corporate','Fruit');

Insert into Client values (25,'John Stanley','Suite G11 Musgrave Park','

Limerick','35343456546','[email protected]','Private','Chocolate');

Insert into Client values (26,'Henry Temple',' Bungalow Mall','

Cavan','35343456542','[email protected]','Corporate','Carrot');

Insert into Client values (27,'Earl Russell','27 A King Street',' Carlow

',null,'[email protected]','Private','Ginger');

Insert into Client values (28,'Edward George Stanley','4 Byron Street','

Dublin','35344854555','[email protected]','Corporate','Fruit');

Insert into Client values (29,'Benjamin Disraeli','Dutywa Medical Centre','

Fingal','35344854561','[email protected]','Private','Chocolate');

Insert into Client values (30,'William Ewart Gladstone','1 Ludidi Building','

Limerick','35344854567','[email protected]','Corporate','Carrot');

Insert into Client values (31,'Benjamin Disraeli','89 King Road','

Limerick','35344854573','[email protected]','Private','Ginger');

Insert into Client values (32,'William Ewart Gladstone','33 King Street','

Cavan','35344854579','[email protected]','Corporate','Fruit');

Insert into Client values (33,'John White','The Colcade',' Carlow

','35344854585','[email protected]','Private','Chocolate');

Insert into Client values (34,'Kate Ewart Gladstone','5 Main Street','

Dublin','35344854591','[email protected]','Corporate','Carrot');

Insert into Client values (35,'Robert Arthur Talbot ','Vincent Street','

Fingal',null,'[email protected]','Private','Ginger');

Insert into Client values (36,'William Gladstone','Dublin road','


Insert into Client values (37,'Archibald Philip Primrose','29 Albany Street','

Limerick','35344854609','[email protected]','Private','Chocolate');



Insert into Client values (38,'Robert Arthur ','Sibaya Sempilo Medical','

Cavan',null,'[email protected]','Corporate','Carrot');

Insert into Client values (39,'Arthur James Balfour ','45 Oriental Plaza','

Donegal','35344854621','[email protected]','Private','Ginger');

Insert into Client values (40,'Henry Bannerman','Main Road 8','




Bakers Insert Records

Insert into Baker values (41,'Larry','McGrath','95 Bloomfield

Road','Drumlish','Longford','01-May-87','Master Baker','Tart','02-May-01',20,18);

Insert into Baker values (42,'Peter','Brien','120 Ormeau

Road','Carrickmacross','Monaghan','03-Aug-88','No Formal Training','Scones','08-Apr-

02',10,20);

Insert into Baker values (43,'Hubert','Donnelly','216 Grosvenor

Road','Annascaul','Kerry','06-Nov-89','Appreticeship','Brown Bread','15-Mar-03',12,1);

Insert into Baker values (44,'Arron','Donoghue','74A Ligoniel

Road','Clonaslee','Cork','09-Feb-91','Master Baker','Loaves','19-Feb-04',9,18);

Insert into Baker values (45,'Ronan','Dowling','216 Grosvenor

Road','Clonaslee','Longford','14-May-92','No Formal Training','Wedding Cakes','25-

Jan-05',15,1);

Insert into Baker values (46,'Charles','Kelly','Linenhall

Street','Ballyclerahan','Tipperary','17-Aug-93','Appreticeship','Sponge Cake','01-Jan-

06',20,2);

Insert into Baker values (47,'Dermot','McDonald','72 New Road','Cavan','Cavan','20-

Nov-94','Master Baker','Tart','08-Dec-06',30,3);

Insert into Baker values (48,'Barry','Morrissey','Linenhall Street','Belmullet','Mayo','23-

Feb-96','No Formal Training','Scones','14-Nov-07',40,4);

Insert into Baker values (49,'Brian','Nevin','Water Road 3 ','Drumlish','Longford','28-

May-97','Appreticeship','Brown Bread','20-Oct-08',50,4);

Insert into Baker values (50,'Pauric','Quinlan','179 Ander

Road','Carrickmacross','Monaghan','31-Aug-98','Master Baker','Loaves','26-Sep-

09',16,5);

Insert into Baker values (51,'Niall','Brennan','25 Finaghy South','Annascaul','Kerry','04-

Dec-99','No Formal Training','Wedding Cakes','02-Sep-10',20,6);

Insert into Baker values (52,'Barry','McGrath','169 Newtowns ','Clonaslee','Cork','02-

May-80','Appreticeship','Sponge Cake','09-Aug-11',100,4);

Insert into Baker values (53,'Mark','Kavanagh','Water Road

6','Clonaslee','Longford','05-Jun-81','Master Baker','Tart','10-Jan-14',30,11);

Insert into Baker values (54,'Brendan','Kiernan','13/25 Finaghy

South','Ballyclerahan','Tipperary','09-Jul-82','No Formal Training','Scones','11-Feb-

14',30,11);



Insert into Baker values (55,'Brian','Nolan','Dublin

Road','Carrickmacross','Monaghan','12-Aug-83','Appreticeship','Brown Bread','15-

Mar-14',40,12);

Insert into Baker values (56,'Pauric','Collins','13/25 Finaghy

Road','Annascaul','Kerry','14-Sep-84','Master Baker','Loaves','16-Apr-14',17,12);

Insert into Baker values (57,'Niall','Donohoe','3 Killaughey

Road','Ballydehob','Cork','18-Oct-85','No Formal Training','Wedding Cakes','18-May-

14',45,5);

Insert into Baker values (58,'Barry','Forde','68 New Road','Clonaslee','Laois','21-Nov-

86','Appreticeship','Sponge Cake','19-Jun-14',39,20);

Insert into Baker values (59,'Mark','Jackson','Main Street

4','Ballyclerahan','Tipperary','25-Dec-87','Master Baker','Tart','21-Jul-14',75,19);

Insert into Baker values (60,'Brendan','McCarey','179 Ander

Road','Cavan','Cavan','27-Jan-89','No Formal Training','Scones','22-Aug-14',65,18);



Cake Insert Records

Insert into Cake values (101,'Brack','Anniversary','egg',120,175,'Oven Bake',1200,42);

Insert into Cake values (102,'Sponge','Party Cake','water',60,180,'Oven

Bake',2000,42);

Insert into Cake values (103,'Gateau','Homemade','chocolate',120,180,'Not Oven

Bake',2500,55);

Insert into Cake values (104,'Chocolate','Wedding Cake','chocolate',60,185,'Check

after 15 minutes of baking',3000,46);

Insert into Cake values (105,'Porter','Anniversary','egg',30,200,'Not Oven

Bake',1500,60);

Insert into Cake values (106,'Chocolate Fudge Cake','Birthday

Cake','sugar',120,175,'Check after 15 minutes of baking',1200,60);

Insert into Cake values (107,'Charlotte','Birthday Cake','water',60,180,'Not Oven

Bake',2000,60);

Insert into Cake values (108,'Chocolate Satin Cake','Birthday

Cake','chocolate',120,180,'Check after 15 minutes of baking',2500,49);

Insert into Cake values (109,'Butter Cake','Engagement','chocolate',60,185,'Oven

Bake',3000,51);

Insert into Cake values (110,'Coconut Cake','Anniversary','egg',30,200,'Oven

Bake',1500,50);

Insert into Cake values (111,'Coffee Cake','Wedding Cake','egg',120,175,'Check

after 15 minutes of baking',1200,42);

Insert into Cake values (112,'Colomba','Anniversary','water',60,180,'Oven

Bake',2000,47);

Insert into Cake values (113,'Cheesecake','Birthday Cake','chocolate',120,180,'Not

Oven Bake',2500,55);

Insert into Cake values (114,'Fondant','Wedding Cake','chocolate',60,185,'Not Oven

Bake',3000,47);

Insert into Cake values (115,'Fraisier','Bride and Groom','egg',30,200,'Not Oven

Bake',1500,48);

Insert into Cake values (116,'Funnel Cake','Party Cake','egg',120,175,'Oven

Bake',1200,49);



Insert into Cake values (117,'Carrot','Anniversary','water',60,180,'Oven Bake',2000,49);

Insert into Cake values (118,'Jelly Roll','Christening Cake','sugar',120,180,'Not Oven

Bake',2500,50);

Insert into Cake values (119,'Chocolate Cheesecake','Wedding

Cake','chocolate',60,185,'Not Oven Bake',3000,52);

Insert into Cake values (120,'Fruit ','Novelty ','fruits',30,200,'Not Oven Bake',1500,47);



Cake Orders Insert Records

Insert into Cake_Order values (101,'21','02-May-13','02-Jul-13','Small',2,20,40,'Cash');

Insert into Cake_Order values (103,'40','03-Jun-13','04-Jul-

13','Medium',5,30,50,'Cheque');

Insert into Cake_Order values (105,'30','05-Jul-13','10-Jul-13','Large',1,40,60,'Credit

Card');

Insert into Cake_Order values (107,'31','06-Aug-13','30-Aug-13','Small',1,20,40,'Cash');

Insert into Cake_Order values (109,'22','07-Sep-13','21-Sep-

13','Medium',5,30,50,'Cheque');

Insert into Cake_Order values (111,'21','09-Oct-13','22-Oct-13','Large',5,40,60,'Credit

Card');

Insert into Cake_Order values (113,'35','10-Nov-13','10-Dec-13','Small',2,20,40,'Cash');

Insert into Cake_Order values (115,'35','12-Dec-13','22-Dec-

13','Medium',10,30,50,'Cheque');

Insert into Cake_Order values (117,'21','13-Jan-14','23-Jan-14','Large',10,40,60,'Credit

Card');

Insert into Cake_Order values (119,'21','14-Feb-14','24-Feb-14','Small',5,20,40,'Cash');

Insert into Cake_Order values (101,'22','18-Mar-14','28-Mar-

14','Medium',1,30,50,'Cheque');

Insert into Cake_Order values (103,'23','19-Apr-14','23-Apr-14','Large',2,40,60,'Credit

Card');

Insert into Cake_Order values (120,'40','21-May-14','27-May-14','Small',6,20,40,'Cash');

Insert into Cake_Order values (120,'39','22-Jun-12','30-Jun-

12','Medium',6,30,50,'Cheque');

Insert into Cake_Order values (111,'36','24-Jul-13','27-Jul-13','Large',5,40,60,'Credit

Card');

Insert into Cake_Order values (101,'37','11-Feb-14','24-Feb-14','Small',6,20,40,'Cash');

Insert into Cake_Order values (120,'37','01-Mar-14','11-Mar-

14','Medium',6,30,50,'Cheque');



Insert into Cake_Order values (109,'37','28-Jan-14','28-Feb-14','Large',2,40,60,'Credit

Card');

Insert into Cake_Order values (101,'40','05-Jul-13','05-Aug-13','Small',2,20,40,'Cash');

Insert into Cake_Order values (102,'22','10-Dec-13','22-Dec-

13','Medium',3,40,60,'Cheque');

Commit;



7. Single Table Queries

1. Show all Shops Opened after 01 May 1987 which Address contains letter

“o” from Town Grangemore or Ballymore with Square Footage between

20 and 60 with no Website.

Select Shop_Id,Address,Town,Date_Opened,Web_Site,SQ_Footage

From Shop

Where Address like ‘%o%’ And Date_Opened > ’01-may-1987’

And Town IN(‘Ballymore’,’Grangemore’)

And SQ_Footage Between 20 And 60

And Web_Site IS Null;

Qualified Column Names

Select Shop.Shop_Id,Shop.Address,Shop.Town,Shop.Date_Opened,

Shop.Web_Site,Shop.SQ_Footage

From Shop

Where Shop.Address like ‘%o%’ And Shop.Date_Opened > ’01-may-1987’

And Shop.Town IN(‘Ballymore’,’Grangemore’)

And Shop.SQ_Footage Between 20 And 60

And Shop.Web_Site IS Null;



2. Show all Clients with Id number lower than 50 but not Clients between

2 and 5 from towns which first letter is not “D”, with Telephone number. Don’t

show Clients with Chocolate and Carrot cake as their favourite

Select Client_Id,County, Telephone_No,Fav_Cake

From Client

Where Client_Id <50 And Client_Id Not Between 2 and 5

And County Not Like ‘D_%’

And Telephone_No IS NOT NULL

And Fav_Cake NOT IN (‘Chocolate’,’Carrot’);


Select Client.Client_Id,Client.County, Client.Telephone_No,Client.Fav_Cake

From Client

Where Client.Client_Id < 50

And Client.Client_Id Not Between 2 and 5 And Client.County Not Like ‘D_%’

And Client.Telephone_No IS NOT NULL

And Client.Fav_Cake NOT IN (‘Chocolate’,’Carrot’);



3. Show all Bakers from County Longford which lives in Drumlish, Clonaslee, or

Edgeworthstown with a First Name containing letter “n” and Town containing

letter “l”with Baker Id lesser than 50 in Ascending Order

Select Firstname,Town, Baker_Id, County as Co From Baker

Where County = ‘Longford’

And Town In(’Drumlish’,’Edgeworthstown’,’Clonaslee’)

And Firstname Like ’%n%’ And Town Like ’%l%’

And Baker_Id < 50

Order By Baker_Id Asc;

Table Aliases

Select B.Firstname, B.Town, B.Baker_Id, B.County as Co

From Baker B

Where B.County = ‘Longford’

And B.Town In(’Drumlish’,’Edgeworthstown’,’Clonaslee’) And B.Firstname Like ’%n%’

And B.Town Like ’%l%’

And B.Baker_Id < 50

Order By Baker_Id Asc;



4. Show all Cakes in Descending order, with type in upper case and

Description in lower case, with Cake not containing eggs. Show the number of

characters in Column Cooking Instructions call it Instructions

Select Length(Cooking_Instructions) As Instructions ,Upper(Type)As

Cake_Type,Lower(Description)As Cake_Description

From Cake

Where Ingredients<> ‘egg’ Order By Cake_Id Desc;

Table Aliases

Select Length(C.Cooking_Instructions) As Instructions ,Upper(C.Type)As

Cake_Type,Lower(C.Description)As Cake_Description

From Cake C

Where C.Ingredients<> ‘egg’ Order By C.Cake_Id Desc;



5. Show all cake orders where the Cake Id is lower or equal to 110 or greater

than 102 or the title case Cake Size does not contain the letter l or the unit cost

price is either 20 or 30 or the unit cost price is not between 1 and 5.

Select Cake_Id,Cake_Size as CS, Initcap(Cake_Size), Ltrim(Pay_method,’credit.’), Rtrim(Pay_Method,’Card’),Unit_Cost_Price

From Cake_Order

Where Cake_Id <=110

OR Cake_Size Not Like ’%l%’ OR Unit_Cost_Price IN(20,30)

OR Cake_Id > =103

OR Unit_Cost_Price Not Between 1 and 5;

Table Aliases

Select C.Cake_Id,Cake_Size as CS, Initcap(C.Cake_Size),

Ltrim(C.Pay_method,’credit.’),

Rtrim(C.Pay_Method,’Card’),C.Unit_Cost_Price

From Cake_Order C Where C.Cake_Id <=110

OR C.Cake_Size Not Like ’%l%’

OR C.Unit_Cost_Price IN(20,30)

OR C.Cake_Id > =103 OR C.Unit_Cost_Price Not Between 1 and 5;



6. Show all Bakers from Cavan which were born after 01 May 1960 and have

Master Baker Qualification. Order by Baker Id Ascending.

Select Baker_Id,County,Date_Of_Birth,Qualifications

From Baker

Where County=’Cavan’

And Qualifications=’Master Baker’ And Date_Of_Birth>’01-May-1960’

Order by Baker_Id Asc;



7. Queries involving Joins

1. Show all the Shops which are located in a town containing letter ‘e’ from

County Longford or Dublin with baker Hourly fee between 5 and 500. Order by

Shop Id Ascending.

Parent Child Query 2 tables

Select Shop.Shop_Id,Baker.Town,Shop.County,Baker.Baker_Id,Baker.Hourly_Fee

From Shop ,Baker

Where Shop.Shop_Id=Baker.Shop_Id

And Shop.Town like ‘%a%’

And Shop.County In(‘Longford’,’Dublin’) And Baker.Hourly_Fee Between 5 and 500

Order By Shop.Shop_Id Asc;


Select Shop.Shop_Id,Baker.Town,Shop.County,Baker.Baker_Id,Baker.Hourly_Fee

From Shop ,Baker

Where Shop.Shop_Id=Baker.Shop_Id And Shop.Town like ‘%a%’

And Shop.County In(‘Longford’,’Dublin’)

And Baker.Hourly_Fee Between 5 and 500

Order By Shop.Shop_Id Asc;



2. Show the clients with id number more or equal to 30 with Telephone Number

.Also show the Half of the Unit Selling Price and Order by Client Name.


Select Client.Client_Id,Initcap(Name), Telephone_No,Unit_Selling_Price/2 As

Half_Unit_Price From Client,Cake_Order

Where Client.Client_Id=Cake_Order.Client_Id

And Client.Client_Id >= 30

And Telephone_No Is Not Null Order By Name;


Select Client.Client_Id,Initcap(Name),

Telephone_No,Cake_Order.Unit_Selling_Price/2 As Half_Unit_Price

From Client,Cake_Order

Where Client.Client_Id=Cake_Order.Client_Id And Client.Client_Id >= 30

And Client.Telephone_No Is Not Null

Order By Client.Name;



3. Show All Clients with name in the upper case, with Unit Cost Price Not

between 5 and 10 .Also Pay method shouldn’t finish with letter ‘h’ and Cake

ingredient shouldn’t contain letter ‘g’


Select Client.Client_Id,Upper(Client.Name),Ingredients,Unit_Cost_Price As Unit_Cost,Pay_Method

From Client, Cake, Cake_Order

Where Client.Client_Id=Cake_Order.Client_Id

And Cake.Cake_Id=Cake_Order.Cake_Id And Unit_Cost_Price Not Between 5 and 10

And Pay_Method Not Like ‘%h’

And Ingredients Not Like’%g%’;

TABLE ALIAS

Select C.Client_Id,Upper(C.Name),Ca.Ingredients,Co.Unit_Cost_Price As

Unit_Cost,Co.Pay_Method

From Client C , Cake Ca, Cake_Order Co Where C.Client_Id=Co.Client_Id

And Ca.Cake_Id=Co.Cake_Id

And Co.Unit_Cost_Price Not Between 5 and 10

And Co.Pay_Method Not Like ‘%h’ And Ca.Ingredients Not Like’%g%’;



4. Show all Brack, Sponge, Porter or Butter Cakes with Client Id and name. Also

include the Shop Id as “Shop_Number” and Town as “Shop_Town” . Only show

Bakers which has no Formal Training. Baker’s Town needs to contain letter ‘u’. Order all Cake ID Ascending

All tables - joins

Select Cake.Cake_Id, Cake.Type as Cake_Type, Client.Client_Id,Client.Name,Shop.Shop_Id As Shop_Number,Shop.Town

as Shop_Town,Cake_Size,Baker.Baker_Id,Qualifications

From Cake,Client,Shop,Baker,Cake_Order

Where Cake.Cake_Id=Cake_Order.Cake_Id And Client.Client_Id= Cake_Order.Client_Id

And Shop.Shop_Id=Baker.Shop_Id

And Qualifications = ‘No Formal Training’

And Baker.Town Like ‘%u%’

And Baker.Baker_Id=Cake.Baker_Id And Cake.Type In (‘Brack’,’Sponge’,’Porter’,’Butter Cake’)

Order by Cake_Id Asc;



TABLE ALIAS

Select C.Cake_Id, C.Type as Cake_Type, Cl.Client_Id,Cl.Name,S.Shop_Id As Shop_Number,S.Town as

Shop_Town,Co.Cake_Size,B.Baker_Id,B.Qualifications

From Cake C,Client Cl,Shop S,Baker B,Cake_Order Co

Where C.Cake_Id=Co.Cake_Id And Cl.Client_Id= Co.Client_Id

And S.Shop_Id=B.Shop_Id

And B.Qualifications = ‘No Formal Training’

And B.Town Like ‘%u%’

And B.Baker_Id=C.Baker_Id And C.Type In (‘Brack’,’Sponge’,’Porter’,’Butter Cake’)

Order by C.Cake_Id Asc;



5. Show First Name, Surname of pair of bakers with identical Hourly Fee

but do not include bakers from Drumlish and Carrickmacross. Count the

characters in Baker’s Town and Show First name in Upper Case and County in

Lower Case. Order by Baker Id Ascending.

Self Join(Aliases)

Select B1.Baker_Id,Upper(B1.Firstname),B1.Surname As Baker_Surname,

B1.Hourly_Fee,B1.Town,Length(B1.Town),Lower(B1.County), B2.Baker_Id,Upper(B2.Firstname),B2.Surname,

B2.Hourly_Fee,B2.Town,Length(B2.Town),Lower(B2.County)

From Baker B1, Baker B2

Where B1.Baker_Id > B2.Baker_Id

And B1.Town NOT IN(‘Drumlish’,’Carrickmacross’) And B1.Hourly_Fee = B2.Hourly_Fee

Order by B1.Firstname ASC;



6. Show the cake types and associated client Id. Include whose Cakes which

are not affiliated with any Client Also show the unit Cost price if it was halved

.Ensure that the results include those cakes with where the quantity is more or

equal 5 and the cooking time is not null. Order all by Cake Id descending.

Outer Join (+)

Select Cake.Cake_Id,Client_Id,Unit_Cost_Price/2 As `

Half_Unit_Cost,Initcap(Cake_Size) From Cake , Cake_Order

Where Cake.Cake_Id = Cake_Order.Cake_Id(+)

And Cake.Cake_Id >= 1

And Cake.Cake_Id Is Not NULL

Order BY Cake.Cake_Id Desc;



TABLE ALIAS

Select C.Cake_Id,Co.Client_Id,Co.Unit_Cost_Price/2 As

Half_Unit_Cost,Initcap(Co.Cake_Size)

From Cake C, Cake_Order Co Where C.Cake_Id = Co.Cake_Id(+)

And C.Cake_Id >= 1

And C.Cake_Id Is Not NULL

Order BY C.Cake_Id Desc;



7. Show All Clients with name in Upper Case and type in lower Case containing

letter “r”. Make sure Client are not from Carlow and Dublin. The Length of

baker’s county should be exactly this same like length of Client County.

Equi Join based on 2 Tables Client and Baker(not linked via Pk and FK)

Select Client.Client_Id,Upper(Name),Lower(Type),

Length(Client.County), Baker_Id ,Length(Baker.County) From Client, Baker

Where Length(Baker.County) = Length(Client.County)

And Client.County Not IN (‘Carlow’,’Dublin’)

And Type like ‘%i%’;



TABLE ALIAS

Select C.Client_Id,Upper(C.Name),Lower(C.Type),

Length(C.County), B.Baker_Id ,Length(B.County)

From Client C, Baker B Where Length(B.County) = Length(C.County)

And C.County Not IN (‘Carlow’,’Dublin’)

And C.Type like ‘%i%’;



8. Show clients names and Type in Capital letter with no Telephone number

.Double Bakers Fee and call it “Double_Fee”. Also show only client with name

starting with letter B and the length of Clients Name characters should be more

or equal to Clients Type.

A Non-Equijoin using Charity and Event Table (don’t use =)

Select Initcap(Client.Name),Initcap(Type),Hourly_Fee *2 As Double_Fee

,Telephone_No,Baker_Id

From Client ,Baker

Where Length(Client.Name) >= Length(Client.Type) And Telephone_No IS NOT NULL

And Client.Name Like ‘B%’;




Select Initcap(Client.Name),Initcap(Client.Type),Baker.Hourly_Fee *2 As

Double_Fee ,Client.Telephone_No,Baker.Baker_Id

From Client ,Baker

Where Length(Client.Name) >= Length(Client.Type)

And Client.Telephone_No IS NOT NULL And Client.Name Like ‘B%’;



8. Summary Queries

Q1.Show the Cake Orders with a maximum, Minimum, Average Unit Cost Price

where Cake size contains letter “m” and Quantity is 2, 6, 10, 20 or 30.

Select Min(Unit_Cost_Price),Max(Unit_Cost_Price),Avg(Unit_Cost_Price)

From Cake_Order

Where Cake_Size like ‘%m%’

And Quantity IN(2,6,10,20,30);

TABLE ALIAS

Select Min(C.Unit_Cost_Price),Max(C.Unit_Cost_Price),Avg(C.Unit_Cost_Price)

From Cake_Order C

Where C.Cake_Size like ‘%m%’

And C.Quantity IN(2,6,10,20,30);



2. Show the Shop with Maximum, Minimal and average Annual sales. Change

their names to “Maximim_Salary”,”Minimum_Salary” and “Average_Salary”. Insure that County Doesn’t start with letter ‘I’ and Sq Footage is not between

10 and 30

Select Max(S.Annual_Sales)As Maximum_Annual_Sales , Min(S.Annual_Sales)As

Minimum_Annual_Sales,Count(S.SQ_Footage) , Avg(S.Annual_Sales),Sum(S.SQ_Footage)

From Shop S

Where S.County Not Like ‘i%’

And S.Sq_Footage Not between 10 and 30;

Q3. Count the number of Oven Temperatures call them “Oven_Temperature”

and Count the number of Cake Ids for Cakes with Calories lower than 2000 and

cooking Time lower or equal 200

Select Count(*),Count(C.Oven_Temp)As Oven_Temperature From Cake C

Where C.Calories < 2000

And C.Cooking_Time <= 200;



4. Show the Maximum , Minimal and Average Annual Sales for a Shop which

Doesn’t have a telephone number and the Length of Characters in County is smaller than 20.

Select Max(Annual_Sales),Min(Annual_Sales),Avg(Annual_Sales),

Count(Annual_Sales) From Shop

Where Telephone_No is Null

And Length (County) < 20;

5. Show the Difference between the Maximum and Minimum Cake Calories call

it Calories Difference .Show only Cakes with Id smaller or equal 115 and Oven

Temperature between 160 and 189

Select Max(Calories)-Min(Calories) As Calories_Difference

From Cake

Where Cake_Id <= 115

And Oven_Temp Between 160 and 189;



6. Show pay method and maximum, minimum and average unit cost Price for a

Cake which quantity is lower than 40 and Order and Group By pay method

Select C.Pay_Method,Max(C.Unit_Cost_Price),Min(C.Unit_Cost_Price), Count(Unit_Cost_Price)

From Cake_Order C

Where C.Quantity < 40

Group BY C.Pay_Method Order by C. Pay_Method;

Q7. Show Maximum , Minimum, Average Unit Selling Price For Cakes which

contain letter” S” and Cake Size is Small Or Medium with a Selling Prize Lesser

than 20000 . Order by Cake Size and Group By Cake Size Asceding

Select Max(Co.Unit_Selling_Price),Min(Co.Unit_Selling_Price),

Avg(Co.Unit_Selling_Price)

From Cake C,Cake_Order Co

Where C.Cake_Id=Co.Cake_Id And C.Type like ‘%s%’

And Co.Cake_Size IN(‘Small’,’Medium’)

And Co.Unit_Selling_Price< 20000

Group BY Co.Cake_Size Order By Co.Cake_Size Asc;



8. Show the Minimum, Maximum, Average Quantity for Cake Orders Group by

Cake Size where Pay Method is Cash or Credit Card Having the Minimal

Quantity lower than 30 .

Select C.Cake_Size ,Max(C.Quantity),Avg(C.Quantity),

Count(C.Quantity)

From Cake_Order C Where C.Pay_Method In(’Cash’,’Credit Card’)

Group By C.Cake_Size

Having Min(C.Quantity) <30

Order By C.Cake_Size;



9. Subqueries

1. Show all the earliest date hire Bakers, First name, Surname

from Drumlish,Cavan ,Clonaslee and Ballydehob with Hourly

Fee between 10 and 30 Euros.

Select B.Firstname, B.Surname, B.Town, B.Hourly_Fee

From Baker B

Where B.Town In (‘Cavan’,’Drumlish’,’Clonaslee’,’Ballydehob’)

And B.Hourly_Fee Between 10 And 30

And B.Date_Hired =

(Select Min(Date_Hired)

From Baker);



2. Show all the Shops with a Town not containing letter “l” which has

Annual Sales higher than Shops with Poor Location and with the highest

Annual Sales.

Select S.Shop_Id, S.Annual_Sales

From Shop S

Where Town Not Like ‘%l%’

And S.Annual_Sales >

(Select Max(S.Annual_Sales)

From Shop S

Where Location = ‘Poor’);

3. Show The Cake ID, Type ,Cooking Time ,Oven Temperature and

Calories of any Sponge Cake that have a higher Cooking Time than

the average Cooking Time of Porter Cake

Select C.Cake_Id, C.Type as Cake_Type, C.Cooking_Time, C.Oven_Temp

As Oven_Temperature ,C.Calories From Cake C

Where C.Type = ‘Sponge’

And C.Cooking_Time >

(Select Avg(C.Cooking_Time)

From Cake C

Where C.Type = ‘Porter’);



4. Show all The Cake Orders with Client Id, Cake Id, Date Ordered and

Cake Size for whose ordered before 01 May 2013 that have this same

size as any Cake Order on or after 01 May of 2013. Date ordered needs

to be between 01 May of 2009 and 01 January 2014.

Select Cake_Id, Client_Id, Date_Ordered, Cake_Size

From Cake_Order

Where Date_Ordered < ’01-May-2013’

And Date_Ordered Between ’01-May-2009’ And ’01-Jan-2014’

And Cake_Size In (Select Cake_Size

From Cake_Order

Where Date_Ordered > ’01-May-1913’);



5. Show the First name, Surname and Date of Birth of Bakers Who work

for Shops which location contain letter o with Shop Sq Footage between

70 and 120

Select Firstname, Surname,Date_Of_Birth From Baker

Where Baker.Shop_Id In

(Select Shop.Shop_Id

From Shop

Where Shop.Location Like ‘%o%’

And Shop.Sq_Footage Between 70 and 120);



6. Show the Bakers First and Last Name with Date of Birth after 02

May 1967 which Baked Cakes with Type containing less than 15

characters where the Cake in the Cake Order Pay Method is Cash.

Order by First Name.

Select B.Firstname,B.Surname, B.Date_Of_Birth

From Baker B

Where B.Date_Of_Birth > ’02-May-1967’

And B.Baker_Id In

(Select C.Baker_Id

From Cake C

Where Length(C.Type) < 15

And C.Cake_Id In

(Select Co.Cake_Id

From Cake_Order Co

Where Co.Pay_Method = ‘Cash’))

Order BY B.Firstname;



10. View Table Queries

1. Create Vertical View which will show First name, Surname, County,

Qualifications and Baker Id. Call the View “Master_Baker”

Create View Master_Baker As

Select Baker_Id,Firstname,Surname,County,Qualifications From Baker;

Select * From Master_Baker;



2. Create a horizontal View in which Hourly Fee of Baker will be doubled in For

Baker with Master Baker as Qualification. Call the view Bakers_Double_Hourly_Fee

Drop View Bakers_Double_Hourly_Fee;

Create View Bakers_Double_Hourly_Fee As

Select Baker_Id, Firstname, Surname, Address, Town,County,

Date_Of_Birth,Qualifications, Specialism, Date_Hired,Hourly_Fee, Hourly_Fee*2 as Double_Fee,Shop_Id

From Baker

Where Qualifications= ’Master Baker’

And Date_Of_Birth > ’01-May-1967’ ;

Select*

From Bakers_Double_Hourly_Fee ;



3. Create View that will show All Clients with name in the upper case, County

with Cake Cooking Time, Oven Temperature, Cake Size and Unit Cost Price Not

between 5 and 10 .Also Pay method shouldn’t finish with letter ‘h’ and Cake

ingredient shouldn’t contain letter ‘g’.

Create View All_Orders As

Select Client.Client_Id,Upper(Client.Name)As

Client_Name,Client.County,Cooking_Time, Oven_Temp,Ingredients,Unit_Cost_Price As

Unit_Cost,Pay_Method,Cake_Size

From Client, Cake, Cake_Order

Where Client.Client_Id=Cake_Order.Client_Id And Cake.Cake_Id=Cake_Order.Cake_Id

And Unit_Cost_Price Not Between 5 and 10

And Pay_Method Not Like ‘%h’

And Ingredients Not Like’%g%’;

Select *

From All_Orders;



4. Create view showing the highest, lowest and average Unit Cost Price. Call

them Maximum Unit Cost, Minimum Unit Cost and Average Unit Cost. Also count

the difference between Maximum and Minimum Unit Cost Price and call it Unit

Price Difference.

Create View Unit_Prices As Select Min(Unit_Cost_Price)As Minimum_Unit_Cost,Max(Unit_Cost_Price) As

Maxiumum_Unit_Cost,Avg(Unit_Cost_Price) As Average_Unit_Cost,

Max(Unit_Cost_Price)-Min(Unit_Cost_Price) As Unit_Price_Difference

From Cake_Order Group By Cake_Size;

Select *

From Unit_Prices;



5. Create a View Showing the Cake ID, Type containing letter ‘a’ ,Cake

Description which contains word “OVEN”, Ingredients and Calories lower than

26000 .Call The View “Cake_Calories_Desc”. Without Check Option

Create View Cake_Calories_Desc As

Select Cake_Id,Type,Description, Ingredients,Cooking_Instructions,Calories

From Cake

Where Type Like ‘%a%’ And Cooking_Instructions Like ‘%Oven%’

And Calories < 26000;

Select *

From Cake_Calories_Desc ;



Checking- Two inputs should go into the View-WITHOUT CHECK OPTION

Insert into Cake_Calories_Desc Values(122, ‘Brack’, ‘Birthday Cake’, ‘Milk’,’Oven

Bake’, 1200);

Insert into Cake_Calories_Desc Values(130, ‘Brack’, ‘Birthday Cake’, ‘Milk’,’

Bake’, 2000);



Update will work-Because it’s without Check Option View

Update Cake_Calories_Desc

Set Type = ‘Sponge’

Where Cake_Id=122;

6. Create a View Showing the Cake ID, Type containing letter ‘a’,Cake

Description which contains word “OVEN”, Ingredients and Calories lower than

26000 Call The View “Cake_Calories”. With Check Option

Create View Cake_Calories As Select Cake_Id,Type,Description, Ingredients,Cooking_Instructions,Calories

From Cake

Where Type Like ‘%a%’

And Cooking_Instructions Like ‘%Oven%’ And Calories < 2600

With Check Option;

Select *

From Cake_Calories ;



The First Insert will work because it meets the criteria

Insert into Cake_Calories Values(122, ‘Brack’, ‘Birthday Cake’, ‘Milk’,’Oven Bake’,

1200);

The Second Insert will not work because it doesn’t meet the criteria

Insert into Cake_Calories Values(130, ‘Brack’, ‘Birthday Cake’, ‘Milk’,’ Bake’,

2000);

Update Cake_Calories

Set Calories =2700

Where Cake_Id=122;



Delete From Cake_Calories

Where Cake_Id=122;

7. Create View showing Cake Id,Client Id for cakes containg letter “m” and Cake Order not more than 6 . Call The View “Cake_Order_New”. With Check

Option

Create View Cake_Orders_New As

Select Cake_Id,Client_Id,Cake_Size,Quantity

From Cake_Order

Where Cake_Size Like ‘%m%’ And Quantity < 6

With Check Option;

Select * From Cake_Orders_New;



Insert will Fail– didn’t meet the criteria

Insert into Cake_Orders_New Values (12,22,’Large’,5);

Insert will Work-Meet the Criteria Insert into Cake_Orders_New Values(12,22,’Small’,5);

Insert into Cake_Orders_New Values(13,22,’Small’,10);



Insert into Cake_Orders_New Values(13,22,’Small’,5);

Update Cake_Orders_New

Set Cake_Size=’Large’

Where Cake_Id=13;



8. Create View showing Cake ID, Client Id for cakes that contain letter “m” and

Cake Order not more than 6. Call The View “Cake_Order_New” . With Check

Option

Create View Cake_Orders_New As

Select Cake_Id,Client_Id,Cake_Size,Quantity

From Cake_Order Where Cake_Size Like ‘%m%’

And Quantity < 6

With Check Option;

Update will FAIL


Set Cake_Size = ‘Little’

Where Cake_Id=13;



UPDATE will work


Set Cake_Size = ‘Small’

Where Cake_Id=13;



11. Data Manipulation Language(DML)Insert, Update,

Delete

1. Attempt to delete Client with Id number 21 .Demonstrate it

doesn’t Work

Client has children in another table (Cake_Order)

Select Client_Id,Name,County From Client

Where Client_Id=21;

Delete

From Client

Where Client_Id=21;

2. Remove Cake Orders that have Clients from County Dublin and Cavan

(Remove from child table- Cake Order)

Delete From Cake_Order Where Client_Id In

(Select Client_Id

From Client

Where County In(’ Dublin’,’ Cavan’));



3. Insert John Smith into the Clients table with Id number 41 from 3

Glasson Road without County, Telephone Number and Email Address.

John is a private client with favourite cake is Sponge

Insert Into Client (Client_Id,Name,Address,County,Telephone_No,

Email_Address,Type,Fav_Cake)Values (41,’John Smith’,’3 Glasson

Road’,null,null,null,’Private’,’Sponge’);

4. Attempt to insert Sponge Anniversary Cake into the Cake Table

with Id number 130 which contains eggs and its oven Bake.

Sponge has 300 Calories and Baker id its 61. It will fail cos there

is no Baker with that Id number.

Insert into Cake values (130,’Sponge’,’Anniversary’,’eggs’,120,180,’Oven Bake’,3000,61)



5. Update Cake Order Table. First Attempt to update Cake Id 101 to Cake

id 160 this will fail... Update Cake id 1 to Cake Id 5. This will work

Update Cake_Order

Set Cake_Id = 160 Where Cake_Id = 101;

Update Cake_Order

Set Cake_Id = 105 Where Cake_Id = 101;



6. Update the quantities in Cake order table by 50 % for all cake that

have eggs as their ingredients.

Select Quantity

From Cake_Order

Where Cake_Id In

(Select Cake_Id

From Cake

Where Ingredients = ‘egg’);

Update Cake_Order

Set Quantity=Quantity *1.5

Where Cake_Id In

(Select Cake_Id

From Cake


Select Quantity

From Cake_Order

Where Cake_Id In

(Select Cake_Id

From Cake




12. Conclusion

Birthday Cake Production Project helped me to understand how the Oracle SQL

database works and how to retrieve information from it. In my project I showed that I

am able to sort data , create tables , assign the primary and foreign keys and do

the relationships between tables . In Addition I have learned about Data

Manipulation Language (DML) which allowed me to enter new rows, change the

existing ones or remove unwanted rows from my database. I have also query the

database to find relevant information. Type of queries used in my project:

Single table Queries

Self-join Queries

Summary Queries

Outer join Queries

In addition I have learned about Data Control Language which plays a key role in

security. There are two keywords Grant which enables me to specify access right on

object within the Oracle database and the other Keyword Revoke which is used to

remove the access rights.

all_sql_bcp_a_roguska

Documents

cake table

shop table

cake order table

client table

baker table

table statements

table of contents

shop inserts