7 short labs in Database

Hey, I have 7 short labs and it shows the steps one by one in the file.
databasesqldatabase
ATTACHED FILE(S)
GEIT 3341 DATABASE I LAB 4

GEIT 3341 Database I

Lab 4

Writing & Executing SQL Queries

Due Date:

Objective(s) Target CLO(s) Reference
To practice some SQL DML commands 6 Chapter 6
by writing and executing SELECT SQL
queries.

Name

ID

Section

Instruction:

Save this file as Lab4_YOURID_SectionID and for each query below, express the query in SQL,
run it, and include the output. That is, for each query, include the query as expressed in SQL
and the output generated from running it as shown in the example below. When no specific
columns are specified, list all columns.

Example: Select the employee first name, last name, and address of all employees in
department 5.

Query in SQL:

SELECT Fname,Lname,Address

FROM EMPLOYEE

WHERE Dno=5;

Query Output:
GEIT 3341 DATABASE I LAB 4

FNAME LNAME ADDRESS

—————————— —————————— ——————————

Franklin Wong 638 Voss,Houston,TX
John Smith 731 Fondren,Houston,TX
Ramesh Narayan 975 Fire Oak,Humble,TX
Joyce English 5631 Rice,Houston,TX

Important Note: Your queries should always work without modification if the underlying
data is changed via insert/update/delete operations.

1. Select all rows in the table DEPT_LOCATIONS. (0.5 point)
2. Select first name, middle initial and last name of all employees
supervised by the employee whose Social Security Number (SSN) is 333445555. (0.5 point)
3. Select the first name and salary of all female employees that work in department 5.
(0.5 point)
4. Select project name and project number of all projects controlled by department 5.
(0.5 point)
5. Select the department name and project name managed by the department. (1 point)

6. Use the LIKE operator and write a query to show the dependents whose name starts with
the letter A (include all columns). (1 point)

7. Produce a relation showing the employee first name, name of his/her dependent,
and relationship to the employee. (1 point)
8. Find the first and last name of all employees whose salary is above the average.(1 point)
9. Find the first and last name of all employees with no dependents.(1 point)

10. Produce a relation showing the department number and how many locations in each
department ordered by the number of locations in descending order. Hint: you must use
GROUP BY. (1.25 point)

11. For employees with dependents, produce a relation showing the first and last name of the
employee, and the number of dependents he/she has ordered by the number of
dependents in descending order. That is, your query should display this output: (1.25 point)
GEIT 3341 DATABASE I LAB 4

FNAME LNAME Number of Dependents

—————————————————————————————–

Franklin Wong 3

John Smith 3

Jennifer Wallace 1

12. Modify the previous query to only show employees whose number of dependents is
greater than 1. That is, your query should display the following output: (0.5 point)

FNAME LNAME Number of Dependents

—————————————————————————————–
John Smith 3

Franklin Wong 3

GEIT 3341 DATABASE I LAB 7

GEIT 3341 Database I

Lab 7

Stored Procedures & Triggers

Due Date:

Objective(s) Target CLO(s) Reference
To practice writing and calling stored 6 Chapter 8
procedures and functions.
To practice creating triggers and making
them fire by executing the triggering
event.

ID

Name

Section

Stored Procedures:

1. Complete and test the following stored procedure department_locations
that has one input parameter called dno that stores a department number,
and when called, will produce a report showing the location(s) of this
department. Here is an example to illustrate this procedure: (1.5 points)

SQL> exec department_locations(5);

Department Locations
==================
Bellaire
Sugarland
Houston
GEIT 3341 DATABASE I LAB 7

create or replace procedure department_locations(dno IN INTEGER) IS

cursor locations_cursor is

begin

dbms_output.put_line(‘ Department Locations ‘);

dbms_output.put_line(‘=================== ‘);

open locations_cursor;

loop

end loop;

close locations_cursor;

end;
GEIT 3341 DATABASE I LAB 7

2. Write a stored procedure from scratch called show_details that has one
input parameter called in_Ssn that stores a social security number of an
employee, and when called, will produce a report showing the first and last
name of the employee, and the department name that the employee works
in. Here is an example to illustrate this procedure: (1.5 points)

SQL> exec show_details(‘123456789 ‘);

FirstName LastName Department Name

================================

John Smith Research

Functions:

3. Write a function called number_of_dependents that accepts a social
security number for some employee and returns the number of dependents
for this employee. (1 points)

GEIT 3341 DATABASE I LAB 7

For testing, write and submit an anonymous block so that the function
displays the number of dependents for the employee like (assuming that the
function was called using SSN=123456789) this: (1 points)

