SQL is the acronym for Structured Query Language. It is a domain-specific language that is designed to store, manipulate and retrieve data from relational database management systems (RDBMS). In this blog, we’ll discuss the top SQL interview questions asked in most interview processes. Let’s get started!
Top SQL Interview Questions:
What are the few databases that use SQL?
Database Management Systems (DBMS) like SQL Server, MySQL, PostgreSQL, Oracle, Sybase, MS Access, Informix use SQL as their standard database language.
Major functions that are achieved by SQL –
- Creation of new databases, tables, views
- Performing data manipulation such as Insert, Update and Delete on data
SQL scope pertains to:
- Data Query
- Data Manipulation (Insert, Update, Delete)
- Data Definition (Creation of Schemas, Schema Manipulation)
- Data Access Control
Sublanguages that are commonly in use with respect to databases are:
- Data Query Language (DQL): DQL statements are used to perform queries on data within the schema objects.
- SELECT statement is strictly a DQL example. In case added with WHERE, FROM clauses it becomes a Data Manipulation Language
- Data Manipulation Language (DML): Used for inserting, updating, deleting data in databases.
- INSERT INTO employees ( first_name, last_name) VALUES (‘kesar’,’kula’)
- Data Query Language (DQL): Main purpose of DQL is to get relation of schema based on the query provided.
- Data Definition Language (DDL): It’s a syntax to create and modify objects like, tables, users etc.
- Data Control Language (DCL): It is used to control access to data that is stored in a database. Basically, it’s the logical group in SQL Commands which is used for Authorization
- GRANT, REVOKE are the examples of DCL
Basic important SQL Commands:
- SELECT – Extracts data from the database
- UPDATE – Updates data in the database
- DELETE – Deletes data from the database
- CREATE TABLE – Creates a new table
- ALTER TABLE – Modify the table
- DROP TABLE – Deletes the table
- CREATE DATABASE – Creates new database
- ALTER DATABASE – Modify the database
- INSERT INTO – Insert’s data into database
- CREATE INDEX – To create an index
- DROP INDEX – Deletes an index
What is ORDER BY clause?
ORDER BY clause sorts data ascending and descending order based on one or multiple columns
Significance of DISTINCT keyword?
DISTINCT is used along with SELECT statement to fetch only unique records, eliminating all the duplicate records
Example : SELECT DISTINCT column1, column2, …
What is a view in SQL?
View is a composition of tables in the form of a SQL statement, meaning, it is a virtual table based on the result set of a SQL statement.
CREATE VIEW [view_name] AS
SELECT column1, coloum2
Constraints in SQL
Constraints are to specify rules relating the data in the table. Constraints are applied on Single or multiple fields in table either during creation or after creation using ALTER.
NOT NULL – Restricts insertion of NULL values into a column
DEFAULT – Assigns a default value automatically if no value has been assigned to a specific field
CHECK – Verifies all the values in a field if satisfies a given condition
INDEX – Used for faster retrieval of records
UNIQUE – Unique values are ensured in the fields
What is PRIMARY KEY?
It is a constraint which identifies each row in a table uniquely. It must contain only UNIQUE values and has NOT NULL constraint included. A table has only one primary key.
What is FOREIGN KEY?
It consists of a single or collection of fields in a table which refers to the PRIMARY KEY in another table.
Foreign key ensures integrity between two tables.
What is JOIN and types of JOINS.
SQL Join is used to combine records so we get data from more tables in a database based on the relationship between the columns in the tables.
There are four different types of JOINs:
INNER JOIN : This retrieves records having matching values in both the tables. It is extensively used for queries.
INNER JOIN tableB;
LEFT(OUTER) JOIN: This retrieves all the records from the left table that have matched in the right table.
FROM tableA a
LEFT JOIN tableB b
RIGHT(OUTER) JOIN: This retrieves all the records or rows from the right table having matching records in the left table
FROM tableA a
RIGHT JOIN tableB b
FULL(OUTER) JOIN: This retrieves all the records with match from either left or the right table
FROM tableA a
FULL JOIN tableB b
There are two more special joins:
SELF JOIN where table is joined to itself based on relation between its own columns.
CROSS JOIN which is a cartesian product of two tables included in the join statement. It will have the same number of rows as the cross product of two tables. Also if a WHERE clause is used in cross joint, the query would work same as n INNER JOIN
INDEX and its Types
Database index is a data structure that provides a quick lookup into the date of a column or multiple columns in a table. It helps in faster retrieval of data with an additional cost of writes and memory to maintain the indexes.
CREATE INDEX indexName ON tableName(column1, column2) ;
DROP INDEX indexName;
There are types of indexes used for multiple purposes in different scenarios:
UNIQUE and NON_UNIQUE INDEX
Unique index as the name suggests helps in data integrity which ensures that no two rows of data in a table will have identical key values.
Non-Unique indexes are used to solely improve the performance of the query by having sorted order of the data values that are frequently used.
CLUSTERED and NON-CLUSTERED INDEX
Clustered indexes are the ones whose row order in the database corresponds to the row order in the index. Hence there can be only one clustered index in the given table. Whereas multiple non clustered indexes can exist in a given table.
These cluster indexes help improve query performance by providing a linear access path to the data that is stored in the database.
Entities and Relationships:
Entity is a real world object which is easily identifiable.
Relationship: Relationships are a link between entities that have something to do with each other. Example – Employees in the Company.
Types of Relationships in SQL
- One-to-Many and Many-to-One
- Self reference relationships
This brings us to the end of the blog on the top SQL interview questions. We hope that you were able to gain valuable insights from the same. Happy learning and good luck!