Futureqtech.com Presents SQL Interview Questions for Developers, Testers

SQL Interview Questions :

What is SQL?

Structured Query Language, an ANSI (American National Standards Institute) standard language for accessing databases.

Using SQL we can Access Oracle, Sybase, DB2, SQL Server, MySQL, DB/400 and other Database Management Systems

Q. When SQL was appeared?

Structured Query Language was first appeared by IBM in 1974and it is Free Software(any body can use with free of cost).

Q. Who should learn SQL?

• Database Developers

• Database Testers

• Database Administrators

Q. What are the Usages of SQL?

•    Creating new databases
•    Creating new tables in a database
•    Inserting records in a database
•    Updating records in a database
•    Deleting records from a database
•    Retrieving data from a database
•    Executing queries against a database
•    Creating stored procedures in a database
•    Creating views in a database
•    Setting permissions on tables, procedures, and views
Etc… 

Q) What are important SQL Language Elements?

Identifiers: Names of Database objects such as tables, views, columns, and databases etc…

Data Types: Define the type of data that is contained by a column.

Constants: Symbols that represent specific data types.

Operators: Perform Arithmetic, Comparison, and Logical Operations.

Functions: Built-in Functions to perform specific operations.

Clauses: Constituent components of statements and queries.

Expressions: Produce either scalar values, or tables consisting of columns and rows of data.

Queries: Retrieve the data based on specific criteria. This is an important element of SQL.
Statements: 

Q. what is SQL Process?

When we are executing an SQL command for any RDBMS, the system determines the best way to carry out our request and SQL engine figures out how to interpret the task.
There are various components included in the process. These components are Query Dispatcher, Optimization engines, Classic Query Engine and SQL query engine etc. Classic query engine handles all non-SQL queries but SQL query engine won’t handle logical files. 

Q. Is SQL supports Programming?

No, SQL doesn’t have Conditional and Loop statements, using SQL Commands we can access databases. 

Q. What are the sub sets of SQL?

•    Data Definition Language
•    Data Manipulation Language
•    Data Control Language 

Q. What is Data Definition Language?

Data Definition Language (DDL) allows us to create, alter, and delete database objects such as schemas, tables, views, sequences, catalogs, indexes, and aliases.

Q. What is Data Manipulation Language?

DML is a language which enables users to access and manipulate data.

Data Manipulation Language is used to Perform below Operations:
•    Insertion of data into the database
•    Retrieval of data from the database
•    Updating data in the database
•    Deletion of data in the database 

What is Data Control Language?

Data Control Language (DCL) allows us to control access to the database. ‘DCL’ commands include-
‘GRANT’ to allow specific users to perform specified tasks
‘REVOKE’ to cancel previously denied or granted permissions

Q. What is Database?

A database is a systematic collection of data, Databases support storage and manipulation of data, and Databases make data management easy.

Q. What is Table?

A Table in a Relational Database is a predefined format of rows and columns that define an entity.
Each column contains a different type of attribute and each row corresponds to a single record.
Each table is provided with a name.

Q. What is Database Management System?

> A database management system, or DBMS, is software designed to assist in maintaining and utilizing large collection of data.

> The alternative to using a DBMS is to store the data in files and write application-specific code to manage it.

Using a DBMS to manage data has many advantages like:

•    Data independence
•    Efficient data access
•    Data integrity and security
•    Data administration
•    Concurrent access and data recovery

Q. What is Database Engine?

Software that stores and retrieves data in a database. It may be self-contained program or the part of DBMS that performs the storage and retrieval operations.

Q. What is Database Server?

Database Management Systems provide Database server functionality, database server provides database services to other computer programs or computers.

What is MS Access?

MS Access was launched in 1992 by Microsoft Corporation as part of MS Office.
Microsoft Access is entry-level database management software. It is not only an inexpensive but also powerful database for small-scale projects.
MS Access uses the Jet database engine which utilizes a specific SQL language dialect (sometimes referred to as Jet SQL).
MS Access comes with the professional edition of MS Office package. MS Access is user friendly database management system.

Q. What is Oracle?

Oracle is a relational database management system developed by ‘Oracle Corporation and launched in 1977.
Oracle supports all major Operating systems includes, MS Windows. NetWare, UnixWare, OS/2 and most UNIX flavors. 

Q. What is MS SQL Server?

MS SQL Server is a Relational Database Management System developed by Microsoft Inc. Its primary query languages are T-SQL and ANSI SQL. 

Q. What is Sybase?

