Question 1

Tom has designed a payroll software for XYZ technology.The sofware will store the salary details into the database and later he can retreive the same

Accepted Answer

Application Programmer

Question 2

In SQL is a case sensitive language and the data stored inside the table are case in-sensitive. Say true or false?

Accepted Answer

TRUE

Question 3

Which of the following is not a valid relational database?

Accepted Answer

XML Database

Question 4

Database is a shared collection of logically unrelated data, designed to meet the needs of an organization. State True or False.

Accepted Answer

FALSE

Question 5

Which of the following represents the degree of the relation?

Accepted Answer

Number of attributes (columns)

Question 6

A table consists of ______ primary keys.

Accepted Answer

1

Question 7

We need to ensure that the amount withdrawn should be less then the credit card limit amount, to ensure this integrity what type constraint will be us

Accepted Answer

Table level CHECK constraint

Question 8

Which of the following options is not correct?

Accepted Answer

alter table emp drop column_name;

Question 9

An emp table contains fields employ name, desig and salary. How do you drop column salary?

Accepted Answer

alter table emp drop column salary;

Question 10

Which of the following is not modification of the database

Accepted Answer

XML Database

Question 11

In a relational database a referential integrity constraint can be done using

Accepted Answer

Foreign Key

Question 12

___________ removes data from the table, but structure remains the same.

Accepted Answer

TRUNCATE

Question 13

A relational database consists of a collection of

Accepted Answer

Tables (relations)

Question 14

Column header is referred as

Accepted Answer

Attribute

Question 15

Consider the below table structure: Column Name DataType Constraint Empname Varchar(20) Not Null EmpId int(10) PK Phoneno bigint(10) Not Null insert i

Accepted Answer

Value for phoneno is missing., empid value should be given without single quotes.

Question 16

Examine the structure of the STUDENT table: Column Name DataType Constraint Stud_id int(3) PK Name Varchar(20) Not Null Address Varchar(30) DOB Date W

Accepted Answer

INSERT INTO student VALUES(101, 'Smith', '15-JAN-1990', 'New York');

Question 17

State True or False. COMMIT ends the current transaction by making all pending data changes permanent.

Accepted Answer

TRUE

Question 18

Merge is not supported by MySQL. The other possible way of doing the work of merge statement, is by using ON DUPLICATE KEY UPDATE in the insert statem

Accepted Answer

True

Question 19

Which of the below is a reference option for deleting rows from a table?

Accepted Answer

ON DELETE CASCADE

Question 20

To remove a relation from SQL database, we use ___________ command.

Accepted Answer

DROP

Question 21

What is the Default format for Date datatype?

Accepted Answer

YYYY-MM-DD (ISO standard) / DD-MON-RR (Oracle)

Question 22

Numeric datatype is used to store_______

Accepted Answer

Whole numbers and decimals

Question 23

Which statement about SQL is true?

Accepted Answer

Null values are displayed last in ascending sequences.

Question 24

Select the suitable option for retrieving all the employees who have a manager?

Accepted Answer

SELECT empname, manager_id FROM employee WHERE manager_id IS NOT NULL;

Question 25

Select the suitable option for retrieving all the employees whose last name is "kumar"?

Accepted Answer

SELECT * FROM employee WHERE last_name = 'kumar';

Question 26

How to retrieve department_id column without any duplication from employee relation?

Accepted Answer

SELECT DISTINCT department_id FROM employee;

Question 27

Which statement is true when a DROP TABLE command is executed on a table?

Accepted Answer

Any pending transactions are rolled back; the structure and data cannot be recovered.

Question 28

Which statements are true regarding constraints? Select one or more: A constraint is enforced only for the INSERT operation on a table. A foreign key

Accepted Answer

A foreign key cannot contain NULL values. A columns with the UNIQUE constraint can contain NULL values. A constraint can be disabled even if the constraint column contains dat

Question 29

You need to remove all the data from the employee table while leaving the table definition intact. You want to be able to undo this operation. How wou

Accepted Answer

DELETE FROM employee;

Question 30

