SQL Basics Flashcards
What are Data definition Language commands?
commands which are used to define the database. CREATE, ALTER, DROP and TRUNCATE are some common DDL commands.
What are Data manipulation language (DML) commands?
commands which are used for manipulation or modification of data.
INSERT, UPDATE, and DELETE are some common DML commands.
What are Data Control Language (DCL) ?
Set of SQL statements used to manage security permissions for users and objects.
DCL includes statements such as GRANT, REVOKE, and DENY.
Give an example of Using OR statement
SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ProductCategoryID = 2
OR ProductCategoryID = 3 OR ProductCategoryID = 4;
Give an example of using IN statement
SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ProductCategoryID IN (2, 3, 4);
What is BETWEEN used for in SQL?
BETWEEN is another shortcut that can be used when filtering for an upper and lower bound for the value instead of using two conditions with the AND operator. The following two queries are equivalent:
SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ListPrice BETWEEN 1.00 AND 10.00;
What are Scalar Sub Queries ?
Scalar subqueries return a single value. Outer queries must process a single result.
What are Multi-valued Subqueries?
Multi-valued subqueries return a result much like a single-column table. Outer queries must be able to process multiple values.
What are self-contained Subqueries?
Self-contained subqueries can be written as stand-alone queries, with no dependencies on the outer query.
A self-contained subquery is processed once when the outer query runs and passes its results to that outer query.
What are Correlated Subqueries?
Correlated subqueries reference one or more columns from the outer query and therefore depend on it.
Correlated subqueries cannot be run separately from the outer query.
Can query results include results of Inner Query ?
One restriction you should keep in mind is that when using a nested query, the results returned to the client can only include columns from the outer query.
So if you need to return columns from both tables, you should write the query using a JOIN.
What is Scalar Function ?
Operate on a single row and return a single value.
Functions like DAY(),YEAR(),UPPER(),SQRT() are scalar
What are Logical Function ?
Compare Multiple values to determine a Single Output
What are Ranking Function?
Operate on a partition (set) of rows
What is Rowset Function ?
Return a virtual table that can be used in a FROM clause in a SQL statements
What is Aggregate Function ?
Take one or more input values, return a single summarizing value
What are common types of Error in MSSQL?
Syntax errors, Data type errors, Permission errors, Locking errors, Dead Lock errors
What is a dead lock error in MSSQL?
A deadlock error in MSSQL occurs when two or more transactions are trying to access the same resources in a circular fashion. This can cause a situation where neither transaction can complete, and SQL Server is forced to intervene to break the deadlock.
How to reduce the likelihood of dead locks occuring ?
Use explicit locks whenever possible. This will help to ensure that transactions are only accessing the resources they need.
Use the NOLOCK hint when possible. This hint tells SQL Server to not take any locks on the data being read, which can help to prevent deadlocks.
How to trouble shoot dead locks?
The error message will usually provide some information about the resources that were involved in the deadlock.
You can use the SQL Server Management Studio to view the current deadlock graph. This graph will show you the transactions that were involved in the deadlock, as well as the resources that they were trying to access.
You can use the DBCC TRACEON (3604) command to enable deadlock logging. This will cause SQL Server to log all deadlocks that occur. You can then use these logs to troubleshoot deadlock problems.
Once you have identified the cause of the deadlock, you can take steps to address the problem. This may involve changing the way that your application accesses data, or it may involve changing the way that SQL Server manages locks.
How do you troubleshoot SQL errors?
Check the permissions on your tables and columns.
Use the SQL Server Profiler to trace your queries.
Use the SQL Server Error Log to view recent errors.
Consult the SQL Server documentation for more information about errors.
What is Data Integrity?
Data Integrity refers to the consistency and maintenance of the data through the life cycle of the database.
In a database, data integrity can be ensured through the implementation of Integrity
Constraints in a table.
Integrity constraints help apply business rules to the database tables.
The constraints can either be at a column level or a table level.
EXISTS()
Returns TRUE if a subquery contains any rows. subquery
Is a restricted SELECT statement. The INTO keyword is not allowed.
First Normalization rule
All attributes should be atomic
Second Normalization rule
Non-Key attributes should not have partial dependencies on Composite Key
Third Normalization rule
Non key attributes should not have any dependencies on each other(Non key attributes)
Denormalization
Process of Adding back some data redundancy to improve the performance
Primary Key Constraint
A primary key constraint ensures the uniqueness of a row associated with any given key value.
Foreign Key Constraint
A primary key constraint ensures the uniqueness of a row associated with any given key value.
Check Constraint
A check constraint can implement domain restrictions on attributes
ALTER TABLE movies
ADD CONSTRAINT movies_imdb_rating_ch
CHECK (imdb_rating BETWEEN 1 AND 10);
Regular Expression (RX) Check Constraints
Check constraints can use regular expressions to apply sophisticated pattern matching to text validation
What is Database Modelling
Database modeling is the design discipline aimed at creating blueprints for database systems
Controlling data redundancy is a core theme of database modeling.
Steps involved in database design
1.Define Entities
2.Define Entity Relationships
3.Define Attributes in Entities
Why use One-One Relationship ?
If the Number of attributes / columns are more in number and only few of them are used regularly , then we can split the data into two tables
What is normalization ?
Process of reducing the data redundancy
What is DELETE ON CASCADE?
A DELETE statement may cause a cascade of additional deletions based on foreign key constraints.
A foreign key constraint with an ON DELETE CASCADE clause removes all the referenced rows whenever a parent row is deleted.
Virtual columns
Virtual or computed columns provide mechanism for storing computations rather than whole queries directly in the database for later use
ALTER TABLE movies ADD profit AS (worldwide_gross - estimated_budget);
What are views ?
Views allow useful queries to be stored directly in the database for later use.
The CREATE [OR REPLACE] VIEW statement stores a query with a given name as a view
Common Table Expressions using the WITH Clause
The common table expression (CTE) using the WITH clause was added to SQL as a method for establishing “statement scoped views”
for a query
A traditional view is a stored query as a named object in the database that needs to be managed over time
A WITH clause creates dynamic views with optional column names that are only available to the associated query for the duration of the statement
How to optimize queries in MS SQL?
Use indexes: Indexes are a way of storing data in a way that makes it faster to retrieve. If you are frequently querying a particular column or set of columns, you should create an index on that column or set of columns.
Use the correct join type: There are different types of joins, and the type of join you use can affect the performance of your query. For example, an inner join is usually faster than a left join.
Use the correct data type: The data type you use for a column can affect the performance of your query. For example, a varchar(255) column is usually faster than a varchar(max) column.
Use the correct tools.: There are a number of tools available that can help you to optimize your queries. For example, the SQL Server Management Studio has a Query Profiler that can help you to identify performance bottlenecks.
List some best practices fror query writing in SQL ?
Avoid wrapping functions around columns specified in the WHERE and JOIN clauses. Doing so makes the columns non-deterministic and prevents the query processor from using indexes.
Use schema names when creating or referencing database objects in the procedure. It takes less processing time for the Database Engine to resolve object names if it doesn’t have to search multiple schemas. It also prevents permission and access problems caused by a user’s default schema being assigned when objects are created without specifying the schema.
Use the Transact-SQL TRY…CATCH feature for error handling inside a procedure. TRY…CATCH can encapsulate an entire block of Transact-SQL statements. This not only creates less performance overhead, it also makes error reporting more accurate with significantly less programming
Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQL statements in the body of the procedure. This prevents passing NULL to columns that don’t allow null values
Is SQL server platform dependent ?
Yes,SQL servers needs Windows OS to run
What is database made up of ?
Collection of different objects like Tables , views , synonyms , procedure , functions , triggers etc
What is Identity(seed,increment) used for?
To apply auto increment values for a column in a table
A table contains one Identity function only
Can we add values manually to Identity column
?
Yes we can but only when IDENTITY_INSERT property is ON
What is natural join?
A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables. A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.
What are ranking functions?
these are used to assign ranks to each row - rowwise and group of rows wise like ROW_NUMBER(),RANK(),DENSE_RANK()
what clauses are generally used with Ranking functions and provide the Syntax
Partition by( (OPTIONAL) used for group of rows) and Order by(REQUIRED) clauses
SYNTAX:
RANKING FUNCTION() OVER(PARTITION BY<COLUMN> ORDER BY <COLUMN> [ASC/DESC])</COLUMN></COLUMN>
ROW_NUMBER() VS RANK() vs DENSE_RANK()
ROW_NUMBER - After sorting the rows , this function gives number sequentially
RANK() - After sorting the rows , this function gives same number if the value of the columns match and gives the next value based on the number of same values for the previous row . For example 1,2,2,4…….
DENSE_RANK() - similar to RANK() but gives expected result . For example 1,2,2,3…….
What are stored function types in SQL?
Scalar valued functions , table valued functions
What are table valued functions ?
A table-valued function returns more than one column from the table (it returns a table)
