SQL Database cs619

SQL Database

What is SQL?

SQL (pronounced “ess-que-el”) stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.

1-Database

a structured set of data held in a computer, especially one that is accessible in various ways.

-Table

table is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns, but can have any number of rows

No leave space between table name

CREATE TABLE [Student]

(

[stid] INT NOT NULL PRIMARY KEY,

[Name] NVARCHAR(50) NOT NULL

)

What is tuple

a tuple is one record (one row)

What is DBMS ?

Dbms is software in which we create databases .create users with some privileges and work on record manipulations

The database management system is a collection of programs that enables user to store, retrieve, update and delete information from a database.

What is RDBMS ?

RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.

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).

What is SQL ?

Structured Query Language(SQL) is a language designed specifically for communicating with databases. SQL is an ANSI (American National Standards Institute) standard. Its fourth generation language, example oracle , sql server ,my sql, ms access, sql light

What are the different type of SQL’s statements ?

This is one of the most frequently asked SQL Interview Questions for freshers. SQL statements are broadly classified into three. They are

DDL – Data Definition Language

DDL is used to define the structure that holds the data. For example, Create, Alter, Drop and Truncate table.
Referential integrity

A foreign key constraint is the main mechanism used to enforce referential integrity between tables in a relational database. A column defined as a foreign key is used to reference a column defined as a primary key in another table.

DML – Data Manipulation Language

DML is used for manipulation of the data itself. Typical operations are Insert, Delete, Update and retrieving the data from the table. The Select statement is considered as a limited version of the DML, since it can’t change the data in the database. But it can perform operations on data retrieved from the DBMS, before the results are returned to the calling function.

3. DCL – Data Control Language

–Database administrator

Create user

Create db

Db privileges.
DCL is used to control the visibility of data like granting database access and set privileges to create tables, etc. Example – Grant, Revoke access permission to the user to access data in the database.

What are the Advantages of SQL ?
SQL is not a proprietary language used by specific database vendors. Almost every major DBMS supports SQL, so learning this one language will enable programmers to interact with any database like ORACLE, SQL ,MYSQL etc.
SQL is easy to learn. The statements are all made up of descriptive English words, and there aren’t that many of them.
SQL is actually a very powerful language and by using its language elements you can perform very complex and sophisticated database operations.
what is a field in a database ?

A field is an area within a record reserved for a specific piece of data.
Examples: Employee Name, Employee ID, etc.

What is a Record in a database ?

A record is the collection of values / fields of a specific entity: i.e. an Employee, Salary etc.

What is a Table in a database ?

A table is a collection of records of a specific type. For example, employee table, salary table etc.

What is a database transaction?

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.

What are properties of a transaction?

Expect this SQL Interview Questions as a part of an any interview, irrespective of your experience. Properties of the transaction can be summarized as ACID Properties.

Atomicity

A transaction consists of many steps. When all the steps in a transaction get completed, it will get reflected in DB or if any step fails, all the transactions are rolled back.

Consistency

The database will move from one consistent state to another, if the transaction succeeds and remain in the original state, if the transaction fails.

Isolation

Every transaction should operate as if it is the only transaction in the system.

Durability

Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis.

What is a Database Lock ?

