Corona Update

Top 50+ SQL Interview Questions

In this SQL interview questions section; will discuss some of the most important asked SQL questions & queries in a Job interview. SQL questions will start from basic to advanced levels of questions with answer and examples.

The SQL Structured Query Language interview questions most important for that candidates who want to start career as a SQL database developer.

SQL database interview question

Q1- What is SQL?

SQL stands for STRUCTUED QUERY LANGUAGE; SQL is a database computer language, it is used to store data from data table by the help of queries.

The SQL database is designed for the retrieval and management of data in a relational database. And basically, a program which is considered when it comes to maintaining, creating, deploying, controlling as well as monitoring the use of a database. It can also be considered as a file manager which is good enough to be trusted for managing the data kept in a database than a file system, ...Learn Basic SQL Database →

Q2- What is a query?

A SQL QURERY Or SQL DB QURERY is a code written in order to get the information back from the database.

Query can be designed in such a way that it matched with our expectation of the result set. Simply, we can say that a question to the Database.

Q3- What is SUBQUERY?

The SQL SUBQUERY is also called 'Inner Query'; SUBQUERY is always executed first, and the result of SUBQUERY is passed on to the main query. SUBQUERY is always inside the main query, and main query is also called 'Outer Query' [Basically, A subquery is a query within another query].

Q4- What is a SQL HEAP?

A SQL HEAP is a table does not contain any clustered file OR Non clustered file index.

Q5- What is the difference between SQL and PL/SQL?

SQL PL/SQL
SQL is a single query or command executive PL/SQL is not a command executive language
SQL is not a Full programming language PL/SQL is a Full programming language
Data source for reports, web pages Application language to build, format and display report, web pages
Declarative in nature and Manipulating data Procedural in nature and Creating Applications

Q6- What is a PRIMARY KEY in SQL?

A PRIMARY KEY is a combination of fields which uniquely specify a row. The PRIMARY KEY is a special kind of unique key; and it has implicit NOT NULL constraint [It means, Primary key values cannot be NULL].

PRIMARY KEY Important Conditions

  • A Primary key is a set of columns that uniquely identifies each row in the table.
  • Uniquely identifies a single row in the table
  • Null values not allowed in PRIMARY KEY column

Q7- What is a FOREIGN KEY in SQL?

A FOREIGN KEY is one table which can be related to the PRIMARY KEY of another table, relationship needs to be created between two tables by referencing FOREIGN KEY with the PRIMARY KEY of another table.

Q8- What is the DBMS?

A Database Management System (DBMS) is a software application that interacts with the user, applications and the database itself to capture and analyze data.

Q9- What are the different types of DBMS?

  • RDMS [Relational Database Management System] - The data is stored in relations (tables) for example – MySQL.
  • NON - RDMS [Non-Relational Database Management System] - There is no concept of relations, tuples and attributes for example – Mongo.

Q10- How to check the SQL SERVER version?

We can checked SQL SERVER version by running the following command:

SELECT  @@Version;

Q11- What are the uses of SQL?

  • SQL is responsible for maintaining the relational data and the data structures present in the database.
  • To Execute queries against a database
  • To RETRIEVE data, INSERT records, UPDATE records, DELETE records, CREATE new databases, CREATE new table, CREATE views in a database.

Q12- What are Fields and Tables? How they are useful?

A table is a set of different rows and columns and is organized in a model. The manner of columns and rows are vertical and horizontal.

There are some specific numbers of columns which remains present and is generally known as fields in a table & there is no strict upper limit on the overall number of records which are defined by rows in the table.

Q13- What is a join?

An SQL JOIN clause combines rows from two or more tables. It creates a set of rows in a temporary table.

Q14- What are the types of join?

There are four basic Join available in SQL database; list mentioned below:

  • Inner Join
  • Left Join OR Left Outer Join
  • Right Join OR Right Outer Join
  • Full Join

Learn Complete SQL Join →

Q15- What is difference between Cross and Natural Join?

CROSS JOIN - The Cross Join delivers the cross product result of two tables.

NATURAL JOIN - The Natural Join result depends on every one of the columns having a similar name and information types in both the tables.

Q16- What is the difference between SQL and MySQL?

SQL is a Standard Query Language based on the English language whereas MySQL is a Database Management System.

  • SQL - SQL is the core of relational database which is used for accessing and managing database.
  • MySQL - MySQL is an Relational Database Management System (RDMS) such as SQL Server, Informix etc.

