Short Description:
The ALTER clause in SQL is used to modify or change the structure of an existing database object like a table, adding, removing, or modifying columns, constraints, or settings.
What SQLInterview
Says: The ALTER clause in SQL is a powerful command that allows modifications to the structure of database objects. It can be used to add, modify, or drop columns, constraints, indexes, and other properties of tables or databases. This command provides flexibility in altering existing schemas without the need to recreate the entire object, enabling database administrators to make changes to database structures efficiently.
SQL Cheat Sheet
SQL Cheat Sheet provides quick, concise reference material for SQL queries, aiding users in efficiently recalling commands and syntax, facilitating faster query writing and troubleshooting during interviews or practical scenarios.
-
Table Modifications
-
Window Functions
ALTER
-- Create a new table called "products"
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(50),
price DECIMAL(10, 2)
);
-- Add a new column to the "products" table called "description"
ALTER TABLE products
ADD COLUMN description TEXT;
-- Change the data type of the "price" column from DECIMAL(10, 2) to FLOAT(10)
ALTER TABLE products
MODIFY COLUMN price FLOAT(10);
-- Rename the "name" column to "product_name"
ALTER TABLE products
RENAME COLUMN name TO product_name;
-- Drop the "description" column from the "products" table
ALTER TABLE products
DROP COLUMN description;
AND
Short Description:
The AND clause in SQL is a logical operator used to combine multiple conditions in a query. It requires that all specified conditions must be true for the row to be included in the result set.
What SQLInterview Says:
The AND clause in SQL is a logical operator that allows the conjunction of multiple conditions within a WHERE clause. When using AND, all specified conditions must evaluate to true for a row to be included in the query result. It's commonly used to narrow down results by combining different criteria. This operator operates by checking each condition and returning rows that meet all the specified conditions simultaneously.
/**
In this example, the AND clause is used to combine two conditions in the WHERE clause.
The first condition specifies that only customers from the USA should be included in the result set,
while the second condition further restricts the result set to only customers from New York.
**/
SELECT *
FROM customers
WHERE country = 'USA' -- Only include customers from the USA
AND city = 'New York'; -- Only include customers from New York
/**
The AND clause can also be used in combination with other logical operators like OR to create more complex conditions.
**/
SELECT *
FROM products
WHERE (category = 'Electronics' OR category = 'Computers') -- Only include products in the Electronics or Computers category
AND price >= 500 -- Only include products with a price of $500 or higher
AND in_stock = true; -- Only include products that are in stock
ARRAY
Short Description:
The ARRAY in SQL is a data type that allows the storage and manipulation of arrays, which are ordered collections of elements of the same data type within a single column in a database table.
What SQLInterview Says:
The ARRAY in SQL is a data type that enables the storage of arrays—ordered collections of elements—within a single column of a table. It allows for the storage of multiple values of the same data type in a structured format. PostgreSQL, for example, supports arrays of various data types, enabling efficient storage and retrieval of arrays within a database column. This functionality provides flexibility in managing and querying data, especially when dealing with multiple related values that belong together.
SELECT ARRAY[1,2,6+4, 5];
SELECT ARRAY['hello', 'there', 'im an array'];
SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];
SELECT ARRAY(select age from users);
-- Insert data into the 'products' table with tags stored as arrays
INSERT INTO products (tags)
VALUES
('{"electronics", "gadgets"}'),
('{"clothing", "fashion"}'),
('{"books", "education", "reading"}');
-- Query to find products tagged with both 'books' and 'education'
SELECT * FROM products WHERE ARRAY['books', 'education'] <@ tags;
AS
Short Description:
AS command in PostgreSQL is used to assign aliases to columns or tables in SQL queries.
What SQLInterview Says:
AS command in PostgreSQL is a versatile tool that allows you to assign aliases to columns or tables in SQL queries. Aliases provide alternative names for columns or tables, making the query results more readable and understandable. They can be used to give more meaningful names, simplify query syntax, or handle naming conflicts when joining multiple tables.
-- Example 1: Assigning aliases to columns
SELECT first_name AS "First Name", last_name AS "Last Name" FROM employees;
-- This query selects the columns first_name and last_name from the employees table and assigns them aliases "First Name" and "Last Name" respectively.
-- Example 2: Assigning an alias to a table
SELECT e.employee_id, e.first_name, d.department_name
FROM employees AS e
JOIN departments AS d ON e.department_id = d.department_id;
-- Here, the tables employees and departments are given aliases 'e' and 'd' respectively for brevity and easier reference in the query.
-- Example 3: Using column alias in calculations
SELECT quantity, price, quantity * price AS "Total Price" FROM orders;
-- In this query, a column alias "Total Price" is assigned to the result of the calculation quantity * price, providing a meaningful name for the
calculated value.
-- Example 4: Using table alias to simplify query
SELECT c.customer_id, c.first_name, o.order_date
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id;
-- The table aliases 'c' and 'o' are used to simplify the query when joining the customers and orders tables based on the customer_id column.
ASCII
Short Description:
ASCII in SQL returns the ASCII value of the first character in a string.
What SQLInterview Says:
The ASCII function in SQL is used to obtain the ASCII (American Standard Code for Information Interchange) value of the first character in a string expression. It takes a single character as input and returns an integer representing the ASCII value of that character. If the input string is empty, the function returns 0.
-- ASCII function example in SQL
-- Return the ASCII value of the first character in the string 'Hello'
SELECT ASCII('Hello') AS First_Character_ASCII;
-- will return the ASCII value of the first character 'H', which is 72.
-- Return the ASCII value of the first character in an empty string
SELECT ASCII('') AS Empty_String_ASCII;
-- will return 0 since there's no character in the empty string.
AVG
Short Description:
AVG in SQL is an aggregate function used to calculate the average value of a set of values within a column.
What SQLInterview Says:
AVG function in SQL is an aggregate function that computes the average value of a set of numerical values within a column. It calculates the sum of all values in the specified column and divides it by the total number of values to derive the average. This function is commonly used to obtain the average value of a dataset, providing valuable insights into the central tendency of numerical data.
-- AVG function example in SQL
-- Calculate the average salary of employees
SELECT AVG(salary) AS average_salary FROM employees;
BEGIN
Short Description:
BEGIN statement in SQL is used to start a transaction explicitly, indicating the beginning of a series of SQL commands that should be treated as a single unit of work.
What SQLInterview Says:
BEGIN statement in SQL initiates an explicit transaction, marking the start of a sequence of SQL statements that need to be treated as a single unit of work. It is often used in conjunction with other transaction control statements (COMMIT, ROLLBACK) to manage atomicity, consistency, isolation, and durability (ACID properties) of the database transactions. The commands executed between BEGIN and COMMIT or ROLLBACK are treated as a single logical transaction, ensuring that either all changes are applied (committed) or none are (rolled back).
-- Create a sample table
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10, 2)
);
-- Begin a transaction
BEGIN;
-- Inserting data into the employees table
INSERT INTO employees (employee_name, department, salary)
VALUES ('John Doe', 'IT', 60000.00),
('Jane Smith', 'HR', 55000.00);
-- Checking data after insertion
SELECT * FROM employees;
-- Commit the transaction
COMMIT;
BETWEEN
Short Description:
BETWEEN in SQL is a comparison operator used to check if a value lies within a specified range.
What SQLInterview Says:
BETWEEN operator in SQL is used in a WHERE clause to determine whether a value lies within a specified range of values. It's inclusive, checking if a value is greater than or equal to a starting point and less than or equal to an ending point. This operator is helpful for filtering data based on a range of values in a column.
-- BETWEEN operator example in SQL
-- Retrieve products with prices between $10 and $50
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
-- Retrieve orders placed between specific dates
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
-- Retrieve products whose names are alphabetically between 'C' and 'M'
SELECT * FROM products WHERE product_name BETWEEN 'C' AND 'M' ORDER BY product_name;
BTRIM
Short Description:
BTRIM in SQL removes specified characters from the beginning and end of a string.
What SQLInterview Says:
The BTRIM function in SQL is used to remove specified characters or spaces from the beginning and end of a string. It trims all leading and trailing occurrences of the characters specified in the trim expression from the input string. If no trim characters are provided, it removes spaces by default.
-- Remove trailing and leading spaces from the string
SELECT BTRIM(' Hello ') AS Trimmed_String;
-- will return 'Hello' by removing the leading and trailing spaces.
-- Remove 'l' and 'o' characters from the beginning and end of the string
SELECT BTRIM('Hello', 'lo') AS Custom_Trimmed_String;
-- will return 'Hel' by removing 'l' and 'o' characters from both ends of the string.
CASE
Short Description:
CASE in SQL is a conditional expression that allows for multiple conditions and return values based on those conditions.
What SQLInterview Says:
CASE expression in SQL provides conditional logic similar to IF-THEN-ELSE statements. It evaluates a set of conditions and returns a result based on the first condition that evaluates to true. It is versatile and allows the formulation of complex conditional logic within SQL queries.
-- CASE expression example in SQL
SELECT
column1,
CASE
WHEN column2 = 'Value1' THEN 'Result1'
WHEN column2 = 'Value2' THEN 'Result2'
ELSE 'DefaultResult'
END AS new_column
FROM
your_table;
-- CASE expression with multiple conditions example in SQL
SELECT
student_id,
name,
age,
grade,
CASE
WHEN age BETWEEN 6 AND 10 THEN 'Child'
WHEN age BETWEEN 11 AND 14 THEN 'Pre-Teen'
WHEN age BETWEEN 15 AND 18 THEN 'Teenager'
ELSE 'Other'
END AS age_group
FROM
students;
-- Multiple CASE expressions example in SQL
SELECT
employee_id,
first_name,
last_name,
salary,
department,
CASE
WHEN department = 'IT' THEN
CASE
WHEN salary > 80000 THEN 'High Bonus'
WHEN salary BETWEEN 60000 AND 80000 THEN 'Medium Bonus'
ELSE 'Low Bonus'
END
WHEN department = 'Sales' THEN
CASE
WHEN salary > 70000 THEN 'High Bonus'
WHEN salary BETWEEN 50000 AND 70000 THEN 'Medium Bonus'
ELSE 'Low Bonus'
END
ELSE 'No Bonus'
END AS bonus_category
FROM
employees;
CHECK
Short Description:
CHECK constraint in SQL is used to enforce specific conditions or rules on the values that can be inserted or updated in a column, ensuring that only values meeting the specified criteria are allowed.
What SQLInterview Says:
CHECK constraint in SQL is a table constraint that allows defining conditions or rules for column values. When applied to a column, it restricts the values that can be inserted or updated to those that satisfy the specified condition. This constraint ensures data integrity by validating the data before it is added or modified in the database, preventing invalid or unwanted values from being stored.
-- Create an 'orders' table with a CHECK constraint on 'order_amount' column
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
order_date DATE,
order_amount DECIMAL(10, 2) CHECK (order_amount > 0) -- Applying CHECK constraint
);
CREATE TABLE products (
product_num integer,
description text,
price numeric CHECK (price > 0) discounted_price numeric CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);
CHR
Short Description:
CHR function is used to return a character based on the ASCII code provided as an argument.
What SQLInterview Says:
CHR function in SQL takes an ASCII code as an argument and returns the character represented by that ASCII code. It's particularly useful when converting numerical ASCII codes into their corresponding characters. For example, CHR(65) returns the character 'A' as 65 represents the ASCII code for 'A'.
-- Using CHR to retrieve the character for ASCII code 65
SELECT CHR(65) AS character_from_ascii;
/* character_from_ascii */
/* A */
COALESCE
Short Description:
COALESCE in SQL is a function used to return the first non-null value from a list of expressions.
What SQLInterview Says:
COALESCE function in SQL is utilized to evaluate a list of expressions and return the first non-null value encountered in the list. It takes multiple arguments and returns the first argument that is not NULL. If all arguments are NULL, it returns NULL. This function is commonly used to handle NULL values within queries, allowing substitution of NULL with alternate values.
-- COALESCE function example in SQL
-- COALESCE can handle potential NULL values within a column (column2 in this case).
-- It substitutes NULL values with 'N/A' in the result set, aiding in better data presentation.
-- Return the first non-null value among the provided values
SELECT COALESCE(NULL, 5, 10) AS Result;
-- Use COALESCE to handle potential NULL values in a column
SELECT
column1,
COALESCE(column2, 'N/A') AS column2_handled_nulls
FROM
your_table;
CONCAT
Short Description:
CONCAT in SQL is used to concatenate two or more strings together into a single string.
What SQLInterview Says:
The CONCAT function in SQL is employed to join multiple strings into a single string. It takes two or more string expressions as arguments and concatenates them in the order they are specified. If any argument is NULL, it treats it as an empty string for concatenation.
-- Concatenate 'Hello' and 'World' with a space in between
SELECT CONCAT('Hello', ' ', 'World') AS Concatenated_String;
-- will return 'Hello World' by concatenating the strings 'Hello', a space, and 'World'.
-- Concatenate 'SQL' and 'Interview' without any space
SELECT CONCAT('SQL', 'Interview') AS Concatenated_String_No_Space;
-- will return 'SQLInterview' by concatenating 'SQL' and 'Interview' without any space.
-- Concatenate 'Hello' and a NULL value (treated as empty string)
SELECT CONCAT('Hello', NULL) AS Concatenated_With_NULL;
-- will return 'Hello' because the NULL is treated as an empty string for concatenation purposes.
COUNT
Short Description:
COUNT in SQL is an aggregate function used to count the number of rows or non-null values within a column.
What SQLInterview Says:
The COUNT function in SQL is an aggregate function that tallies the number of rows in a specified column or the number of non-null values within that column. It returns the count of rows that meet the specified criteria, providing insights into the number of records or instances that match the conditions given in the query.
-- COUNT function example in SQL
-- Count the number of employees
SELECT COUNT(*) AS total_employees FROM employees;
CREATE TABLE
Short Description:
CREATE TABLE statement in SQL is used to define and create a new table in a database, specifying the table name, columns, data types, constraints, and other properties.
What SQLInterview Says:
CREATE TABLE statement is a Data Definition Language (DDL) command in SQL used to create a new table in a database. It defines the structure of the table by specifying column names, their data types, constraints (such as primary keys, foreign keys, etc.), default values, and other table properties. This statement allows users to organize and store data in a structured format within a database.
-- Creating a new table named 'employees'
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10, 2)
);
DATE_PART
Short Description:
DATE_PART() function is used to extract a specific part (such as year, month, day, hour, etc.) from a date or timestamp.
What SQLInterview Says:
DATE_PART() function in SQL is used to retrieve a specific component, such as year, month, day, hour, minute, second, etc., from a date or timestamp. It takes two arguments: the date part to extract and the date or timestamp from which to extract the specified part. This function is valuable for dissecting date or timestamp values and obtaining specific components, allowing for detailed analysis or manipulation of temporal data.
-- DATE_PART function example in SQL
-- Extract the year from the order date
SELECT order_date, DATE_PART('year', order_date) AS order_year
FROM orders;
-- Extract month numbers from timestamps
SELECT timestamp_column, DATE_PART('month', timestamp_column) AS month_number
FROM events;
-- Determine the day of the week for specific dates
SELECT date_column, DATE_PART('dow', date_column) AS day_of_week
FROM calendar;
-- Calculate ages of users based on their birthdates
SELECT user_name, birth_date, DATE_PART('year', CURRENT_DATE) - DATE_PART('year', birth_date) AS age
FROM users;
-- Determine the quarter of the year from timestamps
SELECT timestamp_column, CEIL(DATE_PART('month', timestamp_column) / 3.0) AS quarter
FROM events;
-- Identify weekdays (1 = Monday, 7 = Sunday) from dates
SELECT date_column, MOD(DATE_PART('dow', date_column) + 6, 7) + 1 AS weekday_number
FROM calendar;
DELETE
Short Description:
DELETE statement in SQL is used to remove rows from a table based on specified criteria, effectively deleting data from the table.
What SQLInterview Says:
DELETE statement in SQL is used to remove rows from a table that match specific conditions provided in the WHERE clause. It is a Data Manipulation Language (DML) statement that permanently deletes data from a table. It removes specific rows while retaining the table structure and other data. Care should be taken when using DELETE as it permanently erases data, and there's usually no direct undo capability after a commit.
-- Deleting employees with salaries less than 50000.00 from the 'employees' table
DELETE FROM employees
WHERE salary < 50000.00;
DENSE_RANK
Short Description:
DENSE_RANK function in SQL is used to assign a rank to each row in a result set, ensuring consecutive ranking without gaps. If multiple rows share the same rank, the next rank value increments by one without leaving gaps in the ranking sequence.
What SQLInterview Says:
DENSE_RANK function in SQL is a window function used to assign ranks to rows within a result set based on specified ordering criteria. It computes rankings in a way that ensures consecutive ranks without leaving gaps, even if multiple rows have the same values that determine their ranking. Unlike RANK, DENSE_RANK doesn’t leave gaps in the ranking sequence and provides consecutive rankings to rows sharing the same rank value.
select first_name, last_name, country, dense_rank() over (order by country)
from users;
-- Create a 'students' table with sample data
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
name VARCHAR(50),
score INTEGER
);
-- Insert data into the 'students' table
INSERT INTO students (name, score)
VALUES
('Alice', 85),
('Bob', 92),
('Charlie', 78),
('David', 85),
('Emma', 92);
-- Assign ranks to students based on their scores using DENSE_RANK
SELECT
name,
score,
DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM students;
DISTINCT
Short Description:
The DISTINCT command in PostgreSQL is used to retrieve unique values from one or more columns in a table.
What SQLInterview Says:
The DISTINCT command in PostgreSQL is a useful tool when you want to eliminate duplicate values and retrieve only unique data points from one or more columns in a table. It filters out redundant entries, allowing you to focus on distinct values. This command is commonly used in scenarios where you need to obtain unique combinations of values or count the occurrences of distinct values.
-- Example 1: Retrieve unique values from a single column
SELECT DISTINCT column_name
FROM table_name;
-- This query retrieves unique values from the specified column in the given table.
-- Example 2: Retrieve unique combinations of values from multiple columns
SELECT DISTINCT column1, column2
FROM table_name;
-- This query retrieves unique combinations of values from the specified columns in the given table.
-- Example 3: Retrieve unique values and calculate their count
SELECT column_name, COUNT(DISTINCT another_column) AS count
FROM table_name
GROUP BY column_name;
-- This query retrieves unique values from the specified column in the given table and calculates the count of distinct values.
-- Example 4: Retrieve unique values and sort them in ascending order
SELECT DISTINCT column_name
FROM table_name
ORDER BY column_name ASC;
-- This query retrieves unique values from the specified column in the given table and sorts them in ascending order.
DROP
Short Description:
DROP statement is used to delete or remove database objects, such as tables, views, indexes, or databases themselves.
What SQLInterview Says:
DROP statement in SQL is used to delete existing database objects like tables, views, indexes, or databases themselves. It permanently removes the specified object from the database schema. This operation cannot be rolled back, so it's crucial to use it with caution as dropping an object removes all associated data and schema definitions.
-- Drop a table named 'employees'
DROP TABLE employees;
-- Drop an index named 'idx_product_price' from the 'products' table
DROP INDEX idx_product_price;
-- Drop a view named 'monthly_sales_report'
DROP VIEW monthly_sales_report;
-- Drop a schema named 'sales_schema'
DROP SCHEMA sales_schema;
-- Drop a sequence named 'order_sequence'
DROP SEQUENCE order_sequence;
-- Drop a function named 'calculate_discount'
DROP FUNCTION calculate_discount(int);
-- Drop a database named 'old_database'
DROP DATABASE old_database;
EXCEPT
Short Description:
EXCEPT operator is used to retrieve distinct rows from the left query result that do not appear in the right query result.
What SQLInterview Says:
EXCEPT operator in SQL is used to compare two queries and retrieve the distinct rows from the first (left) query that are not present in the second (right) query. It returns only distinct rows by comparing the result sets of both queries and eliminating rows found in the second query result from the first query result. This operator is beneficial for finding the difference between two result sets, allowing you to identify unique rows present in one set but not in another.
-- EXCEPT function example in SQL
-- Retrieve unique department names that do not have any managers
SELECT department_name
FROM departments
EXCEPT
SELECT department_name
FROM managers;
-- Find products that have not been purchased
SELECT product_name
FROM all_products
EXCEPT
SELECT product_name
FROM purchased_products;
-- Retrieve students who haven't scored in Biology class
SELECT student_name
FROM all_students
WHERE class = 'Biology'
EXCEPT
SELECT student_name
FROM biology_scores;
EXTRACT
Short Description:
EXTRACT in SQL is used to extract and retrieve specific parts (such as year, month, day, etc.) from date and time values.
What SQLInterview Says:
EXTRACT function in SQL is employed to retrieve specific components, like year, month, day, hour, minute, etc., from a date or timestamp field in the database. It allows users to extract and work with individual components of date and time values stored in the database. This function is useful for performing analysis or filtering based on specific date or time components.
-- EXTRACT function example in SQL
-- Extract the year from the date '2023-07-15'
SELECT EXTRACT(YEAR FROM DATE '2023-07-15') AS Extracted_Year;
-- will return 2023, extracting the year component from the date.
-- Extract the month from the timestamp '2023-07-15 08:30:00'
SELECT EXTRACT(MONTH FROM TIMESTAMP '2023-07-15 08:30:00') AS Extracted_Month;
-- will return 7, extracting the month component from the timestamp.
-- Extract the day of the week from the timestamp '2023-07-15 08:30:00'
SELECT EXTRACT(DOW FROM TIMESTAMP '2023-07-15 08:30:00') AS Extracted_Day_Of_Week;
-- will return 6, where Sunday is 0 and Saturday is 6, extracting the day of the week component (Saturday in this case) from the timestamp.
FROM
Short Description:
FROM command in PostgreSQL is used to specify the table or tables from which data is retrieved in an SQL query.
What SQLInterview Says:
FROM command is a fundamental part of an SQL query in PostgreSQL. It allows you to specify the table or tables that will be used as the data source for the query. The FROM clause is typically used immediately after the SELECT statement and precedes any JOIN or WHERE clauses. It enables you to define the source tables for retrieving data and perform operations such as filtering, joining, and aggregating.
-- Example 1: Selecting data from a single table
SELECT * FROM employees;
-- This query retrieves all columns and rows from the employees table.
-- Example 2: Joining multiple tables
SELECT c.customer_id, c.first_name, o.order_date
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id;
-- The FROM clause specifies the tables customers and orders, and the JOIN condition connects the two tables based on the customer_id column.
-- Example 3: Using subquery as a source
SELECT product_name, price
FROM (
SELECT * FROM products WHERE category = 'Electronics'
) AS subquery;
-- The subquery inside the FROM clause acts as a temporary table that filters the products table to retrieve only electronics products.
-- Example 4: Combining tables with UNION
SELECT customer_id FROM customers
UNION
SELECT customer_id FROM archived_customers;
-- The FROM clause is used to specify multiple tables (customers and archived_customers) that are combined using the UNION operator.
-- Example 5: Cross-joining tables
SELECT * FROM table1, table2;
-- This query uses a comma-separated list of tables in the FROM clause, resulting in a cross join that combines all rows from table1 with all rows from
table2.
-- Example 6: Self-joining a table
SELECT e.employee_id, e.first_name, m.first_name AS manager_name
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.employee_id;
-- The FROM clause is used to perform a self-join on the employees table to retrieve employee details along with their corresponding manager names.
-- Example 7: Using table aliases
SELECT e.first_name, d.department_name
FROM employees AS e
JOIN departments AS d ON e.department_id = d.department_id;
-- Table aliases 'e' and 'd' are assigned in the FROM clause to simplify the query and reference the tables with shorter names.
FULL OUTER JOIN
Short Description:
FULL OUTER JOIN function combines the results of both LEFT JOIN and RIGHT JOIN. It returns all records when there is a match in either the left or right table. If there is no match, NULL values are included for columns from the table without a matching row.
What SQLInterview Says:
FULL OUTER JOIN in SQL combines the results of both the LEFT JOIN and RIGHT JOIN. It retrieves all records from both tables specified in the query. If there are matching rows between the tables, those records are included in the result set. If there's no match for a row from one table in the other table, NULL values are added for the columns from the table without a match. This type of join is useful for merging data from two tables, keeping all records from both tables, and identifying unmatched rows.
-- Connect employees with their managers (if available)
SELECT e.employee_name, m.manager_name
FROM employees e
FULL OUTER JOIN managers m ON e.manager_id = m.manager_id;
-- Retrieve all students and their enrolled courses (if any)
SELECT s.student_name, c.course_name
FROM students s
FULL OUTER JOIN student_courses sc ON s.student_id = sc.student_id
FULL OUTER JOIN courses c ON sc.course_id = c.course_id;
GENERATED COLUMN
Short Description:
GENERATED COLUMN in SQL allows the creation of columns in a table that derive their values based on expressions or functions. These columns are computed dynamically when rows are inserted or updated, rather than storing physical data.
What SQLInterview Says:
GENERATED COLUMN in SQL enables the creation of columns whose values are determined by an expression or function based on other columns within the same table. These columns don't store data physically; instead, they are computed dynamically when rows are inserted or updated. They offer the ability to generate values based on specified rules, providing efficient ways to derive information without storing redundant data.
-- Create a table 'user_data' with a generated column
CREATE TABLE user_data (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100),
username_length INT GENERATED ALWAYS AS (LENGTH(username)) STORED
);
-- Insert data into the 'user_data' table
INSERT INTO user_data (username, email) VALUES
('john_doe', 'john@example.com'),
('jane_smith', 'jane@example.com'),
('alice', 'alice@example.com');
-- Retrieve data from the 'user_data' table
SELECT user_id, username, email, username_length FROM user_data;
GRANT
Short Description:
GRANT SQL clause used to grant specific privileges or permissions on database objects to users or roles.
What SQLInterview Says:
GRANT clause in SQL is utilized to provide specific privileges or permissions on database objects, such as tables, views, sequences, or schemas, to users or roles within the database. This command is crucial for managing security and access control within PostgreSQL. It allows the database administrator to delegate certain rights to other users or roles, enabling them to perform actions like SELECT, INSERT, UPDATE, DELETE, etc., on the specified database objects.
-- Grant SELECT privilege on a table to a specific user
GRANT SELECT ON table_name TO username;
-- Grant INSERT, UPDATE, DELETE privileges on a table to a role
GRANT INSERT, UPDATE, DELETE ON table_name TO role_name;
-- Grant ALL privileges on all tables in a schema to a role
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO role_name;
-- Grant usage on a schema to a specific user
GRANT USAGE ON SCHEMA schema_name TO username;
-- Grant EXECUTE privilege on a function to a role
GRANT EXECUTE ON FUNCTION function_name(argument_type) TO role_name;
-- Grant SELECT privilege on specific columns of a table to a user
GRANT SELECT (column1, column2) ON table_name TO username;
-- Grant SELECT privilege on a table to a role with the ability to grant this privilege to others
GRANT SELECT ON table_name TO role_name WITH GRANT OPTION;
GROUP BY
Short Description:
GROUP BY in SQL is a clause used to group rows that share a common value in one or more columns, allowing aggregate functions to be performed on each group separately.
What SQLInterview Says:
GROUP BY clause in SQL is used to group rows together based on a specified column or columns. It collects rows that have identical values in the specified column(s) and forms them into groups. After grouping, aggregate functions like SUM, COUNT, AVG, etc., can be applied to each group independently. It's valuable for performing aggregate operations on subsets of data within a table.
-- GROUP BY clause example in SQL
-- Calculate the total sales amount for each product category
SELECT category, SUM(sales_amount) AS total_sales
FROM products
GROUP BY category;
-- Count the number of orders placed by each customer
SELECT customer_id, COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC;
-- Calculate the average salary per department for employees hired after 2020
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department;
-- Group sales by year and month
SELECT EXTRACT(YEAR FROM sale_date) AS sales_year, EXTRACT(MONTH FROM sale_date) AS sales_month, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date)
ORDER BY sales_year, sales_month;
HAVING
Short Description:
HAVING in SQL is a clause used to filter grouped results based on specified conditions involving aggregated values, allowing for targeted selection of grouped data.
What SQLInterview Says:
HAVING clause in SQL is utilized to filter aggregated data based on specified conditions after grouping rows using the GROUP BY clause. Unlike the WHERE clause that filters individual rows before grouping, HAVING operates on aggregated values, allowing for the selection of grouped results that meet certain criteria. This clause is particularly valuable when needing to apply conditions to the results of aggregate functions (e.g., SUM, COUNT, AVG) to determine which grouped data should be included in the final output. It aids in narrowing down grouped data sets by imposing conditions on the results of these aggregates, facilitating more precise and tailored analysis of data in SQL queries.
--Find the total amount of orders for each customer and display only customers with a total amount greater than $1000.
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 1000;
--Retrieve the count of orders for customers whose status includes completed orders and have more than 2 completed orders.
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 2;
IN
Short Description:
IN function in SQL is an operator used to check if a value matches any value in a list of specified values.
What SQLInterview Says:
IN function is useful for filtering data based on multiple values within a specified list, providing a concise and efficient alternative to using multiple OR conditions.
-- Select employees from departments 'HR' and 'Finance'
SELECT *
FROM employees
WHERE department IN ('HR', 'Finance');
-- Select orders with statuses 'Completed', 'Shipped', or 'Processed'
SELECT *
FROM orders
WHERE status IN ('Completed', 'Shipped', 'Processed');
INITCAP
Short Description:
INITCAP function in SQL is used to capitalize the first letter of each word in a string while converting the remaining characters to lowercase.
What SQLInterview Says:
INITCAP function in SQL is designed to capitalize the first letter of every word within a string, ensuring that subsequent characters are converted to lowercase. It's commonly employed for formatting purposes, especially when consistent capitalization is needed for titles, names, or sentences.
-- Using INITCAP to capitalize the first letter of each word in a string
SELECT INITCAP('tHis Is a sAMPLE string') AS capitalized_string;
-- capitalized_string: This Is A Sample String
INNER JOIN
Short Description:
INNER JOIN function is used to combine rows from two or more tables based on a related column between them. It returns only the rows where a match is found in both tables, excluding rows that don't have a matching row in the other table.
What SQLInterview Says:
INNER JOIN in SQL retrieves rows from both tables specified in the query, where there is a match based on the specified condition or columns. It combines rows from tables that have matching values in the related columns and excludes rows that don't have a match in the other table. This join type is useful for selecting data that exists in both tables and filtering out unmatched rows.
-- INNER JOIN function example in SQL
-- Retrieve employee names and their associated departments
SELECT e.employee_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
-- Connect students with their enrolled courses
SELECT s.student_name, c.course_name
FROM students s
INNER JOIN student_courses sc ON s.student_id = sc.student_id
INNER JOIN courses c ON sc.course_id = c.course_id;
INSERT INTO
Short Description:
INSERT INTO clause in SQL is used to add new records or rows into a table, specifying the columns and their corresponding values.
What SQLInterview Says:
INSERT INTO statement in SQL is used to insert new rows into a table. It allows the addition of specific data values into columns or all columns of a table. This statement is vital for populating a database table with fresh records. It can be used with explicit column names to insert data into specific columns or without specifying column names to insert data in the order the columns were defined in the table.
INSERT INTO stocks
VALUES ('MSFT', 280.07, 281.07, '2022-03-7');
-- Insert a new student record into the 'students' table
INSERT INTO students (first_name, last_name, age)
VALUES ('Emma', 'Johnson', 21);
INTERSECT
Short Description:
INTERSECT operator is used to retrieve distinct rows that appear in both the left and right query results.
What SQLInterview Says:
INTERSECT operator in SQL compares two queries and returns distinct rows that are common to both query results. It retrieves rows that appear in both the result set of the first (left) query and the result set of the second (right) query. This operator is valuable for finding the intersection of rows between two sets, allowing you to identify common elements present in both sets.
-- INTERSECT function example in SQL
-- Retrieve common department names between two departments lists
SELECT department_name
FROM departments_A
INTERSECT
SELECT department_name
FROM departments_B;
IS NULL
Short Description:
IS NULL in SQL is a condition used to filter rows where a specified column contains null values.
What SQLInterview Says:
IS NULL condition in SQL is used to filter rows in a query result where the specified column's value is NULL. It returns rows where the column contains no value (i.e., NULL). This condition is valuable when querying for records with missing or unknown data within a specific column.
-- IS NULL condition example in SQL
-- Retrieve records where the 'email' column is NULL
SELECT * FROM users WHERE email IS NULL;
LAG
Short Description:
LAG in SQL is a window function used to access data from preceding rows within the result set based on a specified offset.
What SQLInterview Says:
LAG function in SQL is a window function that allows access to column values from preceding rows within the result set. It retrieves the value of a specified column from the preceding row(s) based on a specified offset within the partition of a query result. This function is beneficial for comparing values from the current row with those in preceding rows or for accessing previous data points in a sequence.
-- LAG function example in SQL
-- Retrieve the previous transaction amount for each customer, ordered by date
-- The LAG function is used to access the previous transaction amount for each customer based on the ordering of transaction dates.
SELECT
customer_id,
transaction_date AS current_transaction_date,
LAG(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS previous_transaction_amount
FROM
transactions;
-- Retrieve order information partitioned by customer_id and order by order_date,
-- showing the previous order's date within the same customer_id
-- This query utilizes the LAG function partitioned by customer_id and ordered by order_date to display the date of the previous order for each customer.
SELECT
order_id,
order_date,
customer_id,
LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order_date
FROM
orders;
LEAD
Short Description:
LEAD in SQL is a window function used to access data from subsequent rows within the result set based on a specified offset.
What SQLInterview Says:
LEAD function in SQL is part of the window function family and is utilized to fetch data from subsequent rows within the result set. It allows retrieval of column values from the next row(s) based on a specified offset within the partition of a query result. This function is helpful for comparing values from the current row with those in subsequent rows or for accessing future data points in a sequence.
-- Retrieve sales information partitioned by product_id and order by sale_date,
-- showing the next sale's date within the same product_id
-- This query uses the LEAD function partitioned by product_id and ordered by sale_date to display the subsequent sale date for each product.
SELECT
sale_id,
sale_date,
product_id,
LEAD(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date) AS next_sale_date
FROM
sales;
-- Retrieve the next transaction date for each customer, ordered by date
-- The LEAD function is used to access the next transaction date for each customer based on the ordering of transaction dates.
SELECT
customer_id,
transaction_date AS current_transaction_date,
LEAD(transaction_date) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS next_transaction_date
FROM
transactions;
LEFT
Short Description:
LEFT() string function is used to extract a specified number of characters from the left side of a string.
What SQLInterview Says:
LEFT() function in SQL is used to retrieve a specified number of characters from the beginning (left side) of a string. It takes two arguments: the input string and the number of characters to extract. The function returns a substring containing the specified number of characters from the left side of the input string. This function is useful for extracting portions of strings, especially when dealing with textual data where specific lengths or portions are required.
-- LEFT function example in SQL
-- Extract the first three characters of a product code
SELECT product_code, LEFT(product_code, 3) AS first_three_chars
FROM products;
-- Extract initials from employee names
SELECT employee_name, LEFT(employee_name, 1) || '.' AS initials
FROM employees;
-- Truncate product descriptions for display
SELECT product_name, LEFT(product_description, 50) || '...' AS truncated_description
FROM products;
-- Extract fixed-length codes from a list
SELECT code, LEFT(code, 4) AS fixed_length_code
FROM codes;
LEFT JOIN
Short Description:
LEFT JOIN function is used to retrieve all records from the left table and matching records from the right table based on a specified condition. It includes all rows from the left table and only matching rows from the right table. If no match is found, NULL values are included for columns from the right table.
What SQLInterview Says:
LEFT JOIN in SQL retrieves all records from the left table specified in the query and matching records from the right table. If there are no matches found in the right table, NULL values are included for the columns from the right table in the result set. This type of join is useful when you want all records from the left table and related data from the right table, regardless of whether there is a match.
-- LEFT JOIN function example in SQL
-- Retrieve all students and their associated course details (if any)
SELECT s.student_name, c.course_name
FROM students s
LEFT JOIN student_courses sc ON s.student_id = sc.student_id
LEFT JOIN courses c ON sc.course_id = c.course_id;
LENGTH
Short Description:
LENGTH in SQL is used to determine the number of characters in a string.
What SQLInterview Says:
LENGTH function in SQL is utilized to calculate the length of a string in terms of the number of characters it contains. It returns an integer representing the number of characters in the specified string expression. This function is commonly used to assess the length of strings, which can be beneficial in various data manipulation and validation tasks.
-- LENGTH function example in SQL
-- Calculate the length of the string 'Hello'
SELECT LENGTH('Hello') AS String_Length;
-- will return 5, as the string 'Hello' consists of five characters.
-- Calculate the length of an empty string
SELECT LENGTH('') AS Empty_String_Length;
-- will return 0 since an empty string contains no characters.
-- Calculate the length of a string with special characters
SELECT LENGTH('SQLInterview!') AS String_With_Special_Characters_Length;
-- will return 13 because 'SQLInterview!' contains 13 characters including letters, digits, and the exclamation mark.
LIKE
Short Description:
LIKE in SQL is a comparison operator used to match patterns in strings using wildcard characters.
What SQLInterview Says:
The LIKE operator in SQL is used in a WHERE clause to search for a specified pattern within a string column. It allows the use of wildcard characters such as % (matches zero or more characters) and _ (matches a single character) to perform pattern-matching operations. This operator is valuable for searching and filtering data based on specific patterns in string values.
-- LIKE operator example in SQL
-- Retrieve products with names starting with 'S'
SELECT * FROM products WHERE product_name LIKE 'S%';
-- Retrieve phone numbers starting with '+1-555'
SELECT * FROM contacts WHERE phone_number LIKE '+1-212%';
-- Retrieve products with names having 'a' as the second letter
SELECT * FROM products WHERE product_name LIKE '_a%';
-- Retrieve customers whose names contain 'er' anywhere in their names
SELECT * FROM customers WHERE customer_name LIKE '%er%';
-- Retrieve words with exactly six characters
SELECT * FROM words WHERE word_column LIKE '______';
-- Retrieve products with a literal percentage symbol in their names
SELECT * FROM products WHERE product_name LIKE '%\%%' ESCAPE '\';
LIMIT
Short Description:
The LIMIT command in PostgreSQL is used to restrict the number of rows returned by an SQL query.
What SQLInterview Says:
The LIMIT command is a powerful tool in PostgreSQL that allows you to control the number of rows returned by a query. It is typically used at the end of the SELECT statement and is followed by an integer value specifying the maximum number of rows to retrieve. This command is useful when you want to fetch only a subset of records, especially in situations where you have a large result set and want to limit the output.
-- Example 1: Limiting the number of rows
SELECT * FROM employees LIMIT 10;
-- This query retrieves the first 10 rows from the employees table.
-- Example 2: Combining LIMIT with ORDER BY
SELECT product_name, price FROM products ORDER BY price DESC LIMIT 5;
-- The query orders the products by price in descending order and retrieves only the top 5 highest-priced products.
-- Example 3: Skipping a certain number of rows
SELECT * FROM employees LIMIT 5 OFFSET 10;
-- The OFFSET keyword skips the first 10 rows and retrieves the next 5 rows from the employees table.
-- Example 4: Using LIMIT with subqueries
SELECT order_id, customer_id
FROM (
SELECT order_id, customer_id, order_date FROM orders ORDER BY order_date DESC
) AS subquery
LIMIT 3;
-- The subquery retrieves the order_id, customer_id, and order_date from the orders table, and the outer query limits the result to the top 3 orders based
on the order date.
-- Example 5: Applying LIMIT to grouped data
SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
ORDER BY average_salary DESC
LIMIT 3;
-- This query calculates the average salary per department and retrieves only the top 3 departments with the highest average salary.
-- Example 6: Using OFFSET without LIMIT
SELECT * FROM employees OFFSET 20;
-- This query skips the first 20 rows and retrieves all remaining rows from the employees table.
-- Example 7: Limiting the number of rows in a correlated subquery
SELECT employee_id, first_name, hire_date
FROM employees e
WHERE hire_date < (SELECT hire_date FROM employees WHERE employee_id = 100) LIMIT 5;
-- The query retrieves employee details for the employees hired before the employee with employee_id = 100 and limits the result to 5 rows.
MAX
Short Description:
MAX in SQL is an aggregate function used to retrieve the maximum value within a column or set of values.
What SQLInterview Says:
MAX function in SQL is an aggregate function that retrieves the maximum value from a specified column or set of values. It scans through the dataset and returns the highest value found within the column. It's often used to find the maximum value within a dataset, providing insights into the largest value present in the specified column.
-- MAX function example in SQL
-- Find the maximum age among employees
SELECT MAX(age) AS maximum_age FROM employees;
MIN
Short Description:
MIN in SQL is an aggregate function used to retrieve the minimum value within a column or set of values.
What SQLInterview Says:
The MIN function in SQL is an aggregate function that retrieves the minimum value from a specified column or set of values. It scans through the dataset and returns the lowest value found within the column. It's commonly used to find the minimum value within a dataset, providing insights into the smallest value present in the specified column.
-- MIN function example in SQL
-- Find the minimum salary among employees
SELECT MIN(salary) AS minimum_salary FROM employees;
NOT
Short Description:
NOT in SQL is a logical operator used to negate a condition or reverse the result of a Boolean expression.
What SQLInterview Says:
NOT operator in SQL is a logical operator used to negate the result of a condition. It reverses the Boolean value of an expression, returning FALSE if the condition is true and TRUE if the condition is false. This operator is valuable for performing operations that require the opposite of a given condition.
-- NOT operator example in SQL
-- Retrieve employees who are NOT in the 'Sales' department
SELECT * FROM employees WHERE NOT department = 'Sales';
-- Retrieve customers with non-null email addresses
SELECT * FROM customers WHERE NOT email_address IS NULL;
-- Retrieve users whose names do NOT start with 'A'
SELECT * FROM users WHERE NOT user_name LIKE 'A%';
NOT NULL
Short Description:
NOT NULL constraint in SQL is used to specify that a column cannot contain any NULL values, ensuring that every row must have a non-null value in that column.
What SQLInterview Says:
NOT NULL constraint in SQL is a table constraint that ensures a column does not allow NULL values. When applied to a column during table creation or modification, it mandates that every row must contain a valid (non-NULL) value for that particular column. It guarantees data integrity by preventing the insertion of NULL values and is beneficial when certain columns are required to always contain data.
CREATE TABLE portfolio(
security_id integer NOT NULL,
ticker text NOT NULL,
price numeric
);
-- Create an 'employees' table with NOT NULL constraints on 'first_name' and 'last_name' columns
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL, -- Applying NOT NULL constraint to 'first_name'
last_name VARCHAR(50) NOT NULL -- Applying NOT NULL constraint to 'last_name'
);
-- Try to insert a record with NULL values (should fail due to NOT NULL constraints)
INSERT INTO employees (first_name, last_name)
VALUES
('Bob', NULL); -- This insertion will fail
NULLIF
Short Description:
NULLIF() function is used to compare two expressions. It returns NULL if the two expressions are equal; otherwise, it returns the first expression.
What SQLInterview Says:
NULLIF() function in SQL compares two expressions. If the two expressions are equal, it returns NULL. However, if the expressions are not equal, it returns the first expression. This function is useful when you want to replace specific values with NULL, typically to handle scenarios where specific values should be treated as missing or invalid.
-- NULLIF function example in SQL
-- Replace 'N/A' with NULL in the product descriptions
SELECT product_name, NULLIF(product_description, 'N/A') AS updated_description
FROM products;
-- Calculate the ratio while handling division by zero
SELECT numerator, denominator, NULLIF(numerator / NULLIF(denominator, 0), 1) AS calculated_ratio
FROM fractions;
-- Filter certain values from a result set
SELECT item_name, NULLIF(sold_quantity, 0) AS non_zero_sold_quantity
FROM sales;
OR
Short Description:
OR command in PostgreSQL is a logical operator used to combine multiple conditions in a WHERE clause to retrieve rows that meet at least one of the specified conditions.
What SQLInterview Says:
OR command allows you to perform logical operations on multiple conditions in a SQL query. It is commonly used in conjunction with the WHERE clause to create more complex filtering conditions. When used, the OR operator evaluates each condition separately and returns rows that satisfy at least one of the conditions. This provides flexibility in querying the database and expands the range of search possibilities.
-- Example 1: Retrieving rows that satisfy at least one condition
SELECT * FROM employees WHERE department = 'Sales' OR department = 'Marketing';
-- This query retrieves all employees from the Sales or Marketing department.
-- Example 2: Using OR with other operators
SELECT * FROM orders WHERE total_amount > 1000 OR status = 'Pending';
-- The query retrieves orders with a total amount greater than 1000 or those with a 'Pending' status.
-- Example 3: Combining OR with AND
SELECT * FROM products WHERE (category = 'Electronics' OR category = 'Appliances') AND price < 500;
-- This query fetches products that belong to the 'Electronics' or 'Appliances' category and have a price less than 500.
-- Example 4: Using OR in a subquery
SELECT * FROM employees WHERE department = 'HR' OR employee_id IN (SELECT manager_id FROM employees WHERE department = 'HR');
-- The query retrieves all employees from the HR department or those who are managers in the HR department.
-- Example 5: Combining OR with LIKE operator
SELECT * FROM customers WHERE name LIKE '%Smith%' OR email LIKE '%smith%';
-- This query fetches customers with a name containing 'Smith' or an email containing 'smith'.
-- Example 6: Nesting OR conditions
SELECT * FROM orders WHERE (status = 'Shipped' AND total_amount > 500) OR (status = 'Cancelled' AND total_amount > 1000);
-- The query retrieves orders that are either shipped with a total amount greater than 500 or cancelled with a total amount greater than 1000.
-- Example 7: Using OR in a JOIN condition
SELECT o.order_id, c.customer_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id OR o.sales_rep_id = c.employee_id;
-- This query joins the orders and customers tables based on either the customer_id matching or the sales_rep_id matching.
ORDER BY
Short Description:
ORDER BY clause is used to sort the result set of a query based on specified columns or expressions in ascending or descending order.
What SQLInterview Says:
ORDER BY clause in SQL is used to sort the rows retrieved by a SELECT statement. It arranges the result set in either ascending (default) or descending order based on the specified columns or expressions. This clause is helpful for organizing query results according to certain criteria, such as alphabetical order, numerical order, or date order, making it easier to analyze and understand the data.
-- ORDER BY function example in SQL
-- Retrieve employee names and their salaries in descending order of salary
SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;
-- List song names and their durations in descending order of song duration within each album
SELECT song_name, duration, album_name
FROM songs
ORDER BY album_name, duration DESC;
-- Retrieve product names and their prices in ascending order of price
SELECT product_name, price
FROM products
ORDER BY price ASC;
OVER
Short Description:
OVER in SQL is used in conjunction with window functions to define the window or set of rows over which a function operates.
What SQLInterview Says:
OVER clause in SQL is used with window functions to specify the window or set of rows within the result set that the function will operate on. It defines the partitioning and ordering of rows for window functions, enabling them to perform calculations or retrieve values within specific partitions or orderings of the result set. This clause is crucial for controlling the scope and behavior of window functions.
-- OVER clause example in SQL
SELECT
department,
employee_id,
salary,
AVG(salary) OVER (PARTITION BY department) AS avg_salary_per_department
FROM
employees;
PARTITION BY
Short Description:
PARTITION BY clause in SQL is used in conjunction with analytical functions to divide the result set into partitions based on specified column(s), enabling separate calculations or analysis within each partition.
What SQLInterview Says:
PARTITION BY clause in SQL is used with analytical functions to create partitions within the result set. It divides the data into separate groups based on specified column(s) or expressions. When combined with analytical functions like ROW_NUMBER(), RANK(), or SUM(), it allows performing calculations or analysis separately within each partition. This clause is beneficial for comparing or evaluating data within specific groups or subsets.
-- Creating a sample table
CREATE TABLE sales (
sale_id SERIAL PRIMARY KEY,
product_id INT,
sale_amount DECIMAL(10, 2),
sale_date DATE
);
-- Inserting sample data
INSERT INTO sales (product_id, sale_amount, sale_date)
VALUES (1, 150.00, '2023-01-05'),
(2, 200.00, '2023-01-05'),
(1, 170.00, '2023-01-06'),
(2, 190.00, '2023-01-06'),
(1, 180.00, '2023-01-07'),
(2, 210.00, '2023-01-07');
-- Using PARTITION BY with SUM() to calculate total sales amount per product for each date
SELECT product_id, sale_date, sale_amount,
SUM(sale_amount) OVER(PARTITION BY product_id, sale_date) AS total_sales
FROM sales
ORDER BY sale_date, product_id;
POSITION
Short Description:
POSITION in SQL is used to find the position of a substring within a string.
What SQLInterview Says:
POSITION function in SQL is used to determine the position of a specified substring within a string expression. It returns the position of the first occurrence of the substring within the string. If the substring is not found, it returns 0. The function takes two arguments: the substring to search for and the string in which to search for that substring.
-- Find the position of 'llo' within the string 'Hello'
SELECT POSITION('llo' IN 'Hello') AS Substring_Position;
-- will return 3, as 'llo' starts from the 3rd position within 'Hello'.
-- Find the position of 'SQL' within 'SQLInterview'
SELECT POSITION('SQL' IN 'SQLInterview') AS Substring_Position_SQL;
-- will return 1, as 'SQL' starts from the 1st position within 'SQLInterview'.
-- Find the position of 'World' within 'Hello'
SELECT POSITION('World' IN 'Hello') AS Substring_Position_Not_Found;
-- will return 0, as 'World' is not found within 'Hello'.
RANDOM
Short Description:
RANDOM clause in SQL is used to retrieve random rows from a table. It is often employed in conjunction with ORDER BY to shuffle the result set and fetch a random selection of rows.
What SQLInterview Says:
RANDOM clause in SQL, particularly in PostgreSQL, is a function used to generate random values. When used in combination with ORDER BY, it allows the retrieval of random rows from a table. This can be beneficial in scenarios where you need to sample or select a random subset of data for analysis or presentation. The RANDOM function generates a random value for each row and then sorts the result set based on these values, allowing you to obtain a randomized selection.
SELECT *
FROM products
ORDER BY RANDOM() -- Order the rows randomly
LIMIT 5; -- Retrieve only 5 random rows
RANK
Short Description:
RANK() function in SQL assigns a unique rank to each row within a result set based on the specified ordering, allowing for ties and leaving gaps in ranks.
What SQLInterview Says:
RANK() function in SQL is used to assign a unique rank to each row within a result set, based on a specified ordering. It handles ties by assigning the same rank to rows with identical values and creates gaps in ranks for subsequent rows. This function is useful for ranking data, but it may not produce consecutive ranks in the case of ties.
-- Creating a sample table
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
student_name VARCHAR(100),
subject VARCHAR(50),
score INT
);
-- Inserting sample data
INSERT INTO students (student_name, subject, score)
VALUES ('John', 'Math', 85),
('Alice', 'Math', 78),
('Bob', 'Math', 90),
('Eva', 'Math', 85),
('Mike', 'Math', 92);
-- Using RANK() to rank students' scores within the Math subject
SELECT student_id, student_name, subject, score,
RANK() OVER(PARTITION BY subject ORDER BY score DESC) AS student_rank
FROM students
WHERE subject = 'Math';
REPLACE
Short Description:
REPLACE function in SQL is used to replace occurrences of a specified substring within a string with another substring.
What SQLInterview Says:
REPLACE function in SQL is employed to replace occurrences of a specific substring within a string with a different substring. It searches for a target string and replaces it with the provided replacement string in the given input string. This function is helpful for modifying or formatting string data within queries.
-- Replacing 'apple' with 'orange' in the string
SELECT REPLACE('An apple a day keeps the doctor away', 'apple', 'orange') AS replaced_string;
-- output: An orange a day keeps the doctor away
-- Replace 'o' with '0' in a string
SELECT REPLACE('Hello World', 'o', '0') AS replaced_string;
-- Output: Hell0 W0rld
REVERSE
Short Description:
REVERSE function in SQL is used to reverse the order of characters within a string value.
What SQLInterview Says:
REVERSE function in SQL is used to reverse the order of characters within a string. It takes a string input and returns the characters in reverse order. This function is often used to reverse the sequence of characters within a string for various purposes, such as data transformation or formatting.
-- Reversing a string using the REVERSE function
SELECT REVERSE('SQLInterview') AS reversed_string;
-- -- This query reverses the string 'SQLInterview' and returns the result 'weivretnILQS'
-- Reversing a numeric string
SELECT REVERSE('1234567890') AS reversed_numeric_string;
-- This query reverses the numeric string '1234567890' and returns the result '0987654321'.
REVOKE
Short Description:
REVOKE: SQL clause used to revoke specific privileges or permissions on database objects from users or roles.
What SQLInterview Says:
REVOKE clause in SQL is used to retract or revoke previously granted permissions or privileges from users or roles on database objects such as tables, views, schemas, or functions. This command plays a crucial role in managing security within a database environment by removing specific access rights previously granted using the GRANT command. It allows database administrators to restrict or modify user or role access to certain operations on database objects.
-- Revoke SELECT privilege on a table from a specific user
REVOKE SELECT ON table_name FROM username; -- Revoking SELECT privilege
-- Revoke INSERT, UPDATE, DELETE privileges on a table from a role
REVOKE INSERT, UPDATE, DELETE ON table_name FROM role_name; -- Revoking multiple privileges
-- Revoke ALL privileges on all tables in a schema from a role
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name FROM role_name; -- Revoking all privileges
-- Revoke usage on a schema from a specific user
REVOKE USAGE ON SCHEMA schema_name FROM username;
-- Revoke EXECUTE privilege on a function from a role
REVOKE EXECUTE ON FUNCTION function_name(argument_type) FROM role_name;
-- Revoke SELECT privilege on specific columns of a table from a user
REVOKE SELECT (column1, column2) ON table_name FROM username;
-- Revoke SELECT privilege on a table and cascade to dependent objects
REVOKE SELECT ON table_name FROM username CASCADE;
RIGHT
Short Description:
RIGHT() string function is used to extract a specified number of characters from the right side of a string.
What SQLInterview Says:
RIGHT() function in SQL extracts a specified number of characters from the end (right side) of a string. It takes two arguments: the input string and the number of characters to extract. This function returns a substring containing the specified number of characters from the right side of the input string. It's beneficial for extracting portions of strings, particularly when dealing with textual data requiring specific lengths or portions from the end of the string.
-- Extract file extensions from file names
SELECT file_name, RIGHT(file_name, CHAR_LENGTH(file_name) - POSITION('.' IN REVERSE(file_name))) AS file_extension
FROM files;
-- Extract file extensions from file names
SELECT file_name, RIGHT(file_name, CHAR_LENGTH(file_name) - POSITION('.' IN REVERSE(file_name))) AS file_extension
FROM files;
-- Display last 20 characters of product descriptions
SELECT product_name, RIGHT(product_description, 20) AS last_chars_description
FROM products;
RIGHT JOIN
Short Description:
RIGHT JOIN function is used to retrieve all records from the right table and matching records from the left table based on a specified condition. It includes all rows from the right table and only matching rows from the left table.
What SQLInterview Says:
RIGHT JOIN in SQL retrieves all records from the right table specified in the query and matching records from the left table. If there are no matches found in the left table, NULL values are included for the columns from the left table in the result set. This type of join is beneficial when you want all records from the right table and related data from the left table, and it allows you to perform operations considering the right table as the primary focus.
-- RIGHT JOIN function example in SQL
-- Retrieve all departments and their associated employees (if any)
SELECT d.department_name, e.employee_name
FROM departments d
RIGHT JOIN employees e ON d.department_id = e.department_id;
ROLLBACK
Short Description:
ROLLBACK clause in SQL is used to undo or revert the changes made in a transaction that has not been committed yet, returning the database to its state before the transaction began.
What SQLInterview Says:
ROLLBACK statement in SQL is a crucial command used to undo and discard changes made within a transaction. It is typically used to revert any modifications if an error occurs during a series of database operations performed within a transaction. When executed, ROLLBACK reverts all changes back to the last COMMIT or SAVEPOINT, ensuring data integrity by discarding any uncommitted changes.
BEGIN; -- Start a transaction
UPDATE customers
SET status = 'Inactive'
WHERE age > 50; -- Update customer status for certain conditions
-- ... other operations within the transaction
-- An error occurs or conditions are not as expected
-- Roll back the changes made within the transaction
ROLLBACK;
-- Check the current state of the 'customers' table after rollback
SELECT * FROM customers;
ROW_NUMBER
Short Description:
ROW_NUMBER() function in SQL assigns a unique sequential integer to each row within a partition of a result set based on the specified ordering.
What SQLInterview Says:
ROW_NUMBER() function in SQL is used to assign a unique sequential integer to each row within a specified partition of a result set. It is particularly useful for ranking or identifying specific rows based on a defined order within a partition. This function doesn't affect the actual table; it's used in queries to generate a row number for each row.
-- Creating a sample table
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name VARCHAR(100),
department VARCHAR(50),
salary NUMERIC
);
-- Inserting sample data
INSERT INTO employees (employee_name, department, salary)
VALUES ('John Doe', 'IT', 60000),
('Jane Smith', 'HR', 55000),
('Alice Johnson', 'IT', 62000),
('Bob Williams', 'Finance', 58000),
('Eva Brown', 'HR', 57000);
-- Using ROW_NUMBER() to assign a row number based on salary within each department
SELECT employee_id, employee_name, department, salary,
ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;
SAVEPOINT
Short Description:
SAVEPOINT statement in SQL creates a named point within a transaction to which you can later roll back, providing a way to partially undo changes made within a transaction.
What SQLInterview Says:
SAVEPOINT statement in SQL allows you to set a named marker within a transaction, creating a point to which you can roll back later if needed. It provides a level of granularity for rolling back changes within a transaction, allowing partial rollback rather than the entire transaction. This feature is helpful in complex transactions where you might need to undo specific changes without reverting all modifications.
-- Creating a sample table
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name VARCHAR(100),
salary DECIMAL(10, 2)
);
-- Inserting sample data
INSERT INTO employees (employee_name, salary)
VALUES ('John Doe', 50000.00),
('Jane Smith', 60000.00),
('Alice Johnson', 55000.00);
-- Starting a transaction
BEGIN;
-- Update salary for employee 'John Doe'
UPDATE employees SET salary = 52000.00 WHERE employee_name = 'John Doe';
-- Setting a SAVEPOINT
SAVEPOINT salary_update;
-- Update salary for employee 'Jane Smith'
UPDATE employees SET salary = 65000.00 WHERE employee_name = 'Jane Smith';
-- Checking data after updates
SELECT * FROM employees;
-- Rolling back to the SAVEPOINT
ROLLBACK TO SAVEPOINT salary_update;
-- Checking data after rolling back
SELECT * FROM employees;
-- Committing the transaction
COMMIT;
SELECT
Short Description:
The SELECT command in PostgreSQL is used to retrieve data from one or more tables in a database.
What SQLInterview Says:
The SELECT command in PostgreSQL is a fundamental SQL statement that allows you to retrieve data from one or more tables in a database. It forms the core of most SQL queries and provides the ability to specify which columns to include in the result set, apply filters using WHERE clause conditions, perform aggregations with functions like COUNT, SUM, and AVG, and join multiple tables together.
-- Example 1: Retrieve all columns from a table
SELECT *
FROM table_name;
-- This query retrieves all columns from the specified table.
-- Example 2: Retrieve specific columns from a table
SELECT column1, column2
FROM table_name;
-- This query retrieves only the specified columns from the given table.
-- Example 3: Apply filtering using WHERE clause
SELECT *
FROM table_name
WHERE condition;
-- This query retrieves all columns from the specified table where the specified condition is true.
-- Example 4: Perform aggregation using functions
SELECT column, COUNT(*)
FROM table_name
GROUP BY column;
-- This query calculates the count of records for each distinct value in the specified column.
-- Example 5: Join multiple tables together
SELECT *
FROM table1
JOIN table2 ON table1.column = table2.column;
-- This query combines data from multiple tables based on the specified join condition.
-- Example 6: Sort the result set in ascending order
SELECT *
FROM table_name
ORDER BY column ASC;
-- This query retrieves all columns from the specified table and sorts them in ascending order based on the specified column.
-- Example 7: Limit the number of rows returned
SELECT *
FROM table_name
LIMIT 10;
-- This query retrieves the first 10 rows from the specified table.
-- Example 8: Calculate additional values using expressions
SELECT column1, column2 + column3 AS total
FROM table_name;
-- This query retrieves column1 and calculates the sum of column2 and column3 as the alias "total".
-- Example 9: Calculate values using expressions
SELECT SELECT (15 + 5) / 4
-- This query calculates the result of adding 15 and 5 together, which equals 20, and then divides that sum by 4.
SUBSTRING
Short Description:
SUBSTRING() function is used to extract a substring from a string based on a specified starting position and length.
What SQLInterview Says:
SUBSTRING() function in SQL is used to extract a portion of a string, known as a substring, based on specified starting position and length parameters. It takes three arguments: the input string, the starting position (from where to start extracting), and an optional length parameter (the number of characters to extract). This function is valuable for extracting specific parts of a string, enabling operations on partial or desired portions of textual data.
-- SUBSTRING function example in SQL
-- Extract the middle name from the full name column
SELECT full_name, SUBSTRING(full_name FROM '\s(.+)\s') AS middle_name
FROM employees;
-- Extract area codes from phone numbers
SELECT phone_number, SUBSTRING(phone_number FROM '\(([0-9]{3})\)') AS area_code
FROM contacts;
-- Extract email domains from email addresses
SELECT email_address, SUBSTRING(email_address FROM '@(.+)$') AS email_domain
FROM users;
SUM
Short Description:
SUM in SQL is an aggregate function used to calculate the total sum of values within a column or set of values.
What SQLInterview Says:
SUM function in SQL is an aggregate function that computes the sum of all values in a specified column or set of values. It adds up all the numeric values present in the specified column and returns the total sum. This function is commonly used to obtain the aggregated sum of numerical data, providing insights into the combined value of the dataset.
-- SUM function example in SQL
-- Calculate the total sales amount
SELECT SUM(sales_amount) AS total_sales FROM sales;
TRANSLATE
Short Description:
TRANSLATE function in SQL is used to replace characters in a string based on a mapping of each character in the input string to a corresponding character in the provided translation string.
What SQLInterview Says:
TRANSLATE function in SQL is utilized to replace individual characters in a string based on a one-to-one mapping specified in the translation string. It searches for occurrences of characters in the input string and replaces them with corresponding characters from the translation string. This function allows for character-level substitution and is useful for specific character transformations within a string.
-- Using TRANSLATE to replace characters in a string
SELECT TRANSLATE('Hello World', 'Hlo', '123') AS translated_string;
-- Output: 1e223 W3r2d
-- Translate punctuation characters to empty space
SELECT TRANSLATE('Hello, World! How are you?', ',!?', ' ') AS translated_string;
-- Output: Hello World How are you
UNION
Short Description:
UNION in SQL is used to combine the results of two or more SELECT queries into a single result set, removing duplicate rows by default.
What SQLInterview Says:
UNION operator in SQL is employed to merge the result sets of multiple SELECT queries into a unified result set. It effectively combines rows from different queries into one result set while removing duplicate rows by default. However, if you wish to retain duplicate rows, you can use UNION ALL. This operator is useful for aggregating data from multiple sources or tables with similar structures.
-- UNION example in SQL
-- Combine the results of two SELECT queries into a single result set
-- UNION combines the results of both queries into a single result set, removing duplicate rows from the combined result set.
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
UNION ALL
Short Description:
UNION ALL in SQL is used to combine the results of two or more SELECT queries into a single result set, including all rows (including duplicates) from each query.
What SQLInterview Says:
UNION ALL operator in SQL is similar to UNION but retains all rows, including duplicates, from the result sets of the individual SELECT queries. It combines rows from different queries into one result set without eliminating duplicate rows. This operator is beneficial when you want to combine and see all results from multiple sources without eliminating any duplicates.
-- UNION ALL example in SQL
-- Combine the results of two SELECT queries into a single result set, including duplicates
-- UNION ALL combines the results of both queries into a single result set, including all rows from both result sets, even if there are duplicates.
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;
UNIQUE
Short Description:
UNIQUE constraint in SQL is used to ensure that the values in a column or a group of columns are unique across all the rows in a table, preventing duplicate entries.
What SQLInterview Says:
UNIQUE constraint in SQL is a database constraint that ensures the uniqueness of values within one or more columns of a table. When applied to a column or a combination of columns, it guarantees that no two rows in the table have the same value(s) for that column or combination of columns. It prevents duplicate entries and is useful for maintaining data integrity by enforcing uniqueness among specified columns.
CREATE TABLE portfolio(
secid integer UNIQUE,
ticker text NOT NULL,
price numeric
);
-- Create a 'students' table with a UNIQUE constraint on the 'email' column
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
student_name VARCHAR(50),
email VARCHAR(100) UNIQUE -- Applying UNIQUE constraint to 'email' column
);
-- Insert data into the 'students' table
INSERT INTO students (student_name, email) VALUES
('John Doe', 'john@example.com'),
('Jane Smith', 'jane@example.com'),
('Alice Johnson', 'alice@example.com');
-- Try to insert a duplicate email (should fail due to UNIQUE constraint)
INSERT INTO students (student_name, email) VALUES
('Bob Harris', 'john@example.com'); -- This insertion will fail
-- Retrieve all records from the 'students' table
SELECT * FROM students;
UPPER
Short Description:
UPPER function in SQL is used to convert characters in a string to uppercase, transforming all lowercase letters to their uppercase equivalents within the specified string.
What SQLInterview Says:
UPPER function in SQL is a string manipulation function used to convert all characters in a string expression to uppercase. It is commonly employed to standardize text data by converting lowercase letters to their corresponding uppercase forms. This function facilitates case-insensitive comparisons and ensures uniformity in text data.
-- Retrieve usernames and their corresponding uppercase versions
SELECT username, UPPER(username) AS uppercase_username
FROM users;
VIEW
Short Description:
VIEW statement in SQL is used to create a virtual table based on the result set of a SELECT query, allowing users to query the view as they would a regular table without storing the actual data.
What SQLInterview Says:
VIEW statement in SQL is used to create a virtual table that does not store data itself but instead represents the result set of a query. It acts as a saved query that can be referenced and queried just like a regular table. Views can simplify complex queries, enhance security by restricting access to specific columns or rows, and provide a way to encapsulate frequently used queries into reusable objects.
-- Creating a VIEW named 'high_salary_employees'
CREATE VIEW high_salary_employees AS
SELECT employee_id, employee_name, department, salary
FROM employees
WHERE salary > 60000.00;
-- Querying the created VIEW
SELECT * FROM high_salary_employees;
Continue with Google