When data is locked, then that means that another database session can NOT update that data until the lock is released (which unlocks the data and allows otherdatabase users to update that data. Locks are usually released by either a ROLLBACK or COMMIT SQL statement12. What are the type of locks ?

Shared Lock

When a shared lock is applied on data item, other transactions can only read the item, but can’t write into it.

Exclusive Lock

When an exclusive lock is applied on data item, other transactions can’t read or write into the data item.

Database Normalization Interview Questions

Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database

What are the different type of normalization?

In database design, we start with one single table, with all possible columns. A lot of redundant data would be present since it’s a single table. The process of removing the redundant data, by splitting up the table in a well defined fashion is called normalization.

First Normal Form (1NF)

A relation is said to be in first normal form if and only if all underlying domains contain atomic values only. After 1NF, we can still have redundant data.

Second Normal Form (2NF)

A relation is said to be in 2NF if and only if it is in 1NF and every non key attribute is fully dependent on the primary key. After 2NF, we can still have redundant data.

Third Normal Form (3NF)

A relation is said to be in 3NF, if and only if it is in 2NF and every non key attribute is non-transitively dependent on the primary key.

Database Keys and Constraints SQL Interview Questions

What is a primary key?

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.

What is a Composite 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.

What is a Composite Primary Key ?

A Composite primary key is a set of columns whose values uniquely identify every row in a table. What it means is that, a table which contains composite primary key will be indexed based on the columns specified in the primary key. This key will be referred in Foreign Key tables.

For example – if the combined effect of columns, “Employee_ID” and “Employee Name” in a table is required to uniquely identify a row, its called a Composite Primary Key. In this case, both the columns will be represented as primary key.

What is a Foreign 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.

What is a Unique Key ?

Unique key is same as primary with the difference being the existence of null. Unique key field allows one value as NULL value.

SQL Insert, Update and Delete Commands Interview Questions

Define SQL Insert Statement ?

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.

Define SQL Update Statement ?

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.

Define SQL Delete Statement ?

SQL Delete is used to delete a row or set of rows specified in the filter condition.

The basic format of an SQL DELETE statement is, DELETE FROM command followed by table name followed by filter condition that determines which rows should be updated.

What are wild cards used in database for Pattern Matching ?

SQL Like operator is used for pattern matching. SQL ‘Like’ command takes more time to process. So before using “like” operator, consider suggestions given below on when and where to use wild card search.

1) Don’t overuse wild cards. If another search operator will do, use it instead.
2) When you do use wild cards, try not to use them at the beginning of the search pattern, unless absolutely necessary. Search patterns that begin with wild cards are the slowest to process.
3) Pay careful attention to the placement of the wild card symbols. If they are misplaced, you might not return the data you intended.

SQL Joins Interview Questions and answers

Define Join and explain different type of joins?

Another frequently asked SQL Interview Questions on Joins. 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

Return all rows from the right table, even if there are no matches in the left table.

Outer Join

Left Join

Return all rows from the left table, even if there are no matches in the right table.

Full Join

Return rows when there is a match in one of the tables.

What is Self-Join?

Self-join is query used to join a table to itself. Aliases should be used for the same table comparison.

What is Cross Join?

Cross Join will return all records where each row from the first table is combined with each row from the second table.

Database Views Interview Questions

What is a view?

The views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database

What is a materialized view?

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.

What are the advantages and disadvantages of views in a database?

Advantages:

Views don’t store data in a physical location.
The view can be used to hide some of the columns from the table.
Views can provide Access Restriction, since data insertion, update and deletion is not possible with the view.

Disadvantages:

When a table is dropped, associated view become irrelevant.
Since the view is created when a query requesting data from view is triggered, its a bit slow.
When views are created for large tables, it occupies more memory.
What is a stored procedure?

Stored Procedure is a function which contains a collection of SQL Queries. The procedure can take inputs , process them and send back output.

What are the advantages of a stored procedure?

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.

What is a trigger?

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.

Explain the difference between DELETE , TRUNCATE and DROP commands?

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.

What is the difference between Cluster and Non cluster Index?

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.

What is Union, minus and Interact commands?

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.

–Dependency in tables

When child table depends on other parent table due to foreign key and primary key relationships

35 relationship in database

–one to one

one table connect to only one table other side

one person run one bike

—one to many

one table able to connect many objects

one class has many students

—-many to many..

Many object relate with many objects

Many books read many students

Static and Dynamic Address Mapping

Static A static mapping is configured so that traffic is always mapped a specific way. You could map all traffic to and from a specific private network location to a specific Internet location. For instance, to set up a Web server on a computer on your private network, you create a static mapping that maps [Public IP Address, TCP Port 80] to [Private IP Address, TCP Port 80].

Dynamic Dynamic mappings are created when users on the private network initiate traffic with Internet locations. The NAT automatically adds these mappings to its mapping table and refreshes them with each use. Dynamic mappings that are not refreshed are removed from the NAT mapping table after a configurable amount of time. For TCP connections, the default time-out is 24 hours. For UDP traffic, the default time-out is one minute.

Most Commanly used DATA Base Engines.

