Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

ASP.NET Flashcards > SQL Basics > Flashcards

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Give an example of using IN statement

A

SELECT ProductCategoryID AS Category, ProductName

FROM Production.Product

WHERE ProductCategoryID IN (2, 3, 4);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What are Scalar Sub Queries ?

A

Scalar subqueries return a single value. Outer queries must process a single result.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What are Logical Function ?

A

Compare Multiple values to determine a Single Output

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What are Ranking Function?

A

Operate on a partition (set) of rows

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is Rowset Function ?

A

Return a virtual table that can be used in a FROM clause in a SQL statements

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is Aggregate Function ?

A

Take one or more input values, return a single summarizing value

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What are common types of Error in MSSQL?

A

Syntax errors, Data type errors, Permission errors, Locking errors, Dead Lock errors

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

EXISTS()

A

Returns TRUE if a subquery contains any rows. subquery
Is a restricted SELECT statement. The INTO keyword is not allowed.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

First Normalization rule

A

All attributes should be atomic

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Second Normalization rule

A

Non-Key attributes should not have partial dependencies on Composite Key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Third Normalization rule

A

Non key attributes should not have any dependencies on each other(Non key attributes)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Denormalization

A

Process of Adding back some data redundancy to improve the performance

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Primary Key Constraint

A

A primary key constraint ensures the uniqueness of a row associated with any given key value.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Foreign Key Constraint

A

A primary key constraint ensures the uniqueness of a row associated with any given key value.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Check Constraint

A

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);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

Regular Expression (RX) Check Constraints

A

Check constraints can use regular expressions to apply sophisticated pattern matching to text validation

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

What is Database Modelling

A

Database modeling is the design discipline aimed at creating blueprints for database systems
Controlling data redundancy is a core theme of database modeling.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

Steps involved in database design

A

1.Define Entities
2.Define Entity Relationships
3.Define Attributes in Entities

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

Why use One-One Relationship ?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

What is normalization ?

A

Process of reducing the data redundancy

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

What is DELETE ON CASCADE?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Virtual columns

A

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);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

What are views ?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

Common Table Expressions using the WITH Clause

A

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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

How to optimize queries in MS SQL?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

List some best practices fror query writing in SQL ?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

Is SQL server platform dependent ?

A

Yes,SQL servers needs Windows OS to run

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

What is database made up of ?

A

Collection of different objects like Tables , views , synonyms , procedure , functions , triggers etc

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

What is Identity(seed,increment) used for?

A

To apply auto increment values for a column in a table
A table contains one Identity function only

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

Can we add values manually to Identity column
?

A

Yes we can but only when IDENTITY_INSERT property is ON

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

What is natural join?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

What are ranking functions?

A

these are used to assign ranks to each row - rowwise and group of rows wise like ROW_NUMBER(),RANK(),DENSE_RANK()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

what clauses are generally used with Ranking functions and provide the Syntax

A

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>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

ROW_NUMBER() VS RANK() vs DENSE_RANK()

A

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…….

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

What are stored function types in SQL?

A

Scalar valued functions , table valued functions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

What are table valued functions ?

A

A table-valued function returns more than one column from the table (it returns a table)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

What are scalar-valued functions?

A

They return single column or value

53
Q

What are sub blocks ?

A

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
Q

How many parameters does a stored procedure take?

A

Stored procedure takes two parameters - in & out parameter

55
Q

What is View?

A

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
Q

How can we store query on Sql server?

A

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
Q

What is WAITFOR used for ?

A

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
Q

What are cursors?

A

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
Q

What is pivot and unpivot?

A

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
Q

What are Locks?

A

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
Q

What locking strategy is used in SQL Server?

A

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
Q

What are different types of locks used in SQL Server?

A

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
Q

Microsoft uses which type of locking?

A

Microsoft SQL
Server uses page, row, and table locking

64
Q

What are transactions?

A

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
Q

What happens if a server shutdown when all the statements in the transaction are executed except the COMMIT or ROLLBACK command?

A

It is the responsibility of the SQL server to COMMIT or ROLLBACK these transactions when it restarts again

66
Q

How does an SQL server handle transactions?

A

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
Q

What are the ways in which a transaction is interrupted ?

A

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
Q

Specify a strategy to access the database by applications to avoid deadlocks

A

Ensure that data resources are
always accessed in the same order, such as always modifying account data before inserting transaction data

69
Q

What are transaction savepoints?

