DBMS -Database Management Systems: Questions With
Solutions (Expert Verified)
1. Evaluate these two SQL statements:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary
DESC;
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;
What is true about them? Right Ans - The two statements produce identical
results.
Which statement correctly describes SQL and /SQL*Plus? Right Ans - Both
SQL and /SQL*plus allow manipulation of values in the database.
Which view should a user query to display the columns associated with the
constraints on a table owned by the user? Right Ans -
USER_CONS_COLUMNS
Evaluate the following SQL statements:
DELETE FROM sales;
There are no other uncommitted transactions on the SALES table.
Which statement is true about the DELETE statement? Right Ans - It
removes all the rows in the table and deleted rows can be rolled back
Examine the statement:
Create synonym emp for hr.employees;
What happens when you issue the statement? Right Ans - You create an
alternative name for the employees table in the HR schema in your own
schema
Which statement is true regarding the UNION operator? Right Ans - NULL
values are not ignored during duplicate checking
Which CREATE TABLE statement is valid? Right Ans - CREATE TABLE
ord_details
(ord_no NUMBER(2),
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));
, Which object privileges can be granted on a view? Right Ans - DELETE,
INSERT, SELECT, UPDATE
You need to create a table for a banking application. One of the columns in the
table has the following requirements:
You want a column in the table to store the duration of the credit period The
data in the column should be stored in a format such that it can be easily
added and subtracted with DATE data type without using conversion The
maximum period of the credit provision in the application is 30 days the
interest has to be calculated for the number of days an individual has taken a
credit for
Which data type would you use for such a column in the table? Right Ans -
INTERVAL DAY TO SECOND
The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4,3)
Which statement finds students who have a grade point average (GPA) greater
than 3.0 for the calendar year 2001? Right Ans - SELECT student_id, gpa
FROM student_grades
WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa
> 3.0;
Which statement is true regarding the USING and ON clauses in table joins?
Right Ans - The WHERE clause can be used to apply additional conditions in
SELECT statement containing the ON or the USING clause
The user Alice wants to grant all users query privileges on her *DEPT table*.
Which SQL statement accomplishes this? Right Ans - GRANT select ON dept
TO PUBLIC;
You need to display the date 11-Oct-2007 from table DUAL in words as
*`Eleventh of October, Two Thousand Seven'*. Which SQL statement would
give the required
result? Right Ans - SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp
"of" Month, Year') FROM DUAL;
Solutions (Expert Verified)
1. Evaluate these two SQL statements:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary
DESC;
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;
What is true about them? Right Ans - The two statements produce identical
results.
Which statement correctly describes SQL and /SQL*Plus? Right Ans - Both
SQL and /SQL*plus allow manipulation of values in the database.
Which view should a user query to display the columns associated with the
constraints on a table owned by the user? Right Ans -
USER_CONS_COLUMNS
Evaluate the following SQL statements:
DELETE FROM sales;
There are no other uncommitted transactions on the SALES table.
Which statement is true about the DELETE statement? Right Ans - It
removes all the rows in the table and deleted rows can be rolled back
Examine the statement:
Create synonym emp for hr.employees;
What happens when you issue the statement? Right Ans - You create an
alternative name for the employees table in the HR schema in your own
schema
Which statement is true regarding the UNION operator? Right Ans - NULL
values are not ignored during duplicate checking
Which CREATE TABLE statement is valid? Right Ans - CREATE TABLE
ord_details
(ord_no NUMBER(2),
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));
, Which object privileges can be granted on a view? Right Ans - DELETE,
INSERT, SELECT, UPDATE
You need to create a table for a banking application. One of the columns in the
table has the following requirements:
You want a column in the table to store the duration of the credit period The
data in the column should be stored in a format such that it can be easily
added and subtracted with DATE data type without using conversion The
maximum period of the credit provision in the application is 30 days the
interest has to be calculated for the number of days an individual has taken a
credit for
Which data type would you use for such a column in the table? Right Ans -
INTERVAL DAY TO SECOND
The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4,3)
Which statement finds students who have a grade point average (GPA) greater
than 3.0 for the calendar year 2001? Right Ans - SELECT student_id, gpa
FROM student_grades
WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa
> 3.0;
Which statement is true regarding the USING and ON clauses in table joins?
Right Ans - The WHERE clause can be used to apply additional conditions in
SELECT statement containing the ON or the USING clause
The user Alice wants to grant all users query privileges on her *DEPT table*.
Which SQL statement accomplishes this? Right Ans - GRANT select ON dept
TO PUBLIC;
You need to display the date 11-Oct-2007 from table DUAL in words as
*`Eleventh of October, Two Thousand Seven'*. Which SQL statement would
give the required
result? Right Ans - SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp
"of" Month, Year') FROM DUAL;