Microsoft SQL Server ( C#.net etc)
Oracle (Java) or Oracal form builder (Mostly used, Powerful Database)
IMB DB2
Microsoft Access
My SQL (PHP)
SQL Lite (android application)
Informix (IMB another data base)
Sybase
Teradata

What is SQL Data Adopter?

SqlDataAdapter provides the communication between the Dataset and the SQL database. We can use SqlDataAdapter Object in combination with Dataset Object.

What are DataSet and data adapter?

Dataset is an offline technique use in .net for get data from database then work offline with get tables .when we need to update then upgrade main databases. The Connection Object and the DataSet can’t see each other. They need a go-between so that they can communicate. This go-between is called a Data Adapter. The Data Adapter contacts your Connection Object, and then executes a query that you set up. The results of that query are then stored in the DataSet.

What is Datareader?

A DataReader is an object returned from the ExecuteReader method of a DbCommand object. It is a forward-only cursor over the rows in the each result set. Using a DataReader, you can access each column of the result set, read all rows of the set, and advance to the next result set if there are more than one.

Execute reader is built in function which capable datareader to read data from database.

What is the difference between Executescalar and Executenonquery?

ExecuteScalar() only returns only single value.the value from the first column of the first row of your query. ExecuteReader() returns an object that can iterate over the entire result set.ExecuteNonQuery() does not return data at all: only the number of rows affected by an insert, update, or delete.

What is the difference between dataset and data reader?

DataReader provides forward-only and read-only access to data, while the DataSetobject can hold more than one table (in other words, more than one row set) from the same data source as well as the relationships between them. DataSet is a disconnected architecture while DataReader is a connected architecture.

What is Query:

A query is an inquiry into the database using the SELECT statement. A query is used to extract data from the database in a readable format according to the user’s request. For instance, if you have an employee table, you might issue a SQL statement that returns the employee who is paid the most.

-Insert query

Insert in to student (id,name,rono) values(1,akram,5)

-Delete query

Delete from student

Where id = 1

Update query

Update student set

Name= amjad,

Rono = 6

Where id = 1

Select query

Select id,nae,rono from student

Difference between DBMS and RDBMS

No.	DBMS	RDBMS
1)	DBMS applications store data as file.	RDBMS applications store data in a tabular form.
2)	In DBMS, data is generally stored in either a hierarchical form or a navigational form.	In RDBMS, the tables have an identifier called primary key and the data values are stored in the form of tables.
3)	Normalization is not present in DBMS.	Normalization is present in RDBMS.
4)	DBMS does not apply any security with regards to data manipulation.	RDBMS defines the integrity constraint for the purpose of ACID (Atomocity, Consistency, Isolation and Durability) property.
5)	DBMS uses file system to store data, so there will be no relation between the tables.	in RDBMS, data values are stored in the form of tables, so a relationship between these data values will be stored in the form of a table as well.
6)	DBMS has to provide some uniform methods to access the stored information.	RDBMS system supports a tabular structure of the data and a relationship between them to access the stored information.
7)	DBMS does not support distributed database.	RDBMS supports distributed database.
8)	DBMS is meant to be for small organization and deal with small data. it supports single user.	RDBMS is designed to handle large amount of data. it supports multiple users.
9)	Examples of DBMS are file systems, xml etc.	Example of RDBMS are mysql, postgre, sql server, oracle etc.

After observing the differences between DBMS and RDBMS, you can say that RDBMS is an extension of DBMS. There are many software products in the market today who are compatible for both DBMS and RDBMS. Means today a RDBMS application is DBMS application and vice-versa.

Difference Between SQL vs NoSQL

There are a lot of databases used today in the industry. Some are SQL databases, some are NoSQL databases. The conventional database is SQL database system that uses tabular relational model to represent data and their relationship. The NoSQL database is the newer one database that provides a mechanism for storage and retrieval of data other than tabular relations model used in relational databases.

Following is a list of differences between SQL and NoSQL database:

Index	SQL	NoSQL
1)	Databases are categorized as Relational Database Management System (RDBMS).	NoSQL databases are categorized as Non-relational or distributed database system.
2)	SQL databases have fixed or static or predefined schema.	NoSQL databases have dynamic schema.
3)	SQL databases display data in form of tables so it is known as table-based database.	NoSQL databases display data as collection of key-value pair, documents, graph databases or wide-column stores.
4)	SQL databases are vertically scalable.	NoSQL databases are horizontally scalable.
5)	SQL databases use a powerful language “Structured Query Language” to define and manipulate the data.	In NoSQL databases, collection of documents are used to query the data. It is also called unstructured query language. It varies from database to database.
6)	SQL databases are best suited for complex queries.	NoSQL databases are not so good for complex queries because these are not as powerful as SQL queries.
7)	SQL databases are not best suited for hierarchical data storage.	NoSQL databases are best suited for hierarchical data storage.
8)	MySQL, Oracle, Sqlite, PostgreSQL and MS-SQL etc. are the example of SQL database.	MongoDB, BigTable, Redis, RavenDB, Cassandra, Hbase, Neo4j, CouchDB etc. are the example of nosql database

