SQL Deck 2 Flashcards
If you wanted to delete information from a table what statement would you use?
Truncate [Table Name]
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 data of a certain target in the database.
DROP and TRUNCATE are DDL(Data Definition Language) commands, whereas DELETE is a DML(Data Manipulation Language) command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back. TRUNCATE can be rolled back if wrapped in a transaction.
What are the basic parts of a simple TSQL Query
They normally begin with a command statement describing the action to take, followed by a clause that describes the target of the command (such as the specific table within a database affected by the command) and finally, a series of clauses that provide additional instructions.
When are “Joins” used?
JOINS are used to JOIN tables together with related information when you need data from more than one table
What are the different type of Joins? (Write examples of each)
Inner Join: Select all records from Table A and Table B, where the join condition is met.
Ex: select first_name, last_name, order_date, order_amount
from customers c
inner join orders o
on c.customer_id = o.customer_id
Left Join:Select all records from Table A, along with records from Table B for which the join condition is met (if at all).
Ex:select first_name, last_name, order_date, order_amount
from customers c
left join orders o
on c.customer_id = o.customer_id
Right Join: Select all records from Table B, along with records from Table A for which the join condition is met
Ex:select first_name, last_name, order_date, order_amount
from customers c
right join orders o
on c.customer_id = o.customer_id
Full Join: Select all records from Table A and Table B, regardless of whether the join condition is met or not.
Ex: select first_name, last_name, order_date, order_amount
from customers c
full join orders o
on c.customer_id = o.customer_id
What is data normalization?
Database normalization is the process of restructuring a relational database in accordance with a series of so-called normal forms. Essentially used to organize a database into tables and columns
Why would we go through the process of normalizing our data?
There are three main reasons to normalize a database. The first is to minimize duplicate data, the second is to minimize or avoid data modification issues, and the third is to simplify queries.
How many normal forms are there?
There are three.
First Normal Form – The information is stored in a relational table with each column containing atomic values. There are no repeating groups of columns.
Second Normal Form – The table is in first normal form and all the columns depend on the table’s primary key.
Third Normal Form – the table is in second normal form and all of its columns are not transitively dependent on the primary key
How do you declare a variable in TSQL?
To assign a value to a variable, use the DECLARE statement.
Ex:
DECLARE @MyAge int
If a stored procedure is too slow how can you enhance the speed?
There are many ways to enhance the speed of stored procedures.
- SET NOCOUNT ON-SET NOCOUNT ON and can increase performance by decreasing network traffic.SQL Server returns informational messages when running select or DML operations and this can suppress those messages.
- Use schema name before objects.
It helps SQL Server to find the object.
Ex. SELECT EmpID, EmpName, EmpSalary FROM dbo.Employee
3.Use EXISTS () instead of COUNT ()
This SQL optimization technique concerns the use of EXISTS (). If you want to check if a record exists, use EXISTS () instead of COUNT (). While COUNT () scans the entire table. This will give you better performance.
- Specify column names instead of using * in SELECT statement
What is a SQL Server?
SQL Server is a relational database management system. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications
