Sql

What are the different types of Joins?

(INNER) JOIN: Returns dataset that have matching values in both tables. LEFT (OUTER) JOIN: Returns all records from the left table and matched records from the right. RIGHT (OUTER) JOIN: Returns all records from the right table and the matched records from the left. A FULL JOIN returns all the rows from the joined tables

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

1. 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. 2. 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. 3. sp_executesql instead of Execute for dynamic queries The sp_executesql allows for cache plan reuse and protects from SQL Injection.

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

A FOREIGN KEY is a field (or collection of fields) 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 reference a maximum of 253 other tables and columns as foreign keys.

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

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

A primary key is a field or set of fields with values that are unique throughout a table. Values of the key can be used to refer to entire records, because each record has a different value for the key. Each table can only have one primary key.

What is a relational Database?

A relational database is a collection of information that organizes data in predefined relationships where data is stored in one or more tables (or "relations") of columns and rows, making it easy to see and understand how different data structures relate to each other.

What is a SQL Server stored procedure?

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

When are "Joins" used?

Anytime you need to retrieve data from multiple tables

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

Data Definition Language (DDL) statements defines data structures. Use these statements to create, alter, or drop data structures in a database. These statements include: -ALTER -Collations -CREATE -DROP -DISABLE TRIGGER -ENABLE TRIGGER -RENAME -UPDATE STATISTICS -TRUNCATE TABLE Data Manipulation Language (DML) affect the information stored in the database. Use these statements to insert, update, and change the rows in the database. -BULK INSERT -DELETE -INSERT -SELECT -UPDATE -MERGE

What is data normalization?

Data normalization or database normalization is the process of organizing data in a proper manner. It consist on following a defined set of rules to for storing data, determining the way data will be organized in the databse. There are a few rules for normalization, but for practical purposes only three are considered. Each rule is called a normal form. If the first rule is oberved, the database is said to be in "first normal form." If the first three rules are observed, the database is considered to be in "third normal form." Although other levels of normalization are possible, third normal form is considered the highest level necessary for most applications.

How many normal forms are there?

Database Normalization is a process and it should be carried out for every database you design. The process of taking a database design, and apply a set of formal criteria and rules, is called Normal Forms. The database normalization process is further categorized into the following types: 1. First Normal Form (1 NF) 2. Second Normal Form (2 NF) 3. Third Normal Form (3 NF) 4. Boyce Codd Normal Form or Fourth Normal Form ( BCNF or 4 NF) 5. Fifth Normal Form (5 NF) 6. Sixth Normal Form (6 NF)

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

Key differences between DELETE and TRUNCATE The DELETE statement is used when we want to remove some or all of the records from the table, while the TRUNCATE statement will delete entire rowsfrom a table. DELETE is a DML command as it only modifies the table data, whereas the TRUNCATE is a DDL command

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

SQL Injection is a code injection technique that hackers can use to insert malicious SQL statements into input fields for execution by the underlying SQL database. Developers can prevent SQL Injection vulnerabilities in web applications by utilizing parameterized database queries with bound, typed parameters and careful use of parameterized stored procedures in the database. This can be accomplished in a variety of programming languages including Java, .NET, PHP, and more.

What is a SQL Server?

SQL Server is a relational database management system, or RDBMS, developed and marketed by Microsoft. Similar to other RDBMS software, SQL Server is built on top of SQL, a standard programming language for interacting with relational databases. SQL Server is tied to Transact-SQL, or T-SQL, the Microsoft's implementation of SQL that adds a set of proprietary programming constructs.

What language is used to write Stored procedures?

Stored procedures can be written in a variety of programming languages from object-oriented programming languages to traditional programming languages. You can write stored procedures in the following programming languages: Java, SQL procedural language, REXX, and Traditional programming languages: C, C++, COBOL, PL/I, and Assembler.

What is a database "Table"?

Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record. For example, a table that contains employee data for a company might contain a row for each employee and columns representing employee information such as employee number, name, address, job title, and home telephone number.

How do you declare a variable in TSQL?

The DECLARE statement initializes a Transact-SQL variable by: - Assigning a name. The name must have a single @ as the first character. - Assigning a system-supplied or user-defined data type and a length. For numeric variables, a precision and scale are also assigned. For variables of type XML, an optional schema collection may be assigned. - Setting the value to NULL. For example, the following **DECLARE** statement creates a local variable named **@mycounter** with an int data type.

What are the basic parts of a simple SQL Query

The fundamental structure of SQL queries includes three clauses that are select, from, and where clause. What we want in the final result relation is specified in the select clause. Which relations we need to access to get the result is specified in from clause. How the relation must be operated to get the result is specified in the where clause.

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

There are many advantages. Some of the main ones are: - Data redundancy or duplication is resolved. - Minimize/avoids data modification problems. - Simplifies querys. - The database is easier to understand and can be expanded without affecting existing data. - Finding, sorting, and indexing can be faster because the table is small and more rows can be accommodated on the data page. - In general, it makes it easier to map objects to the data.

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

To delete one or more rows in a table: Use the DELETE statement with a WHERE clause to specify a search condition. The DELETE statement removes zero or more rows of a table, depending on how many rows satisfy the search condition that you specify in the WHERE clause. You can use DELETE with a WHERE clause to remove only selected rows from a declared temporary table, but not from a created temporary table.

What language do you use to communicate with the database?

Transact-SQL