SQL Interview prep

What is a relational Database?

A Relational Database is a type of database that stores data in tables. It organizes data into tables with rows and columns, where each column represents a particular type of data and each row represents a single record.

What is a SQL Injection Attack and how do you protect yourself against these?

A SQL Injection Attack is a type of security exploit in which an attacker injects malicious SQL statements into an application's input to manipulate the database. To protect against SQL Injection Attacks, developers should use parameterized queries and input validation.

What is a Sql Server stored procedure?

A SQL Server Stored Procedure is a precompiled block of code that is stored in a database. It is used to perform a specific task or set of tasks in the database.

What is a Database?

A Database is an organized collection of data that can be accessed, managed, and updated. It is a critical component of most software applications and is used to store and organize data.

What is a Foreign Key and how many can one table have?

A Foreign Key is a field in one table that refers to the Primary Key in another table. It is used to establish relationships between tables. A table can have multiple Foreign Keys.

What is a Primary Key and how many can one table have?

A column or set of columns that uniquely identify a table

What is a database "Table"?

A database "Table" is a collection of related data stored in rows and columns. It is the primary data storage object in a relational database.

What is data normalization?

Data normalization is the process of organizing data in a database to minimize redundancy and improve data integrity. It involves dividing large tables into smaller tables and defining relationships between them.

What are indexes?

Indexes are used to improve the performance of queries by providing faster access to data. They are created on columns in tables and can speed up data retrieval.

When are "Joins" used?

Joins are used to combine data from multiple tables in a relational database. They are used when data from one table needs to be combined with data from another table.

What language do you use to communicate with the database?

T-SQL is used to communicate with the database.

What language is used to write Stored procedures?

T-SQL is the language used to write Stored Procedures in SQL Server.

What are the different types of Joins? (Write examples of each)

The different types of joins include INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

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 remove all data from a table. The key difference between TRUNCATE and other options to remove data (e.g., DELETE) is that TRUNCATE removes all data from the table, whereas DELETE removes only specific rows.

What are the basic parts of a simple TSQL Query?

The basic parts of a simple T-SQL Query include the SELECT statement, which specifies the columns to retrieve, the FROM clause, which specifies the table or tables to retrieve the data from, and the WHERE clause, which filters the data based on a specific condition.

What are the different types of statements available to you in TSQL?

The different types of statements available in T-SQL include Data Manipulation Language (DML) statements (e.g., SELECT, INSERT, UPDATE, DELETE), Data Definition Language (DDL) statements (e.g., CREATE, ALTER, DROP), and Control Flow statements (e.g., IF, WHILE, BEGIN/END).

How many normal forms are there?

There are six normal forms (1NF, 2NF, 3NF, BCNF, 4NF, and 5NF).

How do you declare a variable in TSQL?

To declare a variable in T-SQL, you use the DECLARE statement followed by the variable name and data type. Ex. Declare @Name nvarchar(50) = 'Vincent'

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.

Why would we go through the process of normalizing our data?

We go through the process of normalizing our data to improve data integrity and reduce data redundancy. This can lead to more efficient data storage and retrieval, as well as easier database maintenance.