SQL Interview Prep
What are the different types of statements available to you in TSQL?
Data Manipulation Language (DML) statements: These statements are used to manipulate data within a table. The most common DML statements are SELECT, INSERT, UPDATE, and DELETE. Data Definition Language (DDL) statements: These statements are used to create or modify the structure of database objects such as tables, views, indexes, and stored procedures. Examples of DDL statements are CREATE, ALTER, and DROP. Data Control Language (DCL) statements: These statements are used to control access to database objects. The most common DCL statements are GRANT, REVOKE, and DENY. Transaction Control Language (TCL) statements: These statements are used to control transactions in a database. The most common TCL statements are COMMIT, ROLLBACK, and SAVEPOINT.
What is a Database?
In the context of SQL, a database is a collection of related data that is organized and stored in a structured way. It is designed to efficiently store and manage large amounts of information, and provide fast and reliable access to that information when needed. A database typically includes tables, which hold specific types of data, and relationships between those tables, which help organize and connect the data. SQL, or Structured Query Language, is a programming language that is used to interact with databases and perform various operations, such as creating tables, inserting and retrieving data, and modifying the database structure.
What language is used to write Stored procedures?
T-SQL (Transact-SQL) is the language used to write stored procedures in SQL Server. T-SQL allows developers to write complex stored procedures that can manipulate data, perform calculations, and implement business logic. It also includes features for error handling, transactions, and dynamic SQL execution.
How many normal forms are there?
There are several normal forms, but the most commonly used are: 1. First Normal Form (1NF) 2. Second Normal Form (2NF) 3. Third Normal Form (3NF) 4. Boyce-Codd Normal Form (BCNF) 5. Fourth Normal Form (4NF) 6. Fifth Normal Form (5NF) There are also higher normal forms, such as Domain/Key Normal Form (DKNF) and Sixth Normal Form (6NF), but these are less commonly used.
If you wanted to delete information from a table what statement would you use?
To delete information from a table in SQL, you would use the DELETE statement. The syntax for the DELETE statement is as follows: DELETE FROM table_name WHERE condition;
Why would we go through the process of normalizing our data?
We go through the process of normalizing our data to avoid data redundancy and inconsistency. When data is stored in a non-normalized form, it can lead to data anomalies such as insertion, update, and deletion anomalies, which can cause inconsistencies in the data. Normalizing the data eliminates these anomalies and ensures that the data is consistent and accurate. Additionally, normalized data tends to be easier to maintain and update over time.
What are the basic parts of a simple TSQL Query
A simple TSQL query consists of three basic parts: SELECT statement: used to select the columns or fields that you want to retrieve from the table(s). FROM statement: specifies the table(s) that you want to retrieve the data from. WHERE statement: used to filter the data that is returned based on specific criteria or conditions. For example, the following TSQL query retrieves all the columns from the Customers table where the City column is set to 'London': SELECT * FROM Customers WHERE City = 'London';
What is the truncate statement used for? What is the key difference between this and your other options to remove data?
The TRUNCATE statement is used to delete all the rows from a table, effectively emptying the table. It differs from the DELETE statement in that TRUNCATE does not keep a log of individual row deletions, whereas DELETE does. This means that TRUNCATE can be faster and use fewer system resources than DELETE when removing all the rows from a large table. However, it is important to note that TRUNCATE cannot be rolled back, while DELETE can be rolled back.
What is a Sql Server stored procedure?
A SQL Server stored procedure is a precompiled and stored set of SQL statements that performs a specific task or a series of tasks in a database. Stored procedures are used to simplify complex queries and to centralize business logic within a database. They can also improve performance and security by reducing the amount of data sent over the network, and by limiting the exposure of database objects to end-users. Stored procedures can be called from within other SQL statements, or from application code. They can also take parameters and return values.
What is a SQL Injection Attack and how do you protect yourself against these?
A SQL injection attack is a type of security exploit where an attacker adds malicious SQL statements to a web application's input fields to manipulate the database. This can give the attacker access to sensitive information, modify or delete data, and even take over the entire database server. To protect against SQL injection attacks, it is important to use parameterized queries, which allow the database engine to distinguish between the SQL code and the user input. Another important step is to validate all user input, including input from forms, cookies, headers, and URLs, to ensure that it contains only the expected characters and is within the expected length. Additionally, it is recommended to limit database privileges to the least amount necessary to perform required tasks, and to monitor the system for unusual activity.
What is a relational Database?
A relational database is a type of database that organizes data into one or more tables, with a unique key identifying each row within the table. Each table represents an entity or a set of related entities, and the relationships between these entities are represented by the relationships between the tables.The tables are linked through keys that define the relationships between the data stored in the tables. Relational databases use Structured Query Language (SQL) to query and manipulate the data stored in the tables.
What is Data Normalization?
Data normalization is the process of organizing data in a database to reduce redundancy and dependency. The main idea behind normalization is to eliminate or minimize data repetition, such that each piece of data is only stored in one place. Normalization is achieved by dividing larger tables into smaller, related tables and defining relationships between them. There are several levels of normalization, with each level having specific rules and guidelines that need to be followed to achieve the desired normalization. The commonly used levels are first normal form (1NF), second normal form (2NF), and third normal form (3NF). By normalizing data, we can reduce data redundancy, improve data integrity, and make it easier to maintain and update the database. However, over-normalization can lead to complexity and poor performance, so it's important to strike a balance between normalization and practicality.
How do you declare a variable in TSQL?
In T-SQL, you can declare a variable using the DECLARE statement. Here is the syntax: DECLARE @variable_name data_type; For example, to declare an integer variable named myInt, you would use: DECLARE @myInt int; You can also initialize the variable with a value using the SET statement. For example: DECLARE @myInt int = 42;
What is a Foreign Key and how many can one table have?
In a relational database, a foreign key is a column or set of columns that refers to the primary key of another table. It establishes a relationship between two tables and enforces referential integrity between them. A table can have multiple foreign keys, depending on the number of relationships it has with other tables. However, each foreign key must reference a unique primary key in its parent table.
What is a Primary Key and how many can one table have?
In a relational database, a primary key is a column or set of columns that uniquely identifies each record in a table. It is used to enforce the integrity of the data and to establish relationships between tables. Each table can have only one primary key.
What is a database "Table"?
In a relational database, a table is a collection of data that is organized into rows and columns. Each row represents a single record or instance of the entity being stored in the table, while each column represents a specific attribute or property of the entity. A table is often used to store related data, and it is named based on the type of data it contains. For example, in a database for an e-commerce website, there may be tables for products, customers, orders, and payments.
What language do you use to communicate with the database?
In general, there are several programming languages you can use to communicate with a database, depending on the database management system you are using and the tools available for it. In the context of .NET, the most common language used to communicate with a database is SQL (Structured Query Language), which is a language specifically designed for managing and manipulating relational databases. However, .NET also provides various APIs and libraries (such as ADO.NET and Entity Framework) that allow you to use programming languages like C# or VB.NET to communicate with a database using SQL queries and commands.
What are the different types of Joins? (Write examples of each)
Inner Join: An inner join returns only the rows that have matching values in both tables. The syntax for an inner join is: SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column; For example, if we have two tables, "orders" and "customers", and we want to retrieve all orders along with their corresponding customer information, we can use an inner join as follows: SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id; Left Join: A left join returns all the rows from the left table (the first table in the join clause) and the matching rows from the right table (the second table in the join clause). If there is no match in the right table, the result will contain null values for the right table columns. The syntax for a left join is: SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column; For example, if we have two tables, "customers" and "orders", and we want to retrieve all customers along with their corresponding orders (if any), we can use a left join as follows: SELECT * FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id; Right Join: A right join returns all the rows from the right table (the second table in the join clause) and the matching rows from the left table (the first table in the join clause). If there is no match in the left table, the result will contain null values for the left table columns. The syntax for a right join is: SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column; For example, if we have two tables, "orders" and "customers", and we want to retrieve all orders along with their corresponding customer information (if any), we can use a right join as follows: SELECT * FROM orders RIGHT JOIN customers ON orders.customer_id = customers.customer_id; Full Outer Join: A full outer join returns all the rows from both tables and null values for the columns where there is no match. The syntax for a full outer join is: SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column; For example, if we have two tables, "customers" and "orders", and we want to retrieve all customers along with their corresponding orders (if any), and all orders even if there is no matching customer, we can use a full outer join as follows: SELECT * FROM customers FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;
When are "Joins" used?
Joins are used in SQL when you need to combine data from two or more tables based on a related column between them. They are used to retrieve data that is scattered across multiple tables by joining them together to create a single result set that contains data from all tables involved in the join. Joins are used when you need to: Combine data from two or more tables into a single result set Retrieve data based on relationships between tables Query data from a normalized database schema
If a stored procedure is too slow how can you enhance the speed?
Optimize queries: The first step to improving the performance of a stored procedure is to optimize the SQL queries used within it. This includes creating indexes on tables, using the correct join types, and using the appropriate SQL functions. Avoid using cursors: Cursors can be very slow and resource-intensive. Whenever possible, it is best to use set-based operations to manipulate data. Use temporary tables: Sometimes, it can be helpful to use temporary tables to improve the performance of a stored procedure. For example, if you need to perform multiple operations on a large set of data, you could create a temporary table that holds the results of the first operation and use it as input for the second operation. Use table variables instead of temporary tables: Table variables are similar to temporary tables but are stored in memory instead of on disk. They can be a good choice for small to medium-sized datasets. Re-evaluate the business logic: If a stored procedure is running slowly, it may be worth re-evaluating the business logic to see if there are any opportunities to simplify the code. Monitor performance: It is important to monitor the performance of stored procedures over time to identify areas for improvement. SQL Server provides several tools for monitoring performance, including SQL Server Profiler and SQL Server Management Studio.
What is a SQL Server?
SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is designed to store and manage large amounts of data in a structured way, and to provide a set of tools and features for managing and querying that data. SQL Server supports the Structured Query Language (SQL) and is widely used by businesses and organizations of all sizes for a variety of applications, including data warehousing, business intelligence, and e-commerce.
in SQL: What are indexes?
indexes are database objects that improve the speed of data retrieval operations on tables. An index is created on one or more columns of a table, which creates a copy of that column's data in a separate structure, allowing faster searches and sorting of data. When a SQL query includes a WHERE clause that filters rows based on a specific column, the query can use an index on that column to quickly locate the matching rows rather than scanning the entire table. This can greatly improve the performance of queries, especially on large tables with millions of rows.