Difference between DBMS and Database

A database is a collection of organized data and the system that manages a collection of databases is called a Database Management System. The database holds the records, fields and cells of data. The DBMS is the tool used to manipulate the data inside the database. However, the term database is increasingly used as shorthand for Database Management System. To make the distinction simple, consider and operating system and the individual files stored in the system. Just like you need an operating system to access and modify files in the system, you need a DBMS to manipulate databases stored in the database system.

What is the difference between Normalization and Denormalization?

What are the difference between DDL, DML and DCL commands?

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

CREATE – to create objects in the database
ALTER – alters the structure of the database
DROP – delete objects from the database
TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
COMMENT – add comments to the data dictionary
RENAME – rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

SELECT – retrieve data from the a database
INSERT – insert data into a table
UPDATE – updates existing data within a table
DELETE – deletes all records from a table, the space for the records remain
MERGE – UPSERT operation (insert or update)
CALL – call a PL/SQL or Java subprogram
EXPLAIN PLAN – explain access path to data
LOCK TABLE – control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

GRANT – gives user’s access privileges to database
REVOKE – withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are 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

‹ SQLupDifference between TRUNCATE, DELETE and DROP commands ›

DML commands can’t be

Permalink Submitted by ramakrishna (not verified) on Thu, 2006-01-05 09:35.

DML commands can’t be rollback when a DDL command is executed immediately after a DML. DDL after DML means “auto commit”. The changes will return on disk not on the buffer. If the changes return on the buffer it is possible to rollback not from the disk.

DEAR SIR,

Let me tell you the difference between DDL,DML,TCL and DCL:

DDL COMMANDS:
CREATE,ALTER,DROP AND TRUNCATE ARE CALLED DDL COMMANDS. They are called Data Definition since they are used for defining the data. That is the structure of the data is known through these DDL commands.

DML COMMANDS:
DML commands are used for data manipulation. Some of the DML commands
insert,select,update,delete etc. Even though select is not exactly a DML language command oracle still recommends you to consider SELECT as an DML command.

TCL:
For revoking the transactions and to make the data commit to the database we use TCL. Some of the TCL commands are:
1. ROLLBACK
2. COMMIT

ROLLBACK is used for revoking the transactions until last commit.
COMMIT is used for commiting the transactions to the database.
Once we commit we cannot rollback. Once we rollback we cannot commit.
Commit and Rollback are generally used to commit or revoke the transactions that are with regard to DML commands.

DCL:
Data Control Language is used for the control of data. That is a user can access any data based on the priveleges given to him. This is done through DATA CONTROL LANGUAGE. Some of the DCL Commands are:
1. GRANT
2. REVOKE.

“THESE ARE REFERRED FROM THE BOOK ‘ORACLE 9i complete Reference’ written by Kooch and George. This is an Oracle Press Release.” If you want further details about this topic I can give you. Since I don’t know your e-mail address I am not able to send you the complete information. IYou can send your email address to my email and i will discuss about this topic with you. If you can send me your email address I can send you an complete attachment of this topic.

Thanking you,

SQL statements are often divided into three categories:

DML (Data Manipulation Language). These SQL statements are used to retrieve and manipulate data. This category encompasses the most fundamental commands including DELETE, INSERT, SELECT, and UPDATE. DML SQL statements have only minor differences between SQL variations. DML SQL commands include the following:

DELETE to remove rows.
INSERT to add a row.
SELECT to retrieve row.
UPDATE to change data in specified columns.

DDL (Data Definition Language). These SQL statements define the structure of a database, including rows, columns, tables, indexes, and database specifics such as file locations. DDL SQL statements are more part of the DBMS and have large differences between the SQL variations. DML SQL commands include the following:

CREATE to make a new database, table, index, or stored query.
DROP to destroy an existing database, table, index, or view.
DBCC (Database Console Commands) statements check the physical and logical consistency of a database.

DCL (Data Control Language). These SQL statements control the security and permissions of the objects or parts of the database(s). DCL SQL statements are also more part of the DBMS and have large differences between the SQL variations. DML SQL commands include the following:

GRANT to allow specified users to perform specified tasks.
DENY to disallow specified users from performing specified tasks.
REVOKE to cancel previously granted or denied permissions.

What is cardinality in DBMS?

In the case of Data Modelling, Cardinality defines the number of attributes in one entity set, which can be associated with the number of attributes of other set via relationship set. In simple words, it refers to the relationship one table can have with the other table. They can be One-to-one, One-to-many, Many-to-one, Many-to-many.

In the case of SQL, Cardinality refers to a number. It gives the number of unique values that appear in the table for a particular column. For eg: you have a table called Person with column Gender. Gender column can have values either ‘Male’ or ‘Female”. Then the cardinality of Gender column is 2, since there are only two unique values that could possibly appear in that column – Male and Female.

Cardinality in DBMS

In DBMS you may hear cardinality term at two different places and it has two different meanings as well.

In Context of Data Models:
In terms of data modeling, cardinality refers to the relationship between two tables. They can be of four types:

One to One – A single row of table 1 associates with single row of table 2

One to Many – A single row of table 1 associates with more than one rows of table 2

Many to One – Many rows of table 1 associate with a single row of table 2

Many to Many – Many rows of table 1 associate with many rows of table 2

In Context of Query Optimization:
In terms of query, the cardinality refers to the uniqueness of a column in a table. The column with all unique values would be having the high cardinality and the column with all duplicate values would be having the low cardinality. These cardinality scores helps in query optimization

Difference Between DBMS and Data Warehouse

The key difference between DBMS and data warehouse is the fact that a data warehouse can be treated as a type of a database or a special kind of database, which provides special facilities for analysis, and reporting while, DBMS is the overall system which manages a certain database. Data warehouses mainly store data for the purpose of reporting and analysis that would help an organization in the process making decisions, while a DBMS is a computer application that is used to organize, store and retrieve data. A data warehouse needs to use a DBMS to make data organization and retrieval more efficient.

Difference Between 1NF and 2NF and 3NF

Normalization is a process that is carried out to minimize the redundancies that are present in data in relational databases. This process will mainly divide large tables in to smaller tables with fewer redundancies. These smaller tables will be related to each other through well defined relationships. In a well normalized database, any alteration or modification in data will requires modifying only a single table. First normal form (1NF), Second normal form (2NF) and the Third Normal Form (3NF) was introduced by Edgar F. Codd, who is also the inventor of the relational model and the concept of normalization.

What is 1NF?

1NF is the First normal form, which provides the minimum set of requirements for normalizing a relational database. A table that complies with 1NF assures that it actually represents a relation (i.e. it does not contain any records that are repeating), but there is no universally accepted definition for 1NF. One important property is that a table that comply with 1NF could not contain any attributes that are relational valued (i.e. all the attributes should have atomic values).

What is 2NF?

2NF is the Second normal form used in relational databases. For a table to comply with 2NF, it should be complied with 1NF and any attribute that is not a part of any candidate key (i.e. non-prime attributes) should fully depend on any of the candidate keys in the table.

What is 3NF?

3NF is the Third normal form used in relational database normalization. According to the Codd’s definition, a table is said to be in 3NF, if and only if ,that table is in the second normal form (2NF), and every attribute in the table that do not belong to a candidate key, should directly depend on every candidate key of that table. In 1982 Carlo Zaniolo produced a differently expressed definition for 3NF. Tables that comply with the 3NF generally do not contain anomalies that occur when inserting, deleting or updating records in the table.

What is the difference between 1NF and 2NF and 3NF?

1NF, 2NF and 3NF are normal forms that are used in relational databases to minimize redundancies in tables. 3NF is considered as a stronger normal form than the 2NF, and it is considered as a stronger normal form than 1NF. Therefore in general, obtaining a table that complies with the 3NF form will require decomposing a table that is in the 2NF. Similarly, obtaining a table that complies with the 2NF will require decomposing a table that is in the 1NF. However, if a table that complies with 1NF contains candidate keys that are only made up of a single attribute (i.e. non-composite candidate keys), such a table would automatically comply with 2NF. Decomposition of tables will result in additional join operations (or Cartesian products) when executing queries. This will increase the computational time. On the other hand, the tables that comply with stronger normal forms would have fewer redundancies than tables that only comply with weaker normal forms