This employee has 3 dependents

Triggers:

1. Use the CREATE TABLE command in Chapter 7 to create the following table
called ORDERS:

Column Name Data Type
OrderNo INTEGER

Product VARCHAR(20)
Units INTEGER
Price NUMBER
Total NUMBER

Next, write a statement-level trigger called update_order that will fire
whenever Units and/or Price is inserted/updated. When this happens, the
Total column will be set by trigger as the product of Units and Price. First, test
the trigger by inserting the following row:

INSERT INTO ORDERS(OrderNo,Product,Units,Price) VALUES(1001, ‘PC’, 2, 999);
(3 points)
GEIT 3341 DATABASE I LAB 7

Check that the Total column for this order is set correctly by the trigger to 1998.

Now, update the order like this:

UPDATE ORDERS SET Units=3 WHERE orderNo=1001;

Verify that the Total column for this order is set correctly by the trigger to 2997.

Finally, update the order like this:

UPDATE ORDERS SET Price=1200 WHERE orderNo=1001;

Verify that the Total column for this order is set correctly by the trigger to 3600.

2. Convert the trigger in the previous question from a statement-level trigger
to a row-level trigger. Insert another order and test it as outlined in the
previous question.
(2 points)
GEIT 3341 DATABASE 1 LAB 5

GEIT 3341 Database I

Lab 5

Schema Creation using SQL

Due Date:

Objective(s) Target CLO(s) Reference
1. Practice the CREATE TABLE 6 Chapter 6,7
command of SQL DDL by
creating two tables and linking
them together.
2. Use the CHECK constraint to
specify a domain for certain
columns.
3. To use the INSERT command
of SQL DML in order to
populate the tables with data.
4. Use the ALTER TABLE
command to add a new
column.
5. Use the UPDATE command to
assign a value of the new
column for each row.

Name

ID

Section

GEIT 3341 DATABASE 1 LAB 5

Instructions:

Use the CREATE TABLE command you learned in Chapter 7 and the INSERT command
you learned in Chapter 6 to create the following schema consisting of 3 tables. For each
field, pick the most appropriate data type for it. While creating the STORE table, add a
CHECK constraint to ensure that values of the STORE_YTD_SALES$ are greater than
zero.

Table Name: EMPLOYEE
Primary Key: EMP_CODE
EMP_CODE EMP_LNAME EMP_FNAMEEMP_DOB EMP_PHONE
1 Williamson John 29-DEC-62 (06) 434-0095
2 Ratula Nancy 12-MAR-67 (02) 526-1192
3 Greenboro Lottie 02-NOV-59 (03) 231-6292
4 Rumpersto Jennie 11-APR-64 (06) 224-1122
5 Shawn Michael 23-DEC-60 (06) 599-0406
6 Jones Rose 13-SEP-65 (06) 111-6262
7 Rosten Peter 25-OCT-65 (02) 111-1133
8 Bret Hart 12-AUG-67 (02) 796-1122
9 Ron Frank 11-NOV-60 (03) 432-1356
10 Elain Roberts 23-FEB-62 (06) 732-1967

Table Name: STORE
Primary Key: STORE_CODE
Foreign Key: REGION_CODE references REGION_CODE in REGION table
STORE_CODESTORE_NAME STORE_YTD_SALES$REGION_CODE
1 Access Jungle 20000.65 1
2 Database Corner 55000.00 2
3 PC Master 110000.44 2
4 Computer City 82000.11 1
GEIT 3341 DATABASE 1 LAB 5

Table Name: REGION
Primary Key: REGION_CODE
REGION
REGION_CODE REGION_DESCRIPT
1 East
2 West
3 North
4 South

After creating the three tables, use the ALTER TABLE command to add a new column
STORE_CODE to the EMPLOYEE table. Then use the ALTER TABLE command again to
designate it as a foreign that references STORE_CODE in STORE table. After this, use the
UPDATE command to set the STORE_CODE of each employee according to the following
table:

EMP_CODE STORE_CODE
1 1
2 2
3 2
4 4
5 1
6 2
7 3
8 1
9 2
10 3

Hand in:

1. The three CREATE TABLE commands for the three tables. (5 points)
2. The ALTER TABLE command to add the STORE_CODE column. (1 points)
3. The ALTER TABLE command to designate STORE_CODE as a foreign key. (1 points)
4. The UPDATE commands you used to assign a STORE_CODE for each employee.
(2 points)
5. Screen shots of the content of the three tables. (1 points)
GEIT 3341 DATABASE I LAB 6

