SQL Flashcards
How do you declare a variable in TSQL?
You declare a variable in TSQL by starting with the keyword “Declare”. Then, you provide the name you choose to use preceded by the “@” symbol. Finally, you must also assign the datatype to the variable.
How many normal forms are there?
Normal Forms are a set of rules or guidelines used in relational database design for organizing and structuring data in a way that minimizes redundancy, dependency, and the potential for data anomalies.
There are 3 main normal forms:
First Normal Form (1NF) –
* Must contain a Primary Key
* No duplicated rows or columns
* Each column must have only 1 value for each row in the table
* One cell cannot hold more than one value
Second Normal Form (2NF) –
* Meets 1NF standards
* All non-key attributes are fully dependent on a PK.
Third Normal Form (3NF) – No non-key attributes is dependent on another non-key attribute
If a stored procedure is too slow, how can you enhance the speed?
Create indexes on frequently used columns used in search or filter conditions
Make sure the sql queries in the stored proc are well-organized
If you wanted to delete information from a table, what statement would you use?
“Delete” statement to delete specific records based on conditions.
What are indexes?
They are data structures in a database that provide a way to efficiently retrieve and access data. They are similar to bookmarks because they help you locate specific information without scanning the entire content.
Create Clustered INDEX idx_UserName ON Users(UserName) => Users is the table and the index is on the UserName column.
Clustered: rows are organized based on the order of the index column; one clustered index per table.
Non-Clustered: no effect on order of rows, creates a sorted order of indexed columns; multiple non-clustered indexes per table
What are the basic parts of a simple TSQL Query?
Select statement
From clause
Where clause
Order By clause
Group by clause
Joins
What is a database “Table”?
A structured collection of ROWS and COLUMNS used to store data. Each row is a record and each column represents the specific field or attribute of that record.
What are the different types of statements available to you in TSQL?
- Data Definition Language (DDL): Create, Alter, Drop, Truncate
- Data Query Language (DQL): Select
- Data Manipulation Language (DML): Select, Insert, Update, Delete
- Data Control Language (DCL): Grant, Revoke
- Transaction Control Language (TCL): Commit, Rollback, Savepoints
What are the different types of Joins? (Write examples of each)
Inner join: return only the rows where there is a match in both tables
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
Left Join (or Left Outer Join): return all rows from left table and only the matched rows from right
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Right Join (or Right Outer Join): return all rows from the right table and only the matched rows from left
SELECT Employees.EmployeeName, Orders.OrderID
FROM Employees
RIGHT JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID;
Full Outer Join: returns all rows when there is a match in either the left or the right table. Null values will be returned in non-matching columns.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Cross Join: Cartesian product: all possible combos of rows from both tables
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
CROSS JOIN Departments;
Self Join: Joins a table with itself
SELECT e1.EmployeeName AS Employee, e2.EmployeeName AS Supervisor
FROM Employees e1
INNER JOIN Employees e2 ON e1.SupervisorID = e2.EmployeeID;
What is a Database?
A structured collection of data that is organized for efficient storage, retrieval, and management. It typically consists of tables (to store the information), procedures (sets of instructions to perform specific tasks), and functions (which are reusable routines that can be called to perform specific operations.
What is a Foreign Key and how many can one table have?
A foreign key uses the Primary Key from another table where the information is the same. There is typically no limit to how many foreign keys one table can use, but depending on the server or the specific DBMS (Database Management System), there may be limits to consider.
What is a Primary Key and how many can one table have?
A column or a set of columns (composite key) that uniquely identifies each row in a table, specifically uniquely identifies the row within a specified column.
What is a relational Database?
A relational database is made up of tables of information that can share information with other tables within the same database by establishing primary and foreign key relationships.
What is a SQL Injection Attack and how do you protect yourself against these?
It is a type of security vulnerability that occurs when an attacker is able to manipulate a web application’s SQL query by injecting malicious SQL code.
To protect against this, it’s important to validate and clean user inputs, use secure coding practices, and limit the permissions of accounts to the bare minimum needed.
What is a Sql Server stored procedure?
It is a statement that can be re-utilized to add, access, or manipulate date stored in the database.