SQL Interview Questions

There is given sql interview questions and answers that has been asked in many companies. For PL/SQL interview questions, visit our next page.

1) What is SQL?

SQL stands for structured query language. It is a database language used for database creation, deletion, fetching rows and modifying rows etc. sometimes it is pronounced as se-qwell.

2) When SQL appeared?

It appeared in 1974.

3) What are the usages of SQL?

To execute queries against a database
To retrieve data from a database
To inserts records in a database
To updates records in a database
To delete records from a database
To create new databases
To create new tables in a database
To create views in a database

4) Does SQL support programming?

No, SQL doesn’t have loop or Conditional statement. It is used like commanding language to access databases.

5) What are the subsets of SQL?

Data definition language (DDL)
Data manipulation language (DML)
Data control language (DCL)

6) What is data definition language?

Data definition language(DDL) allows you to CREATE, ALTER and DELETE database objects such as schema, tables, view, sequence etc.

7) What is data manipulation language?

Data manipulation language makes user able to access and manipulate data. It is used to perform following operations.

Insert data into database
Retrieve data from the database
Update data in the database
Delete data from the database

8) What is data control language?

Data control language allows you to control access to the database. It includes two commands GRANT and REVOKE.

GRANT: to grant specific user to perform specific task.

REVOKE: to cancel previously denied or granted permissions.

9) What are tables and fields in database?

A table is a set of organized data. It has columns and rows. Columns can be categorized as vertical, and Rows are horizontal.

A table contains specified number of column called fields but can have any number of rows which is known as record.

10) What is a primary key?

A primary key is a combination of fields which uniquely specify a row. This is a special kind of unique key. Primary key values cannot be NULL.

11) What is a foreign key?

A foreign key is specified as a key which is related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.

12) What is a unique key?

A Unique key constraint uniquely identifies each record in the database. This provides uniqueness for the column or set of columns.

13) What are the type of operators available in SQL?

Arithmetic operators
Logical operators
Comparison operator

14) What is view in SQL?

A view is a virtual table which contains a subset of data within a table. Views are not virtually present, and it takes less space to store. View can have data of one or more tables combined, and it is depending on the relationship.

15) What is an Index in SQL?

Index is used to increase the performance and allow faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.

16) Which are the different types of indexes in SQL?

There are three types of Indexes in SQL:

Unique Index
Clustered Index
NonClustered Index

17) What is Unique Index?

Unique Index:

This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when primary key is defined.

18) What is Clustered Index in SQl?

Clustered Index:

The clustered index is used to reorder the physical order of the table and search based on the key values. Each table can have only one clustered index.

19) What is NonClustered Index in SQL?

NonClustered Index:

NonClustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 non-clustered indexes.

20) What is the difference between SQL, MySQL and SQL Server?

SQL or Structured Query Language is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases. On the other hand, MySQL and Microsoft’s SQL Server both are relational database management systems that use SQL as their standard relational database language.

21) What is the difference between SQL and PL/SQL?

SQL or Structured Query Language is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases. On the other hand, PL/SQL is a dialect of SQL which is used to enhance the capabilities of SQL. It was developed by Oracle Corporation in the early 90’s. It adds procedural features of programming languages in SQL.

22) Is it possible to sort a column using a column alias?

Yes. You can use column alias in the ORDER BY clause for sorting.

23) What is the difference between clustered and non clustered index in SQL?

There are mainly two type of indexes in SQL, Clustered index and non clustered index. The differences between these two indexes is very important from SQL performance perspective.

One table can have only one clustered index but it can have many non clustered index.(approximately 250).
clustered index determines how data is stored physically in table. Actually clustered index stores data in cluster, related data is stored together so it makes simple to retrieve data.
reading from a clustered index is much faster than reading from non clustered index from the same table.
clustered index sort and store data rows in the table or view based on their key value, while non cluster have a structure separate from the data row.

24) What is the SQL query to display current date?

There is a built in function in SQL called GetDate() which is used to return current timestamp.

25) Which are the most commonly used SQL joins?

Most commonly used SQL joins are INNER JOIN and (left/right) OUTER JOIN.