A

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
Q

How to work with savepoints?

A

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
Q

What is searched case expression?

A

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
Q

What is Simple case expression?

A

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
Q

Why Functions should be used over stored procedures?

A

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
Q

Why to have LEFT JOIN and RIGHT JOIN when A LEFT JOIN B is same as B RIGHT JOIN A?

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
Q

What are Indexes ?

A

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
Q

What is Unique Index for a column?

A

A Unique Index on a column makes sure that column accepts only unique values and Index is created on that column itself

77
Q

What is Multicolumn Index?

A

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
Q

What is a B-tree index?

A

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
Q

What are Bit Map Indexes?

A

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
Q

What is in-memory database?

A

An in-memory database stores the data in memory and uses a disk for backup

81
Q

What is on-disk database?

A

An on-disk database stores the data on disk and uses memory for backup

82
Q

What is index?

A

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
Q

What is a row store index?

A

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
Q

What is a rowstore?

A

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
Q

Can using index cause less than optimal performance

A

Yes , an incorrect index choice can cause less than optimal performance

86
Q

What does a query optimizer do?

A

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
Q

How to design index ?

A

Overall index design strategy should provide various indexes for the query optimizer to choose from to optimize a query

88
Q

What is clustered index?

A

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
Q

What is non-clustered index?

A

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
Q

indexes are automatically created when ?

A

PRIMARY KEY and UNIQUE constraints are defined on table columns.

91
Q

Which type of index is created when UNIQUE constraint is added to a table ?

A

Non-clustered index

92
Q

Which type of index is created when a PRIMARY KEY constraint is added while creating a table?

A

clustered index

93
Q

Which type of index is created when a PRIMARY KEY constraint is added on already existing table with clustered index?

A

it creates non clustered index using primary key column

94
Q

what is a table scan?

A

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
Q

What is a row scan?

A

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
Q

What are different types of views

A

Views can be Simple views and complex Views.

97
Q

What is a simple view ?

A

Simple Views operate on a Single table. It allows DML operations. Changes made through the simple view are applied to the table.

98
Q

What is a complex view ?

A

Complex views operate on Multiple tables. So DML operations are not supported

99
Q

Why views are created ?

A

Views are created to hide columns from users and simplify complex database designs

100
Q

What is sorting via expression?

A

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
Q

What is sorting via numeric placeholders?

A

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
Q

Equality operator

A

=

103
Q

InEquality operator

A

<>

104
Q

What does wild card character ‘ _ ‘ mean ?

A

It means one character in string matching expression
Eg : _ _ t _ implies four letter word with t as its third letter

105
Q

How to test whether an expression is null?

A

using ‘IS NULL’ operator

106
Q

How to test whether an expression is not null?

A

using ‘IS NOT NULL’ operator

107
Q

Does inner join order in Query matter in output results?

A

No, it is up to the server to choose the starting table (driving table) in the query and return the results

108
Q

What are Equi-joins?

A

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
Q

What are Non-Equi-joins?

A

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
Q

What is a B-Tree?

A

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
Q

What are some of the properties of B-Tree?

A
  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
Q

What is a B+ Tree?

A

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
Q

Why B+ trees are a good choice for database systems?

A

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
Q

What does Source , initial Catalog in Connection string refer to ?

A

Initial Catalog refers to Database name
Source refers to database server name

115
Q

What is dirty read?

A

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
Q

What is non repeatable read?

A

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
Q

What is phantom read?

A

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
Q

What are isolation levels in sql server?

A

Read uncommitted , read committed, repeatable read, snapshot isolation , serializable

119
Q

What is read uncommitted isolation level?

A

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
Q

What is read committed isloation level?

A

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
Q

When do you use sub query?

A

Sub queries are used to do things that are otherwise not doable or very difficult . Generally avoided

122
Q

Can sub queries be used with select and from?

A

Yes

123
Q

What is connection timeout?

A

Timeout limit for your connection object it can try connecting to server

124
Q

What is command timeout?

A

Timeout limit for your SQL query to execute

125
Q

What are different places for configuring lsolation level settings ?

A

Database, connection, statement

126
Q

What are isolation levels in database?

A

Read committed , Read committed snapshot

127
Q

What is repeatable read isolation level?

A

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
Q

What is serialization anomaly?

A

The result of successfully committing a group of transactions is inconsistent with all the possible orderings of running those transactions one at a time

ASP.NET Flashcards (14 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions