Question Details

(solution) Mary Jones one of the employees has completed her Engineering


 Mary Jones one of the employees has completed her Engineering studies a UNISA and applies for an Engineering position in the company. The user enters the following SQL to update her records, but receives an error message.

UPDATE EMPVIEW401 SET GROUPNAME = ?ENGINEERING?

WHERE NAME = ?Mary Jones?;

You explain to the user that the error message occurred because:


ICT3722/202/2/2016 Tutorial Letter 202/2/2016

 

Database Practice ICT3722

 

Semester 2 School of Computing IMPORTANT INFORMATION:

 

This tutorial letter contains the questions for Assignment 2.

 

Due date 26 August 2016

 

Unique Number: 796318 1

 

Open Rubric ICT3722/202/2/2016

 

ICT3722-16-S2-Ass2

 

Use the same tables and data as for assignment 1.

 

Read the question and if required run the SQL against the tables and data.

 

Select the most relevant answer from the list of choices provided.

 

When you are done, logon to myUNISA, select the module code: ICT3722, select

 

Assignment2. Complete the online MCQ. When done submit the assignment.

 

Keep in mind that you cannot go back once you?ve submitted the assignment.

 

Keep a copy of your solutions should there be any queries afterwards.

 

1. The user enters two queries, Q1 and Q2.

 

Q1:

 

SELECT CUSTID, PRODID

 

FROM CUSTOMERS

 

NATURAL JOIN SALES;

 

Q2:

 

SELECT CUSTID, NAME

 

FROM CUSTOMERS

 

NATURAL JOIN PROVINCES;

 

Q1 returns data, but not Q2.

 

He does not understand why this happening. You studied both queries and make the

 

following conclusion:

 

1) The results returned by Q1 are correct. The CUSTID and PRODID are

 

unique to the tables CUSTOMERS and SALES.

 

No results will be returned by Q2 as the field name NAMES resides in both

 

the CUSTOMERS and the PROVINCES tables. This query cannot be

 

resolved by a NATURAL JOIN query.

 

2 ICT3722/202/2/2016 2) The results returned by Q1 are correct. The NATURAL JOIN clause uses

 

the CUSTID from the CUSTOMERS table join it with the CUSTID in the

 

SALES table, then uses the PRODID from the SALES table and join it with

 

the PRODID in the PRODUCTS table.

 

No results are returned by Q2. This can be solved by inserting a prefixes

 

to distinguish the column names that appear in both tables:

 

SELECT C.CUSTID, P.NAME

 

FROM CUSTOMERS C

 

NATURAL JOIN PROVINCES P; 3) The results from Q1 are correct as the field names CUSTID and PRODID

 

are unique to CUSTOMERS and SALES respectively.

 

Q2 does not return data as the field name NAME resides in both the

 

CUSTOMERS and the PROVINCES tables. This can be solved by

 

including a WHERE clause:

 

SELECT CUSTID, NAME

 

FROM CUSTOMERS C

 

WHERE CUSTOMERS.PROVID = PROVINCES.PROVID

 

NATURAL JOIN PROVINCES; 4) Although Q1 returns data the data is a Cartesian product as there is no

 

WHERE clause joining the fields from the two tables.

 

No results will be returned by Q2 as the field name NAMES resides in both

 

the CUSTOMERS and the PROVINCES tables. This query cannot be

 

resolved by a NATURAL JOIN query. 2. SELECT C.CUSTID, C.NAME,

 

P.NAME, 3 ICT3722/202/2/2016

 

COUNT(SALEDATE) "NUM SALES"

 

FROM CUSTOMERS C

 

JOIN PROVINCES P ON (C.PROVID = P.PROVID)

 

JOIN SALES S ON (C.CUSTID = S.CUSTID)

 

GROUP BY C.CUSTID, C.NAME, P.NAME

 

ORDER BY COUNT(SALEDATE);

 

The purpose of the above SQL is to __

 

1) join the three tables, CUSTOMERS, PROVINCES and SALES by selecting

 

the CUSTID and NAME from CUSTOMERS table, the NAME of the

 

province from the PROVINCE table and the SALEDATE from the SALES

 

table. The GROUP BY function is used to determine the total number of

 

sales made by a specific customer. The report is sorted in descending

 

order according to the number of sales. 2) join the three tables, CUSTOMERS, PROVINCES and SALES by selecting

 

the CUSTID and NAME from CUSTOMERS table, the NAME of the

 

province from the PROVINCE table and the SALEDATE from the SALES

 

table. The GROUP BY function is used to count the number of sales made

 