26) What are the different types of joins in SQL?

Joins are used to merge two tables or retrieve data from tables. It depends on the relationship between tables.

Following are the most commonly used joins in SQL:

Inner Join
Right Join
Left Join
Full Join

27) What is Inner Join in SQL?

Inner join:

Inner join returns rows when there is at least one match of rows between the tables.

28) What is Right Join in SQL?

Right Join:

Right join is used to retrieve rows which are common between the tables and all rows of Right hand side table. It returns all the rows from the right hand side table even though there are no matches in the left hand side table.

29) What is Left Join in SQL?

Left Join:

Left join is used to retrieve rows which are common between the tables and all rows of Left hand side table. It returns all the rows from Left hand side table even though there are no matches in the Right hand side table.

30) What is Full Join in SQL?

Full Join:

Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.

31) What is “TRIGGER” in SQL?

Trigger allows you to execute a batch of SQL code when an insert, update or delete command is executed against a specific table.

Actually triggers are special type of stored procedures that are defined to execute automatically in place or after data modifications.

32) What is self join and what is the requirement of self join?

Self join is often very useful to convert a hierarchical structure to a flat structure. It is used to join a table to itself as like if that is the second table.

33) What are set operators in SQL?

Union, Intersect or Minus operators are called set operators.

34) What is the difference between BETWEEN and IN condition operators?

The BETWEEN operator is used to display rows based on a range of values. The IN condition operator is used to check for values contained in a specific set of values.

35) What is a constraint? Tell me about its various levels.

Constraints are representators of a column to enforce data entity and consistency. There are two levels :

column level constraint
table level constraint

36) Write an SQL query to find names of employee start with ‘A’?

SELECT* FROM Employees WHERE EmpName like ‘A%’

37) Write an SQL query to get third maximum salary of an employee from a table named employee_table.

SELECTTOP 1 salary
FROM(
SELECTTOP 3 salary
FROMemployee_table
ORDERBY salary DESC ) AS emp
ORDERBY salary ASC;

38) What is the difference between DELETE and TRUNCATE statement in SQL?

The main differences between SQL DELETE and TRUNCATE statements are given below:

No.	DELETE	TRUNCATE
1)	DELETE is a DML command.	TRUNCATE is a DDL command.
2)	We can use WHERE clause in DELETE command.	We cannot use WHERE clause with TRUNCATE
3)	DELETE statement is used to delete a row from a table	TRUNCATE statement is used to remove all the rows from a table.
4)	DELETE is slower than TRUNCATE statement.	TRUNCATE statement is faster than DELETE statement.
5)	You can rollback data after using DELETE statement.	It is not possible to rollback after using TRUNCATE statement.

39) What is ACID property in database?

ACID property is used to ensure that the data transactions are processed reliably in a database system.

A single logical operation of a data is called transaction.

ACID is an acronym for Atomicity, Consistency, Isolation, Durability.

Atomicity: it requires that each transaction is all or nothing. It means if one part of the transaction fails, the entire transaction fails and the database state is left unchanged.

Consistency: the consistency property ensure that the data must meet all validation rules. In simple words you can say that your transaction never leaves your database without completing its state.

Isolation: this property ensure that the concurrent property of execution should not be met. The main goal of providing isolation is concurrency control.

Durability: durability simply means that once a transaction has been committed, it will remain so, come what may even power loss, crashes or errors.

40) What is the difference among NULL value, zero and blank space?

Ans: 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’. On the other hand, zero is a number and blank space is treated as a character.

41) What is the usage of SQL functions?

SQL functions are used for following purpose:

To perform calculations on data.
To modify individual data items.
To manipulate the output.
To format dates and numbers.
To convert data types.

42) Which are the different case manipulation functions in SQL?

There are three case manipulation functions in SQL:

LOWER
UPPER
INITCAP

43) What is the usage of NVL function?

The NVL function is used to convert NULL value to a actual value.

44) Which function is used to return remainder in a division operator in SQL?

The MOD function returns the remainder in a division operation.

45) What is the syntax and use of the COALESCE function?

The syntax of COALESCE function:

COALESCE(exp1, exp2, … expn)

The COALESCE function is used to return the first non-null expression given in the parameter list.

46) What is the usage of DISTINCT keyword?

The DISTINCT keyword is used to ensure that the fetched value is only a non-duplicate value