what are the different types of keys in sql database
In a SQL database, there are several types of keys that are used to define relationships
between tables and ensure data integrity:
1. Primary key: A primary key is a unique identifier for a record in a table. It is a
column or set of columns that can uniquely identify each row in the table.
2. Foreign key: A foreign key is a field in one table that refers to the primary key in
another table. It is used to establish a relationship between the two tables.
3. Unique key: A unique key is a constraint that ensures that a column or set of
columns in a table contains only unique values. It can be used as an alternative
to a primary key when the primary key cannot be used for some reason.
4. Candidate key: A candidate key is a column or set of columns that can be used
as a primary key. It is unique and can be used to identify each row in the table.
5. Composite key: A composite key is a combination of two or more columns that
can be used as a primary key. It is used when a single column cannot uniquely
identify each row in the table.
6. Super key: A super key is a set of one or more columns that can be used to
uniquely identify each row in a table. It is not necessarily the minimum set of
columns that can be used as a key.
what are window functions with examples in sql database
Window functions are a type of analytic function in SQL that perform calculations across
rows in a result set without altering the underlying data. Window functions are used to
perform complex calculations and analysis, such as calculating moving averages,
ranking results, and comparing values across rows. The syntax for window functions is
similar to other functions in SQL, but includes an OVER() clause that defines the window
or set of rows to perform the calculation on.
Here are some examples of window functions in SQL:
1. RANK(): This function assigns a rank to each row in the result set based on a
specified order. For example, the following SQL code assigns a rank to each
product based on their sales:
SELECT product, sales, RANK() OVER (ORDER BY sales DESC) as rank
FROM sales_data;
2. LAG() and LEAD(): These functions return the value of a column from a previous
or subsequent row in the result set. For example, the following SQL code
calculates the difference in sales between the current and previous month:
SELECT month, sales, LAG(sales) OVER (ORDER BY month) as
prev_sales,
sales - LAG(sales) OVER (ORDER BY month) as sales_diff
FROM sales_data;
3. SUM() and AVG(): These functions calculate the sum or average of a column
across a specified window or set of rows. For example, the following SQL code
calculates the moving average of sales for each product over a rolling 3-month
period:
SELECT product, month, sales,
AVG(sales) OVER (PARTITION BY product ORDER BY month ROWS
BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
FROM sales_data;
4. NTILE(): This function divides the result set into a specified number of buckets
and assigns each row to a bucket. For example, the following SQL code assigns
each product to a quartile based on their sales:
SELECT product, sales, NTILE(4) OVER (ORDER BY sales DESC) as
quartile
FROM sales_data;
what are stored procedures in sql
Stored procedures are precompiled blocks of SQL code that are stored in a SQL
database and can be executed on demand. They are a type of database object that
encapsulates a series of SQL statements and can accept input parameters and return
values. Stored procedures are commonly used to simplify complex SQL operations,
improve performance, and maintain data integrity.
Here are some characteristics and benefits of stored procedures in SQL:
1. Reusability: Stored procedures can be reused by multiple applications or scripts,
which reduces the amount of code duplication and increases consistency.
2. Performance: Stored procedures can improve performance by reducing network
traffic and minimizing the amount of code that needs to be compiled.
3. Security: Stored procedures can be used to restrict access to sensitive data or
operations, which can help prevent unauthorized access.
4. Encapsulation: Stored procedures can encapsulate complex logic, which can
make it easier to maintain and modify the code over time.
5. Modularity: Stored procedures can be organized into modules or libraries, which
can help simplify the overall architecture of the database.
CREATE PROCEDURE get_customer_orders
@customer_id int
AS
BEGIN
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = @customer_id
END
In this example, the stored procedure accepts an input parameter (@customer_id) and
returns a result set of orders for that customer. The stored procedure can be executed
by calling its name and passing in a value for the input parameter:
EXEC get_customer_orders 1234;
This would return a list of orders for the customer with the ID 1234.
what are subqueries and how to use it and its advantages
Subqueries, also known as nested queries or inner queries, are a powerful feature of
SQL that allow you to execute a query within another query. In a subquery, you use the
result of one query as the input for another query.
To use a subquery, you typically enclose the inner query within parentheses and use it
as an expression in the outer query. The syntax for a basic subquery is:
SELECT column1, column2, ...
FROM table1
WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);
This example selects all columns from table1 where column1 is in the results of the
subquery, which selects column1 from table2 where a certain condition is met.
One of the main advantages of subqueries is their ability to simplify complex queries by
breaking them down into smaller, more manageable pieces. Subqueries also allow you
to query multiple tables or combine different criteria in a single query. They are also
useful for generating dynamic values, such as calculating aggregate functions based on
a subset of data.
However, subqueries can also have some disadvantages, such as reduced performance
due to additional processing required for the nested query. In some cases, it may be
more efficient to use a join or a temporary table instead of a subquery.
Overall, subqueries are a useful tool for SQL developers and can help you write more
efficient and effective queries.
what are views , temp tables and cte in sql and their advantages
In SQL, views, temporary tables, and Common Table Expressions (CTEs) are used to
organize and manipulate data in a database. Each of these has its own advantages and
use cases.
1. Views: A view in SQL is a virtual table that contains a query's results, but it does
not store data in the database. A view is created by selecting data from one or
more tables and can be used to simplify complex queries or to restrict access to
certain data.
Advantages of views:
Views can be used to simplify complex queries by encapsulating frequently used
joins, subqueries, and aggregations into a single object.
Views can be used to restrict access to sensitive data by providing a layer of
security, allowing users to access only the data they need to see.
Views can be used to hide the complexity of a database schema, making it easier
for application developers to write code.
2. Temporary Tables: A temporary table is a table that exists only for the duration
of a database session or transaction. Temporary tables can be created and
populated with data just like permanent tables, and they can be used to store
intermediate results that can be reused in other queries.
Advantages of temporary tables:
Temporary tables can be used to break down complex queries into smaller, more
manageable parts.
Temporary tables can be used to store intermediate results that can be reused in
other queries, reducing the need for repetitive subqueries and joins.
Temporary tables can be used to isolate data for testing and debugging
purposes.
3. Common Table Expressions (CTEs): A CTE is a temporary result set that is
defined within the execution of a single SELECT, INSERT, UPDATE, DELETE, or
CREATE VIEW statement. CTEs are similar to subqueries, but they can be
referenced multiple times within a single query.
Advantages of CTEs:
CTEs can simplify complex queries by breaking them down into smaller, more
manageable parts.
CTEs can be used to improve query performance by allowing the database to
reuse intermediate results.
CTEs can be used to make queries more readable and maintainable, by
separating the logical structure of the query from the physical implementation.
sql query to create table with constraints and insert sample data in it
Sure, here's an example SQL query to create a table with constraints and insert some
sample data into it:
CREATE TABLE Customers (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
age INT CHECK(age >= 18),
created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO Customers (id, name, email, age) VALUES
(1, 'John Doe', 'johndoe@[Link]', 25),
(2, 'Jane Smith', 'janesmith@[Link]', 30),
(3, 'Bob Johnson', 'bobjohnson@[Link]', 42);
This query creates a table called Customers with columns for id, name, email, age, and
created_at. The id column is the primary key and is required. The name and email
columns are also required, and the email column has a unique constraint to ensure that
no two customers have the same email address. The age column has a check constraint
to ensure that the age is greater than or equal to 18. The created_at column has a
default value of the current timestamp.
The INSERT statement inserts three rows of sample data into the table. Each row
includes values for the id, name, email, and age columns. The created_at column is not
included in the INSERT statement, so it will be automatically set to the current
timestamp.
top 15 sql data engineer interview basic questions with answers
Sure, here are 15 SQL data engineer interview questions along with sample answers:
1. What is the difference between a primary key and a foreign key? Answer: A
primary key is a column or group of columns in a table that uniquely identifies
each row in the table. A foreign key is a column or group of columns in a table
that refers to the primary key of another table, creating a relationship between
the two tables.
2. What is a stored procedure? Answer: A stored procedure is a set of SQL
statements that are stored in the database and can be executed as a single unit.
It can be called from within a SQL statement, another stored procedure, or an
application.
3. What is an index? Answer: An index is a database object that helps to speed up
queries by providing a fast access path to data in a table. It is created on one or
more columns in a table and helps to optimize data retrieval operations.
4. What is normalization? Answer: Normalization is the process of organizing data in
a database to minimize redundancy and dependency. It involves dividing a table
into smaller tables and defining relationships between them to reduce data
duplication.
5. What is the difference between a left join and an inner join? Answer: An inner join
returns only the rows that have matching values in both tables being joined,
while a left join returns all the rows from the left table and the matching rows
from the right table. If there is no match in the right table, the result will still
include the row from the left table.
6. What is a view? Answer: A view is a virtual table based on the result of a select
statement. It is stored in the database and can be used in place of a table for
data retrieval operations.
7. What is a transaction? Answer: A transaction is a sequence of one or more SQL
statements that are executed as a single unit of work. It is used to ensure data
consistency and integrity by committing all the changes or rolling back the entire
transaction if any part of it fails.
8. What is a subquery? Answer: A subquery is a query that is embedded within
another query. It is used to retrieve data from one or more tables and use it as a
condition or value in the outer query.
9. What is a trigger? Answer: A trigger is a database object that is associated with a
table and is executed automatically in response to certain events, such as an
insert, update, or delete operation on the table.
10. What is a database index? Answer: A database index is a data structure that
improves the speed of data retrieval operations on a table by providing a quick
access path to specific data within the table.
11. What is the difference between UNION and UNION ALL? Answer: UNION combines
the result sets of two or more SELECT statements and eliminates duplicate rows,
while UNION ALL combines the result sets of two or more SELECT statements
without removing duplicate rows.
12. What is a cursor? Answer: A cursor is a database object that is used to retrieve
data from a table one row at a time. It is often used in stored procedures to
process each row of a result set individually.
13. What is a join? Answer: A join is a SQL operation that combines data from two or
more tables based on a common column. It is used to retrieve data from multiple
tables in a single query.
14. What is a null value? Answer: A null value is a special marker used in SQL to
indicate that a data value does not exist in the database.