Best SQL Interview Practice Questions

So you’ve landed the interview, now all you need to do is prepare. Below we will go through some of the best SQL Interview Practice Questions to get you brushed up and ready to ace that interview. These questions are sourced from real-world interviews with tech giants like Google, Oracle, Amazon, and Microsoft.

• A. SQL Basics
• B. SQL Databases
• C. SQL Keys
• D. SQL Joins
• E. SQL Commands
• F. SQL Relationships
• G. Normalization & Denormalization
• H. SQL Indexing
• I. OLTP & OLAP
• J. PostgresSQL

A. SQL Basics

1. What is SQL?

Standard Query Language or SQL is the standard language used for accessing and manipulating databases, especially relational databases where data is organized into rows and columns.

2. What is the difference between SQL and MySQL?

SQL is a standard language for retrieving and manipulating structured databases. MySQL on the other hand is a relational database management system, like SQL Server, Oracle, or IBM DB2, that is used to manage SQL databases.

3. What is a Query?

To put it simply, a request for data from a database. There are two types of database queries; select query and action query.

1-- Select Query
2SELECT id, neighbourhood
3FROM airbnb_listings
4WHERE price < 150;
5
6-- Action Query
7UPDATE airbnb_listings
8SET price = 200
9WHERE id = 2595;

5. What is a Sub-query?

A query placed inside of another query, it can also be referred to as a nested query or inner query. It is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved allowing us to target specific rows to preform various operations.

For example using two tables 'contacts' and 'students' we want to retrieve details of students using a sub-query.

1SELECT name, email, dob, address
2FROM myDb.contacts
3WHERE roll_no IN (
4 SELECT roll_no
5 FROM myDb.students
6 WHERE subject = 'Maths');

4. What is the difference between WHERE and HAVING?

Since the WHERE clause comes before theGROUP BY clause, it cannot be used for filtering data in conjunction with aggregate functions, it can only be used to filter data in each row.

The HAVING clause is used to reduce the number of rows after filtering data with the GROUP BY clause. The HAVING clause filters data in each group of data formed by the GROUP BY clause rather than each row. It follows the GROUP BY clause and precedes the ORDER BY clause.

1# ERROR
2SELECT name, COUNT(orders) AS total_orders
3FROM purchases
4WHERE COUNT(orders) > 20
5GROUP BY 1
6ORDER 2 DESC;
7
8# CORRECT
9SELECT name, COUNT(orders) AS total_orders
10FROM purchases
11GROUP BY 1
12HAVING COUNT(orders) > 20
13ORDER 2 DESC;

B. SQL Databases

1. What is a Database?

Is an organized collection of structured information, or data, typically stored electronically in a computer system.

2. What is DBMS?

Database Management System (DBMS) is a software that is used to define, create, and maintain a database and provides controlled access to the data. It ensures that our data is consistent, organized, and is easily accessible by serving as an interface between the database and its end-users or application software.

3. What is RDBMS?

Relational Database Management System (RDBMS) is a software used to store, manage, query, and retrieve data stored in a relational database, a structured database which organizes information into tables (relations), rows and columns. Most modern database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2, and Amazon Redshift are based on RDBMS.

4. What is the difference between a DBMS and RDBMS?

RDBMS is a more advanced version of DBMS as it stores data in the form of a collection of tables, and relations can be defined between the common fields of these tables. DBMS on the other hand, stores data as file.

5. What are Tables and Fields?

A table is an organized collection of data stored in the form of rows and columns. Columns, also known as fields, can be categorized as vertical and rows, also known as records, as horizontal.

6. What is Data Integrity?

The is the overall accuracy, completeness, and consistency of data. It also refers to the safety of data in regard to regulatory compliance, such as GDPR compliance and security. It is maintained by a collection of processes, rules, and standards implemented during the design phase.

7. What are Constraints in SQL?