The SQL statements executed in a user session as follows: create table product(pid int(10),pname varchar(10)); Insert into product values(1,'pendrive'

Accepted Answer

The table contains: p1-pen, p2-pencil (rows before the savepoint are kept).

Question 31

All columns in the SELECT list that are not in group functions must be in the GROUP-BY clause. State True or False.

Accepted Answer

TRUE

Question 32

Group functions can be used in the where clause. State True or False.

Accepted Answer

FALSE

Question 33

Single row functions can be nested to any level. State true or False.

Accepted Answer

TRUE

Question 34

What will be the output for the below query: select ceil(5.3) from dual;

Accepted Answer

6

Question 35

We need to create a report to display the order id, ship date and order total of your ORDER table. If the order has not been shipped, your report must

Accepted Answer

SELECT ordid, IFNULL(shipdate, 'Not Shipped') SHIPDATE, IFNULL(total,'Not Available') TOTAL FROM order;

Question 36

To generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should hav

Accepted Answer

SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM customers;

Question 37

To display the names of employees that are not assigned to a department. Evaluate this SQL statement: SELECT last_name, first_name FROM employee WHERE

Accepted Answer

Change the = operator to IS NULL in the WHERE clause.

Question 38

Which statement is true regarding the default behavior of the ORDER BY clause?

Accepted Answer

Ascending order

Question 39

ABC company wants to give each employee a $100 salary increment. You need to evaluate the results from the EMP table prior to the actual modification.

Accepted Answer

You need to give the arithmetic expression in the SELECT clause, not the UPDATE clause.

Question 40

Select the suitable option for fetching the output of the following query. select substr("Oracle World",1,6) from dual;

Accepted Answer

Oracle

Question 41

Which operator is NOT appropriate in the join condition of a non-equi join SELECT statement?

Accepted Answer

=

Question 42

In which cases would you use an outer join?

Accepted Answer

Only when the tables have a primary key/foreign key relationship. The tables being joined have only matched dat

Question 43

To display the names of employees who earns more than the average salary of all employees. SELECT last_name, first_name FROMemployee WHEREsalary > AVG

Accepted Answer

Use a subquery in the WHERE clause to compare the average salary valu

Question 44

To create a report displaying employee last names, department names, and locations. Which query should you use to create an equi- join?

Accepted Answer

department_id =

Question 45

Which statement would display the highest credit limit available in each income level in each city in the Customers table?

Accepted Answer

SELECT city, income_level, MAX(cust_credit_limit) FROM customers GROUP BY city, income_level;

Question 46

Which SQL statement produces an error?

Accepted Answer

SELECT job_id, SUM(salary) FROM emp_dept_vu WHERE department_id IN (10,20) GROUP BY job_id HAVING SUM(salary) > ;

Question 47

The COMMISSION column shows the monthly commission earned by the employee. Emp_Id Dept_Id Commission 1 10 500 2 20 1000 3 10 4 10 600 5 30 800 6 30 20

Accepted Answer

Listing the employees who earn the same amount of commission as employee 3, Finding the number of employees who earn a commission higher than the average

Question 48

What statement would display the age of Customers with the alias name as AGE?

Accepted Answer

SELECT (SYSDATE - dob) / 365 AS AGE FROM customers;

Question 49

Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?

Accepted Answer

DROP VIEW emp_dept_vu;

Question 50

CREATE INDEX emp_dept_id_idx ON employee(dept_id); Which of the following statements are true with respect to the above index?

Accepted Answer

May reduce the amount of disk I/O for SELECT statements.

Question 51

An owner can give specific privileges on the owner's objects to others. State True or False.

Accepted Answer

TRUE

Question 52

The owner has all the privileges on the object. State true or False.

Accepted Answer

TRUE

Question 53

Which SQL statement grants a privilege to all the database users?

Accepted Answer

GRANT SELECT ON employees TO PUBLIC;

Question 54

Equijoin is called as ______.

Accepted Answer

Inner join / Simple join

Question 55

_____ is used to retrieve records that do not meet the join condition

Accepted Answer

Outer join

Question 56

Joining a table to itself is called as ______.

Accepted Answer

Self join

Question 57

The _______ join is based on all columns in the two tables that have the same data type.

Accepted Answer

Natural

Question 58

The _______ join produces the cross product of two tables.

Accepted Answer

Cross join

Question 59

Tom has designed a payroll software for XYZ technology.The sofware will store the salary details into the database and later he can retreive the same

Accepted Answer

Application Programmer

Question 60

Which of the following is not a valid relational database?

Accepted Answer

XML Database

Question 61

SQL is a case sensitive language and the data stored inside the table are case in-sensitive. Say true or false?

Accepted Answer

TRUE

Question 62

Which of the following represents the degree of the relation?

Accepted Answer

Number of attributes (columns)

Question 63

Database is a shared collection of logically unrelated data, designed to meet the needs of an organization. State True or False.

Accepted Answer

FALSE

Question 64

We need to ensure that the amount withdrawn should be less then the credit card limit amount, to ensure this integrity what type constraint will be us

Accepted Answer

Table level CHECK constraint

Database Management

Key Topics to Study

Success Primer Exam