SQL Interview Questions And Answers

SQL stands for Structured Query language. It is a database language that allows you to manage, manipulate and retrieve data from relational databases. It is also the standard database language used by all Relational Database Management Systems (RDBMS) such as SQL Server, MySQL, PostgreSQL, Oracle Database, IBM DB2, Sybase, and Microsoft Access.

A query creates a virtual table based on existing tables or constants built into tables.

Syntax:

{

    ( Query

         [ ORDER BY clause ]

         [ result offset clause ]

         [ fetch first clause ]

    ) |

    Query INTERSECT [ ALL | DISTINCT ] Query |

    Query EXCEPT [ ALL | DISTINCT ] Query |

    Query UNION [ ALL | DISTINCT ] Query |

    SelectExpression | VALUES Expression

}

SQL Query is a combination of keywords, identifiers and clauses. It includes three main clauses that are SELECT, FROM, and WHERE clause.

Basic structure of SQL Query:

SELECT

<field>, <field>, <field>

FROM

<database table>

WHERE

<field> = ‘Value’;

In Simple Syntax:

SELECT column1, column2 FROM table1, table2 WHERE column2=’value’

Here,

SELETE – This clause used to specify one or more columns to be retrieved; to specify multiple columns, use a comma and a space between column names.

— column1, column2… are the fields of a table whose values you want to fetch.

FROM – This clause used to specify the table(s) that contain the data.

……from table1, table2

WHERE – This clause used to specify the database table(s) from which you want your query to display data.

……. column2=’value’;

ORDERBY – This clause used to specify the order in which the records appear in a query. In the SELECT clause, include the fields that you want to use in the SQL query’s ORDER BY (and GROUP BY) clauses.

In SQL, WHERE clause is used to further restrict the number of records that are retrieved from the database to only those that are of interest. The clause typically takes the form of one or more ‘conditions. Only those records that meet one or more of the conditions are retrieved from the specified database table.

The HAVING clause is used in database systems to fetch the data/values from the groups according to the given condition.

The syntax for the HAVING clause in SQL is:

SELECT column1, column2, … column1n_n,

       aggregate_function (aggregate_ column)

FROM table Name

GROUP BY column1, column2, … column_n

HAVING condition;

The only difference is that the WHERE clause cannot be used with aggregate functions, whereas the HAVING clause can use aggregate functions.

IN clause retrieves all records which match with the given set of values. It acts as multiple OR conditions.

EXISTS clause is a Boolean operator that returns either True or False. It’s used in combination to a sub-query.

The EXISTS clause is faster than IN clause when the subquery results are very large. The IN clause is faster than EXISTS clause when the subquery results are very small.

SUM: This function adds all the values in a data set and returns that value.

Syntax: SUM(ALL | DISTINCT column)

MAX: This returns the largest value in a set of numbers.

Syntax: MAX(column | expression)

MIN: This returns the smallest value in a set of numbers.

Syntax: MIN(column | expression)

COUNT: The COUNT function reports the number of rows in a group and includes ones with null values.

Syntax: COUNT ( [ALL | DISTINCT] column | expression | *)

AVG: This function calculates the average of numerical values in a collection.

Syntax: AVG( ALL | DISTINCT)

SQL Commands are divided into five different categories. There are – DDL, DML, DCL, DQL, and TCL.

DDL-Data Definition Language:

It is used to define the structure of a database.  It means creating new tables and objects or altering their attributes (such as their data type, table name, etc.)

• CREATE: This command is used to creates a new table, a view of a table, or other object in database.

Syntax: CREATE TABLE table_name;

• DROP: This command is used to deletes or drop the entire table along with the table schema in database.

Syntax: DROP TABLE table_name;

• TRUNCATE: This command is used to removes all rows data from a table.

Syntax: TRUNCATE TABLE table_name

• ALTER: This command is used to changes or alters objects in the database, such as tables and views.

Syntax: ALTER TABLE table_name

ADD column_name datatype;

• BACKUP DATABASE: This statement is used in SQL Server to create a backup of an existing SQL database.

Syntax: BACKUP DATABASE:

DML-Data Manipulation Language:

It is used to manipulate and perform operations on data in a database.

• UPDATE: This command is used to update or changes existing data in a table.

Syntax: UPDATE table_name

SET my_column = my_value

WHERE my_column = my_value;

• DELETE: This command is used to delete or removes data from a table.

Syntax: DELETE FROM table_name

WHERE my_column = my_value;

• SELECT: This command is used to retrieves data from the database.

Syntax: SELECT column_name

FROM table_name;

• USE: This command is used to select a database and perform SQL operations into that database.

Syntax: USE DatabaseName;

• INSERT: This command is used to adds a new data into a table.

Syntax: INSERT INTO table_name (column_1, column_2, column_3)

• MERGE: This statement is used to select rows from one or more sources for update or insertion into a table or view. MERGE combination of INSERT,UPDATE, and DELETE statements.

Syntax:

MERGE target_table  USING source_table

ON merge_condition

WHEN MATCHED

    THEN update_statement

WHEN NOT MATCHED

    THEN insert_statement

WHEN NOT MATCHED BY SOURCE

    THEN DELETE statment;

DCL-Data Control Language:

It is used to control user permissions and access to a database.

• GRANT: This command is used to give an access or privileges on the database objects to the users.

Syntax: GRANT privileges ON object TO user;

REVOKE: This command is used to revokes the given access to the user.

Syntax: REVOKE privileges ON object FROM user;

DQL-Data Query Language:

It is used to fetch the data from the database.

• SELECT: This command is used to select data from a database and the data returned is stored in a result table, called the result-set.

Syntax:

A particular column from the table can be selected with,

SELECT column_name FROM Table_Name;

And all the columns can be selected by using * as shown below,

SELECT * FROM Table_Name;

TCL-Transaction Control Language:

It is a set of special commands that managing and controlling transactions within the database.

• COMMIT: This command is used for saving every transaction to the database.

Syntax: COMMIT;

• ROLLBACK: This command is used for undo the changes that have been made to the database temporarily.

Syntax: ROLLBACK;   ( or )    ROLLBACK  [WORK] [TO SAVEPOINT]

• SAVEPOINT: This command is used for returning a transaction to a specific point without affecting the whole transaction.

Syntax: SAVEPOINT savepoint_name;

• SET TRANSACTION: This command is used for sets different options for your transaction.

Syntax: SET TRANSACTION [Read Write | Read Only];

SELECT – This command is used to extracts data from a database

UPDATE – This command is used to updates data in a database

DELETE – This command is used to deletes data from a database

INSERT INTO – This command is used to inserts new data into a database

CREATE DATABASE – This command is used to creates a new database

ALTER DATABASE – This command is used to modifies a database

CREATE TABLE – This command is used to creates a new table

ALTER TABLE – This command is used to modifies a table

DROP TABLE – This command is used to deletes a table

CREATE INDEX – This command is used to creates an index (search key)

DROP INDEX – This command is used to deletes an index

Constraints in SQL can be defined at the column level, where it is specified as part of the column definition and will be applied to that column only, or declared independently at the table level. In other words, if the inserted data meets the constraint rule, it’ll be inserted successfully on server. However, the insert operation will be revoked, If the inserted data violates the defined constraint.

SQL allows two types of constraints. It could be column level or table level.

Column level: This Constraint applied only to one column.

Table Level: This Constraint applied to the whole table.

Following are most commonly used constraints in SQL:

NOT NULL Constraint: this constraint is used to ensure that a column cannot have NULL value.

CREATE INDEX: this constraint is used to create and retrieve data from the database very quickly.

UNIQUE Constraint: this constraint is used to ensure that all values in a column are different.

CHECK Constraint: this constraint is used to ensure that all values in a column satisfy certain conditions.

PRIMARY Key: this constraint is used to Uniquely identified each rows/record in a database table. t is a combination of NOT NULL and UNIQUE constraints.

FOREIGN Key: this constraint is used to Uniquely identified a rows/record in any another database table.  

DEFAULT Constraint: this constraint is used to provide a default value for a column when none is specified.  