GEIT 3341 Database I
Lab 6
Creating & Manipulating Views
Due Date:
Objective(s) Target CLO(s) Reference
1) To practice creating views
using the CREATE VIEW
command of SQL DDL.
2) To practice granting and
revoking privileges using the
GRANT and REVOKE
privileges.

6 Chapter 7

Name ID Section

Instructions:
1. Create a view called V1 to show the employee first name, last name,
project name he/she works on but only for employees who spend more
than 10 hours on the project. That is, your view should show the
following content:(2 points)
FNAMELNAMEPNAME
——————————————————————————
John SmithProductX
Jennifer WallaceNewbenefits
Jennifer WallaceReorganization
AliciaZelaya Newbenefits
Ramesh NarayanProductZ
Joyce English ProductY
Joyce English ProductX
GEIT 3341 DATABASE I LAB 6
AhmadJabbarComputerization

Write your view definition here.
Screen shot of the content of the view.
GEIT 3341 DATABASE I LAB 6

2. Create another view called V2 to show the employee first name, last
name, dependent name, and relationship. Customize the column names
as shown below: (2 points)

First Name Last NameDependent Name Relationship
————————————————————————————————————–
JohnSmithElizabethSpouse
JohnSmithMichaelSon
JohnSmithAliceDaughter
Franklin Wong Alice Daughter
Franklin Wong Theodore Son
Franklin Wong JoySpouse
Jennifer Wallace AbnerSpouse

Write your view definition here.
Screen shot of the content of the view.
GEIT 3341 DATABASE I LAB 6

3. Create another view called V3 to show the employee first and last name
of all employees who have a dependent whose name starts with A . That
is, your view should display the following:(2 points)

FNAMELNAME
————————————————————
JohnSmith
Franklin Wong
Jennifer Wallace
Write your view definition here.
Screen shot of the content of the view.
GEIT 3341 DATABASE I LAB 6

4. Write a query based on the view V1 in step 1 to retrieve allrows with
project name equals ProductX. (1 point)

Write your query definition here.
Screen shot of the content of the query.
GEIT 3341 DATABASE I LAB 6

5. Write a query based on the view V2 in step 2 to retrieve allrows with
relationship as Spouse. (1 point)
Write your query definition here.
Screen shot of the content of the query.
GEIT 3341 DATABASE I LAB 6

The next two steps must be completed in the lab.

6. Using the GRANT command, give your instructor (whose username is
instructor) a SELECT privilege on either V1, V2 or V3. Check with your
instructor that he can access your view. (1 point)

7. Finally, use the REVOKE command to revoke the SELECT privilege
granted in the previous step from your instructor. Check with him again
that he no longer can access your view. (1 point)

GEIT 3341 DATABASE I LAB 8

GEIT 3341 Database I
Lab 8
Database Design using ERDPlus
Due Date:
Objective(s) Target CLO(s) Reference
To practice Entity Relationship (ER)
modeling of Chapter 12 by using a
tool called ERDPlus to

1) Graphically create E-R diagrams
based on business rules.
AND
2) Generate the database by
automatically generating
complete DDL scripts.
1 Instructor Demo
Chapter 12

ID Name Section

GEIT 3341 DATABASE I LAB 8

Instructions:
Use the ERDPlus tool to create an ER diagram to support the following
requirements for a particular company:
– The company is organized into DEPARTMENTs. Each department has a
name, number and an employee who manages the department. We keep
track of the start date of the department manager.
– Each department controls a number of PROJECTs. Each project has a name,
number and is located at a single location.
– We store each EMPLOYEE’s first name, middle initial, last name, social security
number, address, salary, sex, and birthdate. Each employee works for one
department but may work on several projects. We keep track of the number of
hours per week that an employee currently works on each project. We also keep
track of the direct supervisor of each employee.
– Each employee may have a number of DEPENDENTs. For each dependent, we
keep track of their name, sex, birthdate, and relationship to employee.

Use the following table to set the data types:

Attribute Name Data Type
Name VARCHAR(20)
Ssn CHAR(9)
DOB DATE
Salary NUMERIC(6,2) where 6 is the Length and 2 is the
Precision
Pname VARCHAR(20)
Pnumber INTEGER
Plocation VARCHAR(20)
Dependent_name VARCHAR(20)
Sex CHAR(1)
Relationship VARCHAR(20)
Dname VARCHAR(20)
Dnumber INTEGER
Hours NUMERIC(3,1) where 3 is the Length and 1 is the
Precision
GEIT 3341 DATABASE I LAB 8