Q17- Does SQL support programming?

  • SQL refers to the Standard Query Language, which is not actually the programming language.
  • SQL doesn't have a loop, Conditional statement, logical operations, it can not be used for anything other than data manipulation.
  • It is used like commanding (Query) language to access databases.
  • The primary purpose of SQL is to retrieve, manipulate, update and perform complex operations like joins on the data present in the database.

Q18- What is a DDL?

DDL stand for Data Definition Language; it is the subset of the database which defines the data structure of the database in the initial stage when the database is about to be created. It have the following commands: CREATE, ALTER and DELETE database objects such as schema, tables, view etc.

Q19- Why do we use SQL constraints?

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.

Q20- Which constraints we can use while creating database in SQL?

There are Five constraints are used in SQL to create database:

  • 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

Q21- What are the various levels of constraints?

Constraints are the representation of a column to enforce data entity and consistency. There are two levels of a constraint use in SQL

  • column level constraint
  • table level constraint

Q22- What is the main difference b/w ‘BETWEEN’ and ‘IN’ operators?

BETWEEN operator is used to display rows based on a range of values in a row whereas the IN condition operator is used to check for values contained in a specific set of values.

BETWEEN Operator [Student Table]

SELECT  * FROM table_name
WHERE ROLL_NO BETWEEN 10 AND 50;

IN Operator [Student Table]

SELECT  * FROM table_name
WHERE ROLL_NO IN (8,15,25);

Q23- What are different Clauses used in SQL?

  • WHERE - WHERE condition is used to define the condition, extract and display only those records which fulfill the given condition
  • GROUP BY - GROUP BY is used with SELECT statement to group the result of the executed query using the value specified in it. It matches the value with the column name in tables and groups the end result accordingly.
  • HAVING - HAVING clause is used in association with GROUP BY clause. It is applied to the each group of result or the entire result as single group and much similar as WHERE clause, the only difference is you cannot use it without GROUP BY clause
  • ORDER BY - ORDER BY clause is to define the order of the query output either in ascending (ASC) or in descending (DESC) order. Ascending (ASC) is the default one but descending (DESC) is set explicitly.
  • USING - USING clause comes in use while working with SQL Joins. It is used to check equality based on columns when tables are joined. It can be used instead ON clause in Joins.

Clause Syntax Example

WHERE CLAUSE

SELECT  column_name(s) 
FROM table_name
WHERE [condition];

GROUP BY CLAUSE

SELECT  column_name(s)
FROM table_name
GROUP BY column_name;

HAVING CLAUSE

SELECT  column_name(s)
FROM table_name
GROUP BY column_name HAVING [condition];

ORDER BY CLAUSE

SELECT  column_name(s)
FROM table_name
WHERE [condition]
ORDER BY column_name ASC|DESC;

USING CLAUSE

SELECT  column_name(s)
FROM table_name
JOIN table_name
USING column_name;

Q24- Is it possible to order the records by two columns?

Yes; It is possible to order the records by using two columns.

Syntax Used to ORDER two colums

SELECT  * FROM table_name
ORDER BY column_name1, column_name2;

Q25- Why are SQL functions used?

  • Perform some calculations on the data
  • Modify individual data items
  • Manipulate the output
  • Format dates and numbers
  • Convert the data types

Q26- What is normalization?

Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of Normalization is to add, delete or modify field that can be made in a single table.

Q27- What are all the different normalizations?

  • 1NF - This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns.
  • 2NF - Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.
  • 3NF - This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints.
  • 4NF - Fourth normal form (4NF) is a level of database normalization where there are no non-trivial multivalued dependencies other than a candidate key. It builds on the first three normal forms (1NF, 2NF and 3NF) and the Boyce-Codd Normal Form (BCNF).

Q28- What is Denormalization.

Denormalization is an approach to speeding up read-oriented data retrieval performance in a relational database, where the database administrator selectively adds back specific instances of redundant data after the data structure has been normalized.

  • A denormalized database should not be confused with a database that has never been normalized.
  • Denormalization is a technique used to access the data from higher to lower normal forms of database. It is also process of introducing redundancy into a table by incorporating data from the related tables.

Q29- What is an Index?

An index is performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.

Q30- What is the different types of indexes in SQL and define them?

Unique Index - Prevents duplicate entries within uniquely indexed columns. They are automatically generated if a Primary Key is available.

Clustered Index - Used to organize or edit the arrangement within the table, with respect to the key value. Each table is only allowed to have a single clustered index only.

Non Clustered Index - NonClustered Index only manages the order of logic within entries. It does not manage the arrangement and tables can have multiple NonClustered Indexes.

Q31- What is UNION and UNION ALL keyword in SQL?

