SQL Interview Prep

If you wanted to delete information from a table what statement would you use?

The DELETE Statement in SQL is used to delete existing records from a table. We can delete a single record or multiple records depending on the condition we specify in the WHERE clause. If you want to delete specific records in your table, you would use the DELETE statement. If you want to delete all of the records from a table, then you would use the TRUNCATE statement.

When are "Joins" used?

The SQL Joins clause is used when you need to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

What are indexes?

A SQL index is a quick lookup table for finding records users need to search frequently. An index is small, fast, and optimized for quick lookups. It is very useful for connecting the relational tables and searching large tables.

What is a database?

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A series of rows and columns with sectioned information stored by record.

What is a relational Database? Collapse

A relational database is a type of database that stores and provides access to data points that are related to one another via a common column or datapoint.

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

Normalization makes sure that all of your data looks and reads the same way across all records. Normalization will standardize fields including company names, contact names, URLs, address information (streets, states and cities), phone numbers and job titles.

What are the basic parts of a simple TSQL Query?

Put simply: when you are writing queries within Microsoft SQL Server, you are effectively using T-SQL. All applications that communicate with SQL Server, regardless of the application's user interface, do so by sending T-SQL statements to the server. However, ​in addition to SQL Server, other database management systems (DBMS) also support T-SQL. Another Microsoft product, Microsoft Azure SQL Database​, supports most features of T-SQL. SELECT TOP 10 (*) FROM users ORDER BY age; Difference #5 Finally, and as referenced before, T-SQL offers functionality that does not appear in regular SQL. One example is the ISNULL function. This will replace NULL values coming from a specific column. The below would return an age of "0" for any rows that have a value of NULL in the age column. SELECT ISNULL (0, age) FROM users;

What is data normalization?

The process of refining the structure of a database to minimize redundancy and improve integrity of a database. Data Normalization is what we as SQL developers would use - since we're dealing with a lot of data stored in different tables, it can get pretty tedious to extract that data especially if it's organized poorly. Data Normalization allows us to eliminate data redundancy as well as enhance the data integrity in the table. It's a multi-step process that sets the data into tabular form and removes the duplicated data from relational tables.

How many normal forms are there?

There are 3 normal forms for SQL. First(1NF), Second(2NF) and Third(3NF) normalization forms.

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

There are different types of joins available in SQL below are the most popular: INNER JOIN creates a result table by combining rows that have matching values in two or more tables. LEFT OUTER JOIN includes in a result table unmatched rows from the table that is specified before the LEFT OUTER JOIN clause. RIGHT OUTER JOIN creates a result table and includes into it all the records from the right table and only matching rows from the left table. SELF JOIN joins the table to itself and allows comparing rows within the same table. CROSS JOIN creates a result table containing paired combination of each row of the first table with each row of the second table.

If a stored procedure is too slow how can you enhance the speed?

There are many ways to enhance the speed of a stored procedure in SQL. Some of which are below: Use SET NOCOUNT ON (this statement is used to stop the message, which shows the number of rows affected by SQL statement like INSERT, UPDATE and DELETE. Removing extra overhead) Use schema name before objects (It helps SQL Server to find the object) Use EXISTS() instead of COUNT() (This SQL technique concerns the use of EXISTS(). If you want to check if a record exists, use EXISTS() instead of COUNT() as COUNT() scans the entire table. Ex. SELECT Count(1) FROM dbo.EmployeeEx. IF ( EXISTS (SELECT 1 FROM db.Employees)) Specifying column names Use Join query instead of sub-query and co-related subquery (Ensure proper JOIN is used or not)

How do you declare a variable in TSQL?

To declare a variable in TSQL(Transact SQL), first you need prefix with DECLARE statement followed by variable name that starts with @ and the datatype. DECLARE @MyVariable INT;

What is the truncate statement used for? What is the key difference between this and your other options to remove data? Collapse

1. The truncate command only removes all rows of a table. It does not remove the columns, indexes, constraints, and schema. The first and most important difference between TRUNCATE and DELETE command in SQL is that truncate doesn't log row level details while delete is logged. Since TRUNCATE is not logged, it's not possible to rollback. But some database may provide rollback functionality for truncate, on the other hand, DELETE is always logged and can be rolled back. Another option is DROP. It is used to drop the whole table. With the help of the "DROP" command we can drop (delete) the whole structure in one go i.e. it removes the named elements of the schema. By using this command the existence of the whole table is finished or say lost. 2. The truncate statement is used to erase all the data within the tables rows while keeping the table structure intact. The main difference of using TRUNCATE vs DELETE or DROP is that the table structure will stay intact using TRUNCATE. While the other options DELETE and DROP will erase the entirety of a table or what column of a table that is selected using the where clause Also note that DELETE is the option that allows the ROLLBACK command to bring back data that was deleted. 3. TRUNCATE TableName; DELETE from; DELETE from WHERE ; DROP table ;

What is a SQL Server?

SQL Server is a relational database management system, or RDBMS, developed and marketed by Microsoft. In other words, SQL Server is a database management tool that acts as a database server where you can store and retrieve data as requested through many other applications locally or across a network.

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

The different types of statements available for TSQL are: DDL (Data Definition Language) DML (Data Manipulation Language) DCL (Data Control Language) TCL (Transaction Control Language)