SQL Questions II

Question 1

What is SQL?

Answer 1

It stands for Structured Query Language, and it’s a programming language used for interaction with relational database management systems (RDBMS). This includes fetching, updating, inserting, and removing data from tables.

Question 2

What are SQL dialects? Give some examples.

Answer 2

The various versions of SQL, both free and paid, are also called SQL dialects. All the flavors of SQL have a very similar syntax and vary insignificantly only in additional functionality. Some examples are Microsoft SQL Server, PostgreSQL, MySQL, SQLite, T-SQL, Oracle, and MongoDB.

Question 3

What are the main applications of SQL?

Answer 3

Using SQL, we can:

-> create, delete, and update tables in a database
-> access, manipulate, and modify data in a table
-> retrieve and summarize the necessary information from a table or several tables
-> add or remove certain rows or columns from a table

All in all, SQL allows querying a database in multiple ways. In addition, SQL easily integrates with other programming languages, such as Python or R, so we can use their combined power.

Question 4

What types of SQL commands (or SQL subsets) do you know?

Answer 4

Data Definition Language (DDL) – to define and modify the structure of a database.

Data Manipulation Language (DML) – to access, manipulate, and modify data in a database.

Data Control Language (DCL) – to control user access to the data in the database and give or revoke privileges to a specific user or a group of users.

Transaction Control Language (TCL) – to control transactions in a database.

Data Query Language (DQL) – to perform queries on the data in a database to retrieve the necessary information from it.

Question 5

What is a database?

Answer 5

A structured storage space where the data is kept in many tables and organized so that the necessary information can be easily fetched, manipulated, and summarized.

Question 6

What is DBMS, and what types of DBMS do you know?

Answer 6

It stands for Database Management System, a software package used to perform various operations on the data stored in a database, such as accessing, updating, wrangling, inserting, and removing data. There are various types of DBMS, such as relational, hierarchical, network, graph, or object-oriented. These types are based on the way the data is organized, structured, and stored in the system.

Question 7

What is RDBMS? Give some examples of RDBMS.

Answer 7

It stands for Relational Database Management System. It’s the most common type of DBMS used for working with data stored in multiple tables related to each other by means of shared keys. The SQL programming language is designed to interact with RDBMS. Some examples of RDBMS are MySQL, PostgreSQL, Oracle, MariaDB, etc.

Question 8

What types of SQL subqueries do you know?

Answer 8

Single-row – returns at most one row.
Multi-row – returns at least two rows.
Multi-column – returns at least two columns.
Correlated – a subquery related to the information from the outer query.
Nested – a subquery inside another subquery.

Question 9

What is an SQL query, and what types of queries do you know?

Answer 9

A query is a piece of code written in SQL to access or modify data from a database.

There are two types of SQL queries: select and action queries. The first ones are used to retrieve the necessary data (this also includes limiting, grouping, ordering the data, extracting the data from multiple tables, etc.), while the second ones are used to create, add, delete, update, rename the data, etc.

Question 10

What is a constraint, and why use constraints?

Answer 10

A set of conditions defining the type of data that can be input into each column of a table. Constraints ensure data integrity in a table and block undesired actions.

Question 11

What SQL constraints do you know?

Answer 11

DEFAULT – provides a default value for a column.
UNIQUE – allows only unique values.
NOT NULL – allows only non-null values.
PRIMARY KEY – allows only unique and strictly non-null values (NOT NULL and UNIQUE).
FOREIGN KEY – provides shared keys between two or more tables.

Question 12

What is a join?

Answer 12

A clause used to combine and retrieve records from two or multiple tables. SQL tables can be joined based on the relationship between the columns of those tables. Check out our SQL joins tutorial for more context, plus our dedicated guide to SQL joins interview questions.

Question 13

What types of joins do you know?

Answer 13

(INNER) JOIN – returns only those records that satisfy a defined join condition in both (or all) tables. It’s a default SQL join.

LEFT (OUTER) JOIN – returns all records from the left table and those records from the right table that satisfy a defined join condition.

RIGHT (OUTER) JOIN – returns all records from the right table and those records from the left table that satisfy a defined join condition.

FULL (OUTER) JOIN – returns all records from both (or all) tables. It can be considered as a combination of left and right joins.

Question 14

What is a primary key?

Answer 14

