?

SQL Interview Questions

Structured Query Language(SQL) is a language designed specifically for communicating with databases. SQL is an ANSI (American National Standards Institute) standard.
SQL statements are broadly classified into three. They are
DDL – Data Definition Language
DML – Data Manipulation Language
DCL – Data Control Language
A field is an area within a record reserved for a specific piece of data. Examples: Employee Name, Employee ID, etc.
Database transaction takes database from one consistent state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through.
Database lock tells a transaction, if the data item in questions is currently being used by other transactions.
A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future. To define a field as primary key, following conditions had to be met :
1. No two rows can have the same primary key value.
2. Every row must have a primary key value.
3. The primary key field cannot be null.
4. Value in a primary key column can never be modified or updated, if any foreign key refers to that primary key.
A Composite primary key is a type of candidate key, which represents a set of columns whose values uniquely identify every row in a table.
For example - if "Employee_ID" and "Employee Name" in a table is combined to uniquely identify a row its called a Composite Key.
When a "one" table's primary key field is added to a related "many" table in order to create the common field which relates the two tables, it is called a foreign key in the "many" table.
For example, the salary of an employee is stored in salary table. The relation is established via foreign key column “Employee_ID_Ref” which refers “Employee_ID” field in the Employee table.
SQL Update is used to update data in a row or set of rows specified in the filter condition. The basic format of an SQL UPDATE statement is, Update command followed by table to be updated and SET command followed by column names and their new values followed by filter condition that determines which rows should be updated.
In order to avoid data duplication, data is stored in related tables. Join keyword is used to fetch data from related tables. "Join" return rows when there is at least one match in both table. Type of joins are
Right Join
Outer Join
Left Join
Full Join
Self-join is query used to join a table to itself. Aliases should be used for the same table comparison.
Cross Join will return all records where each row from the first table is combined with each row from the second table.
The views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.
Materialized views are also a view but are disk based. Materialized views get updates on specific duration, base upon the interval specified in the query definition. We can index materialized view.
Advantages:
1. Views don't store data in a physical location.
2. The view can be used to hide some of the columns from the table.
3. Views can provide Access Restriction, since data insertion, update and deletion is not possible with the view.
Disadvantages:
1. When a table is dropped, associated view become irrelevant.
2. Since the view is created when a query requesting data from view is triggered, its a bit slow.
3. When views are created for large tables, it occupies more memory.
Stored Procedure is a function which contains a collection of SQL Queries. The procedure can take inputs , process them and send back output.
Database triggers are sets of commands that get executed when an event(Before Insert, After Insert, On Update, On delete of a row) occurs on a table, views.
Stored Procedures are precomplied and stored in the database. This enables the database to execute the queries much faster. Since many queries can be included in a stored procedure, round trip time to execute multiple queries from source code to database and back is avoided.
Once delete operation is performed, Commit and Rollback can be performed to retrieve data.
Once the truncate statement is executed, Commit and Rollback statement cannot be performed. Where condition can be used along with delete statement but it can't be used with truncate statement.
Drop command is used to drop the table or keys like primary,foreign from a table.
A clustered index reorders the way records in the table are physically stored. There can be only one clustered index per table. It makes data retrieval faster.
A non clustered index does not alter the way it was stored but creates a completely separate object within the table. As a result insert and update command will be faster.
MINUS operator is used to return rows from the first query but not from the second query. INTERSECT operator is used to return rows returned by both the queries.
PL/SQL is a dialect of SQL that adds procedural features of programming languages in SQL. It was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL.
A NULL value is not same as zero or a blank space. A NULL value is a value which is ‘unavailable, unassigned, unknown or not applicable’. Whereas, zero is a number and blank space is a character.
The BETWEEN operator displays rows based on a range of values. The IN condition operator checks for values contained in a specific set of values.
Group functions in SQL work on sets of rows and returns one result per group. Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.
It doesn’t have a GROUP BY clause. The subject_code should be in the GROUP BY clause.
SELECT subject_code, count(name)
FROM students
GROUP BY subject_code;
A subquery is a SELECT statement embedded in a clause of another SELECT statement. It is used when the inner query, or the subquery returns a value that is used by the outer query. It is very useful in selecting some rows in a table with a condition that depends on some data which is contained in the same table.
Null values can be inserted into a table by one of the following ways -
Implicitly by omitting the column from the column list.
Explicitly by specifying the NULL keyword in the VALUES clause.
VARCHAR2 represents variable length character data, whereas CHAR represents fixed length character data.
The DML statements are used to add new rows to a table, update or modify data in existing rows, or remove existing rows from a table.
Relational Database Management system (RDBMS) is a database management system (DBMS) that is based on the relational model. Data from relational database can be accessed or reassembled in many different ways without having to reorganize the database tables. Data from relational database can be accessed using an API , Structured Query Language (SQL).
SQL INSERT statement is used to add rows to a table. For a full row insert, SQL Query should start with “insert into “ statement followed by table name and values command, followed by the values that need to be inserted into the table. The insert can be used in several ways:
1. To insert a single complete row.
2. To insert a single partial row.
Request a Call Back

Enter your Phone Number

Quick Contact

* Required Field

Reviews

Get A Free Quote / Need a Help ? Contact Us
?