Your diagram should look like the one shown in Figure 1 below:

Figure 1: ER Diagram

What you need to hand in:

1. Using the Export Image… option of MENU (see Figure 2), generate an image for the diagram
(which will be in a PNG format) and hand in this image.(8 points)
2. Using the Generate SQL option (see Figure 3), generate the DDL, click on the Copy button,copy
this script into a Notepad/Word file and hand in this file.(2 points)

GEIT 3341 DATABASE I LAB 8

Figure 2: Export Image Option

GEIT 3341 DATABASE I LAB 8

Figure 3: Generate SQL Option

GEIT 3341 DATABASE I LAB 3

GEIT 3341 Database I

Lab 3

Web Development with Apex II

Due Date:

Submission Method: Demo

Objective(s) Target CLO(s) Reference
How to develop a web database 5 Instructor Demo
application using Oracle ApplicationAny book and/or
Express. In today’s lab, you will learntutorial on Oracle
how to develop a web application fromApplication Express
existing tables.

Name

ID

Section

Instructions:

This is a hands-on step-by-step lab that will teach you how to

• Build a first cut of an app based on existing tables
• Learn how to install a dataset to generate the tables needed by the application
• Learn how to add pages, and link pages
GEIT 3341 DATABASE I LAB 3

PART 1: Preparing the dataset (0.5 point)

1. From your APEX workspace home page, select SQL Workshop, click Utilities, and then
click Sample Datasets.
2. On the Project Data row, click Install.

3. Click Next.
4. Click Install Dataset.
5. Click Exit.

PART 2: Creating the Application (5.5 points)

A. Step 1: Creating and naming the app
1. In the App Builder menu, click App Builder.
2. Click Create.
3. Click New Application
4. In the Create Application wizard, for Name enter Lab3_ID
5. Click Set Appearance button in Appearance.
6. On the Appearance dialog, for Theme Style select Vita – Slate.
7. Click Choose New Icon.

8. On the Choose Application Icon dialog, select an icon color and an icon of your
choosing. Click Set Application Icon.
9. Click Save Changes.
GEIT 3341 DATABASE I LAB 3

B. Step 2: Add the dashboard page
1.In the Create Application wizard, click Add Page

2. Click Dashboard.

3. For Chart 1, enter the following:

Pay attention! Don’t click Add Page.
GEIT 3341 DATABASE I LAB 3

4.Click Chart 2, and enter the following:

5.Click Chart 3, and enter the following:

6. Click Add Page

C. Step 3: Add the project page

1. In the Create Application wizard, click Add Page.
2. Click Cards.
3. On the Add Cards Page, enter the following:

4. Click Add page

D. Step 4: Add the Milestone Pages
1. In the Create Application wizard, click Add Page.
2. Click Interactive Report.
3. On the Add Report Page, enter the following:

4. Click Add Page

GEIT 3341 DATABASE I LAB 3

E. Step 5: Add the Tasks Pages(Faceted Interactive Report and Calendar)
1. In the Create Application wizard, click Add Page.
2. Click Faceted Search.
3. On the Add Faceted Search Page, enter the following:

5. Click Add page

6. Place the Tasks Search page up under the Dashboard page. After the edit button, drag
and drop the tasks search to place it under the dashboard page.
7. In the Create Application wizard, click Add Page.
8. Click Interactive Report and fill it with the following.

8. In the Create Application wizard, click Add Page
9. Click Calendar.
10. On the Add Calendar Page, enter the following:

GEIT 3341 DATABASE I LAB 3

F. Step 6: Generating the App
1. Scroll to the bottom of the page, and click Create Application.

2. Once the application has been generated, your new app will be displayed in the
application home page. Click Run Application.
3. Enter your user credentials. Click Sign In.
4. Your runtime environment is ready! Play around with the new application!

GEIT 3341DATABASE ILAB 2
GEIT 3341 Database I
Lab 2
Web Development with Apex
Due Date:
Submission Method: Project Link, Username & Password
Objective(s)Target CLO(s)Reference
How to develop a web database
application using Oracle Application
Express. In today’s lab, you will learn
how to develop a web application from
a spreadsheet.
5Instructor Demo Any
book and/or tutorial
on Oracle
Application Express
NameIDSection
Instructions:
This is a hands-on step-by-step lab that will teach you how to
• Build your first app through uploading a Spreadsheet
• Improve the Search and Report pages

GEIT 3341DATABASE ILAB 2
PART 1: Create an app from Spreadsheet
You will build in this part a simple application based on a spreadsheet.
1. From your APEX workspace home page, click App Builder.

