MySQL Question

College of Computing and Informatics

Assignment 2
Deadline: 27/11/2024 @ 23:59
[Total Mark for this Assignment is 8]
Student Details:
Name: ###

ID: ###

CRN: ###
Instructions:

• You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on
Blackboard via the allocated folder. These files must not be in compressed format.

• It is your responsibility to check and make sure that you have uploaded both the correct files.
• Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between
words, hide characters, use different character sets, convert text into image or languages other than English
or any kind of manipulation).

• Email submission will not be accepted.
• You are advised to make your work clear and well-presented. This includes filling your information on the cover
page.

• You must use this template, failing which will result in zero mark.
• You MUST show all your work, and text must not be converted into an image, unless specified otherwise by
the question.

• Late submission will result in ZERO mark.
• The work should be your own, copying from students or other resources will result in ZERO mark.
• Use Times New Roman font for all your answers.

Restricted – ‫مقيد‬

Question One

Pg. 01
Learning
Outcome(s):

Question One

CLO 3

Consider the following EMPLOYEE relation from a company database. Write

Create EntityRelationship
model, Relational
model, and write
SQL queries

2 Marks

SQL queries to answer the following questions:
EmpID

Name

Salary

Gender

Address

123

Sarah

50000

Female

Jeddah

234

Mohamed

45000

Male

Riyadh

376

Ahmed

70000

Male

Jeddah

498

Maryam

10000

Female

Jeddah

555

Muna

30000

Female

Riyadh

1- Retrieve the count of Female Employees.
2- Retrieve the average Salary of all Employees.
3- Delete the record of the Employee named “Muna”.
4- Insert a new employee into the table.

Restricted – ‫مقيد‬

Question Two

Pg. 02
Learning
Outcome(s):

Question Two

2 Marks

Answer the following questions by referring to the tables below:
CLO 3: Create
EntityRelationship
model, Relational
model, and write
SQL queries.

COURSE
Course_name

Course_number

Credit_hours

Department

Intro to Computer Science

CS310

4

CS

System Analysis and
Design

IT353

4

IT

Discrete Mathematics

MATH410

3

MATH

IT403

3

IT

Fundamentals of
Database

SECTION
Section_ID

Course_no

Semester

Year

Instructor

80

MATH410

Fall

23

Sara

95

CS310

Fall

23

Ali

103

IT353

Fall

24

Khaled

115

MATH410

Fall

24

Ahmed

120

CS310

Spring

24

Ali

140

IT403

Fall

24

Eman

a) Create a virtual table called (COURSE_ON_SEC) that summarize courses
information along with its section and instructor, listed alphabetically by
course name.
b) Retrieve the section ID, course name, course number, department, and
instructor of the courses taught by instructor “Ali” from
COURSE_ON_SEC table.

Restricted – ‫مقيد‬

Question Three

Pg. 03
Learning
Outcome(s):
CLO4 – Design a
database starting

Question Three

2 Marks

You are given a table with unnormalized design. You are required to transform the
provided table into First Normal Form (1NF), and Second Normal Form (2NF). For
each step, provide the new table(s) and explain the changes made.

from the
conceptual design

Order_ID

Supplier_Name

Products_Info

Order_Date

001

Amazon

P1, product101,
product102

01-11-2024

002

Noon

P2, product201,
product202,
product203

04-11-2024

003

Alim

P3, product301

10-11-2024

to the
implementation of
database
schemas

Restricted – ‫مقيد‬

Question Four

Pg. 04
Learning
Outcome(s):
CLO3:

Question Four

2 Marks

Using join:
–

write an SQL query to retrieve the names and addresses of all employees who work in
Research departments.

Create EntityRelationship
model, Relational
model, and write
SQL queries.

Restricted – ‫مقيد‬

–

Write an SQL query to retrieve the names of all employees who have a higher salary
than their manager. Use a self-join to compare the salaries.