by a specific customer. The report is sorted in ascending order according to

 

the number of sales. 3) join the three tables, CUSTOMERS, PROVINCES and SALES by selecting

 

the CUSTID and NAME from CUSTOMERS table, the NAME of the

 

province from the PROVINCE table and the SALEDATE from the SALES

 

table. The GROUP BY function is used to count the number of sales made

 

by a specific customer. The report is not sorted in any specific order. 4) This query has no purpose and will result in an error as there are fields with

 

similar names in the CUSTOMER and PROVINCES tables. 3. Study the following SQL and the consequent output: 4 ICT3722/202/2/2016 SELECT EMPID, GROUPID, SALARY

 

FROM EMPLOYEES

 

WHERE GROUPID = (SELECT GROUPID FROM EMPLOYEES WHERE EMPID = 500)

 

AND SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES);

 

Output: Which of the following best describe the purpose of the above SQL?

 

1) The query consists of two queries. The first query determines the

 

GROUPID for employee = 500 and the second query the average salary

 

from EMPLOYEES. The output is a cartesian product as the second

 

query returns more than one record. The output is not reliable. 2) The query consists of three query blocks, the outer query and the inner

 

queries. The inner queries are executed first, producing the results 400

 

and 43000 respectively.

 

The outer block is now processed and uses the values that were returned

 

by the inner search.

 

The query could be read as:

 

SELECT EMPID, GROUPID, SALARY

 

FROM EMPLOYEES

 

WHERE GROUPID = 400 AND SALARY > 43000;

 

This is an example of a multi-row query. 3) The query will return an error. There cannot be more than one SELECT 5 ICT3722/202/2/2016

 

statement in one statement.

 

4) The query consists of three query blocks, the outer query and the inner

 

queries. The inner queries are executed first, producing the results 400

 

and 43000 respectively.

 

The outer block is now processed and uses the values that were returned

 

by the inner search.

 

The query could be read as:

 

SELECT EMPID, GROUPID, SALARY

 

FROM EMPLOYEES

 

WHERE GROUPID = 400 AND SALARY > 43000;

 

Both the inner queries return single values so this statement is called a

 

single-row query. 4. Which of the following SQL will correctly select all the employees that have people reporting

 

to them?

 

1) SELECT E.NAME||' '||E.SURNAME EMP

 

FROM EMPLOYEES E, EMPLOYEES M

 

WHERE E.EMPID = M.EMPID; 2) SELECT DISTINCT E.NAME||' '||E.SURNAME EMP

 

FROM EMPLOYEES E, EMPLOYEES M

 

WHERE E.EMPID = M.EMPID; 3) SELECT E.NAME||' '||E.SURNAME EMP

 

FROM EMPLOYEES E

 

WHERE E.EMPID IN

 

(SELECT M.MENTORID FROM EMPLOYEES M); 4) SELECT E.NAME||' '||E.SURNAME EMP

 

FROM EMPLOYEES E

 

WHERE E.EMPID =

 

(SELECT M.MENTORID FROM EMPLOYEES M); 6 ICT3722/202/2/2016 5. The user enters the following two queries. Q1 returns data but Q2 not.

 

Q1:

 

SELECT SURNAME, GROUPID FROM EMPLOYEES

 

WHERE GROUPID =

 

(SELECT GROUPID FROM EMPLOYEES

 

WHERE SURNAME = 'Pretorius');

 

Q2:

 

SELECT SURNAME, GROUPID FROM EMPLOYEES

 

WHERE GROUPID =

 

(SELECT GROUPID FROM EMPLOYEES

 

WHERE SURNAME = 'Jones');

 

You studies both queries and the results and concluded that __

 

1) Both Q1 and Q2 are incorrect. There should be an 'IN' command in the

 

WHERE clause, example:

 

SELECT SURNAME, GROUPID FROM EMPLOYEES

 

WHERE GROUPID IN (SELECT GROUPID FROM EMPLOYEES WHERE

 

SURNAME = 'Jones'); 2) Both Q1 and Q2 are multi-row queries, thus in the inner query only one

 

record can be returned. To solve this add a DISTINCT function and a

 

GROUP BY clause in the queries, example:

 

SELECT SURNAME, DISTINCT GROUPID FROM EMPLOYEES

 

WHERE GROUPID GROUP BY (SELECT GROUPID FROM EMPLOYEES WHERE

 

SURNAME = 'Jones'); 3) Both Q1 and Q2 are multi-row queries, thus in the inner query only one

 

record can be returned. To solve this add a HAVING clause in the

 

