SQL Interview Questions and Answers

SQL is a standard language which is used to accessing and manipulating databases. It stands for Structured Query Language. It can be used to develop a web application for a server-side scripting language, like ASP, PHP, etc.

It consists of three components that are listed below:-

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

There are many advantages of SQL, and some of them are mentioned below:

• No coding needed: It is easy to manage without any need to write the substantial amount of code
• Well defined standards: Long established are used by the SQL databases that are being used by ISO and ANSI.
• Portability: We can use this program in PCs, servers, laptops and mobile phones.
• Interactive Language
• Multiple data views

It was initially developed in the early 1970s at IBM by Donald D. Chamberlin and Raymond F. Boyce.

It is a collection of data that is organized to be easily accessed, managed and updated. It is distributed into rows, columns, and tables. It's indexed to make it easier to find related information.

Table: It is a collection of related data that consists of rows and columns. It has a specified number of columns but can have any number of rows.

Field: It is a column in a table that is designed to maintain specific information about all records in the table.

In SQL, Joins is used to merge records from two or more tables in a database, based on a related column between them.

Here are the four types of the joins in SQL

• INNER JOIN: It returns records that have matching values in both tables
• LEFT JOIN: It returns all records from the left table and the matched records from the right table
• RIGHT JOIN: Right Join returns all records from the right table and the matched records from the left table
• FULL JOIN: It returns all records when there is a match in either left or right table

A primary key is a column in a table that designated to uniquely identifies the rows in that table. Primary key holds a unique value for each row and cannot contain null values.

It is a rule used to limit the type of data that can insert into a table, to maintain the integrity and accuracy of the data inside the table.

It can be divided into two types.

• Column level constraints
• Table-level constraints

These are the most common constraints that can be applied to a table.

• NOT NULL
• DEFAULT
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY etc

• Mysql is available free because it is open source, but SQL is not an open source.
• Mysql offers updateable views, but SQL offers indexed views which are more powerful.
• Mysql does not support XML, but SQL supports XML.
• Mysql not supported Auto tuning but SQL can.
• Mysql did not support User-defined functions, but SQL supported.
• Mysql support Transaction is very much limited but SQL support extensively and entirely offered.
• Mysql not offered Stored procedures and full joins but offered in SQL.
• Mysql does not support Cursor feature but is there in SQL

It is a set of one or more than one fields or columns of a table that uniquely identify a record in the table. It is little like a primary key, but it can accept only one null value.

It is a collection of fields in one table that uniquely identifies a row of another table. In simple words, the foreign key is defined in a second table, but it refers to the primary key/unique key in the first table.

SELECT CURDATE();

It is a systematic approach of decomposing tables to remove data redundancy and undesirable characteristics like Insertion, Update and Deletion. Normalization is a multi-step process that put data into the tabular form and removing duplicated data from the tables.

Advantages

• It helps to the reduction of redundant data.
• It has a much more flexible database design.
• It is better to handle on database security.
• It is dealing with data consistency within the database.

Rule of Normalization

• 1NF(First Normal Form)
• 2NF(Second Normal Form)
• 3NF(Third Normal Form)
• BCNF(Boyce and Codd Normal Form)
• 4NF(Fourth Normal Form)

TRUNCATE

• It removes all rows from a table.
• It does not require a WHERE clause.
• Truncate cannot be used with indexed views.
• It is performance wise faster.

DROP

• It removes a table from the database.
• All table's rows, indexes, and privileges will also be removed when we used this command.
• The operation cannot be rolled back.

You can download here sql interview questions pdf after registeration or subscribe.

In SQL, a trigger is a database object that is associated with a table. It will be activated when a defined action is executed for the table. It can be performed when you run one of the following SQL like INSERT, UPDATE and DELETE occurred in a table. It's activation time can be BEFORE or AFTER.

SELECT * FROM employee WHERE name LIKE 'um%';

SELECT salary FROM employee e1 WHERE 3-1 = (SELECT COUNT(DISTINCT salary) FROM employee e2 WHERE e2.salary > e1.salary)

Our updated SQL interview questions queries for freshers are the best online resource to help you prepare for the upcoming interviews.

It is the source of a rowset to be operated upon in a DML statement. These clauses are very common which is used with Select statement, Update statement and Delete statement.

SQL provides with the following clauses that are given below:

• WHERE
• GROUP BY
• HAVING
• ORDER BY etc

WHERE clause is used for filtering rows, and it applies to every row but HAVING term is used to filter groups.

WHERE can be used without the GROUP BY but HAVING clause cannot be used without the GROUP BY.

It is used to rename a table in a specific SQL statement. Renaming is a temporary change without change actual table name in the database. It can be applied to tables as well as fields.

The basic syntax of a table alias
SELECT field1, field2 FROM TABLE_NAME AS TABLE WHERE "WRITE_YOUT_CONDITION";

The basic syntax of a column alias
SELECT field1 AS column1 FROM TABLE_NAME WHERE "WRITE_YOUT_CONDITION";

count(): It returns the number of rows in a table satisfying the condition specified in the WHERE clause. It would return 0 if there were no matching conditions.

Syntax:
SELECT COUNT(*) FROM category WHERE 'status' = 1;

distinct(): It is used to return only different values. In a table, a column often contains many duplicate values, and sometimes we want to list the different values. It can be used with aggregates functions.

Syntax:
SELECT DISTINCT class FROM College ORDER BY department

This information was frequently asked during SQL interview questions for testers.

The aggregate function performs a calculation on a set of values, and it returns a single value as output. It ignores NULL values when it performs calculation except for the COUNT function.

SQL provides many aggregate functions that are listed below.

• AVG()
• COUNT()
• SUM()
• MIN()
• MAX() etc

It is a function that takes one or more values but returns a single value. It is based on user input and returns a single value.

• UCASE()
• LCASE()
• MID()
• LEN()
• ROUND()
• NOW()
• FORMAT()

It is a set of precompiled SQL statements that are used to perform a particular task. A procedure has a name, a parameter list, and SQL statement, etc. It will help in reduce network traffic and increase the performance.

UPDATE `table_name` SET new_field=old_field