A column (or multiple columns) of a table to which the PRIMARY KEY constraint was imposed to ensure unique and non-null values in that column. In other words, a primary key is a combination of the NOT NULL and UNIQUE constraints. The primary key uniquely identifies each record of the table. Each table should contain a primary key and can’t contain more than one primary key.

Question 15

What is a unique key?

Answer 15

A column (or multiple columns) of a table to which the UNIQUE constraint was imposed to ensure unique values in that column, including a possible NULL value (the only one).

Question 16

What is a foreign key?

Answer 16

A column (or multiple columns) of a table to which the FOREIGN KEY constraint was imposed to link this column to the primary key in another table (or several tables). The purpose of foreign keys is to keep connected various tables of a database.

Question 17

What is an index?

Answer 17

A special data structure related to a database table and used for storing its important parts and enabling faster data search and retrieval. Indexes are especially efficient for large databases, where they significantly enhance query performance.

Question 18

What types of indexes do you know?

Answer 18

Unique index – doesn’t allow duplicates in a table column and hence helps maintain data integrity.

Clustered index – defines the physical order of records of a database table and performs data searching based on the key values. A table can have only one clustered index.

Non-clustered index – keeps the order of the table records that don’t match the physical order of the actual data on the disk. It means that the data is stored in one place and a non-clustered index – in another one. A table can have multiple non-clustered indexes.

Question 19

What is a schema?

Answer 19

A collection of database structural elements such as tables, stored procedures, indexes, functions, and triggers.

It shows the overall database architecture, specifies the relationships between various objects of a database, and defines different access permissions for them. Read our database schema guide for a deeper understanding.

Question 20

What is a SQL comment?

Answer 20

A human-readable clarification of what a particular piece of code does. SQL code comments can be single-line (preceded by a double dash –) or span over multiple lines (as follows: /comment_text/). When the SQL engine runs, it ignores code comments. The purpose of adding SQL code comments is to make the code more comprehensive for those people who will read it in the future.

Question 21

What is a SQL operator?

Answer 21

A reserved character, a combination of characters, or a keyword used in SQL queries to perform a specific operation. SQL operators are commonly used with the WHERE clause to set a condition (or conditions) for filtering the data.

Arithmetic (+, -, *, /, etc.)
Comparison (>, <, =, >=, etc.)
Compound (+=, -=, *=, /=, etc.)
Logical (AND, OR, NOT, BETWEEN, etc.)
String (%, _, +, ^, etc.)
Set (UNION, UNION ALL, INTERSECT, and MINUS (or EXCEPT))

Question 22

What is an alias?

Answer 22

A temporary name given to a table (or a column in a table) while executing a certain SQL query. Aliases are used to improve the code readability and make the code more compact. An alias is introduced with the AS keyword:

Question 23

What are some common statements used with the SELECT query?

Answer 23

The most common ones are FROM, GROUP BY, JOIN, WHERE, ORDER BY, LIMIT, and HAVING.

Question 24

What are entities? Give some examples.

Answer 24

An entity is a real-world object, creature, place, or phenomenon for which the data can be gathered and stored in a database table.

Each entity corresponds to a row in a table, while the table’s columns describe its properties. Some examples of entities are bank transactions, students in a school, cars sold, etc.

For example, if you use an E-commerce site for purchasing a product, you are represented as an Entity in DBMS, which has attributes such as Customer ID, Name, Country, Phone Number, etc. An** Entity can be** a Tangible Entity (real-world object) for example, a car, or a non-real-world object such as a user account.

Question 25

What are relationships? Give some examples.

Answer 25

Relationships are the connections and correlations between entities, basically meaning how two or more tables of a database are related to one another. For example, we can find an ID of the same client in a table on sales data and in a customer table.

Question 26

What is NULL value? How is it different from zero or a blank space?

Answer 26

A NULL value indicates the absence of data for a certain cell of a table. Instead, zero is a valid numeric value, and an empty string is a legal string of zero length.

Question 27

What types of SQL functions do you know?

Answer 27

Aggregate functions – work on multiple, usually grouped records for the provided columns of a table, and return a single value (usually by group).

Scalar functions – work on each individual value and return a single value.

Question 28

What are case manipulation functions? Give some examples.

Answer 28

Case manipulation functions represent a subset of character functions, and they’re used to change the case of the text data. With these functions, we can convert the data into the upper, lower, or title case.

UCASE() (in other SQL flavors – UPPER()) – returns a string converted to the upper case

LCASE() (in other SQL flavors – LOWER()) – returns a string converted to the lower case

INITCAP() – returns a string converted to the title case (i.e., each word of the string starts from a capital letter)

Question 29

What are character manipulation functions? Give some examples.

Answer 29

Character manipulation functions represent a subset of character functions, and they’re used to modify the text data.

CONCAT() – joins two or more string values appending the second string to the end of the first one

SUBSTR() – returns a part of a string satisfying the provided start and end points

LENGTH() (in other SQL flavors – LEN()) – returns the length of a string, including the blank spaces

REPLACE() – replaces all occurrences of a defined substring in a provided string with another substring

INSTR() – returns the numeric position of a defined substring in a provided string

LPAD() and RPAD() – return the padding of the left-side/right-side character for right-justified/left-justified value

TRIM() – removes all the defined characters, as well as white spaces, from the left, right, or both ends of a provided string

Question 30

What set operators do you know?

Answer 30

UNION – returns the records obtained by at least one of two queries (excluding duplicates)

UNION ALL – returns the records obtained by at least one of two queries (including duplicates)

INTERSECT – returns the records obtained by both queries

EXCEPT (called MINUS in MySQL and Oracle) – returns only the records obtained by the first query but not the second one

Question 31

What is the difference between a primary key and a unique key?

Answer 31

While both types of keys ensure unique values in a column of a table, the first one uniquely identifies each record of the table, and the second one prevents duplicates in that column.

Question 32

What is the order of appearance of the common statements in the SELECT query?

Answer 32

SELECT – FROM – JOIN – ON – WHERE – GROUP BY – HAVING – ORDER BY – LIMIT

Question 33

In which order the interpreter executes the common statements in the SELECT query?

Answer 33

FROM – JOIN – ON – WHERE – GROUP BY – HAVING – SELECT – ORDER BY – LIMIT

Question 34

What is a view, and why use it?

Answer 34

A virtual table containing a subset of data retrieved from one or more database tables
(or other views). Views take very little space, simplify complex queries, limit access to the data for security reasons, enable data independence, and summarize data from multiple tables.

Question 35

What types of SQL relationships do you know?

Answer 35

One-to-one – each record in one table corresponds to only one record in another table

One-to-many – each record in one table corresponds to several records in another table

Many-to-many – each record in both tables corresponds to several records in another table

Question 36

What is denormalization in SQL, and why use it?

Answer 36

Denormalization is the process opposite of normalization: it introduces data redundancy and combines data from multiple tables. Denormalization optimizes the performance of the database infrastructure in situations when read operations are more important than write operations since it helps avoid complex joins and reduces the time of query running.

Question 37

What is the difference between nested and correlated subqueries?

Answer 37

A correlated subquery is an inner query nested in a bigger (outer) query that refers to the values from the outer query for its execution, meaning that a correlated subquery depends on its outer query.

opiera się na danych z zewnątrznej subquery

Instead, a non-correlated subquery doesn’t rely on the data from the outer query and can be run independently of it.

Question 38

What is the CASE() function?

Answer 38

The way to implement the if-then-else logic in SQL.

This function sequentially checks the provided conditions in the WHEN clauses and returns the value from the corresponding THEN clause when the first condition is satisfied. If none of the conditions is satisfied, the function returns the value from the ELSE clause in case it’s provided, otherwise, it returns NULL.

Question 39

What is the difference between the DELETE and TRUNCATE statements?

Answer 39

DELETE is a reversible DML (Data Manipulation Language) command used to delete one or more rows from a table based on the conditions specified in the WHERE clause.

Instead, TRUNCATE is an irreversible DDL (Data Definition Language) command used to delete all rows from a table.

DELETE works slower than TRUNCATE. Also, we can’t use the TRUNCATE statement for a table containing a foreign key.

Question 40

What is the difference between the DROP and TRUNCATE statements?

Answer 40

DROP deletes a table from the database completely, including the table structure and all the associated constraints, relationships with other tables, and access privileges.

TRUNCATE deletes all rows from a table without affecting the table structure and constraints. DROP works slower than TRUNCATE. Both are irreversible DDL (Data Definition Language) commands.

Question 41

What is the difference between the HAVING and WHERE statements?

Answer 41

The first one works on aggregated data after they are grouped, while the second one checks each row individually. If both statements are present in a query, they appear in the following order: WHERE – GROUP BY – HAVING. The SQL engine interprets them also in the same order.