NUMBER:  A variable-length column. Allowed values are zero, positive and negative numbers

Syntax: NUMBER (n, d)

 CHAR:  A variable length field up to 255 characters in length

Syntax: CHAR (size)

 VARCHAR/VARCHAR2:  A variable length field up to 2000 character in length

Syntax:  VARCHAR (size) / VARCHAR2(size

 DATE/TIME:  A fixed length field. The time is stored as a part of the date. The default format is DD/MON/YY

Syntax: data or time

 LONG:  A variable length filled up to 2 GB in length

Syntax: long

in Oracle PL/SQL, RAW is a data type used to store binary data or data which is in byte-oriented form is called Raw data type. RAW data is that it can only be queried or inserted; RAW data cannot be manipulated. RAW data is always returned as a hexadecimal character value.

The following are the most common logical operators of SQL:

ALL:  This operator is used to compare a value to all values in another value set.

AND:  It allows the existence of multiple conditions in an SQL statement WHERE clause.

ANY:  This operator is used to compare a value to any applicable value in the list according to the condition.

BETWEEN:  This operator is used to search for values that are within a set of values, given the minimum value and the maximum value.

EXISTS: This operator is used to search for the presence of a row in a specified table that meet certain criteria.

IN: this operator is used to compare a value to a list of literal values that have been specified.

LIKE: this operator is used to compare a value to similar values using wildcard operators.

NOT: this operator is used to reverses the meaning of the logical operator with which it is used. Eg: NOT

EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.

OR:  this operator is used to combine multiple conditions in an SQL statement’s WHERE clause.

IS NULL:  this operator is used to compare a value with a NULL value.

UNIQUE: this operator is used to searches every row of a specified table for uniqueness (no duplicates).  

There are different types of joins available in SQL −

INNER JOIN: It is used to returns rows when there is a match in both tables.

LEFT JOIN: It is used to returns all rows from the left table, even if there are no matches in the right table.

RIGHT JOIN: It is used to returns all rows from the right table, even if there are no matches in the left table.

FULL JOIN: It is used to returns rows when there is a match in one of the tables

SELF JOIN: It is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

CARTESIAN JOIN: It is used to returns the Cartesian product of the sets of records from the two or more joined tables.

It is used to join large tables or in an instance where the user wants most of the joined table rows.

It is structured in such a way that, columns with the same name of associated tables will appear once only. (Don’t use ON clause in a natural join).

Syntax:

TableExpression NATURAL [ {LEFT | RIGHT} [ OUTER ] | INNER ] JOIN { TableViewOrFunctionExpression | ( TableExpression ) }

By using the DISTINCT keyword.

Syntax:

Select DISTINCT CompanyID from compnay

To create empty tables using the INTO operator

EXITS Clause

triggers are database objects, actually, a special kind of stored procedure, which “reacts” to certain actions we make in the database.

STUFF function is used to overwrite existing character or inserts a string into another string.

Syntax:

STUFF(string_expression,start, length, replacement characters)

SQL Server is a database management system handled by Microsoft. It’s mostly used in Microsoft server environment that uses SQL. while SQL is a query language and not a database system.

By default, SQL Server runs on port 1433.

SQL is a query language for the database. It is used to handle and maintain data i.e.to perform the CRUD operations. It is used to manage the RDBMS databases. It supports a single storage engine.

MySQL is a relational database management system (RDBMS) that uses SQL to query the database. It is used to work on the database through its GUI tool. It supports multiple storage engines.

DELETE is a DML Command. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. Only the rows and columns specified in the WHERE clause condition will be deleted.

TRUNCATE is a DDL command. The truncate command removes the complete data from an existing table but not the table itself. In TRUNCATE, WHERE clause cannot be used.  

ON A.key = B.key  INNER JOIN TableB  B FROM TableA  A

SELECT <fields>

FROM TableA  A

INNER JOIN TableB  B

ON A.key = B.key 

SELECT <fields>

FROM TableA  A

LEFT JOIN TableB  B

ON A.key = B.key 

SELECT <fields>

FROM TableA  A

FULL OUTER JOIN TableB  B

ON A.key = B.key