Download Introduction to Database: SQL Assignment and more Cheat Sheet Computer science in PDF only on Docsity!
In Academic Affiliation with California State University – Northridge
Department of Information Technology
Continuous Assessment Tool
Second Semester 2023-’
Course Code : COMP
Course Name : Introduction to Database
Course Instructor : Dr. Sohail Iqbal
Due Day : Wednesday
Due Date : 08 /May/
Assessment tool : Assignment 1
Student Name : Mohammed Khamis Khamis ALdhouri
I.D. Number : 202000504
Section : ……………… 1 ……………..……………………
Question No.
Marks Obtained (^) Maximum
Marks
Comments if any O1 O2 O3 O4 O5 O
Outcomes Total / 15
Total Marks(in figures) Total Marks(in words)
Evaluator’s Signature
Internal Moderator’s
Signature
External Moderator’s
Signature
Remark: Candidates are advised to read all instructions before answering.
Objectives : The following student learning outcomes of the course are in this continuous assessment
tool:
O6. Develop database system using basic command and function of SQL
The assignment covers the following graduate attributes:
- Critical Thinking
- Problem Solving
Description of the Tool :
In this assignment, you are required to write the SQL code for the given questions.
Task:
The following is the relational schema of part of the database that Saas Pty Ltd recently implemented:
[O6] [15 Marks]
Employee (EmployeeId, Name, Country, Phone, Email, Fax, Salary, BranchNo)
Branch (BranchNo, BranchName, Phone, Fax, Location, Address)
Project (ProjectId, Title, Duration, Location, Extension, Cost)
Task (EmployeeId, ProjectId, StartDate, CompletionDate)
Using the above schema, write SQL queries that will answer/process the following:
- List name, country and phone of all employees. [1 Mark]
SELECT name, country, phone
From Employee;
- List EmployeeId and fax of all the employees along with the titles of the projects. [1 Mark]
SELECT E.EmployeeId, E.fax, P.title
From employee as E, project as P, task as T
Where E.EmployeeId=T.EmplyeeId and
T.projectId=P.projectId;
- List project id, duration and location of all projects ordered by location in descending order. [1 Mark]
SELECT projectId, Duration, Location
- List all details of projects that are located in Sur, Salalah and Nizwa. [1 Mark]
SELECT *
FROM PROJECT
WHERE LOCATION IN (‘Salalah’, ‘SUR’, ‘NIZWA’);
- List EmployeeId, name and their branch names. [1 Mark]
SELECT E.employeeId, E.name, B.Branchname
FROM Employee as E, Branch as B
Where B.BranchNo=E.BranchNo;
- List all details about all branches ordered by location and, where the location is the same, by address
in descending order. [1 Mark]
SELECT *
FROM Branch
ORDER BY Location, Address DESC;
- Show the average salaries of employees. [1 Mark]
SELECT AVG (Salary)
FROM Employee;
- Show the total number of projects. [1 Mark]
SELECT COUNT(*)
FORM Project;
Submission Date (s): Wednesday (08/May/2024)
Assessment Weighting: 15 Marks
WHAT TO SUBMIT
You are required to submit a soft copy (pdf file) of your assignment which contains SQL code for the
given task through Moodle.
Assessment Criteria:
No_._ Assessment Criteria Marks
3 Task: SQL
Total Mark / 15
End of document
