SQL

Question 1

What is a Database?

Answer 1

A SQL database is a collection of tables that stores a specific set of structured data.

Question 2

What is a relational Database?

Answer 2

A relational database organizes data into tables which can be linked—orrelated—based on data common to each. This capability enables you to retrieve an entirely new table from data in one or more tables with a single query.

Question 3

What is a database “Table”?

Answer 3

A table is a collection of related data held in a table format within a database. It consists of columns and rows.

Question 4

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

Answer 4

Primary key is a column or set of columns that uniquely identity a row in a table. A table can have one primary key. A primary key constraint can be on multiple columns though.

Question 5

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

Answer 5

A Foreign Key is a field(columns) (or collection of fields(columns)) in one table that refers to the primary key in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

A table can have as many foreign keys as you want up to 253.

Question 6

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

Answer 6

SQL injection attacks are when someone finds and exploits unsanitized inputs. They use the warning window provided to see if they can get information, sometimes companies pass too much information. Three ways to protect from injection attacks: Web Application Firewalls - analyze and separate malicious HTTP and HTTPS request heading to your website. Parameterized Queries - Parameterized queries will segregate the data added by an user from the code that runs the application so the two don’t interact with each other. Reduce account privileges so that unauthorized accounts can’t get admin privileges.

Question 7

What is a Sql Server stored procedure?

Answer 7

A SQL stored procedure (SP) is a collection SQL statements and sql command logic, which is compiled and stored on the database. Stored procedures in SQL allows us to create queries to be stored and executed on the server. Stored procs can also be cached and reused.

Question 8

What language is used to write Stored procedures?

Answer 8

Transact SQL or T/SQL for short

Question 9

What language do you use to communicate with the database?

Answer 9

Structured Query Language - SQL

Question 10

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

Answer 10

Data Definition Language - Create, Alter, Drop, Truncate etc…

Data Manipulation Language - Insert, Delete, Bulk Insert, Select, Update, merge etc….

Question 11

What are indexes?

Answer 11

Sql index is a quick lookup table for finding records users need to search frequently. When you create a primary key a clustered index is automatically created with it. Same as when you put a unique constraint on a column.

Question 12

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

Answer 12

SQL delete statement. DELETE FROM table name Where Id = @Id

Question 13

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

Answer 13

A truncate statement is DDL (Data Description Language) statement that marks extents of a table for deallocation. Meaning it removes the data from the table. The key difference is that it won’t delete the schema of the table and there is no where clause to target specific rows. It won’t log the deleted data so it is faster and an increase in performance.

Question 14

What are the basic parts of a simple TSQL Query

Answer 14

Input/Select, Execution, Output

Question 15

When are “Joins” used?

Answer 15

Joins are used to return data that is related in a relational database. Data can be related in 3 ways: one to one (student only as one id), one to many(a customer can have many orders), and many to many (dr can have many patients and patients can have many dr.)

Question 16

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

Answer 16

Inner Join - will return data form both tables where the keys in each table match

Left or Right Join - will return all the rows from one table and matching data from the other table.

Cross Join - will return the product of each table.

Question 17

What is data normalization?

Answer 17

Data normalization rules divides large tables into smaller tables and links them using relationships.

Question 18

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

Answer 18

To prevent oversized data tables, data inconsistency. Input update or delete statements will become frequent anomalies. Meaning they will have to apply to every row that is a duplicate of the targeted row.

Question 19

How many normal forms are there?

Answer 19

6

Question 20

How do you declare a variable in TSQL?

Answer 20

At the top of your new query you Declare @name datatype.

Question 21

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

Answer 21

Use SET NOCOUNT ON - SQL Server returns informational messages when running select or DML operations. In case a procedure has many such statements a cursor or a while loop SQL Server will display lot of such messages increasing network traffic. These messages can be suppressed with SET NOCOUNT ON and can increase performance by decreasing network traffic.

Use fully qualified procedure name - A fully qualified object name is database.schema.objectname. When stored procedure is called as schemaname.procedurename, SQL Server can swiftly find the compiled plan instead of looking for procedure in other schemas when schemaname is not specified. This may not be a great boost to the performance but should be followed as best practice. All objects inside procedure should also be referred as schemaname.objectname.

Using IF EXISTS AND SELECT - IF EXISTS is used to check existence of a record, object etc.. And is a handy statement to improve performance of queries where in one only wants to check existence of a record in a table instead of using that record/row in the query. When doing so use IF EXISTS(SELECT 1 from mytable) instead of IF EXISTS(Select * from mytable) as only thing we are interested in is to check the presence of record/s. So, if the query return 1 then record is present else it’s not. It’s needless to return all column values.

Question 22

What is a SQL Server?

Answer 22

An SQL server is a database management system that controls data storage and help to execute demands and queries of the users, including transactions, files, and indexes