RDBMS MCQ with Answers


Hello friends in this article we are going to discuss about RDBMS Multiple Choice Questions with answer | RDBMS Objective Type Questions with Answers | RDBMS Quiz with answer | RDBMS Interview Questions with answers

1.List all the employees and departemnts though the records are not matching. Which of the below joins will
work?

Full outer Join

2.The query: SELECT isbn, COUNT(book_id) FROM Books GROUP BY book_id; Gives error because,

GROUP BY column is incorrect

3.The EMP table contains these columnsEMPLOYEE IDEMPNAMESALARYHIRE DATENUMBER(4)VARCHAR2 (25)NUMBER(9.2)DATESELECT empname hire date HIREDATE, salaryORDER BY hire date:How will the results be sorted?FROM EMPYou query the database with this SQL statement

ascending by date

4.What is true about updates through a view?(Choose one)

You cannot update a view with group functions.

5.Which statement produces a report that displays the student ID and GPA in the sorted order requested by the registrar?

SELECT student_id.gpaORDER BY gpa DESC:FROM student grades

6.If the below SQL statement is part of the subquery would result in an error because SELECT (AVG(inv_date)) FROM invoice:

You cannot take Average of a date

7.If the below SQL statement is part of the subquery would result in an error because SELECT AVG(inv_date – SYSDATE) FROM invoice;

You cannot take Average of a dates

8.If the below SQL statement is part of the subquery would result in an error because SELECT MAX((SUM(inv_date)) FROM invoice:

Sum function cannot be applied for dates

9.What will be the output of this query? INSERT INTO Temp_Cust SELECT FROM Customer WHERE type = A ;

Inserts into Temp_ Cust table taking selected rows from Customer table

10.What is the error in the below query INSERT INTO Temp_Cust values SELECT FROM Customer;

values clause must not be present while inserting a row through a sub query

11.Which SELECT statement should you use to extract the year from the system date and display it in the for mat “2020”Choose one)

SELECT TO CHAR (SYSDATE, ‘yyyy’) FROM dual;

13.Suppose you have an employees table with 10,000 rows and run the below command to create an emp_c opy table Create table emp_copy as select from employees What constraints from the employees table will be copied to the emp_copy table?

both not null and primary key

14.Jack a Sr. Developer, wants to flush the data in his table for a fresh dataload to happen. Which of the follo wing would a best option?

truncate

15.You need to modify the STUDENTS table to add a primary key on the STUDENT ID column. The table is currently empty

ALTER TABLE students ADD CONSTRAINT stud id pk PRIMARY KEY student_ID;

16.FIRST NAME VARCHAR2(30)DEPT ID NUMBER(2) Which statement produces the number of different departments that have employees with last name Smith?

SELECT COUNT(DISTINCT dept_id) FROM employees en WHERE last name Smith;

17.What would be output of below two queries if not all books have discounts? SELECT COUNT(book_discount) FROM Books; SELECT COUNT(*) FROM Books;

Both will show different values

18.SELECT book_id,COUNT(author_id)
FROM Book Author
GROUP BY book_ld
HAVING book Id=’prime B0199′

It cannot be written in any other way


19.What would be output of below two queries if not all books have discounts? SELECT COUNT(book_discount) FROM Books; SELECT COUNT(*) FROM Books;

Both will show different values

20.Which of the following queries gives the output as the avgb “Salary and number of employees working in the “Department” 10?

SELECT AVG (Salary )COUNT(*)No. Emp in dept 10 FROM staff WHERE department = 10;

21.Which of the following queries will print the name of the degrees from column “DegreeName”, which are n ot unique, along with their number of occurences in the table “Degrees”?

SELECT DegreeName, COUNT(DegreeName) FROM Degrees GROUP BY DegreeName HAVING (COUNT(DegreeName)>1);

22.How do you fetch all the employees details with their departments details from department table even tho ugh the employee is not tagget to any of the department

full join

23.This query may not give expected output. Because:

SELECT b.book_id, a.author_id FROM Books b, Book_Author ba, Authors a WHERE b.book_id id=ba.book_id;

Joining condition to join Authors table is missing

24.What will be the result of this query?
SELECT b.book_id, b.book_title, p.publ_name FROM Books b, Publisher p WHERE b.pub_id=p.publ_id(+)ANDb.book_type=PR;

All PR book titles and publisher names even though there is no publisher names for some of them

25.SELECT ename, sal, 12*Sal + 100
FROM emp;
The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as “monthly salary plus a monthly bonus of $100 , multiplied by 12?

SELECT ename, sal + 100*12 FROM emp:

26.The table contains 20000 rows.
Which statement is valid?

ALTER TABLE commercials MODIFY (description VARCHAR2(1000));

27.EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID. The JOB ID column is a NOT NULL column.

Evaluate this DELETE statement:

DELETE employee_id, salary, job_id
FROM employees
WHERE dept_id id = 90;

Why does the DELETE statement fail when you execute it?

You cannot delete the EMPLOYEE ID column because it is the primary key of the table

28.An Employee table has 3 columns namely Emp_id (NUMBER), Emp_name (VARCHAR2), Emp_addr(VARCHAR2). Will the below statement insert a row to the table? INSERT INTO EmployeeVALUES (100, ‘Prakash’, ‘8th Main, Andheri, Mumbai.):

The statement will give a error message and no rows will be inserted

29.Consider the table Employee(empId,jobBand)
What is the correct DML statement to update the job band of all employees to NULL?

UPDATE employee SET jobBand = NULL;

30.Consider the table Employee(empId,jobBand)
What is the correct SQL statement where all unique job band from employee table?

select DISTINCT jobBand from employee;

31.Consider table Account(accountId,balance) Identify the SQL queries which gives the same result

SELECT * FROM Account WHERE balance between 1000 and 5000;
SELECT * FROM Account WHERE balance>=1000 AND balance<=5000;

32.What is the command to concatenate fname and lname with a space in between and display
the output under the heading of “Full Name”?
Employee(eid,fname,lname,salary)

Select fname || “ “ || lname as ‘Full Name’ from employee;

33.Consider the following table structure and write a query to display all the employees who have
and don’t have department assigned
Emp(fname,lname,dno)
Dept(deptid,dname)

Select fname,deptid,dname from emp e left outer join dept d on (e.dno=d.deptid);

34.Match the following
DDL, DML, DCL, TCL
GRANT, COMMIT, SELECT, CREATE

Ans-
DDL – CREATE
DML – SELECT
DCL – GRANT
TCL – COMMIT

35.you must explicitly commit GRANT on tables. State true or false

Ans-
True

36.Choose the options which are true with respect to views in SQL?

Ans-
View is a virtue table that does not physically exist
Views are used for maintaining logical data independence

Next »


1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *