SQL Flashcards

Question

What is a SQL operator?

Answer 1

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.

Answer 2

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

Answer 3

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: SELECT col_1 AS column FROM table_name;

Answer 4

A condition imposed on a SQL query to filter the data to obtain the desired result. Some examples are WHERE, LIMIT, HAVING, LIKE, AND, OR, ORDER BY, etc.

Answer 5

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

Answer 6

Using the CREATE TABLE statement. For example, to create a table with three columns of predefined datatypes, we apply the following syntax: CREATE TABLE table_name (col_1 datatype, col_2 datatype, col_3 datatype);

Answer 7

Using the UPDATE statement. The syntax is: UPDATE table_name SET col_1 = value_1, column_2 = value_2 WHERE condition;

Answer 8

Using the DROP TABLE statement. The syntax is: DROP TABLE table_name;.

Answer 9

Using the COUNT() aggregate function with the asterisk passed as its argument: SELECT COUNT(*) FROM table_name;.

Answer 10

Using the ORDER BY statement: SELECT * FROM table_name ORDER BY col_1;

Answer 11

Using the asterisk * with the SELECT statement. The syntax is: SELECT * FROM table_name;.

Answer 12

Using the INTERSECT statement: SELECT * FROM table_1 INTERSECT SELECT * FROM table_1;

Answer 13

This statement is used with the SELECT statement to filter out duplicates and return only unique values from a column of a table. The syntax is: SELECT DISTINCT col_1 FROM table_name;

Answer 14

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.

Answer 15

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.

Answer 16

SQL databases are relational, structured, and use tables with predefined schemas, while NoSQL databases are non-relational, schema-less, and designed to handle unstructured or semi-structured data.

Answer 17

Challenges include performance tuning for large datasets, managing indexing strategies, ensuring data integrity with constraints, handling concurrent transactions, and optimizing query execution.

Answer 18

A database object representing a set of SQL statements frequently used for a certain task. A function takes in some input parameters, performs calculations or other manipulations on them, and returns the result. Functions help improve code readability and avoid repetition of the same code snippets.

Answer 19

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. On the other hand, SQL functions can be built-in (defined by the system) or user-defined (created by the user for their specific needs).

Answer 20

AVG() – returns the average value SUM() – returns the sum of values MIN() – returns the minimum value MAX() – returns the maximum value COUNT() – returns the number of rows, including those with null values FIRST() – returns the first value from a column LAST()– returns the last value from a column

Answer 21

LEN() (in other SQL flavors – LENGTH()) – returns the length of a string, including the blank spaces 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) MID() (in other SQL flavors – SUBSTR()) – extracts a substring from a string ROUND() – returns the numerical value rounded to a specified number of decimals NOW() – returns the current date and time

Answer 22

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)

Answer 23

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

Answer 24

Local variables can be accessed only inside the function in which they were declared. Instead, global variables, being declared outside any function, are stored in fixed memory structures and can be used throughout the entire program.

Answer 25

SQL is a standard language for querying and managing relational databases, primarily used for data manipulation and retrieval. PL/SQL (Procedural Language/SQL) is an extension of SQL used in Oracle databases that includes procedural programming constructs like loops, conditions, and exception handling, allowing for complex business logic to be implemented within the database. We have another article on the Top 20 PL/SQL Interview Questions and Answers which is a good review if you know you will be asked about your knowledge of Oracle.

Answer 26

There is no difference between LEFT JOIN and LEFT OUTER JOIN. They are interchangeable. SQL allows the OUTER keyword to be optional, so LEFT JOIN is simply a shorthand for LEFT OUTER JOIN. Both return all records from the left table and the matching records from the right table.

Answer 27

Indexing creates a special data structure that speeds up data retrieval by allowing the database to find rows more efficiently. It works like an optimized lookup table, reducing the need for full table scans. However, excessive indexing can slow down insert, update, and delete operations due to the need for index maintenance.

Answer 28

A stored procedure is a precompiled set of SQL statements that can be executed as a single unit to perform a specific task. Unlike functions, stored procedures can return multiple result sets and modify database objects, while functions typically return a single value and cannot modify data.

Answer 29

By default, the order is ascending. To change it to descending, we need to add the DESC keyword as follows: SELECT * FROM table_name ORDER BY col_1 DESC;

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

Answer 31

The LIKE operator in combination with the % and _ wildcards. The % wildcard represents any number of characters including zero, while _ – strictly one character.

Answer 32

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.

Answer 33

The primary key of a table, based on multiple columns.

Answer 34

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

Answer 35

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

Answer 36

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.

Answer 37

Yes. This is also known as nested views. However, we should avoid nesting multiple views since the code becomes difficult to read and debug.

Answer 38

No. Any views based on that table will become invalid after deleting the base table. If we try to use such a view anyway, we'll receive an error message.

Answer 39

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

Answer 40

In some SQL flavors, such as PostgreSQL, the BOOLEAN data type exists explicitly and takes values TRUE, FALSE, or NULL. In other flavors, such as Microsoft SQL Server, the BIT datatype is used to store Boolean values as integers 1 (true) or 0 (false).

Answer 41

Normalization is a process of database design that includes organizing and restructuring data in a way to reduce data redundancy, dependency, duplication, and inconsistency. This leads to enhanced data integrity, more tables within the database, more efficient data access and security control, and greater query flexibility.

Answer 42

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.

Answer 43

Renaming a column means permanently changing its actual name in the original table. Giving an alias to a column means giving it a temporary name while executing an SQL query, with the purpose to make the code more readable and compact.

Answer 44

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. Instead, a non-correlated subquery doesn't rely on the data from the outer query and can be run independently of it.

Answer 45

While a clustered index defines the physical order of records of a table and performs data searching based on the key values, a non-clustered index keeps the order of records that do not match the physical order of the actual data on the disk. A table can have only one clustered index but many non-clustered ones.

Answer 46

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. The syntax is: CASE WHEN condition_1 THEN value_1 WHEN condition_2 THEN value_2 WHEN condition_3 THEN value_3 ... ELSE value END;

Answer 47

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.

Answer 48

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.

Answer 49

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.

Answer 50

Using the INSERT INTO statement in combination with VALUES. The syntax is: INSERT INTO table_name VALUES (value_1, value_2, ...);

Answer 51

Using the DELETE statement. The syntax is: DELETE FROM table_name WHERE condition;

Answer 52

Using the ALTER TABLE statement in combination with ADD. The syntax is: ALTER TABLE table_name ADD column_name datatype;

Answer 53

Using the ALTER TABLE statement in combination with RENAME COLUMN ... TO ... The syntax is: ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

Answer 54

Using the ALTER TABLE statement in combination with DROP COLUMN. The syntax is: ALTER TABLE table_name DROP COLUMN column_name;

Answer 55

By checking the remainder of the division by 2. In some SQL versions (e.g., PostgreSQL and My SQL), we use the MOD function, in the others (Microsoft SQL Server and SQLite) – the modulo operator (%). To select all even records using MOD: SELECT * FROM table_name WHERE MOD(ID_column, 2) = 0; To select all even records using %: SELECT * FROM table_name WHERE ID_column % 2 = 0;

Answer 56

Using the DISTINCT statement in combination with SELECT or creating a unique key for that table.

Answer 57

Using the INSERT INTO statement in combination with VALUES. The syntax is: INSERT INTO table_name VALUES (value_1, value_2, ...), (value_3, value_4, ...), (value_5, value_6, ...), ...;

Answer 58

Using the OFFSET clause. For example, to find the 6th highest value from a column, we would use the following syntax: SELECT * FROM table_name ORDER BY column_name DESC LIMIT 1 OFFSET 5;

Answer 59

Using the LIKE operator in combination with the % and _ wildcards. For example, we need to find all surnames in a table that start with "A". The query is: SELECT * FROM table_name WHERE surname LIKE 'A_'; Here, we assume that a surname must contain at least two letters. Without this assumption (meaning that a surname can be just A), the query is as follows: SELECT * FROM table_name WHERE surname LIKE 'A%';

Answer 60

Using the MAX() function. Otherwise, in many SQL versions, we can use the following syntax: SELECT id FROM table_name ORDER BY id DESC LIMIT 1; or in Microsoft SQL Server: SELECT TOP 1 id FROM table_name ORDER BY id DESC

Answer 61

Using the RAND() function in combination with ORDER BY and LIMIT. In some SQL flavors, such as PostgreSQL, it's called RANDOM(). For example, the following code will return five random rows from a table in MySQL: SELECT * FROM table_name ORDER BY RAND() LIMIT 5;

SQL Flashcards

Common SQL Interview Questions (85 cards)