Top 30 SQL Interview Questions

If you are searching for SQL interview questions for tester, covering basic to advanced concepts, Coding Tag provides you the comprehensive list of Top 30 most frequently asked SQL interview questions and answers that will be helpful to crack database interview.


1) What is DBMS?

DBMS can be referred as the file manager program designed for data management in the database i.e. how to create, use and maintain data rather than data saving.


2) What is RDBMS?

Relational database management system (RDBMS) is specifically designed software program with the main objective of quick storing, managing, defining constructs and retrieving data.




3) SQL

SQL is the structured query language pronounced as ess-cue-ell is known as the database language used for creating, deleting, fetching and modification of data in the database.


4) Define Table in a Database?

A table can be called as a database object that can be used for storing the record in the terms of rows and columns to hold data.


5) Define Record?

A record is known as the row of the table, that contains arranged sets of interrelated data.

Example

< 402 yuvi 405>


6) What do you mean by the database in DBMS?

Collections of logically related data or records can be known as database


7) Main Objective of DBS?

Effective storage and retrieval of information from the database


8) List some application of DBS system?

  • Reservations systems
  • Telecommunications
  • Banking
  • Sales
  • Scientific applications

9) Name the list of key found in DBMS?

There are mainly six key founds in DBMS to manage table i.e.

  • Candidate key
  • Foreign key
  • Primary key
  • Secondary keys/Alternate key
  • Composite key
  • Super key

10) How can you maintain and identify relations between two or more than two tables in DBMS?

Keys are used in maintaining the relationship between tables


11) Discuss the role of keys in DBMS?

  • Impose Integrity
  • Uniquely identify the records within the table
  • Identify and maintain relations between tables

12) Is it mandatory to use Key? Why we need it?

Key can be referred to as the set of one or more attributes that can be used for unique identification.

For example in real time, if we want to store information of any organization containing a huge amount of unformated data which is increasing day by day. To maintain the data of that organization (if anyone wishes to fetch any specific records of any client), some conditions are required for easy identification.

and There occur a lot of duplicities also. To avoid such problems, keys are used.


13) Database Testing?

Database testing is one of the most considerable data testings, that need a tester with the expertise to investigate tables, procedures and writing queries. It validates the database tables, triggers, junk records and updates the records in a database. With the back end data, the integrity of front-end data is verified. Database testing can be used in an application like Oracle or SQL.

Application of Database testing:

  • Banking project
  • Finance
  • All health insurance related projects in which extensive database testing is required.

14) What is the difference between DELETE and TRUNCATE command?

Delete Command is used to delete a row in a table whereas the truncate command is used to delete entire rows. Delete command is slower in a comparison to truncate statement. You cannot rollback data after applying truncate command.


15) If anyone wants to calculate values from multiple columns in a table which functions can be used in SQL?

Aggregate functions


16) What do you mean by Aggregate functions and the role of these functions in SQL?

Aggregate functions are used to perform calculations on multiple columns and rows in a table and it returns a single value.

Aggregate functions allow us to produce the report of summarized data effortlessly.


17) Name the Aggregate functions found in SQL?

There are seven Aggregate functions used in SQL i.e.

  • COUNT
  • MAX
  • MIN
  • FIRST
  • LAST
  • SUM
  • AVG

MIN(): To returns the smallest value among records

MAX(): To returns the largest value from specified columns

SUM(): To  returns the sum of the numeric values of specified

AVG(): To returns the average value of a particular column

COUNT(): To returns the entire number of values in a given column

FIRST(): To Returns the initial value of the record

LAST(): To Returns the finishing value of the given records


18) Name some leading Database management systems used in MNC?

  • MySQL
  • PostgreSQL
  • MongoDB
  • Microsoft Access
  • Microsoft SQL Server
  • Oracle
  • DB2
  • Sybase

19) What is the different type of statements found in SQL?

SQL Statements can be separated in the following types:

  • DDL – Data Definition Language
  • DQL – Data Query Language
  • TCL – Transaction Control Language
  • DML – Data Manipulation Language
  • DCL – Data Control Language

20) Triggers in SQL

Triggers are identical to the procedures that are stored to bring a response of a specific action applied on a table. Specific action may be insertion, deletion or update. Triggers can also be invoked explicitly. The main components of SQL triggers are action and event.

Syntax: CREATE TRIGGER name {BEFORE|AFTER} (event [OR..]}
ON table_name [FOR [EACH] {ROW|STATEMENT}]
EXECUTE PROCEDURE functionname {arguments}


21) What are the operations performed on Data Manipulation Language?

  • Insertion of data into the database
  • Retrieval of data from the database
  • Updation and deletion of data in the database

22) Name the commands used in Data control language?

We can control the access of database with the use of the commands i.e. GRANT, DENY and REVOKE.

GRANT is used to grant specific task to a user

REVOKE is used to repeal or cancel user access

DENY is used to deny the permission


23) Can we Rollback data in a database, which has been committed with commit keyword?

No


24) What is the different type of TCL commands used in SQL?

The modifications done by DML statements can be managed through the TCL commands i.e. COMMIT, SAVEPOINT, and ROLLBACK. These commands are used in transaction management

Commit:

Commit is used to save changes permanently.

Syntax: COMMIT;

Rollback :

Rollback command is used to restore or undo the work done in the current transaction.

Syntax: ROLLBACK TO savepoint-name;

SAVEPOINT:

This transaction command is used to save a transaction temporary for further rollback requirements.

Syntax: SAVEPOINT savepoint_name;


25) What is the requirement of SQL Alias?

Alias is the process of giving temporary another name to a table or column.

SQL Alias is applicable in the case of large or complex queries.

Following is the syntax for SQL Alias for tables name:

SELECT column-name FROM table-name AS alias-name


26) What do mean by DISTINCT Keyword in SQL?

The Distinct keyword is used along with SELECT Statement to eliminate the duplication at the time of retrieving particular records from the table.

The syntax of the Distinct keyword is

SELECT DISTINCT column-name FROM table-name;


27) Order by clause

Order by clause is used with a select statement for organizing retrieved data in sorted order i.e. ascending or descending order.

The Syntax of Order by clause is

SELECT column-list|* FROM table-name ORDER BY DESC | ASC


28) What is the minimum condition required for joining table in SQL?

 (n-1) where n, is a number of tables.


29) What is the main difference between Alias and Rename?

Alias is the temporary name given to any table whereas Rename is the permanently given name to any column or table.


30. What do you mean by JOIN?

Joins are used for combining rows from two or more than two tables.

There are four type of joins are available in SQL i.e. INNER JOIN, RIGHT JOIN, LEFT JOIN and OUTER JOIN



Leave a Reply

avatar
  Subscribe  
Notify of