TCS Tech Lounge Information Management Basics

According to normalization rules a table is said to be in second NormalForm if and if it is First NormalForm and every non-prime atribute of the table is dependent on the

Ans: Whole of primary key.

____ String function of oracle will trim off unwanted characters from the both sides of string

Ans: trim

considering CNN is a table which stores the population of countries, which of these select statements shows the number of countries with population smaller than 150000

Ans: SELECT COUNT(name) From cnn WHERE population < 150000

select right option for adding primary key constraint to emloyee table

Ans: b. ALTER TABLE customer ADD CONSTRAINT pk_customer PRIMARY KEY(empid)

Or 

ALTER TABLE students  

ADD PRIMARY KEY (S_Id)  

The HAVING clause does which of the following ?

Ans: Acts like a WHERE clause but is used for groups rather than rows.

The effect of the ROLLBACK command in a transaction is to undo all changes to the database resulting from the execution transaction

ANS: TRUE

select one or more right options to update tom's city to pune and department to sales

Ans: ( c )UPDATE EMPLOYEE SET=’Pune’, DEPARTMENT=’Sales’ WHERE name=’Tom’

Create table emp1(id number, name varchar2(10),

dept varchar2(10));

Insert into emp1 values(1,’anu’,1);

Insert into emp1 values(2,’vinu’,1);

Insert into emp1 values(3,’RASa’,1);

Insert into emp1 values(4,’AAdil’,1);

Insert into emp1 values(5,’aadersh’,1);

Insert into emp1 values(6,’Aanand’,1);

What is the output of the following select query

Select * from emp1 where id between 1 and 6;

ANS: ( c ) 1 anu 1 2 vinu 1 3 RASa 1 4 AAdil 1 5 aadersh 1 6 Aanand

The effect of the ROLLBACK command in a transaction is the following:

ANS: Undo all changes to the database resulting from the execution of the transaction 

which is the operator to append two strings in sql

Ans: ||

What is the output of the following select statement? Select ceil(5.1) from dual;

Ans: 6

Evaluate the following SQL statement: SQL>SELECT TO_CHAR(1230,'00,999.99') FROM DUAL; What would be the outcome?

Ans:  01,230.00

The last value used in the sequence is 100, and if you want to reset the sequence and serve next value as 255. Select the appropriate for this

Ans: (c) ALTER SEQUENCE customers_seq INCREAMENT BY 125;

SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department = department.DeparrmentID can aslo be written as

ANS: SELECT * FROM employee JOIN department ON employee.DepartmentID = department DepartmentId

create table emp1 as select * from emp where 1=2

Ans: emp1 table is created

Select the statements that are true HTML was designed for the purpose of transporting and storing data, XML was designed data

Ans: False

Primary key constraint is a combination of unique and not null constraints

Ans: true

Let us assume there exists an employee table with columns EmpID, Ename, Sal and with 3 rows EmpID Ename Sal

123 Smith 6000

345 Alex 8000

678 Sunny 9000

To retrieve all the employee names whose Ename’s second letter start with ‘t’ from ending.

ANS: Select Ename from employee where Ename like ‘%t_’

Syntax for Left Outer join is:

ANS: SELECT * FROM TableA LEFT JOIN TableB ON TableA commonid = TableB commonid

Select the statement that is true

ANS: HTMl is not case sensitive where as XML is case sensetive

NULL will never match any value, unless the join condition uses IS NULL

ANS: True

Select right option for deleting all rows employee table permanently.

Ans:  Truncate table employee;

A table can join to _ _ _ _ _.

ANS: all the options( itself , another table, a view)

what is true about aliases?

Ans: Aliases are very handy when table names are long.

Select one or more right options to add column city to employee table

ALTER TABLE employee ADD CITY VARCHAR2(50)

Select one or more correct options to display city and total no.of employees in that city.

Ans: SELECT city, count(*) FROM employee gropup By city

In the _ _ _ _ _ normal form, a composite attribute is converted to individual attributes.

ANS: First

ALTER TABLE STUDENT_DETAILS ADD CONSTRAINT FOREIGN KEY(ID) REFERENCES STUDENT(ID)

ANS: Query ends up with SQL Error

Primary key constraint is a combination of _ _ and _ _ constraints

ANS: UNIQUE & NOT NULL

In SQL commit , rollback, savepoint are called as ………………….of SQL

Ans: TCL

……………….String function of oracle will trim off unwanted characters from the both sides of string.

Ans: trim

Select one or more correct usage of subquery

Ans: Select * from department where departmented not in (select departmented from employee)

Create table emp1(id number, name varchar2(10),dept varchar2(10));

Insert into emp1 values(1,’anu’,1);

Insert into emp1 values(2,’vinu’,1);

Insert into emp1 values(3,’RASa’,1);

Insert into emp1 values(4,’AAdil’,1);

Insert into emp1 values(5,’aadersh’,1);

Insert into emp1 values(6,’Aanand’,1);

What is the output of the following select query

Select name from where name like %aa%

Ans:  aadersh

Which of the following is incorrect ?

Ans:  ( ALL of these ) a) SELECT SYSDATE-SYSDATE FROM DUAL

            b) SELECT SYSDATE +2 F ROM DUAL

            c) SELECT SYSDATE -2 FROM DUAL

What is the output of the following select statement ?

Select ceil(5.1) from dual ;

Ans: 6

Which of the syntax is correct for insert statement?

Ans: insert into <table_name> values (<list of values>)

The last value used in the sequence is 100, and if you want to reset the sequence and serve next value as 225. Select the appropriate for this

Ans: ALTER SEQUENCE customers_seq INCREMENT BY 125;

. When does FileOutputStream(File file)throw FileNotFoundException
Ans: If file does not exist but cannot be treated

2. With SQL, how can you return the number of records in the "Customer" table?
Ans: SELECT COUNT(*) FROM Customer

3.  Which command is used to delete the entire table data permanently?
Ans: truncate table

6. Select one or more right options to add column city to employee table
Ans: ALTER TABLE employee ADD CITY VARCHAR2(50)

7. Select TCL commands
Ans: savepoint

10. Select one or more correct options to get number of employees where city information is present
Ans: SELECT count(*) FROM employee WHERE city IS NOT null

11. Which clause of select statement is used for ordering the columns data of a table in either(ascending or descending order)
Ans: Order by

12. In SQL Grant, revoke are
Ans: DCL

13. Which of the following sort the rows in SQL
Ans: Order by

14. CREATE SEQUENCE customer_seq START WITH 90; Created a sequence named customers_seq? What will be the output for the below statement SELECT customers_seq.currval FROM dual;
Ans: 91.0

16. Syntax for Left Outer join is:
Ans: SELECT * FORM TableA LEFT JOIN TableB ON TableA.commonid=TableB.commonid

17. Normalization takes care of______
Ans: All the options (Redundancy, Atomicity, Isolation)

18. Create table emp(id number, name varchar2(10), dept varchar2(10));
How to retrieve unique dept for the above table
Ans: select distinct dept from emp;

19. Which one of the following is not a join?
Ans: LEFT INNER

20. Select the statement that is true
Ans: HTML is not case-sensitive where as XML is case-sensitive

21. Which of the following is incorrect?
Ans: All of these are correct (a) SELECT SYSDATE - SYSDATE FROM DUAL; b) SELECT SYSDATE + 2 FROM DUAL; c) SELECT SYSDATE - 2 FROM DUAL;)

22. Considering CNN is a table which stores the population of countries. Which of these statements shows the number of countries with population smaller than 150000
Ans: SELECT COUNT(name) FROM cnn WHERE population<150000

23. What is true about having clause?
Ans: It is used for putting a condition on the grouped data

25. The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true.
Ans: True

27. Which clause of select statement is used for ordering the columns data of a table in either (ascending or descending order)
Ans: Order by

28. Which operator is used for pattern matching?
Ans: LIKE operator

29. Select right option for deleting all rows from employee table permanently
Ans: TRUNCATE table employee

30. In SQL, commit, rollback, savepoint are called as_____of SQL
Ans: TCL

31. Self join is not possible as it becomes cyclic
Ans: False

32. Third normal form addresses______dependency
Ans: Transitive

33. Select the right option for adding primary key constraint to employee table
Ans: ALTER TABLE customer ADD CONSTRAINT pk_customer PRIMARY KEY(emplo)*

34. Primary Key constraint is a combination of____and____constraints
Ans: Unique and Not Null

Select the statements that are true HTML was designed for the purpose of transporting and storing data, XML was designed to display data.

ANS: false

INSERT INTO CUSTOMER(customerid, cutomername) VALUES(1,’alpha’);

INSERT INTO CUSTOMER(customerid, cutomername) VALUES(2,’beta’);SAVEPOINT s1;

INSERT INTO CUSTOMER(customerid, cutomername) VALUES(3,’gamma’);SAVEPOINT s2;

INSERT INTO CUSTOMER(customerid, cutomername) VALUES(4,’tera’);SAVEPOINT s3;

INSERT INTO CUSTOMER(customerid, cutomername) VALUES(5,’omega’);

ROLLBACK TO s3;

SELECT cutomername FROM CUSTOMER;

ANS: alpha, beta, gamma, tera

Select the advantages of DBMS system

ANS: GREATER data integrity and consistency

Select the right option for creating Employee table.

ANS: ( c ) CREATE TABLE EMPLOYEE(empid NUMBER(6), empname VARCHAR2(10))

Select one or more right options to display names of employees who are not working in sales department.

ANS: SELECT name FROM EMPLOYEE e,DEPARTMENT d WHERE d.DEPARTMENT <> ‘Sales’

What is the output of the following select statement ? select round(123.2345,2) from dual

ANS: 123.23

Which command is used to match any string of at least 3 char?

ANS ( b ) ___%

_  __ __ _ _ join will return rows if there is a match in both tables

ANS: INNER JOIN

Which one of the following is not a JOIN?

ANS: LEFT INNER

List dept no., Dept name for all the departments in which there are no employees in the department.

ANS: a. Select * from dept where deptno not in (select deptno fropm emp);

To display alternate records from a particular table _ _ _ _ query is used

ANS: SELECT * FROM tab_name WHERE Mod(primary_key_col,2)=0