Top-30 SQL questions in 2020 interviews that you should know

Top-30 SQL questions in 2020 interviews that you should know

Relational databases are among the most frequently used databases to date, and therefore SQL skills are mandatory for most positions.

In this article with SQL interview questions I will introduce you to the most frequently asked questions on SQL (Structured Query Language).

This article is an ideal guide for learning all concepts related to SQL, Oracle, MS SQL Server and MySQL database.

Our SQL Queries article is a universal resource where you can accelerate your interview preparation. It consists of a set of 30 most common questions that the interviewer can ask during the interview.

It usually starts with basic SQL questions and then moves on to more complex questions based on the discussion and your answers. These SQL questions from the interviews will help you get the most out of it at different levels of understanding.
Let’s get started!

WHAT IS THE DIFFERENCE BETWEEN DELETE AND TRUNCATE OPERATORS?

TRUNCATE: Used to delete a row in the table, You cannot restore data (note: operations are logged differently, but SQL Server has an option to rollback), DDL command, Hurry up.

WHAT ARE THE SUBSETS OF SQL?

WHAT IS MEANT BY DBMS? WHAT TYPES OF DBMS DO EXIST?

There are two types of DBMS:

  • Relational Database Management System: Data is stored in relationships (tables). An example is MySQL.
  • Non-relational Database Management System: There is no concept of relations, tuples or attributes. Example — Mongo.

WHAT IS MEANT BY TABLE AND FIELD IN SQL?

Table: Student_Information
Field: Stu_Id, Stu_Name, Stu_Marks

WHAT ARE SQL CONNECTIONS?

  • Inner Join
  • Right Join
  • Left Join
  • Full Join

WHAT IS THE DIFFERENCE BETWEEN THE CHAR AND VARCHAR DATA TYPE IN SQL?

For example, Char(10) can only store 10 characters and cannot store a string of any other length, while varchar(10) can store a string of any length up to 10, i.e. 6, 8 or 2.

WHAT IS A PRIMARY KEY?

  • Primary key — a column or set of columns that uniquely identifies each row in the table.
  • Unambiguously identifies one row in the table.
  • Zero (Null) values are not allowed.

Example: In the Student table, the Stu_ID is the primary key.

WHAT ARE CONSTRAINTS?

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

WHAT IS THE DIFFERENCE BETWEEN SQL AND MYSQL?

WHAT IS A UNIQUE KEY?

  • Many unique keys in one table are acceptable.
  • NULL values are allowed (note: it depends on the DBMS, in SQL Server a NULL value can be added only once in the UNIQUE KEY field).

WHAT IS A FOREIGN KEY?

  • The foreign key in the child table refers to the primary key in the parent table.
  • Restricting the foreign key prevents actions that break the connection between the child and parent tables.

WHAT IS MEANT BY DATA INTEGRITY?

WHAT IS THE DIFFERENCE BETWEEN CLUSTERED AND NON-CLUSTERED INDICES IN SQL?

  • A cluster index is used to easily and quickly extract data from a database, while reading from an unclassified index is relatively slower.
  • Clustered index changes the way the records are stored in the database — it sorts the rows by column, which is set as a clustered index, while in non-clustered index it does not change the way of storage, but creates a separate object inside the table, which points to the source rows of the table when searching.
  • One table can have only one clustered index, while it can have many unclustered indexes.

WRITE AN SQL QUERY TO DISPLAY THE CURRENT DATE

LIST THE CONNECTION TYPES

Inner Join: In MySQL, this is the most common type. It is used to return all rows from multiple tables for which a connection condition is met.

Left Join: In MySQL, it returns all rows from the left (first) table and only the matching rows from the right (second) table for which the connection condition is met.

Right Join: in MySQL, it returns all rows from the right (second) table and only the matching rows from the left (first) table for which the connection condition is executed.

Full Join: returns all records that have a match in any of the tables. Consequently, it returns all rows from the left table and all rows from the right table.

WHAT DO YOU MEAN BY DENORMALIZATION?

WHAT ARE ENTITIES AND RELATIONSHIPS?

Relationships: relations or connections between entities that have some kind of relation to each other. For example — the client’s name is associated with the client’s account number and contact information, which may be in the same table. There may also be relationships between separate tables (e.g. client to account).

WHAT IS AN INDEX?

DESCRIBE THE DIFFERENT TYPES OF INDEXES

  • Unique Index: This index does not allow a field to have repetitive values if the column is indexed uniquely. If the primary key is defined, the unique index can be applied automatically.
  • Clustered Index: This index changes the physical order of the table and performs a search based on the key values. Each table can have only one Clustered Index.
  • Non-Clustered Index: This index does not change the physical order of the table and maintains the logical data order. Each table can have many unclustered indexes.

WHAT IS NORMALIZATION AND WHAT ARE ITS ADVANTAGES?

  • Better database organization
  • More tables with small rows
  • Effective access to data
  • More flexibility for requests
  • Quick search for information
  • It’s easier to implement data security
  • Allows for easy modification
  • Reducing redundancy and duplication of data
  • Smaller database
  • Provides consistency of data after changes are made

WHAT IS THE DIFFERENCE BETWEEN DROP AND TRUNCATE COMMANDS?

EXPLAIN THE DIFFERENT TYPES OF NORMALIZATION

  • The first normal form (1NF) — there are no repetitive groups in the lines
  • The second normal form (2NF) — each non-key (supporting) column value depends on the whole primary key
  • Third normal form (3NF) — each non-key value depends only on the primary key and does not depend on another non-key column value

WHAT IS THE ACID PROPERTY IN THE DATABASE?

  • Atomicity. Guarantees that the transaction will be fully executed or fail, where the transaction represents a single logical data transaction. This means that if one part of any transaction fails, the entire transaction fails and the database state remains unchanged.
  • Consistency. Guarantees that the data must comply with all validation rules. Simply put, you can say that your transaction will never leave your database in an unacceptable state.
  • Isolation. The main purpose of isolation is to control the mechanism of parallel data change.
  • Longevity. Longevity means that if a transaction is validated (COMMIT), the changes that occur within the transaction will be preserved regardless of what may get in their way (e.g. power loss, failure or errors of any kind).

WHAT DO YOU MEAN BY “TRIGGER” IN SQL?

WHICH OPERATORS ARE AVAILABLE IN SQL?

  • Arithmetic Operators
  • Logical Operators
  • Comparator operators

DO THE VALUES OF NULL MATCH THE VALUES OF ZERO OR SPACE?

WHAT IS THE DIFFERENCE BETWEEN A CROSS JOIN AND A NATURAL JOIN?

WHAT IS A SUBQUERY IN SQL?

WHAT ARE THE TYPES OF SUBQUERIES?

  • A correlated subquery is a query that selects data from a table with a reference to an external query. It is not considered an independent query because it refers to another table or column in the table.
  • Uncorrected subquery: This query is an independent query in which the output data of the subquery is substituted for the main query.

LIST THE WAYS TO GET THE NUMBER OF RECORDS IN A TABLE?

SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

WISSENSCHAFTSCOACHING: BERATUNG, COACHING, LEKTORAT, ÜBERSETZUNG