Constraints are used in SQL to specify the rules concerning data in the table. These constraints can be applied for single or multiple fields in an SQL table either during the creation of the table or after creating using the ALTER TABLE command.

C. SQL Keys

1. What is a Primary Key?

The PRIMARY KEY is a constraint that uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint. Tables in SQL can have one and only one primary key, which is comprised of single or multiple fields (columns).

When creating a table:

1-- Create table with single field as PK
2CREATE TABLE customers( 	
3   id INT NOT NULL,
4   name VARCHAR(255),
5   PRIMARY KEY (id)
6);
7
8-- Create table with multiple fields as PK
9CREATE TABLE customers( 
10   id INT NOT NULL,
11   surname VARCHAR(255),
12   name VARCHAR(255) NOT NULL,
13   CONSTRAINT PK_Customer,
14   PRIMARY KEY (id, name)
15  );

When modifying a table:

1-- Set single field as PK
2ALTER TABLE customers
3ADD PRIMARY KEY (id);
4
5-- Set multiple fields as PK
6ALTER TABLE customers 
7ADD CONSTRAINT PK_customer 
8PRIMARY KEY (id, name);

2. What is a Foreign Key?

A FOREIGN KEY comprises of single or collection of fields in a table that essentially refers to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.

The table with the FOREIGN KEY constraint is labelled as the child table, and the table containing the candidate key is labelled as the referenced or parent table.

When creating a table:

1-- Create table with a single field as unique
2CREATE TABLE Students (  
3   ID INT NOT NULL,
4   Name VARCHAR(255),
5   LibraryID INT,
6   PRIMARY KEY (ID)self
7   FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID)
8);

When modifying a table:

1ALTER TABLE Students   
2ADD FOREIGN KEY (LibraryID)
3REFERENCES Library (LibraryID);

D. SQL Joins

1. What is a Join? List its different types.

A JOIN clause is used to combine rows from two or more tables. The four types of JOIN are INNER, LEFT, RIGHT and FULL. The join command in sql is used to join related data stored in one or more tables. You can join one table to another, to fetch combined results.

A. INNER JOIN

INNER JOIN selects records that have matching values in both tables.

1SELECT name
2FROM customers
3INNER JOIN orders
4ON customers.customer_id = orders.customer_id;

B. LEFT JOIN

LEFT JOIN selects records from the left table that match records in the right table. In the below example the left table is customers.

1SELECT name
2FROM customers
3LEFT JOIN orders
4ON customers.customer_id = orders.customer_id;

C. RIGHT JOIN

RIGHT JOIN selects records from the right table that match records in the left table. In the below example the right table is orders.

1SELECT name
2FROM customers
3RIGHT JOIN orders
4ON customers.customer_id = orders.customer_id;

D. FULL JOIN

FULL JOIN selects records that have a match in the left or right table. Think of it as the OR join.

1SELECT name
2FROM customers
3FULL OUTER JOIN orders
4ON customers.customer_id = orders.customer_id;

2. What is the difference between a LEFT and RIGHT JOIN?

A LEFT JOIN returns all of the rows from the left table and fills the missing values from the right table with NULL values.

A RIGHT JOIN is the inverse of a left join in that it returns all of the rows from the right table while filling the missing values from the left table with NULL values.

3. What is a Self Join?

While most JOINs connect two or more tables to present their data together, a self-join links a table to itself based on its own column(s).

A self-join uses the INNER JOIN or LEFT JOIN clause, and a table alias is used to assign different names to the table within the query.

Let's look at an actual interview question to see this in practice.

Question Level: Hard

Amazon is comparing the monthly wages of their employees in each department to those of their managers and co-workers.

Create a table that compares an employee's salary to that of their manager and to the average salary of their department.

Note: Since managers are their own manager they should be removed from the employees averages as not to skew the results.

Before we get to the solution, consider the following scenario: we have need to create two tables from the table employee to separate the employees from their managers. In our solution we will refer to the employee table as a and the manager table as b.

1SELECT a.department,
2       a.id AS employee_id,
3       a.salary AS employee_salary,
4       b.salary AS manager_salary,
5       ROUND(AVG(a.salary) OVER(PARTITION BY a.department)) AS avg_employee_salary
6FROM employee a
7JOIN employee b 
8ON a.manager_id = b.id
9AND a.id <> b.id
10GROUP BY 1, 2, 3, 4
11ORDER BY 1, 3 DESC;

Result

So by using a self join we can connect the table employee to itself and using a Window Function we can get the AVG salary of employees.

4. What is a Cross-Join?

Cross join also known as a Cartesian product is a table in which each row of the left table joins with each row of the right table, resulting in all possible ordered pairs.

Looking at the two tables below, we can cross join them to produce the table on the right.

E. SQL Commands

1. What is the SELECT statement?

You use the SELECT command almost every time you query data with SQL. It allows you to define what data you want your query to return. By using the SELECT command with an asterisk (*) all of the columns in the table airbnb_listings are returned.

1SELECT * FROM airbnb_listings;

Using the following command we can find out all the neighbourhoods we have listings in.

1SELECT neighbourhood_group
2FROM airbnb_listings;

However you will notice there are duplicate results. To overcome this we use the SELECT DISTINCT command which will eliminate duplicates and only return distinct data.

1SELECT DISTINCT neighbourhood_group
2FROM airbnb_listings;

2. What are some common clauses used with SELECT query in SQL?

The SQL SELECT statement is commonly used together with:

• WHERE clause to filter records based on specific conditions

1SELECT name, neighbourhood, price 
2FROM airbnb_listings
3WHERE price > 300;

• ORDER BY clause to sort the records on some fields in ascending (ASC) or descending (DESC) order

1SELECT name, neighbourhood, price FROM airbnb_listings
2WHERE price > 300
3ORDER BY price DESC;

• GROUP BY clause to group records with identical data and can be used with an aggregation function to produce a summarized result from the database

1SELECT COUNT(id), neighbourhood
2FROM airbnb_listings
3WHERE price > 100
4GROUP BY neighbourhood;

• HAVING clause to filter records in combination with the GROUP BY clause (NOTE: HAVING is different to WHERE since WHERE cannot filter aggregated records)

1SELECT COUNT(id), neighbourhood
2FROM airbnb_listings
3WHERE price > 100
4GROUP BY neighbourhood
5HAVING COUNT(id) > 5;

3. What are UNION, MINUS and INTERSECT commands?

Firstly for these commands to execute they must meet the following criteria:

• Each SELECT statement within the class must have the same number of columns
• The columns must have similar data types
• The columns in each SELECT statement should necessarily have the same order

UNION

This command is used to combine and return the result-set retrieved by two or more SELECT statements

1-- Fetches the union of queries
2SELECT name FROM Students  
3UNION
4SELECT name FROM Contacts;
5-- Fetch the union of queries with duplicates
6SELECT name FROM Students   
7UNION ALL
8SELECT name FROM Contacts;

MINUS

This command is used to remove duplicates from the result-set obtained by the second SELECT query from the result-set obtained by the first SELECT query and then return the filtered results from the first.

1--  Fetch names from students
2SELECT name FROM Students   /* */
3MINUS     /* that aren't present in contacts */
4SELECT name FROM Contacts;

INTERSECT

This command is used to combine the result-set fetched by the two SELECT statements where records from one match the other and then returns this intersection of result-sets.

1-- Fetch names from students
2SELECT name FROM Students   
3INTERSECT    /* that are present in contacts as well */
4SELECT name FROM Contacts;

Let's look at an actual interview question to see this in practice.

Question Level: Easy

Instagram has requested that you rank users from highest to lowest based on the number of messages they both send and receive.

In our solution we can join both queries using UNION ALL while renaming the column sender and reciever to the same column user_i.

1WITH cte AS
2    (SELECT id, sender AS user_i
3    FROM messages
4    UNION ALL
5    SELECT id, reciever AS user_i
6    FROM messages)
7	
8SELECT user_i, COUNT(id) AS msg_total 
9FROM cte
10GROUP BY 1
11ORDER BY 2 DESC;

Result

4. What is the difference between UNION and UNION ALL?

The UNION and UNION ALL operators both combine rows from result sets into a single result set. However:

• The UNION operator removes eliminate duplicate rows
• The UNION ALL operator does not.

Thus, the UNION ALL operator query runs faster since it does not remove duplicate rows.

5. What is an Alias in SQL?

Aliases are used in SQL to give a table, or a column in a table, a temporary name. They are often used to make column names more readable and only exist for the duration of that query. An Alias is created using the keyword AS. Let’s imagine we have two tables ‘employee A’ and ‘employee B’ with a common column.

1SELECT A.emp_name AS "Employee"  /* Alias using AS keyword */
2B.emp_name AS "Supervisor"
3FROM employee A, employee B   /* Alias without AS keyword */
4WHERE A.emp_sup = B.emp_id;

As seen from above using AS is not compulsory.

1FROM employee A, employee B   /* Alias without AS keyword */
2WHERE A.emp_sup = B.emp_id;

6. What is a View?

A view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. A view is created with the CREATE VIEW statement.

1CREATE VIEW customer_maria AS
2SELECT id, name, price
3FROM airbnb_listings
4WHERE price < 100;

7. What are the TRUNCATE, DELETE and DROP statements?

• TRUNCATE statements are used to remove all data entries from a table in a database while keeping the table and structure in place.

1TRUNCATE TABLE airbnb_listings;  

• DELETE statements Are used to delete rows from our table. To remove data we can simply use the DELETE command based on conditions specified with the WHERE command.

1DELETE FROM airbnb_listings
2WHERE id = 1001;

• DROP statements are used to delete a database or table. Before running this command you should be aware that it will delete your whole table, including all data, indexes, and more, so make sure you are certain before you run.

1DROP TABLE room_details;

To delete a specific column we can combine the ALTER TABLE command with the DROP command.

1ALTER TABLE airbnb_listings 
2DROP COLUMN listings_reserved;

8. What is the difference between DROP and TRUNCATE statements?

When a DROP statement is used the table is dropped, including all things associated with the table. This includes the relationships defined on the table with other tables, the integrity checks and constraints, access privileges and other grants that the table has. To create and use the table again in its original form, all these relations, checks, constraints, privileges, and relationships need to be redefined.

However, when a TRUNCATE statement is used and the table is truncated, none of the above problems exist and the table retains its original structure.

9. What is the difference between DELETE and TRUNCATE statements?

The TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

The DELETE command deletes only the rows from the table based on the condition given in the WHERE clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.

10. What is the importance of the TRUNCATE statement?

By using the TRUNCATE TABLE statement, data is removed efficiently and quickly from the table. This statement can also be used to reset values of the identity columns along with data clean-up.

1TRUNCATE TABLE name_of_table 
2RESTART IDENTITY;

This statement can also remove data from multiple tables all at once by mentioning the table names separated by comma.

1TRUNCATE TABLE 
2   table_1, 
3   table_2,
4   table_3;

11. What is the main disadvantage of deleting data from an existing table using the DROP

DROP TABLE command deletes complete data from the table along with removing the complete table structure too. If we just want to remove the data, but not have to recreate the table use the TRUNCATE command.

12. Differentiate between COMMIT and CHECKPOINT.

The COMMIT action ensures that the data consistency of the transaction is maintained, and it ends the current transaction in the section. COMMIT adds a new record in the log that describes the COMMIT to the memory.

A CHECKPOINT on the other hand is used for writing all changes that were committed to disk up to SCN that will be kept in the control file and datafile headers. It guarantees the consistency of the database.

13. How to create empty tables with the same structure as another table?

This can be achieved by fetching the records of one table and placing them into a new table using the INTO operator while fixing a WHERE clause to be false for all records. SQL will prepare the new table with a duplicate structure to accept the fetched records but since no records get fetched due to the WHERE clause in action, nothing is inserted into the new table.

1SELECT* INTO airbnb_listings_copy
2FROM airbnb_listings WHERE 1 = 2;

14. What is Pattern Matching in SQL?

We can search for patterns within our data using the LIKE command. This is useful when you are not sure what exactly it is you are searching for. This kind of SQL query uses wildcards to match a string pattern, rather than writing the exact word. Suppose you want to find listings with country in the title, the code below will achieve this.

1SELECT name
2FROM airbnb_listings
3WHERE name LIKE ‘%country%’;

Other operators for LIKE:

• %x — will select all values that begin with x
• %x% — will select all values that include x
• x% — will select all values that end with x
• x%y — will select all values that begin with x and end with y
• _x% — will select all values have x as the second character
• x% — will select all values that begin with x and are at least two characters long. You can add additional characters to extend the length requirement, i.e. x___%

15. How will you change the datatype of a column?

By using the ALTER TABLE statement with nullability.

1ALTER TABLE TableName 
2ALTER COLUMN ColumnName NVARCHAR(200) [NULL | NOT NULL]

16. What are Common Table Expressions? (CTE's)

Think of a CTE like a temporary table or result set. A CTE is created with the keyword WITH keyword and then referenced to in a SELECT, INSERT, UPDATE, or DELETE statement.

By using CTE's we avoid having to create and drop tables. With a CTE we can simply reference the temporary results set we created.

F. SQL Relationships

1. What are Entities and Relationships?

Entity:

An entity can be a real-world object, either tangible or intangible, that can be easily identifiable. For example, in a college database, students, professors, workers, departments, and projects can be referred to as entities. Each entity has some associated attributes that provide it an identity.

Relationships:

Relations or links between entities that have something to do with each other. For example, an employee's table in a company's database can be associated with the salary table in the same database.

2. List the different types of relationships in SQL.

A. One-to-one Relationship

Such a relationship exists when each record of one table is related to only one record of the other table.

For example each person can have only one passport and each passport belongs to only one person.

B. One-to-many Relationship

Such a relationship exists when each record of one table can be related to one or more than one record of the other table. This relationship is the most common relationship found. A one-to-many relationship can also be said as a many-to-one relationship depending upon the way we view it.

For example, each ‘Customer’ can have more than one ‘Account’ but each ‘Account’ is held by only one ‘Customer’.

C. Many-to-many Relationship

Such a relationship exists when each record of the first table can be related to one or more than one record of the second table and a single record of the second table can be related to one or more than one record of the first table. A many-to-many relationship can be seen as a two one-to-many relationship which is linked by a 'linking table' or 'associate table'. The linking table links two tables by having fields which are the primary key of the other two tables.

For example each customer can buy more than one product and a product can be bought by many different customers.

G. Normalization & Denormalization

1. What is Normalization?

Represents the way of organizing structured data in the database efficiently. It includes the creation of tables, establishing relationships between them, and defining rules for those relationships. Inconsistency and redundancy can be kept in check based on these rules, hence, adding flexibility to the database.

2. What is Denormalization?

Is the inverse process of normalization, where the normalized schema is converted into a schema that has redundant information. The performance is improved by using redundancy and keeping the redundant data consistent. The reason for performing denormalization is the overheads produced in the query processor by an over-normalized structure.

3. What are the various forms of Normalization?

Lets use an example to illustrate the various forms of normalization. Below is a table with sales records of gaming consoles and their details.

1NF First Normal Form

• Each cell to be single valued
• Entries in a column are the same types
• Rows uniquely identified (add Unique ID or add more columns to make unique)

2NF Second Normal Form

• All attributes (non-key columns) are dependent of the key

3NF Third Normal Form

• All fields (columns) can be determined only by the key in the table and no other column

4NF Fourth Normal Form

• No multi-valued dependencies

H. SQL Indexing

1. What is an Index?

Indexes are used to retrieve data from the database quickly, similar to the index of the back of a book. It speeds up the operation of accessing data from a database tables at the cost of additional time since when updating tables indexes too need to be updated.

Looking at the sequential search (table scan) below we can see how time consuming it is to search all records before we get to our desired record.

By creating a tree structure we increase search performance by minimizing the scanning of the records, as from the example below only 10 records are scanned and all other records are bypassed.

1-- Create an index
2CREATE INDEX index_name   
3ON table_name (column_1);
4
5-- Multiple column index
6CREATE INDEX index_name   
7ON table_name (column_1, column_2);
8
9-- Drop the index
10DROP INDEX index_name;   

2. What is the difference between Clustered and Non-clustered index?

Clustered Indexes are sorted as trees, the actual data is stored in the leaf nodes. This type of indexing reorders the way records in the table are physically stored thus a table can only have one clustered index but it is faster than non-clustered indexing.

Non-Clustered Indexes on the other hand is where the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages, instead they contain pointers like page numbers in the index of a book. This type of indexes are created on top of the actual data. Unlike clustered indexing a table can have more than one non-clustered index.

I. OLTP & OLAP

1. What is OLTP?

Online Transaction Processing (OLTP), is a class of software applications capable of capturing, storing, and processing data from transactions in real time. Each transaction involves individual database records made up of multiple fields of columns. In OLTP, the emphasis is on fast processing since OLTP databases are read, written, and updated frequently. If in the case there is a transaction failure built-in system logic ensures data integrity.

For example in a bookstore, OLTP can be used for the price of books, updating customer transactions, and keeping track of employee hours; as you can see the focus is on supporting day to day operations

2. What is OLAP?

Online Analytical Processing (OLAP), uses complex queries to analyze aggregated historical data from OLTP databases and other sources for data mining, analytics and business intelligence projects. In OLAP the emphasis is on response time to these complex queries. Each query involves on or more columns of data aggregated from many rows.

For example in the same bookstore, OLAP can be used to calculate the books with best profit margin, find the most loyal customers, decide employee of the month; as you can see the focus is on supporting business decision making.

3. What are the differences between OLTP and OLAP?

• OLAP is a category of software tools that analyzes data stored in a database, whereas OLTP supports transaction-oriented applications in a 3-tier architecture.
• OLAP creates a single platform for all types of business analysis needs which includes planning, budgeting, forecasting, and analysis, while OLTP is useful for administering day-to-day transactions of an organization.
• OLAP is characterized by a large volume of data, while OLTP is characterized by large numbers of short online transactions.
• In OLAP, a data warehouse is created uniquely so that it can integrate different data sources for building a consolidated database, whereas OLTP uses traditional DBMS.

J. PostgresSQL

1. What is PostgreSQL?

An enterprise-level, flexible, robust, open-source, and object-relational DBMS that supports flexible workloads along with handling concurrent users. It is also known as Postgres as it was based on POSTGRES at Berkeley Computer Science Department. It was developed to help developers build enterprise-level applications by upholding data integrity by making systems fault-tolerant.

2. What data types are available in PostgreSQL?

PostgreSQL supports a variety of data types including:

• UUID
• Numeric types
• Temporal types
• Geometric primitives
• Arbitrary precision numeric
• XML
• Boolean
• Character types
• Array
• JSON

3. What is table partitioning in PostgreSQL?

The process of dividing a large table into smaller partitions. PostgreSQL supports ranges and lists partitioning through table inheritance. This greatly enhances query performance.

4. Define tokens in PostgreSQL?

A token can be a key word, an identifier, a quoted identifier, a literal (or constant), or a special character symbol. Tokens are normally separated by whitespace (space, tab, newline), but need not be if there is no ambiguity (which is generally only the case if a special character is adjacent to some other token type).

5. What are string constants in PostgreSQL?

A string constant in PostgreSQL is a sequence of some character that is bounded by single quotes (').

6. How do you fill in missing values in PostgreSQL?

Missing values in PostgreSQL can be filled with COALESCE command. COALESCE command has the same functionality as IFNULL command in typical SQL-command. The COALESCE command in PostgreSQL can be used to fill in missing values. This is the equivalent to the IFNULL command other SQL.

1COALESCE(Our_NULL_Val, Value_To_Replace)
2
3# Example fill NULL values with '0'
4COALESCE(NULL,0)

One Pager Cheat Sheet

• Review the best SQL Interview Practice Questions sourced from interviews with top tech companies like Google, Oracle, Amazon, and Microsoft to help you ace the interview.
• SQL is a standard language for accessing and manipulating structured databases, such as MySQL, where queries can be used to perform select or action operations and sub-queries can be used to retrieve specific data.
• A nested query or sub-query is used to return data from a database, which is then used by the main query as a condition.
• A Database Management System (DBMS) is a software used to define, create, and maintain a database, while a Relational Database Management System (RDBMS) is an advanced version of a DBMS which stores data in the form of tables and maintains data integrity by enforcing a set of rules and standards.
• All values in a field must be unique and enforced through a unique index to ensure data stored is consistent and accurate.
• A Primary Key is a constraint that uniquely identifies each row in a table and can be comprised of single or multiple fields, while a Foreign Key refers to a Primary Key in another table to ensure referential integrity.
• A Primary Key must contain unique values and not allow null values.
• A JOIN clause is used to combine rows from two or more tables, with the types INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN, while a SELF JOIN links a table to itself and a CROSS JOIN is a Cartesian product of the two tables included in the join.
• An INNER JOIN only returns records that have a match in both tables, whereas other types of joins return all records from one of the tables regardless of whether there is a match in the other.
• You can use SELECT, WHERE, ORDER BY, GROUP BY, HAVING, UNION, MINUS, INTERSECT, TRUNCATE, DELETE, DROP, CREATE VIEW, ALTER TABLE and LIKE commands to query, change and manipulate data in SQL.
• The SELECT DISTINCT command eliminates duplicate rows from a result set, providing only a distinct set of data different from the plain SELECT command.
• SQL has three major types of relationships: one-to-one, many-to-one, and many-to-many, which all involve linking or associating records of one table to another.
• A one-to-one relationship exists between a person and their passport, as each passport can only belong to one person and vice versa.
• Normalization is a way of structuring data efficiently, while Denormalization is the opposite process that adds redundancy to improve performance, both of which are illustrated via 1NF, 2NF, 3NF, and 4NF.
• Denormalization increases data redundancy to improve query performance, reducing the overhead of joins and relationships between tables.
• Indexing is used to improve the speed of data retrieval from a database table, at the cost of additional time for updates, with clustered and non-clustered indexing offering different advantages.
• No table can have more than one Clustered Index, as it would require the data to be physically stored in multiple orders, which is not possible.
• OLTP processes data from transactions in real time for day-to-day operations, whereas OLAP analyzes aggregated data from OLTP databases and other sources for business decision-making.
• OLAP is used to analyze and aggregate data from a data warehouse or other sources for business intelligence and analytics, while OLTP focuses on transaction-oriented applications such as processing orders.
• PostgreSQL is an enterprise-level DBMS that supports a variety of data types such as UUID, Numeric types, Temporal types, and JSON, and also provides features such as Table Partitioning and String Constants to help developers build enterprise-level applications.
• PostgreSQL is a continuation of the POSTGRES project, based on INGRES and developed at the University of California at Berkeley to support relational databases on UNIX-like systems.