 |
IDB220S - INTRODUCTION TO DATABASES - 2ND OPP - JAN 2020 |
|
|
1 Page 1 |
▲back to top |
NAMIBIA UNIVERSITY
OF SCIENCE AND TECHNOLOGY
FACULTY OF COMPUTING AND INFORMATICS
DEPARTMENT OF COMPUTER SCIENCE
QUALIFICATION:
DIPLOMA IN GEOMATICS NQF: 6
BACHELOR OF BUSINESS COMPUTING NQF: 7
BACHELOR OF PROPERTY STUDIES HONS. NQF: 8
BACHELOR OF INFORMATION TECHNOLOGY (SYSTEMS ADMINISTRATION AND NETWORKS) NQF: 7
QUALIFICATION CODE: 27DGEM, 80BSBC,
27BPRS, 80BSAN
LEVEL: 6
COURSE: INTRODUCTION TO DATABASES
1B
COURSE CODE: IDB220S
DATE: JANUARY 2020
DURATION: 3 HOURS
SESSION: 2
MARKS: 100
SECOND OPPORTUNITY / SUPPLEMENTARY EXAMINATION QUESTION PAPER
EXAMINER(S)
MS JOSEPHINA MUNTUUMO
MODERATOR:
MS ROSETHA KAYS
INSTRUCTIONS
Answer all questions.
Please, ensure that your writing is legible, neat and presentable.
When answering questions you should be led by the allocation of marks.
Clearly, mark rough work as such or cross it out unambiguously in ink.
QUESTION PAPER CONSISTS OF 5 PAGES (Including this front page)
|
|
2 Page 2 |
▲back to top |
SECTION A: THEORY
Question 1
[MARKS: 40]
[10 marks]
Choose True or False
Questions
IT
F
1
An alias can be used in the WHERE clause.
2
The ORDER BY clause is always placed at the end of the statement.
3
The SYSDATE function records the current date and time.
4
System Privileges include gaining access to the databases.
5
The WHERE clause restricts rows before inclusion in a group calculation.
6
The HAVING clause is placed before the WHERE clause.
7
INSTR function is used to find a position of a character in a character
string.
8
In Set operators, the numbers of columns inthe Select clause do
not need to match.
9
All Group functions include NULLS in their calculations.
10 The MOD function is used to find the remainder after division.
Question 2
Distinguish the difference between BETWEEN and IN operators in SQL?
Question 3
Describe a view in SQL?
Question 4
Explain the meaning of an Index in SQL?
Question 5
What is the usage of SQL functions? Name any two (2)
Question 6
What do you understand by case manipulation functions?
Question 7
[2 marks]
[4 marks]
[2 marks]
[2 marks]
[2 marks]
[2 marks]
|
|
3 Page 3 |
▲back to top |
Explain the meaning of character-manipulation functions?
Question 8
[2 marks]
What is the usage of the DISTINCT keyword?
Question 9
[4 marks]
List the set operators in SQL?
Question 10
{1 mark]
Is it possible to sort a column using a column alias?
Question 11
Explain the difference between INNER and OUTER Joins.
[2 marks]
Question 12
[1 mark]
What is the usage of NVL() function?
Question 13
[2 Marks]
Examine the code below. Determine what is wrong with the following statement. Rewrite the code
correctly.
SELECT job_id, AVG(Salary)
FROM Employees
WHERE AVG(Salary) > 8000;
Question 14
Study the statement below and evaluate what the code will achieve.
SELECT e.last_name, e.department_id, d.department name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department id = d.department id);
Question 15
What does DCL, DML and DDL stands for?
[1 mark]
[3 marks]
|
|
4 Page 4 |
▲back to top |
SECTION B: PRACTICAL
[MARKS: 60]
The tables that will be used for the following section are: employees; job_history; jobs;
departments; countries; locations; regions; Semester Marks;
Question 1
[4 marks]
Write an SQL Statement to display the first_name, last_name of employees with the pattern 'I_x' in
their first name.
Question 2
[3 marks]
Write an SQL statement to retrieve the name of all the employees who are working in department
number 20 and 80.
Question 3
[7 marks]
Write an SQL Statement to display the first name, last name, department number and department
name, for all employees for departments 80 or 40.
Question 4
[3 marks]
Write an SQL Statement to create a view for all salesmen with columns salesman_id, name, and city.
Question 5
[3 marks]
Write an SQL Statement to update the city name which salesman_id is '5007'
Question 6
[9 marks]
Write a SQL statement to create a table named Semester_Marks using the fields (Student_Reg_No,
FirstName, LastName, Total_Marks, No Courses, Average_Mark) provided. Choose relevant data
types.
Question 7
[5 marks]
Write an SQL statement to populate three (3) rows to the table Semester_Marks (values of your
choice)
Question 8
[2 marks]
Write an SQL statement to confirm that your data has been added to the table.
Question 9
{2 marks]
Write an SQL statement to remove the table Semester_Marks.
Question 10
[5 marks]
Write an SQL statement to display the first name, last name, department number, and department
name for each employee.
Question 11
[6 marks]
The HR department needs a report that displays the last name, department number, and job ID of all
employees whose department location ID is 1700.
Question 12
[5 marks]
|
|
5 Page 5 |
▲back to top |
Write an SQL statement to get the job ID and maximum salary of the employees where maximum
salary is greater than or equal to $4000.
Question 13
[6 marks]
The HR department needs a list of department IDs for departments that do not contain the job ID
ST_CLERK. Use the set operators to create this report.
<<<<<<End of Exam Paper»>>>