Unit 365 Assignment 3

CIT 365 – Assignment # 4 – Intermediate queries (1 table)
Some important notes
1. When you are finished working with SQLPlus, type the following: Commit; Exit; This will permanently save your work
2. It is STRONGLY suggested that you do your scratch work on Notepad or Textpad.

3. For each question, show the question, the SQL statement, and the result.

SELECT AVG (T1) FROM Student;
SELECT (T1+T2+T3)/3 FROM Student;

Assignment 4
1. Create an Oracle table for the following STUDENT data:
STUDENT

Name
ID
Major
Course
Assignment 1
Assignment 2
Midterm
Final
Project
Aisha Ahmed
200312345
CIT
CIT365
83
91
77
82
71
Anood Abdulla
200312356
BUS
CIT365
77
81
66
76
81
Budoor Mahommad
Which students have the letter “s” in their names?
SELECT Name FROM students WHERE Name LIKE '%s%';
+---------------+
| Name |
+---------------+
| Laila Saeed |
| Aisha Ahmed
List the average score for each student. Assume that all assessments have equal weight.
SELECT Name,(A1+A2+Midterm+Final+Project)/5 as Average FROM students;

+-----------------+---------+
| Name | Average |
+-----------------+---------+
| Laila Saeed | 74.4000 |
| Ghulood Ali | 77.4000 |
| Aisha Ahmed | 80.8000 |
| Anood Abdulla | 76.2000 |
| Fatma Essa | 64.6000 |
| Maryam Ibrahim | 87.2000 |
| Budoor Mahommed | 88.2000 |
| Hanan Ahmed | 93.6000 |
| Shamsa Khalid | 93.4000 |
| Hanan Abdulla | 85.6000 |
+-----------------+---------+

7. What is the average score for the student who scored highest on the midterm?
SELECT Name,MAX(Midterm),(A1+A2+Midterm+Final+Project)/5 as HighstMidtermScoredStudentsAverage FROM students;

+-------------+--------------+------------------------------------+
| Name | MAX(Midterm) | HighstMidtermScoredStudentsAverage |
+-------------+--------------+------------------------------------+
| Laila Saeed | 92 | 74.400000000 |
+-------------+--------------+------------------------------------+

8. List the data sorted by Major and within major by Name.
SELECT Major From students ORDER BY Major;
+-------+
| Major |