queries, example: 7 ICT3722/202/2/2016 SELECT SURNAME, GROUPID FROM EMPLOYEES

 

WHERE GROUPID HAVING (SELECT GROUPID FROM EMPLOYEES WHERE

 

SURNAME = 'Jones');

 

4) Both Q1 and Q2 are single-row queries, thus in the inner query only one

 

record can be returned. Q2 queries the surname 'Jones' and as there are

 

more than one 'Jones' Q2 will return an error message. 5) Both Q1 and Q2 are mutli-row queries, thus in the inner query only one

 

record can be returned. Q2 queries the surname 'Jones' and as there are

 

more than one 'Jones' Q2 will return an error message. 6. Which of the folllowing statements best explain the purpose of the SQL statement using the

 

SET operator UNION?

 

SELECT PRODID FROM PRODUCTS

 

UNION

 

SELECT PRODID FROM SALES;

 

The UNION operator returns ___

 

1) the results from the first query but not by the second query. 2) all the rows that appear in either of the two SELECT statements. The

 

duplicate rows are eliminated. 3) only distinct rows that appear in either of the two SELECT statements. 4) all the distinct rows that appear in either of the two SELECT statements. 8 ICT3722/202/2/2016

 

7. Which of the following INSERT statements will correctly insert the following values into the

 

EMPLOYEES table:

 

COLUMN NAME VALUE EMPID 509 NAME Ben SURNAME Nkosi SALARY R 45000 GENDER M MARRIED N STARTDATE Today MENTORID 505 GROUPID 402 1) INSERT INTO EMPLOYEES

 

VALUES

 

(EMPID=509,NAME='Ben',SURNAME='Nkosi',

 

SALARY=45000,GENDER='M',MARRIED='N',STARTDATE=NOW,MENTO

 

RID=505,GROUPID=402); 2) INSERT INTO EMPLOYEES

 

(EMPID, NAME, SURNAME, SALARY,

 

GENDER, MARRIED, STARTDATE, MENTORID, GROUPID)

 

VALUES

 

(509,'Ben','Nkosi',45000,'M','N',SYSDATE,505,402); 3) INSERT INTO EMPLOYEES

 

VALUES

 

(EMPID:509,NAME:'Ben',SURNAME:'Nkosi',

 

SALARY:45000,GENDER:'M',MARRIED:'N',STARTDATE:SYSDATE,M

 

ENTORID:505,GROUPID:402); 4) INSERT INTO EMPLOYEES

 

(EMPID, NAME, SURNAME, SALARY,

 

GENDER, MARRIED, STARTDATE, MENTORID, GROUPID)

 

VALUES

 

(509,'Ben','Nkosi','R45000','M','N',NOW,505,402); 9 ICT3722/202/2/2016 8. The user enters the following SQL statement:

 

INSERT INTO SALES VALUES (103,500,SYSDATE,10,3200);

 

An error code is returned.

 

You studied the code and explain that the ___

 

1) error code indicates that the SQL statement should explicitely state the

 

columns, example:

 

INSERT INTO SALES

 

(CUSTID, PRODID, SALEDATE, QTY, UNITPRICE)

 

VALUES (103,500,SYSDATE,10,3200); 2) error code indicates that there there is no such value as SYSDATE it

 

should be NOW, example:

 

INSERT INTO SALES VALUES (103,500, NOW,10,3200); 3) error is an integrity constraint error message as there is no PRODID 500 in

 

the PRODUCT table. 4) error is a NOT NULL error message. The SYSDATE field should have an

 

explicit value, example:

 

INSERT INTO SALES VALUES

 

(103,500, '10-JUL-2015',10,3200); 5) error is a unique constraint error message as the CUSTID 103 already

 

exists in the table. 10 ICT3722/202/2/2016

 

9. Indicate which of the following SQL will correctly create the table DEPARTMENTS with the

 

following fields:

 

COLUMN NAME TYPE LENGTH CONSTRAINTS DEPTID VARCHAR2 5 PRIMARY KEY DESCRIPTION VARCHAR2 30 MNGID NUMBER 4 FOREIGN KEY

 

REFERENCING

 

EMPLOYEE

 

(EMPID) 1) CREATE TABLE DEPARTMENTS

 

(DEPTID VARCHAR2(5) PRIMARY KEY,

 

DESCRIPTION VARCHAR2(30),

 

MNGID NUMBER(4)

 

CONSTRAINT MNGID_FK

 

REFERENCES EMPLOYEES (EMPID)); 2) CREATE TABLE DEPARTMENTS

 

