Frequently Asked SQL interview topics during the interview:
Common SQL Interview Questions in Realtime :
1) What is difference between SQL and SQL*PLUS?
SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.
2) What are the data types allowed in a table ?
CHAR,VARCHAR2,NUMBER,DATE,RAW,LONG and LONG RAW.
3) Explain the differences between char and varchar2 data types.
When a column is of type char, the memory used will not be dependent on the value. It entirely depends on the bytes defined.
Example: Name CHAR(10)
Each value will occupy 10 bytes of memory space even though the value may be of a smaller size.
When a column is of type varchar2, the memory used entirely depends on the value of the column
Example: Name VARCHAR2(10)
Each value will occupy “x” bytes of memory space depending on the value.
To summarize, char data type is usually used when the value of fixed size is ascertained; for example Student id.
On the other hand, varchar2 should be used for variable length of data; for example Name.
4) How many types of SQL Statements are there in Oracle this is general SQL questions asked in interview.
There are basically 6 types of sql statments.They are
- Data Definition Language(DDL) :: The DDL statements define and maintain objects and drop objects.
- Data Manipulation Language(DML) :: The DML statements manipulate database data.
- Transaction Control Statements :: Manage change by DML
- Session Control :: Used to control the properties of current session enabling and disabling roles and changing .e.g. :: Alter Statements, Set Role
- System Control Statements :: Change Properties of Oracle Instance .e.g.:: Alter System
- Embedded Sql :: Incorporate DDL, DML and T.C.S in Programming Language.e.g:: Using the Sql Statements in languages such as ‘C’, Open, Fetch, execute and close
11) Explain ROWID in oracle this is the common SQL interview questions ?
A ROWID in oracle is a pseudo column that is not within the database. It is used to uniquely identify a row with a table.
The rowid values are used to locate necessary information to locate a row.
Example: Below query selects address of all rows that contain data for emp in department 20
Sql> SELECT ROWID,ename from emp where deptno=20;
12) What is large object in oracle? Explain its purposes.
Large objects (LOB’s) are exclusively used to hold large amounts of data. It can hold data in tetra bytes. Different types of LOBs include internal, external, persistent and temporary. Binary LOB’s are typically used to store graphics, video, or audio data.
Purpose:
• Enables you to access and manipulate the data efficiently in your application
• Is optimized for large amounts of data
• Provides a uniform way of accessing data stored within the database or outside the database
13) Explain DCL command. ?
DCL: Data Control Language
DCL are used to configure and control database objects.
Following are the DCL commands:
GRANT
REVOKE
14) What is TCL command?
TCL – Transaction Control: statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
• COMMIT – save work done
• SAVEPOINT – identify a point in a transaction to which you can later roll back
• ROLLBACK – restore database to original since the last COMMIT
• SET TRANSACTION – Change transaction options like isolation level and what rollback segment to use.
15) Differences between CHAR and NCHAR interview questions and answers in Oracle.
Nchar is used to store fixed length Unicode data. It is often used to store data in different languages. CHAR on the other hand is store fixed length character data.
When data is stored using CHAR, it takes n bytes while NCHAR takes 2n bytes.
CHAR accepts up to 8000 characters while NCHAR accepts 4000 characters.
16) Differences between CHAR and VARCHAR2 in Oracle.
CHAR is used to store fixed length character strings where as Varchar2 can store variable length character strings. However, for performance sake Char is quit faster than Varchar2.
If we have char name[10] and store “abcde”, then 5 bytes will be filled with null values, whereas in case of varchar2 name[10] 5 bytes will be used and other 5 bytes will be freed.
17) Write the command to view the structure of the table.?
Write the command to view the structure of the table.
The desc table_name command is used to view the structure of the table.
18) Explain how to view all columns in an Existing Table this is the most popular SQL interview questions.
Use the command DESC and the table name to view the information about the columns.
Eg: SQL> desc emp;
Name Null? Type
———— ———— ———-
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
If you want to view the data stored in all the columns then use
Select * from table-name
Eg: select * from emp;
19) Define CLOB and NCLOB datatypes.?
CLOB and NCLOB can both be used to store 4 GB of data in the database.
CLOB (Character Large Object) is used specifically to store character set data whole (single-byte character set data).
NCLOB (National Large Object) is specifically used to store Unicode national character set data(multiple character sets.)
20) What is the BFILE datatypes?
Oracle provides Bfile datatypes to store unstructured binary data. The actual data is stored outside the database in the operating system files. A bfile column stores file locator that points to an external file containing data. Bfiles are read-only, one can’t modify them.
21) What is a database this is the basic SQL interview questions for beginners?
• Database offer a single point of mechanism for storing and retrieving information with the help of tables.
• Table is made up of columns and rows where each column stores specific attribute and each row displays a value for the corresponding attribute.
• It is a structure that stores information about the attributes of the entities and relationships among them.
• It also stores data types for attributes and indexes.
• Well known DBMS include Oracle, ibm db2, Microsoft sql server, Microsoft access, mysql and sqlLite.
21) Is it possible to modify a Data type of a column when column contains data ?
a] Yes
b] No
Ans B
22) Which of the following is not correct about the “TABLE” data type ?
a] Can contain any no of columns
b] Simulates a One-dimensional array of unlimited size
c] Column datatype of any Scalar type
d] None of the above
Ans : A
23) When we give SELECT * FROM EMP; How does oracle respond:
When we give select * from emp it will shows the records of table emp if the table contain records. If it hasn’t any records then it will shows no rows selected.
24) What is the difference between “select * from table name” and “select column_name from table name” The above quiries are simple select quiries without having any “where” clause. Secondly which query will be performed faster and why??
The First Query will display all the columns.
the second query will display on the Column_Name column.
the second query will be quicker than the first on.
25) How to find out the database name from SQL*PLUS command prompt?
Sql> select name from v$database will give u the database name from which u r currently Connected
26) What is the parameter substitution symbol used with INSERT INTO command?
& & is substitution variable.
eg. insert into table_name values (&column1, &column2);
The statement prompts for values of column1 and column2.
27) What is the use of DESC in SQL?
DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Explanation : The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.
28) What is output of following query Select 2 from employee;
It depends upon number of rows in table. This query will print 2 as many times as rows in table.
29) What is spool command?
Spool command creates a print file of the report.
Spool command is used to sql file on the disk.
SQL>Spool drive name:path
30) How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?
Only one LONG column is allowed. It is not possible to useLONG column in WHERE or ORDER BY clause.
31) What is the order of SQL statement execution?
here clause, Group By clause, Having clause, Order By clause & Select.
Q: select * from emp
where 1=1;
what is the output?
It will return all the records present in the table emp as
the condition 1=1 is true.
32) Advantages and disadvantages of attributes.
1. if more than one attribute has sane name then it will create the problem.
2. Attribute is the common media through which we can get the information about a entity.
33) What’s the back end processes when we type “Select * from Table”?
First it will look into the System Global Area (SGA) weatherthe query is been exectued earlier.
If it exist, it would retrive the same output present in memory.
If not the query we typed is complied and the resulting parse tree and execution plan is been stored in SGA. Then query gets executed and output is given to the application.
34) Explain about data retrieval?
Data retrieval syntax is often used in combination with data projection. This mechanism is used when there is a need forcalculated result. This is used when there is a special need for calculated data and not the verbatim data, which is different from the way it was stored in the database.
35) Which command displays the SQL command in the SQL buffer, and then executes it?
RUN
36) The INSERT INTO Statements:
INSERT INTO table_name VALUES (value1, value2,….)
INSERT INTO table_name (column1, column2,…) VALUES (value1, value2,….)
37) What are the tables and structures in database?
A table contains rows and columns and Structure shows the type of column, size, constraints of that table
38) Which command executes the contents of a specified file in SQL interview questions for data analysts?
START or @.
39) What is database?
A database is a collection of data that is organized so that its contents can easily be accessed, managed and updated.
Another definition is: A collection of information organized in such a way that a computer program can quickly select desired pieces of data. You can think of a database as an electronic filing system.
40) Explain the where clause by this you will get in this SQL interview guide?
Where clause has a comparison predicate which restricts the number of rows as per the user generated query. This clause should be applied before the GROUP BY clause. This clause functions with the help of comparison predicate, when a comparison predicate does not evaluate a result to be true, all rows from the end result are deleted.
41) How to retrieve the image from the database?
using BLOB object. we can store the images n access them.
42) What does preemptive in preemptive.
Preemptive refers to the fact that each task is allotted fixed time slots and at the end of that time slot the next task is started.
43) What are the data types allowed in a table?
CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.
44) what are the SELECT statements?
SELECT column_name(s) FROM table_name
SELECT DISTINCT column_name(s) FROM table_name
SELECT column FROM table WHERE column operator value
SELECT column FROM table WHERE column LIKE pattern
SELECT column,SUM(column) FROM table GROUP BY column
SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value.
Note that single quotes around text values and numeric values should not be enclosed in quotes. Double quotes may be acceptable in some databases.
45) Which command displays the SQL command in the SQL buffer, and then executes it?
RUN
46)How do you generate file output from SQL?
By use of the SPOOL command
47)What is SQL?
Structured Query Language SQL, which stands for Structured Query Language, is a special-purpose language used to define, access, and manipulate data. SQL is nonprocedural, meaning that it describes the necessary components (i.e. tables) and desired results without dictating exactly how results should be computed.
48) What is the difference between two null values in a column this is the SQL interview questions for experienced very frequently?
No two values are same and if we compare two null values each other the value is garbage. we cannot determine the value.
49) How many codds rule should oracle satisfies ?
Oracle can support codd’s 11.5 rules.
view updated rules fully not supported in oracle.
50) What is difference between SQL and SQL*PLUS?
SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.
Continue Part 2