Sybase is a computer software company, their primary product is Sybase DBMS, which is a relational database management system based upon structured query language. 

Q. What is MySQL?

MySQL is open source Database Management System, developed by Swedish company MySQL AB.
MySQL Supports many different platforms including Microsoft Windows, the major Linux distributions, UNIX, and Mac OS X.
MySQL has free and paid versions, depending on its usage (non-commercial/commercial) and features. MySQL comes with a very fast, multi-threaded, multi-user, and robust SQL database server.

Q. What is DB2?

DB2 is the short name used for DATABASE 2. It is relational database product developed by IBM. in 1983 

Q. What are the categories of operators available in SQL?

•    Arithmetic operators
•    Comparison operators
•    Logical operators

Q. What are Arithmetic operators in SQL?

 

Operator Description
+ (Addition ) Adds values
– (Subtraction) Subtracts Right side value from Left side value
* (Multiplication) Multiplies values on either side of the operator
/ (Division) Divides left hand operand by right hand operand
% (Modulus) Divides left hand operand by right hand operand and returns remainder

Q. What is a Data Relationship and What are they?

Database Relationship is the connection between the tables in a database. There are 4 types of relationships, and they are:

•    One to One Relationship
•    One to Many Relationship
•    Many to One Relationship
•    Many to Many Relationship

Q. What are the Data Definition Language Commands and Operations?

Important Data Definition Language Commands

1) Create
2) Alter
3) Drop
4) Truncate
5) Rename

Important Data Definition Language Operations

1) Create a Database
2) Use Database
3) Rename a Database
4) Drop Database
5) Create a Table
6) Rename Table
7) Add a Column to exiting Table
8) Add multiple columns to existing Table
9) Modify an existing column
10) Rename a Column
11) Drop a Column
12) Truncate a Table
13) Drop a Table


Q. How to Create a Database?

Syntax:

Create Database databaseName;

Example:

Create Database gcreddyDB;

Q. How to Select a Database?

Syntax

Use databaseName;

Example:

Use gcreddyDB;

Q. How to Rename a Database?

Syntax

Alter Database databaseName Modify Name = newdatabseName;

Example:

Alter Database gcreddyDB Modify Name = hyderabad

Or

Alter Database gcreddyDB
Modify Name = hyderabad

Q. How to Drop a Database?

Syntax:

Drop Database databaseName;

Example:

Drop Database gcreddyDB;

Q. How to Create a Table?

Syntax:

Create Table tableName
(
column1_name dataType(size),
column2_name dataType(size),
.
.
.
);

Example:

Create Table Students
(
STID int,
STName char(50),
);
————————————
View Table info
Select * from Students

View Table Schema
Select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ‘Students’;

Q. How to Rename a Table?

Syntax:

EXEC sp_rename ‘old_tablename’, ‘new_tablename’;

Example:

EXEC sp_rename ‘Students’, ‘newStudents’;

Q. How to Add a Column to an existing Table?

Syntax:

Alter Table table_name add column_name dataType(size);

Example:
Alter Table newStudents add City char(50);

Q. How to Add multiple columns to an existing Table?

Syntax:

Alter Table table_name add column1_name dataType(size), column2_name dataType(size);

Or
Alter Table table_name add
column1_name dataType(size),
column2_name dataType(size),
.
.;

Example:

Alter Table newStudents add add1 char(100), add2 char(70);

Or

Alter Table newStudents add
add3 char(100),
add4 char(70),
add5 char (100),
phone int;

Q. How to Modify an existing column?

Syntax:

Alter Table table_name Alter Column column_name dataType(size);

Example:

Alter Table newStudents Alter Column add1 varchar(150);

Q. How to Rename a Column?

Syntax:

EXEC sp_rename ‘table_name.old_column_name’, ‘new_colum_name’;

Example:
ExEC sp_rename ‘newStudents.phone’, ‘mobile’

Q. How to Drop a Column?

Syntax:

Alter Table table_name Drop Column column_name;

Example:

Alter Table new Students Drop Column City;

Q. How to Truncate a Table?

Truncate Table command is used to delete complete data from an existing table

Syntax:

Truncate Table table_name;

Example:

Truncate Table newStudents;

Q. How to Drop a Table?

Drop Table command is used to delete complete Table (Data and Table Structure) from the Database.

Syntax:

Drop Table table_name;

Q. What is the parameter substitution symbol used with INSERT INTO command?

The parameter substitution symbol used with INSERT INTO command is &.

Q. What are the various uses of database triggers?

Database triggers can be used to enforce business rules, to maintain derived values and perform value-based auditing.