(DEPTID VARCHAR2(5) PRIMARY KEY,

 

DESCRIPTION VARCHAR2(30),

 

MNGID NUMBER(4) FOREIGN KEY

 

CONSTRAINT REFERENCES EMPLOYEES (EMPID)); 3) CREATE TABLE DEPARTMENTS

 

(DEPTID VARCHAR2(5) PRIMARY KEY,

 

DESCRIPTION VARCHAR2(30),

 

MNGID NUMBER(4)

 

CONSTRAINT REFERENCES EMPLOYEES (EMPID)); 4) CREATE TABLE DEPARTMENTS

 

(DEPTID VARCHAR2(5) PRIMARY KEY,

 

DESCRIPTION VARCHAR2(30),

 

MNGID NUMBER(4)); 10. The ___ constraint specifies that a column cannot contain a NULL value. 11 ICT3722/202/2/2016 1) PRIMARY KEY 2) UNIQUE 3) NOT NULL 4) FOREIGN KEY 5) CHECK NOT NULL 11. Which of the following SQL will correctly create a VIEW called EMPVIEW401. This view

 

contains the details of all the EMPLOYEES in the GROUP 401.

 

1) CREATE OR REPLACE VIEW EMPVIEW401 AS

 

SELECT E.EMPID, E.NAME||' '||E.SURNAME AS NAME,

 

G.GROUPNAME, G.STARTDATE

 

FROM EMPLOYEES E NATURAL JOIN GROUPS G

 

WHERE E.GROUPID = 401; 2) CREATE OR REPLACE VIEW EMPVIEW401 AS

 

SELECT E.EMPID, E.NAME||' '||E.SURNAME AS NAME

 

G.GROUPNAME, G.STARTDATE

 

FROM EMPLOYEES E, GROUPS G

 

WHERE E.GROUPID = G.GROUPID

 

AND E.GROUPID = 401 3) CREATE OR REPLACE VIEW EMPVIEW401 AS

 

SELECT E.EMPID, E.NAME||' '||E.SURNAME AS NAME,

 

G.GROUPNAME, G.STARTDATE

 

FROM EMPLOYEES E

 

JOIN GROUPS G USING (GROUPID)

 

AND E.GROUPID = 401 4) CREATE OR REPLACE VIEW EMPVIEW401 AS

 

SELECT E.EMPID, E.NAME||' '||E.SURNAME AS NAME,

 

G.GROUPNAME, G.STARTDATE

 

FROM EMPLOYEES E WHERE E.GROUPID = 401

 

JOIN GROUPS G USING (GROUPID) 12. Mary Jones one of the employees has completed her Engineering studies a UNISA and

 

applies for an Engineering position in the company. The user enters the following SQL to

 

update her records, but receives an error message. 12 ICT3722/202/2/2016

 

UPDATE EMPVIEW401

 

SET GROUPNAME = ?ENGINEERING?

 

WHERE NAME = ?Mary Jones?;

 

You explain to the user that the error message occurred because:

 

1) READ ONLY and the fields can never be updated.

 

2) The field name Name does not exist. The user must use the EMPID in the SQL, e.g.

 

UPDATE EMPVIEW401

 

SET GROUPNAME = ?ENGINEERING?

 

WHERE EMPID = 501;

 

3) You can never use a VIEW to update in a main table

 

4) You cannot modify a column in a VIEW where the column is a combination of fields, such

 

as Name and Surname of if the column maps to a non key-preserved table.A View is a

 

13. Which of the following types of columns will automatically right-align the data when it is

 

displayed?

 

1) character 2) date 3) numeric 4) alphanumeric 14. Which of the following is NOT a valid DATATYPE in Oracle 10g or Oracle 11g.

 

1) SYSDATE 2) VARCHAR2 3) NCHAR 4) NUMBER 5) CHAR 13 ICT3722/202/2/2016

 

15. Which of the following SQL will return information on the constraints enforced on the SALES

 

table?

 

1) DESC USER_CONSTRAINTS; 2) SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'SALES'; 3) SELECT ALL FROM USER_CONSTRAINTS WHERE TABLE = "SALES"; 4) SELECT ALL FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'SALES'; ©UNISA 2016 14

 


Solution details:

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
SiteLock

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Sep 13, 2020

EXPERT

Tutor

ANSWER RATING

GET INSTANT HELP/h4>

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

  • As a reference for in-depth understanding of the subject.
  • As a source of ideas / reasoning for your own research (if properly referenced)
  • For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

NEW ASSIGNMENT HELP?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN A DEADLINE.

Order Now