SQL Interview Questions

Question #1) What is SQL?

Structured Query Language is a database tool which is used to create and access database to support software application.

Question #2) What are tables in SQL?

The table is a collection of record and its information at a single view.

Question #3) How do we use DISTINCT statement? What is its use?

DISTINCT statement is used with the SELECT statement. If the records contain duplicate values then DISTINCT is used to select different values among duplicate records.

Syntax: SELECT DISTINCT column_name(s)

FROM table_name;

Question #4) Why do we use SQL constraints? Which constraints we can use while creating database in SQL?

Constraints are used to set the rules for all records in the table. If any constraints get violated then it can abort the action that caused it.

Constraints are defined while creating the database itself with CREATE TABLE statement or even after the table is created once with ALTER TABLE statement.

There are 5 major constraints are used in SQL, such as

• NOT NULL: That indicates that the column must have some value and cannot be left null

• UNIQUE: This constraint is used to ensure that each row and column has unique value and no value is being repeated in any other row or column

• PRIMARY KEY: This constraint is used in association with NOT NULL and UNIQUE constraints such as on one or the combination of more than one columns to identify the particular record with a unique identity.

• FOREIGN KEY: It is used to ensure the referential integrity of data in the table and also matches the value in one table with another using Primary Key

• CHECK: It is used to ensure whether the value in columns fulfills the specified condition

Question #5) What are transaction and its controls?

A transaction can be defined as the sequence task that is performed on databases in a logical manner to gain certain results. Operations performed like Creating, updating, deleting records in the database comes from transactions.

In simple word, we can say that a transaction means a group of SQL queries executed on database records.

There are 4 transaction controls such as

• COMMIT: It is used to save all changes made through the transaction

• ROLLBACK: It is used to roll back the transaction such as all changes made by the transaction are reverted back and database remains as before

• SET TRANSACTION: Set the name of transaction

• SAVEPOINT: It is used to set the point from where the transaction is to be rolled backestion #9) 

Question #6) What are properties of the transaction?

      Properties of transaction are known as ACID properties, such as

• Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully if not then transaction is aborted at the failure point and the previous transaction is rolled back to its initial state as changes undone

• Consistency: Ensures that all changes made through successful transaction are reflected properly on database

• Isolation: Ensures that all transactions are performed independently and changes made by one transaction are not reflected on other

• Durability: Ensures that the changes made in database with committed transactions persist as it is even after system failure

Question #7) How many Aggregate Functions are available there in SQL?

SQL Aggregate Functions calculates values from multiple columns in a table and returns a single value.

There are 7 aggregate functions we use in SQL

• AVG(): Returns the average value from specified columns

• COUNT(): Returns number of table rows

• MAX(): Returns largest value among the records

• MIN(): Returns smallest value among the records

• SUM(): Returns the sum of specified column values

• FIRST(): Returns the first value

• LAST(): Returns Last value

Question #8) What are Scalar Functions in SQL?

Scalar Functions are used to return a single value based on the input values. Scalar Functions are as follows

• UCASE(): Converts the specified field in upper case

• LCASE(): Converts the specified field in lower case

• MID(): Extracts and returns character from text field

• FORMAT(): Specifies the display format

• LEN(): Specifies the length of text field

• ROUND(): Rounds up the decimal field value to a number

Question #9) What are triggers?

Triggers in SQL is kind of stored procedures used to create a response to a specific action performed on the table such as Insert, Update or Delete. You can invoke triggers explicitly on the table in the database.

Action and Event are two main components of SQL triggers when certain actions are performed the event occurs in response to that action.

Syntax: CREATE TRIGGER name {BEFORE|AFTER} (event [OR..]}

ON table_name [FOR [EACH] {ROW|STATEMENT}]

EXECUTE PROCEDURE functionname {arguments}

Question #10) What is View in SQL?

A View can be defined as a virtual table that contains rows and columns with fields from one or more table.

Syntax: CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

Question #11) How we can update the view?

SQL CREATE and REPLACE can be used for updating the view.

Following query syntax is to be executed to update the created view

Syntax: CREATE OR REPLACE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

Question #12) Explain the working of SQL Privileges?

SQL GRANT and REVOKE commands are used to implement privileges in SQL multiple user environments.  The administrator of the database can grant or revoke privileges to or from users of database object like SELECT, INSERT, UPDATE, DELETE, ALL etc.

GRANT Command: This command is used provide database access to user apart from an administrator.

Syntax: GRANT privilege_name

ON object_name

TO {user_name|PUBLIC|role_name}

[WITH GRANT OPTION];

In above syntax WITH GRANT OPTIONS indicates that the user can grant the access to another user too.

REVOKE Command: This command is used provide database deny or remove access to database objects.

Syntax: REVOKE privilege_name

ON object_name

FROM {user_name|PUBLIC|role_name};

Question #13) How many types of Privileges are available in SQL?

There are two types of privileges used in SQL, such as

• System Privilege: System privileges deal with an object of a particular type and specifies the right to perform one or more actions on it which include Admin allows a user to perform administrative tasks, ALTER ANY INDEX, ALTER ANY CACHE GROUP CREATE/ALTER/DELETE TABLE, CREATE/ALTER/DELETE VIEW etc.

• Object Privilege: This allows to perform actions on an object or object of another user(s) viz. table, view, indexes etc. Some of the object privileges are EXECUTE, INSERT, UPDATE, DELETE, SELECT, FLUSH, LOAD, INDEX, REFERENCES etc.

Question #14) What is SQL Injection?

SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database such that once it is executed the database is opened for an attacker. This technique is usually used for attacking Data-Driven Applications to have an access to sensitive data and perform administrative tasks on databases.

For Example: SELECT column_name(s) FROM table_name WHERE condition;

Question #15) What is SQL Sandbox in SQL Server?

SQL Sandbox is the safe place in SQL Server Environment where untrusted scripts are executed. There are 3 types of SQL sandbox, such as

• Safe Access Sandbox: Here a user can perform SQL operations such as creating stored procedures, triggers etc. but cannot have access to the memory and cannot create files.

• External Access Sandbox: User can have access to files without having a right to manipulate the memory allocation.

• Unsafe Access Sandbox: This contains untrusted codes where a user can have access to memory.

More about SQL:

• SQL Interview Questions
• Best SQL Course Training Institute in Marathahalli, Bangalore-SDLC