SQL Basics Flashcards
1
Q
What are Data definition Language commands?
A
commands which are used to define the database. CREATE, ALTER, DROP and TRUNCATE are some common DDL commands.
2
Q
What are Data manipulation language (DML) commands?
A
commands which are used for manipulation or modification of data.
INSERT, UPDATE, and DELETE are some common DML commands.
3
Q
What are Data Control Language (DCL) ?
A
Set of SQL statements used to manage security permissions for users and objects.
DCL includes statements such as GRANT, REVOKE, and DENY.
4
Q
Give an example of Using OR statement
A
SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ProductCategoryID = 2
OR ProductCategoryID = 3 OR ProductCategoryID = 4;
5
Q
Give an example of using IN statement
A
SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ProductCategoryID IN (2, 3, 4);
6
Q
What is BETWEEN used for in SQL?
A
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;
7
Q
What are Scalar Sub Queries ?
A
Scalar subqueries return a single value. Outer queries must process a single result.
8
Q
What are Multi-valued Subqueries?
A
Multi-valued subqueries return a result much like a single-column table. Outer queries must be able to process multiple values.
9
Q
What are self-contained Subqueries?
A
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.
10
Q
What are Correlated Subqueries?
A
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.
11
Q
Can query results include results of Inner Query ?
A
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.
12
Q
What is Scalar Function ?
A
Operate on a single row and return a single value.
Functions like DAY(),YEAR(),UPPER(),SQRT() are scalar
13
Q
What are Logical Function ?
A
Compare Multiple values to determine a Single Output
14
Q
What are Ranking Function?
A
Operate on a partition (set) of rows
15
Q
What is Rowset Function ?
A
Return a virtual table that can be used in a FROM clause in a SQL statements
16
Q
What is Aggregate Function ?
A
Take one or more input values, return a single summarizing value
17
Q
What are common types of Error in MSSQL?
A
Syntax errors, Data type errors, Permission errors, Locking errors, Dead Lock errors
18
Q
What is a dead lock error in MSSQL?
A
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.
19
Q
How to reduce the likelihood of dead locks occuring ?
A
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.
20
Q
How to trouble shoot dead locks?
A
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.
21
Q
How do you troubleshoot SQL errors?
A
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.
22
Q
What is Data Integrity?
A
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.
23
Q
EXISTS()
A
Returns TRUE if a subquery contains any rows. subquery
Is a restricted SELECT statement. The INTO keyword is not allowed.
24
Q
First Normalization rule
A
All attributes should be atomic
25
Second Normalization rule
Non-Key attributes should not have partial dependencies on Composite Key
26
Third Normalization rule
Non key attributes should not have any dependencies on each other(Non key attributes)
27
Denormalization
Process of Adding back some data redundancy to improve the performance
28
Primary Key Constraint
A primary key constraint ensures the uniqueness of a row associated with any given key value.
29
Foreign Key Constraint
A primary key constraint ensures the uniqueness of a row associated with any given key value.
30
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);
31
Regular Expression (RX) Check Constraints
Check constraints can use regular expressions to apply sophisticated pattern matching to text validation
32
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.
33
Steps involved in database design
1.Define Entities
2.Define Entity Relationships
3.Define Attributes in Entities
34
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
35
What is normalization ?
Process of reducing the data redundancy
36
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.
37
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);
38
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
39
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
40
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.
41
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
42
Is SQL server platform dependent ?
Yes,SQL servers needs Windows OS to run
43
What is database made up of ?
Collection of different objects like Tables , views , synonyms , procedure , functions , triggers etc
44
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
45
Can we add values manually to Identity column
?
Yes we can but only when IDENTITY_INSERT property is ON
46
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.
47
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()
48
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 ORDER BY [ASC/DESC])
49
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.......
50
What are stored function types in SQL?
Scalar valued functions , table valued functions
51
What are table valued functions ?
A table-valued function returns more than one column from the table (it returns a table)
52
What are scalar-valued functions?
They return single column or value
53
What are sub blocks ?
Sub blocks are named blocks that can be reused in different applications . Stored procedures and Stored functions are sub blocks
Stored procedures are pre compiled meaning they are compiled only once
54
How many parameters does a stored procedure take?
Stored procedure takes two parameters - in & out parameter
55
What is View?
The view is a stored query. It behaves like a table but no data is associated with it. View provides a way to work with the tables in SQL.
56
How can we store query on Sql server?
Views are stored queries - without any input parameters-support DML operations
Stored procedures - caching - Query Execution plan
Function - take input parameters , Only select , no update ,delete , insert
57
What is WAITFOR used for ?
Blocks the execution of a batch, stored procedure, or transaction until either a specified time or time interval elapses, or a specified statement modifies or returns at least one row.
WAITFOR
{
DELAY 'time_to_pass'
| TIME 'time_to_execute'
| [ ( receive_statement ) | ( get_conversation_group_statement ) ]
[ , TIMEOUT timeout ]
}
58
What are cursors?
Microsoft SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type.
59
What is pivot and unpivot?
PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they're required on any remaining column values that are wanted in the final output. UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
60
What are Locks?
Locks are the mechanism the database server uses to control the simultaneous use of data
resources.
When some portion of the database is locked, any other users wishing to
modify (or possibly read) that data must wait until the lock has been released.
61
What locking strategy is used in SQL Server?
Database writers must request and receive from the server a write lock to modify
data and database readers must request and receive from the server a read lock to
query data.
While multiple users can read data simultaneously, only one write lock
is given out at a time for each table (or portion thereof), and read requests are
blocked until the write lock is released.
This can lead to a long wait
times if there are many concurrent read and write requests
62
What are different types of locks used in SQL Server?
Table locks:
Keep multiple users from modifying data in the same table simultaneously
Page locks:
Keep multiple users from modifying data on the same page (a page is a segment of
memory generally in the range of 2 KB to 16 KB) of a table simultaneously
Row locks:
Keep multiple users from modifying the same row in a table simultaneously
63
Microsoft uses which type of locking?
Microsoft SQL
Server uses page, row, and table locking
64
What are transactions?
transaction is a device for
grouping together multiple SQL statements such that either all or none of the statements
succeed (a property known as atomicity).
65
What happens if a server shutdown when all the statements in the transaction are executed except the COMMIT or ROLLBACK command?
It is the responsibility of the SQL server to COMMIT or ROLLBACK these transactions when it restarts again
66
How does an SQL server handle transactions?
By default, individual SQL statements are auto-committed independently of one another. So, to begin a transaction you must first issue a command
To turn off auto-commit mode use this command
SET IMPLICIT_TRANSACTIONS ON
67
What are the ways in which a transaction is interrupted ?
The server shuts down, in which case, your transaction will be rolled back automatically when the server is restarted.
* You issue an SQL schema statement, such as alter table, which will cause the
current transaction to be committed and a new transaction to be started.
* You issue another start transaction command, which will cause the previous
transaction to be committed.
* The server prematurely ends your transaction because the server detects a deadlock and decides that your transaction is the culprit. In this case, the transaction
will be rolled back and you will receive an error message
68
Specify a strategy to access the database by applications to avoid deadlocks
Ensure that data resources are
always accessed in the same order, such as always modifying account data before inserting transaction data
69
What are transaction savepoints?
In some cases, you may encounter an issue within a transaction that requires a rollback,
but you may not want to undo all of the work that has transpired. For these situations,
you can establish one or more savepoints within a transaction and use them to roll back
to a particular location within your transaction
70
How to work with savepoints?
When using savepoints, remember the following:
* Despite the name, nothing is saved when you create a savepoint. You must eventually issue a commit if you want your transaction to be made permanent.
* If you issue a rollback without naming a savepoint, all savepoints within the transaction will be ignored and the entire transaction will be undone.
71
What is searched case expression?
In Searched Case Expression, you don't specify the column for which the case statement is written.
syntax:
CASE
WHEN C1 THEN E1
WHEN C2 THEN E2
WHEN CN THEN EN
[ELSE ED]
END
All the
expressions returned by the various when clauses must evaluate to the same type (e.g.,
date, number, varchar,subquery)
72
What is Simple case expression?
In Simple Case expression , you specify the column on which the case statement is written.
Syntax:
CASE V0
WHEN V1 THEN E1
WHEN V2 THEN E2
WHEN VN THEN EN
[ELSE ED]
END
73
Why Functions should be used over stored procedures?
Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed).
A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set.
74
Why to have LEFT JOIN and RIGHT JOIN when A LEFT JOIN B is same as B RIGHT JOIN A?
This becomes clear when you are using SELF JOIN to find the relationship between the records of the same table. Depending on the result needed, we have to choose between LEFT OUTER JOIN and RIGHT OUTER JOIN. For example, if you are trying to find employees and their supervisors it is better to choose employees left join supervisors rather than employees right join supervisors because the first query gives only employees with no supervisors whereas the second query gives supervisors with no employees too which is not required for our case.
75
What are Indexes ?
Indexes are special tables that, unlike normal data tables, are kept in a specific order.
Instead of containing all of the data about an entity, however, an index contains only the column (or columns) used to locate rows in the data table, along with information describing where the rows are physically located.
Therefore, the role of indexes is to facilitate the retrieval of a subset of a table’s rows and columns without the need to inspect every row in the table.
76
What is Unique Index for a column?
A Unique Index on a column makes sure that column accepts only unique values and Index is created on that column itself
77
What is Multicolumn Index?
A Multicolumn index is made up of two or more columns.
If for example, you use FirstName and LastName
like(LastName, FirstName) then Index works only if your query uses both LastName and FirstName or only LastName. Because records are stored based on LastName then followed by FirstName
Hence , the order of columns is also important while creating multiple indexes.
78
What is a B-tree index?
B-tree indexes are organized as trees, with one or more levels of branch nodes leading to a single level of leaf nodes.
Branch nodes are used for navigating the tree, while leaf nodes hold the actual values and location information.
79
What are Bit Map Indexes?
Although B-tree indexes are great at handling columns that contain many different
values, such as a customer’s first/last name, they can become unwieldy when built on
a column that allows only a small number of values.
For columns that contain only a small number of values across a large number of rows
(known as low-cardinality data), a different indexing strategy is needed. To handle this
situation more efficiently, Oracle Database includes bitmap indexes, which generate a
bitmap for each value stored in the column.
Bitmap indexes are a nice, compact indexing solution for low-cardinality data, but this
indexing strategy breaks down if the number of values stored in the column climbs too
high in relation to the number of rows (known as high-cardinality data), since the server
would need to maintain too many bitmaps.
80
What is in-memory database?
An in-memory database stores the data in memory and uses a disk for backup
81
What is on-disk database?
An on-disk database stores the data on disk and uses memory for backup
82
What is index?
An index is an on-disk or in-memory structure associated with a table or view that speeds the retrieval of rows from the table or view.
83
What is a row store index?
A rowstore index contains keys built from one or more columns in the table or view. For rowstore indexes, these keys are stored in a tree structure (B+ tree) that enables the Database Engine to find the row or rows associated with the key values quickly and efficiently.
A rowstore index stores data logically organized as a table with rows and columns, and physically stored in a row-wise data format called rowstore, or stored in a column-wise data format called columnstore.
84
What is a rowstore?
Rowstore has been the traditional way to store relational table data. Rowstore refers to table where the underlying data storage format is a heap, a B+ tree (clustered index), or a memory-optimized table. Disk-based rowstore excludes memory-optimized tables.
85
Can using index cause less than optimal performance
Yes , an incorrect index choice can cause less than optimal performance
86
What does a query optimizer do?
The task of the query optimizer is to select an index, or combination of indexes, only when it improves performance, and to avoid indexed retrieval when it hinders performance.
87
How to design index ?
Overall index design strategy should provide various indexes for the query optimizer to choose from to optimize a query
88
What is clustered index?
Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.
If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
89
What is non-clustered index?
Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
90
indexes are automatically created when ?
PRIMARY KEY and UNIQUE constraints are defined on table columns.
91
Which type of index is created when UNIQUE constraint is added to a table ?
Non-clustered index
92
Which type of index is created when a PRIMARY KEY constraint is added while creating a table?
clustered index
93
Which type of index is created when a PRIMARY KEY constraint is added on already existing table with clustered index?
it creates non clustered index using primary key column
94
what is a table scan?
When performing a table scan, the query optimizer reads all the rows in the table, and extracts the rows that meet the criteria of the query. A table scan generates many disk I/O operations
95
What is a row scan?
When the query optimizer uses an index, it searches the index key columns, finds the storage location of the rows needed by the query, and extracts the matching rows from that location
96
What are different types of views
Views can be Simple views and complex Views.
97
What is a simple view ?
Simple Views operate on a Single table. It allows DML operations. Changes made through the simple view are applied to the table.
98
What is a complex view ?
Complex views operate on Multiple tables. So DML operations are not supported
99
Why views are created ?
Views are created to hide columns from users and simplify complex database designs
100
What is sorting via expression?
Adding an expression to your order by clause.
Eg: ORDER BY RIGHT(fed_id, 3) orders the rows by last three digits of fed_id
101
What is sorting via numeric placeholders?
If sorting is based on the columns in the select query, then we can use the relative positions of these columns in order by clause.
Eg: ORDER BY 2 ,5 orders the rows by 2nd and 5th column in the select query
102
Equality operator
=
103
InEquality operator
<>
104
What does wild card character ' _ ' mean ?
It means one character in string matching expression
Eg : _ _ t _ implies four letter word with t as its third letter
105
How to test whether an expression is null?
using 'IS NULL' operator
106
How to test whether an expression is not null?
using 'IS NOT NULL' operator
107
Does inner join order in Query matter in output results?
No, it is up to the server to choose the starting table (driving table) in the query and return the results
108
What are Equi-joins?
values from the two tables must match for the join to succeed.
An equi-join always employs an equals sign
Eg: ON e.assigned_branch_id = b.branch_id
109
What are Non-Equi-joins?
joining tables via ranges of values, are referred to as non-equi-joins.
Eg:
SELECT e.emp_id, e.fname, e.lname, e.start_date
FROM employee e INNER JOIN product p
ON e.start_date >= p.date_offered
AND e.start_date <= p.date_retired
WHERE p.name = 'no-fee checking';
110
What is a B-Tree?
B-Tree or Balanced Tree is a type of self-balancing tree in which each node contains a large number of keys allowing the tree to have a shallower height and larger branching factor.
B-Trees maintain balance by ensuring that each node has a minimum number of keys, so the tree is always balanced. This balance guarantees that the time complexity for operations such as insertion, deletion, and searching is always O(log n), regardless of the initial shape of the tree.
111
What are some of the properties of B-Tree?
1. All leaves are at the same level.
2. All keys of a node are sorted in increasing order
3. Like other balanced Binary Search Trees, the time complexity to search, insert and delete is O(log n)
4. Insertion of Node in B-Tree happens only at Leaf Node.
112
What is a B+ Tree?
B+ Tree is a variation of B-Tree. In a B+ Tree, data pointers are stored only at the leaf nodes of the tree.
In a B+ tree structure of a leaf node differs from the structure of internal nodes.
113
Why B+ trees are a good choice for database systems?
B+ Trees are a good choice for database systems and applications needing quick data retrieval because of its balanced structure, which guarantees predictable performance for a variety of activities and facilitates effective range-based queries.
114
What does Source , initial Catalog in Connection string refer to ?
Initial Catalog refers to Database name
Source refers to database server name
115
What is dirty read?
A dirty read is a situation when a transaction reads data that has not yet been committed . For example, Let’s say transaction 1 updates a row and leaves it uncommotted , meanwhile, Transaction 2 reads the updated row. If transaction rolls back the changes , transaction 2 will have read data that is consisdered never to have existed
116
What is non repeatable read?
When a transaction reads the same row twice and gets a different row each time. For example, suppose transaction T1 reads data. Due to concurrency, another transaction T2 updates the same data and commit, Now if transaction T1 rereads the same data, it will retrieve a different value
117
What is phantom read?
Phantom read occurs when two same queries are executed, but the rows retrieved by the two are different.For example, suppose transaction T1 retrieves a set of rows that satisfy some search criteria. Now, transaction T2 generates some new rows that match the search criteria for transaction T1. If transaction T1 reexecutes the statement that reads the rows, it gets a different set of rows this time
118
What are isolation levels in sql server?
Read uncommitted , read committed, repeatable read, snapshot isolation , serializable
119
What is read uncommitted isolation level?
Read uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes by other transactions, thereby allowing dirty reads. At this level, transcations are not isolated from each other
120
What is read committed isloation level?
This guaranteess that any data committed at the moment it is read. Thus it does not allow dirty read. The transcation holds a read or write lock on the current row, and thus prevents other transcationa from reading, updating or deleting it
121
When do you use sub query?
Sub queries are used to do things that are otherwise not doable or very difficult . Generally avoided
122
Can sub queries be used with select and from?
Yes
123
What is connection timeout?
Timeout limit for your connection object it can try connecting to server
124
What is command timeout?
Timeout limit for your SQL query to execute
125
What are different places for configuring lsolation level settings ?
Database, connection, statement
126
What are isolation levels in database?
Read committed , Read committed snapshot
127
What is repeatable read isolation level?
This level sees only data comitted before transaction began ; it never sees uncommitted data or changes committed by concurrent transactions during transaction’s execution.
128
What is serialization anomaly?
The result of successfully committing a group of transactions is inconsistent with all the possible orderings of running those transactions one at a time