The UNION operator in SQL combines multiple sets highlighted in the SELECT statements. The restrictions of the set are:

  • Column number must be identical,
  • Data Types in the set must be the same,
  • The order of the column highlighted in the SELECT statement must be the same. It automatically eliminates duplicate rows within the results highlighted in the SELECT statement.

UNION ALL does the same function as the UNION, but it includes all, including the duplicate rows.

  • C1, C2, C3 & C4 → Column_Name
  • T1 & T2 → Table_Name
SELECT C1, C2 FROM T1 UNION SELECT C3, C4 FROM T2;

Q32- 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.

VIEW Syntax Example

CREATE VIEW view_name AS 
SELECT column_name(s)
FROM table_name
WHERE [condition];

Q33- What is distinct?

Distinct is used to supress duplicate row in select query More Details →

Q34- What is difference between nested subquery & Co-related sub query?

Nested Sub Query:

  • Nested subquery executes only once for the entire nesting query.
  • Nested subquery does not contain any reference to the outer query row.

Co-related Sub Query:

  • Co-rrelated subquery executes single time for every row which is selected by the outer query.
  • Co-rrelated subquery has a reference to a value from the row selected by the outer query.

Q35- What are the different types of queries in SQL?

These types of queries used in SQL explained below:

  • DDL - Data Definition Language
  • DML - Data Manipulation Language
  • DCL - Data Control Language

Data Definition Language - Allows to work with the Structure or Definition of the data. SQL commands: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME

Data Manipulation Language - It is a query that is used to insert, update, or delete the data from a database table. These queries include INSERT INTO, UPDATE, SELECT and DELETE queries

Data Control Language - It is use to controls the data. SQL Commands: GRANT, REVOKE, DENY. The administrative tasks include creation of user, partitioning of database, providing grants to the user, assigning roles to the user and creating users as well. More Details →

Q36- What are aliases in SQL?

Aliases in SQL are used to provide alternate names for columns and tables in SQL queries. It is used when name of column or table is used other than their original names, but the modified name is only temporary. Aliases are created to make table or column names more readable. The aliases help in resolving anonymity when trying to join tables. If two different tables contain the columns of same name, aliases help in selecting a specific column only in the select clause.

Q37- What are SQL functions?

SQL defines certain standard functions that can be used to perform mathematical manipulations. There are two types of SQL functions, aggregate functions, and scalar(non-aggregate) functions.

  • SQL Aggregate Function - Aggregate functions are Count, Sum, Avg, Max, Min functions
  • SQL Arithmetic Fuction - Arithmetic functions are abs(), ceil(), floor(), exp(), In(), mod(), power(), and sqrt()

Q38- What is a wildcard?

A wildcard character is used to substitute any other character(s) in a string. It is used with LIKE operator to provide a wildcard match of values More Details →

Q39- What is Difference between Truncate and Delete in SQL?

TRUNCATE DELETE
We can’t execute a trigger with TRUNCATE A trigger can be executed, DELETE command.
TRUNCATE is a Data Definition Language command DELETE is a Data Manipulation Language command.
TRUNCATE will not store data in rollback space and will directly delete it. When you use DELETE to delete the data, at that time it store the whole data in rollback space from where you can get the data back after deletion

Q40- What is Trigger?

  • A trigger is a special type of stored procedure that automatically executes when an event occurs in the database server.
  • A Trigger is a process of firing an action when some event like Insert, Update or Delete occurs.
  • Triggers can be defined on the table, view, schema, or database with which the event is associated.

Q41- What is ACID Properties?

ACID properties is combination of below mentions properties:

  • A - Atomicity
  • C - Consistency
  • I - Isolation
  • D - Durability

Q42- What are the Local variables in SQL?

The local variables are not referred by some other function OR out of the function; The local variables can be used just inside the same function, where the local variable placed.

Q43- What are Constraints?

Constraints can be specified while creating or altering the table statement.

Below mentioned list are SQL Constraints:

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

Q44- What are the Global variables in SQL?

The global variables are used throughout the program.

Q45- How many triggers can be applied to a table?

There are Three basics triggers properties applied to a table:

  • A Triggring Statement
  • A Restriction
  • An Action

Q46- How many drivers are in MySQL

Currently, The available drivers in MySQL: Mentioned in List

  • PHP Driver
  • CAP11PHP Driver
  • net5.mxj
  • JDBC Driver
  • ODBC Driver
  • C WRAPPER Driver
  • Python Driver
  • PERL Driver
  • Ruby Driver

Policy

GDATAMART.COM is optimized for learning & job information; The users try to improve your knowledge and practical experience through this site but we cannot warrant full correctness of all content... Learn More →

© 2020 GDATAMART | All Rights Reserved