2. Click onCreate a New App
GEIT 3341DATABASE ILAB 2

4. Click the Copy and Paste option at the top and Select Project and Tasks from the sample
data set list and then click Next.

5. Set Table Name to PROJECT_TASKS and click Load Data.

7. Click Create App
3. ClickFrom a File
6. Click onViewTable
GEIT 3341DATABASE ILAB 2

8. On the Create Application page, click Create App.
9. On the Create Application page, you can see all the listed pages. Click on Edit for more
details for a page.
10. Next to Feature, click Check all then click on Create Application

11. Now, you are redirected to the application’s home page in the App Builder.
12. Click on Run Application to see how end users will see the app (a new web browser will
open). You have to enter your username and password you used to sign in into the APEX
workspace.
13. Explore the application by clicking on all the available buttons. (Click on Home button to
go back to the main page)
14. Click on Administration to review the options if needed
GEIT 3341DATABASE ILAB 2
PART 2: Improve the Faceted Search
This part gives you a first-hand experience with enhancing the application by improving the
faceted search page for better clarity and improved usability.
1. For the runtime application, click on Project Tasks Search

2. In the toolbar (bottom of the page), click Edit page 3 (Note – End users who log directly
into the app will not see this toolbar.)

3. Alternatively, you can also navigate back to the APEX App Builder tab in your browser
manually by selecting the appropriate browser tab or window. Once in the App Builder
click 3 – Project Tasks Search.
4. You will have this window (The page should consist of three panes).
GEIT 3341DATABASE ILAB 2
5. Under Search (from the left pane), within Facets then P3_STATUS

6. Currently the Status facet is a series of checkboxes. However, it is unlikely that users will
want to select more than one at a time, so you will convert it to a radio group.
Continuing with P3_STATUS selected, within the Property Editor (right pane), for
Identification > Type, select Radio Group. Scroll down the properties in the Property
Editor, and for List Entries > Zero Count Entries select Show Last. Then click Save and
Run Page at the top right of your page
GEIT 3341DATABASE ILAB 2

7. In the runtime environment, click one of the statuses. Review how the counts on the
other facets are updated based on your selection. Also notice how you can still select
one of the other statuses or click Clear, within the Status facet, to clear your selection
and again show all the counts for statuses.
8. Navigate back to the development environment (APEX App Builder) by using the edit link
in the Developer toolbar, or manually navigating to the appropriate browser tab in order
to to collapse the last three facets, making it easier to see all available facets. In the
Rendering tree (left pane), under Search, within Facets, click P3_ASSIGNED_TO, hold
down the Control key and then click P3_COST, such that three facets are selected.
In the Property Editor (right pane), within the filter at the top, enter collap, so that only
two attributes are displayed.

9. ClickSave and Run Page
GEIT 3341DATABASE ILAB 2

PART 3: Improve the Report
In this part, you will gain an insight into the abilities of Interactive Reports, and learn how to
enhance a form page.
1. In the runtime environment, click Project Tasks Report.
2. Click the Project column heading, and then select Control Break.

3. To add a computational column, where a new column is defined based on a
computation against one more existing columns in the report click at the top of the
report, click Actions, select Data, and then select Compute.

4. Fill with the appropriate value (shown on the screenshot). The format mask should be
selected from the drop down menu and the computation expression should be filled by
clicking on the right columns and keypad. Click Apply to add a new column Budget V
Cost to your interactive report.
GEIT 3341DATABASE ILAB 2

5. To add up the Budget versus Cost for each project, click Actions, select Data, and then
select Aggregate.
6. On Aggregate, for Column select Budget V Cost, then click Apply.
7. Even though you can see the total budget versus cost for each project, it would be much
easier to view the totals on a chart to get an overall picture of all of the projects. In the
report click Actions, select Chart.
8. Fill the Chart with the appropriate value (shown on the screenshot). Click Apply

9. If you want to sort all the records by Start Date and then End Date, Click View Reports
Icon, click Actions, select Data, and then select Sort. Fill it with the following values and
click Apply
GEIT 3341DATABASE ILAB 2

10. As a developer you also have the ability to save the report as the Primary (default)
Report, or an Alternative Report. For this exercise you will save the report as a named
Alternative Report. In the report, click Actions, select Report, and then select Save
Report. On Save Report, for Save(Only displayed for developers) select As Default
Report Settings. In the Save Default Report dialog, select Alternative, for Name
enter Budget Review, and click Apply.
To go back to how the end user will see the report when they first access the report, at
the top of the report, select 1. Primary Report, and then click Reset.

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more