Advanced SQL Interview Questions & Answers For Freshers 2018 | Mindmajix

Q. What is SQL? SQL (Structured Query Language), It is also pronounced as “SEQUEL” and it an Non-procedural Language which is used to operate all relational database. And it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion and deletion of data from a database.

Features of SQL:

1. Portability
2. Client server architecture,
3. Dynamic data definition,
4. Multiple views of data,
5. Complete database language,
6. Interactive,
7. High level,
8. Structure and SQL standards.

Q. What are the different types of SQL’s statements?

1. DQL - Data Query Language ( or) Data Retrival Language

SELECT Statement

2. DML – Data Manipulation Language DML is used for manipulation of the data itself.

INSERT Statement UPDATE Statement DELETE Statement

3. DDL – Data Definition Language DDL is used to define the structure that holds the data.

CREATE Statement ALTER Statement DROP Statement RENAME Statement TRUNCATE Statement

4. DCL – Data Control Language DCL is used to control the visibility of data.

GRANT Statement REVOKE Statement

5. TCL - Transaction Control Language

COMMIT Statement ROLLBACK Statement SAVEPOINT Statement

Q. What are various DDL commands in SQL? Give brief description of their purposes? DDL Commands are used to define structure of the table

1. CREATE It is used to create database objects like tables, views, synonyms, indexes. Creating Table: Syntax- Create table table_name(columname1 datatype(size), columname2 datatype(size),....);

2. ALTER It is used to change existing table structure. Alter:: a) add b) modify c)drop a) Add: It is used to add columns into existing table Syntax: Alter table table_name add(columnname1 datatype(size), columname2 datatype(size),....);

b) Modify: It is used to change column Datatype or datatype size only. Syntax: Alter table table_name modify(columnname1 datatype(size), columnname2 datatype(size),....);

c) Drop: It is used to drop columns from the table. Method1: If we want to drop single column at a time without using parentheses then we are using following syntax. Syntax: alter table table_namedrop column col_name1; -- drop ONE column

Method2: If we want to drop single or multiple columns at a time with using paranthesis then we are using following syntax. Syntax: alter table table_name drop(column_name_list);

Note: In all databases we can’t drop all columns in the table.

3. DROP It is used to remove database objects from database. Syntax: Drop object object_name; (or) Drop table table_name;
   (or) Drop view view_name;

4. RENAME It is used to renaming a table. Syntax: Rename old table_name to new table_name;

Renaming a column: Syntax: Alter table table_name rename column old column_name to new column_name;

5. TRUNCATE Oracle 7.0 introduced truncate table command it is used to delete all rows permanently from the table. Syntax: truncate table table_name;

Q. What are various DML commands in SQL? Give brief description of their purposes. DML Commands are used to manipulate data within a table. There are:: INSERT, UPDATE, DELETE

1. INSERT − It is used to insert data into in the table

Method1: Syntax: Insert into table_name values(values1, value2, value3,……);

Method2:- Using Substitutional operator (&) Synatx: Insert into table_name values(& columnname1, columnname2,.....);

Method3:- Skipping columns Syntax: Insert into table_name(col1, col2,...) values(val1, val2, val3,...);

2. UPDATE - It is used to change data in a table. Syntax: Update table_name set columnname=new value where columnname=old value;

Note: In all databases we can also use update statement for inserting data into particular shell.

3. DELETE - It is used to delete rows or particular rows from a table. Syntax: Delete from table_name; (or) Delete from tablename where condition;

** Q. About The SQL Buffer?**

All Commands of SQL are Typed at the SQL prompt. Only One SQL Statements is Managed in The SQL Buffer. The Current SQL Statement Replaces the Previous SQL Statement in the Buffer. The SQL Statement Can be Divided Into Different Lines Within The SQL Buffer. Only One Line i.e., The Current Line Can be Active at a Time in the SQL Buffer. At SQL Prompt, Editing is Possible Only in The Current SQL Buffer Line. Every Statement of SQL should be terminated Using Semi Colon ”;” One SQL Statement can Contain Only One Semo Colon. To Run the Previous OR Current SQL Statement in the Buffer Type “/” at SQL Prompt. To Open The SQL Editor Type “ED” at SQL Prompt.

Q. What are Important SQL Functions?

**LOWER Function:(Column/Expression): **

1. It Converts Alpha Character Values to Lower Case.
2. The Return Value Has The Same Data Type as Argument CHAR Type (CHAR or VARCHAR2)

**UPPER Function: **

1. It Converts the Alpha Character Values to Upper Case.
2. The Return Value Has The Same Data Type as Argument CHAR.

INITCAP Function:

1. It Converts Alpha Character Values into Upper Case For The First Letter of Each Word, keeping all Other Letter in Lower Case.
2. Words are Delimited by White Space or Characters That are Not Alphanumeric

LPAD Function:

1. Pads The Character Value Right Justified to a Total Width of ‘n’ Character Positions.
2. The Default Padding Character in Space.

RPAD Function:

1. Pads the Character Value Left Justified to a Total Width of ‘n’ Character ositions.
2. The Default Padding Character is Space.

LTRIM Function:

1. It Enables to TRIM Heading Character From a Character String.
2. All The Leftmost Character That Appear in The SET are Removed.

RTRIM Function:

1. It Enables the Trimming of Trailing Character From a Character STRING.
2. All the Right Most Characters That Appear in The Set are Removed.

TRIM Function:

1. It Enables to TRIM Heading or Trailing Character or Both From a Character String.
2. If LEADING is Specified Concentrates On Leading Characters.
3. If TRAILING is Specified Concentrates on Trailing Characters.
4. If BOTH OR None is Specified Concentrates Both on LEADING and TRAILING.
5. Return the VARCHAR2 Type.

Q. What is Tuple? Tuples are the members of a relation. An entity type having attributes can be represented by set of these attributes called tuple.

Q. What is Query & Query Language?

A query is a statement requesting the retrieval of information. The portion of dimly that involves information retrieval is called a query language.

Q. What are the different aggregate functions in SQL? AVG(), MIN(), MAX(), SUM(), COUNT()

Q.What is data independence? A database system keeps data separate from software data structure.

Q. What is data integrity? Data must satisfy the integrity constraints of the system for data Quality.

Q. What is Dead locking? It is the situation where two transactions are waiting for other to release a lock on an item.

Q. What is decryption? Taking encoded text and converting it into text that you are able to read.

Q. What is two phase locking? It is a most common mechanism that is used control currency in two phases for achieving the serializability. The two phases are growing and shrinking.

A transaction acquires locks on data items it will need to complete the transaction. This is called growing process. A transaction may obtain lock but may not release any lock. One lock is released no other lock may be acquired this is called shrinking process. A transaction may release locks but may not obtain any new locks.

Q. What is projection? The Projection of a relation is defined as projection of all its tuples over a set of attributes. It yields vertical subset of the relation. The projection operation is used to view the number of attributes in the resultant relation or to reorder attributes.

Q. What is Encryption? Encryption is the coding or scrambling of data so that humans can not read them directly.

Q. What is cardinality? The number of instances of each entity involved in an instance of a relation of a relationship describe how often an entity can participate in relation ship. (1:1, 1:many, many:many).

Q. What is Transaction Control? Oracle Server Ensures Data Consistency Based Upon Transaction. Transactions Consist of DML Statements That Make Up One Consistent Change To The Data,

Q. What are the Transaction Start & End Cases?

A Transaction Begins When The First Executable SQL Statement is Encountered. The Transaction Terminates When The Following Specifications Occur. a)A COMMIT OR ROLLBACK is Issued b)A DDL Statement Issued. c)A DCL Statement Issued.

The Usr Exists The SQL * Plus Failure of Machine OR System Crashes. A DDL Statement OR A DCL Statement is Automatically Committed And Hence Implicitly Ends A Transaction.

Q. GRANT Command? Syntax:: SQL> GRANT< Privilage Name1>, , ON TO ; GRANT Command is Used When We Want The Database To Be Shared With Other Users. The Other Users Are GRANTED With Certain Type of RIGHTS. GRANT Command Can Be issued Not Only on TABLE OBJECT, But Also on VIEWS, SYNONYMS, INDEXES, SEQUENCES Etc. SQL> GRANT SELECT ON EMP TO ENDUSERS; SQL> GRANT INSERT, SELECT, DELET ON EMP TO OPERATORS; SQL> GRANT INSERT (Empno, Ename, Job) ON Emp To EndUsers;

Q. REVOKE Command? Syntax:: SQL> REVOKE< Privilage Name1>, , ON FROM; REVOKE Command is Used When We Want One Database To Stop Sharing The Information With Other Users. Revoke Privileges is Assigned Not Only On TABLE Object, But Also on VIEWS, SYNONYMS, INDEXES Etc. SQL> REVOKE INSERT, DELETE ON EMP FROM Operators;

Q. Connecting to Oracle OR SQL * Plus? Double Click the SQLPlus ShortCut on the Desktop. Start -> Run -> Type SQLPlus OR SQLPlusW in Open Box and Click OK. Start -> Programs -> Oracle -> Application Development -> SQLPlus In the Login Box OR Login Prompt Type the User Name and Password as Supplied by the Administrator. The Host String is Optional and is provided by the Administrator.

Q. About PL/SQL Tables?

Objects of Type “TABLE” Are Called PL/SQL Tables. They Are Modeled As Database Tables, But Are Not Same. PL/SQL TABLES Use A “PRIMARY KEY” To Give Array Like Access To Rows. PL/SQL Tables Are Very Dynamic in Operation, Giving The Simulation To Pointers in ‘C’ Language. They Help in Integrating The Cursors For Dynamic Management of Records At Run Time. They Make Runtime Management of Result Sets Very Convenient.

For More Interview Questions Click Here:: SQL Interview Questions