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

(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