College of Computing and Informatics

Assignment 2
Deadline: 27/11/2024 @ 23:59
[Total Mark for this Assignment is 8]
Student Details:
Name: ###

ID: ###

CRN: ###
Instructions:

• You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on
Blackboard via the allocated folder. These files must not be in compressed format.

• It is your responsibility to check and make sure that you have uploaded both the correct files.
• Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between
words, hide characters, use different character sets, convert text into image or languages other than English
or any kind of manipulation).

• Email submission will not be accepted.
• You are advised to make your work clear and well-presented. This includes filling your information on the cover
page.

• You must use this template, failing which will result in zero mark.
• You MUST show all your work, and text must not be converted into an image, unless specified otherwise by
the question.

• Late submission will result in ZERO mark.
• The work should be your own, copying from students or other resources will result in ZERO mark.
• Use Times New Roman font for all your answers.

Restricted – ‫مقيد‬

Question One

Pg. 01
Learning
Outcome(s):

Question One

CLO 3

Consider the following EMPLOYEE relation from a company database. Write

Create EntityRelationship
model, Relational
model, and write
SQL queries

2 Marks

SQL queries to answer the following questions:
EmpID

Name

Salary

Gender

Address

123

Sarah

50000

Female

Jeddah

234

Mohamed

45000

Male

Riyadh

376

Ahmed

70000

Male

Jeddah

498

Maryam

10000

Female

Jeddah

555

Muna

30000

Female

Riyadh

1- Retrieve the count of Female Employees.
2- Retrieve the average Salary of all Employees.
3- Delete the record of the Employee named “Muna”.
4- Insert a new employee into the table.

Restricted – ‫مقيد‬

Question Two

Pg. 02
Learning
Outcome(s):

Question Two

2 Marks

Answer the following questions by referring to the tables below:
CLO 3: Create
EntityRelationship
model, Relational
model, and write
SQL queries.

COURSE
Course_name

Course_number

Credit_hours

Department

Intro to Computer Science

CS310

4

CS

System Analysis and
Design

IT353

4

IT

Discrete Mathematics

MATH410

3

MATH

IT403

3

IT

Fundamentals of
Database

SECTION
Section_ID

Course_no

Semester

Year

Instructor

80

MATH410

Fall

23

Sara

95

CS310

Fall

23

Ali

103

IT353

Fall

24

Khaled

115

MATH410

Fall

24

Ahmed

120

CS310

Spring

24

Ali

140

IT403

Fall

24

Eman

a) Create a virtual table called (COURSE_ON_SEC) that summarize courses
information along with its section and instructor, listed alphabetically by
course name.
b) Retrieve the section ID, course name, course number, department, and
instructor of the courses taught by instructor “Ali” from
COURSE_ON_SEC table.

Restricted – ‫مقيد‬

Question Three

Pg. 03
Learning
Outcome(s):
CLO4 – Design a
database starting

Question Three

2 Marks

You are given a table with unnormalized design. You are required to transform the
provided table into First Normal Form (1NF), and Second Normal Form (2NF). For
each step, provide the new table(s) and explain the changes made.

from the
conceptual design

Order_ID

Supplier_Name

Products_Info

Order_Date

001

Amazon

P1, product101,
product102

01-11-2024

002

Noon

P2, product201,
product202,
product203

04-11-2024

003

Alim

P3, product301

10-11-2024

to the
implementation of
database
schemas

Restricted – ‫مقيد‬

Question Four

Pg. 04
Learning
Outcome(s):
CLO3:

Question Four

2 Marks

Using join:
–

write an SQL query to retrieve the names and addresses of all employees who work in
Research departments.

Create EntityRelationship
model, Relational
model, and write
SQL queries.

Restricted – ‫مقيد‬

–

Write an SQL query to retrieve the names of all employees who have a higher salary
than their manager. Use a self-join to compare the salaries.

Purchase answer to see full
attachment