SQL

Advantages of NoSQL database

1.) NoSQL databases generally process data faster than relational databases. 2.) NoSQL databases are also often faster because their data models are simpler. 3.) Major NoSQL systems are flexible enough to better enable developers to use the applications in ways that meet their needs.

What is a view/materialized view.

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). What is the difference between materialized view and view? A view uses a query to pull data from the underlying tables. A materialized view is a table on disk that contains the result set of a query. Materialized views are primarily used to increase application performance when it isn't feasible(amalga oshadigan / mumkin) or desirable(kerakli) to use a standard view with indexes applied to it.

Third Normal form (3NF)

A table design is said to be in 3NF if both the following conditions hold: · Table must be in 2NF · Transitive functional dependency of non-prime attribute on any super key should be removed. An attribute that is not part of any candidate key is known as non-prime attribute. In other words 3NF can be explained like this: A table is in 3NF if it is in 2NF and for each functional dependency X-> Y at least one of the following conditions hold: · X is a super key of table · Y is a prime attribute of table An attribute that is a part of one of the candidate keys is known as prime attribute. Example: Suppose a company wants to store the complete address of each employee, they create a table named employee_details that looks like this:

What is the need for indexing?

An index is created on a column of a table. Example,we have a database table called User with three columns - Name, Age, and Address. ... This is where index helps us "index is used to speed up search queries by essentially cutting down the number of records/rows in a table that need to be examined

Atomicity

Atomicity is an all-or-none proposition. - ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.

Consistency

Consistency guarantees that a transaction never leaves your database in a half-finished state.

Types of constraints(cheklovlar)

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted. Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table. SQL constraints are used to specify rules for the data in a table. The following constraints are commonly used in SQL: 1. NOT NULL - Ensures that a column cannot have a NULL value 2. UNIQUE - Ensures that all values in a column are different 3. PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table 4. FOREIGN KEY - Uniquely identifies a row/record in another table 5. CHECK - Ensures that all values in a column satisfies(qondirish) a specific condition 6. DEFAULT - Sets a default value for a column when no value is specified 7. INDEX - Use to create and retrieve data from th

De-Normalization?1

Denormalization is a strategy used on a previously-normalized database to increase performance. In computing, denormalization is the process of trying to improve the read performance of a database, at the expense of losing some write performance, by adding redundant copies of data or by grouping data. It is often motivated by performance or scalability in relational database software needing to carry out very large numbers of read operations. Denormalization should not be confused with Unnormalized form. Databases/tables must first be normalized to efficiently denormalize them.

Durability

Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

Which of SELECT, UPDATE, DELETE, ADD's performance is mostly affected by performance of indexes?

Every index that you add to a table will slow down insert/update/delete operations as there are now more physical changes which need to be made when the insert/update/delete operations happen. SELECT - performance is mostly affected by performance of indexes

Global Temporary Tables

Global temporary tables are also stored in tempdb. Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. They are specified with the prefix #, for example ##table_name. create table ##GlobalTemporaryTable ( column_name varchar(20), column_no int )

Normalization

Here are the most commonly used normal forms: · First normal form(1NF) · Second normal form(2NF) · Third normal form(3NF) · Boyce & Codd normal form (BCNF)

Inner vs Outer join?

INNER-JOIN: It merges(or combiens) matched rows from two tables. OUTER-JOIN: It merges(or combines) matched rows from two tables and unmatched rows with NULL values

Why many indexes are not good for performance

If the table is heavily hit by UPDATEs, INSERTs + DELETEs ... these will be very slow with lots of indexes since they all need to be modified each time one of these operations takes place If you do mostly reads (and few updates) then there's really no reason not to index everything you'll need to index. If you update often, then you may need to be cautious on how many indexes you have. There's no hard number, but you'll notice when things start to slow down.

Index Types

In addition to an index being clustered or nonclustered. Database Indexes Explained A database index allows a query to efficiently retrieve data from a database. Indexes are related to specific tables and consist of one or more keys. A table can have more than one index built from it. The keys are a fancy term for the values we want to look up in the index. The keys are based on the tables' columns. By comparing keys to the index it is possible to find one or more database records with the same value. Since an index drastically speeds up data retrieval, it is essential the correct indexes are defined for each table. Missing indexes won't be noticed for small databases, but rest assured, once your tables grow in size, queries will take much longer.

Aggregate functions with examples

In database management an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement such as a set, a bag or a list. Common aggregate functions include : Average() (i.e., arithmetic mean) Count()

Update anomaly

In the above table we have two rows for employee Rick as he belongs to two departments of the company. If we want to update the address of Rick then we have to update the same in two rows or the data will become inconsistent. If somehow, the correct address gets updated in one department but not in other then as per the database, Rick would be having two different addresses, which is not correct and would lead to inconsistent data.

Why do you index a database?

Indexes are data structures that improve data retrieval from tables in a database. ...Indexes come in two flavors: clustered and non-clustered. You can only have one clustered index per table, as it is joined to the table providing sorted storage. The clustered index is the Primary Key. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance. You can createindexes on most columns in a table or a view. ... Instead, I'll focus on those indexes that are implemented most commonly in a SQL Server database.

Are database Indexes useful? What is the role of them?

Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

Nonrepeatable Reads

Nonrepeatable Reads A nonrepeatable read occurs when a transaction reads the same row twice but gets different data each time. For example, suppose transaction 1 reads a row. Transaction 2 updates or deletes that row and commits the update or delete. If transaction 1 rereads the row, it retrieves different row values or discovers that the row has been deleted.

Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database

Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly. Let's discuss about anomalies first then we will discuss normal forms with examples.

Anomalies in DBMS

There are three types of anomalies that occur when the database is not normalized. These are - Insertion, update and deletion anomaly. Let's take an example to understand this. Example: Suppose a manufacturing company stores the employee details in a table named employee that has four attributes: emp_id for storing employee's id, emp_name for storing employee's name, emp_address for storing employee's address and emp_dept for storing the department details in which the employee works. At some point of time the table looks like this:

What is clustered index and non clustered index?

Therefore table can have only one clustered index. ... A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered indexdoes not consist of the data pages. Instead, the leaf nodes contain index rows

Primary key vs unique key. Differences.

1. Primary Key is used to identify a row (record) in a table, whereas Unique-key is to prevent duplicate values in a column (with the exception of a null entry). 2. Indexing: By default SQL-engine creates Clustered Index on primary-key if not exists and Non-Clustered Index on Unique-key

Second normal form (2NF)

A table is said to be in 2NF if both the following conditions hold: · Table is in 1NF (First normal form) · No non-prime attribute is dependent on the proper subset of any candidate key of table. An attribute that is not part of any candidate key is known as non-prime attribute. Example: Suppose a school wants to store the data of teachers and the subjects they teach. They create a table that looks like this: Since a teacher can teach more than one subjects, the table can have multiple rows for a same teacher.

What are transactions?

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent(doimiy) part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased

How do transactions work?

A transaction symbolizes a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions. A transaction generally represents any change in a database. A transaction is the propagation of one or more changes to the database. For example, if you are creating a record or updating a record or deleting a record from the table, then you are performing a transaction on that table. It is important to control these transactions to ensure the data integrity and to handle database errors.

Views, why they are needed?

A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query. A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on the written SQL query to create a view. A view provides several benefits. 1. Views can hide complexity If you have a query that requires joining several tables, or has complex logic or calculations, you can code all that logic into a view, then select from the view just like you would a table. 2. Views can be used as a security mechanism A view can select certain columns and/or rows from a table, and permissions set on the view instead of the underlying tables. This allows surfacing only the data that a user needs to see. 3. Views can simplify supporting legacy code If you need to refactor a table that would break a lot of code, you can replace the table with a view of the sa

What is the use of index in SQL?

An index is used to speed up the performance of queries. It does this by reducing the number of database data pages that have to be visited/scanned. In SQL Server, a clustered index determines the physical order of data in a table. There can be only one clustered index per table (the clustered index IS the table)

First normal form (1NF)

As per the rule of first normal form, an attribute (column) of a table cannot hold multiple values. It should hold only atomic values. Example: Suppose a company wants to store the names and contact details of its employees. It creates a table that looks like this:

When and Why to Use Denormalization

As with almost anything, you must be sure why you want to apply denormalization. You need to also be sure that the profit from using it outweighs any harm. There are a few situations when you definitely should think of denormalization: 1. Maintaining history: Data can change during time, and we need to store values that were valid when a record was created. What kind of changes do we mean? Well, a person's first and last name can change; a client also can change their business name or any other data. Task details should contain values that were actual at the moment a task was generated. We wouldn't be able to recreate past data correctly if this didn't happen. We could solve this problem by adding a table containing the history of these changes. In that case, a select query returning the task and a valid client name would become more complicated. Maybe an extra table isn't the best solution. 2. Improving query performance: Some of the queries may use multiple tables to access

What kind of joins do you know?

CARTESIAN JOIN - CROSS JOIN= SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS, ORDERS; UNION = SELECT C.NAME FROM cars C UNION SELECT X.NAME FROM CASHBOX X ORDER BY NAME; BU FAQAT DISTINCT QILIB CHIQARADI BIR XIL NAME LARNI BITTASINI CHIQARADI 1-INNER-JOIN: It merges(or combiens) matched rows from two tables. 2-OUTER-JOIN: It merges(or combines) matched rows from two tables and unmatched rows with NULL values An SQL join clause combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-table) or more tables by using values common to each. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER , CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-join. Cross join explicit cross join: SELECT * FROM employee CROSS JOIN department; implicit cross join: SELECT * FROM employee, depar

DDL (Data Definition Language)

Data Definition Language (DDL) statements are used to define the database structure or schema. CREATE - to create objects in the database ALTER - alters the structure of the database DROP - delete objects from the database TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed COMMENT - add comments to the data dictionary RENAME - rename an object

DML (Data Manipulation Language)

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples: SELECT - retrieve data from the a database INSERT - insert data into a table UPDATE - updates existing data within a table DELETE - deletes all records from a table, the space for the records remain MERGE - UPSERT operation (insert or update) CALL - call a PL/SQL or Java subprogram EXPLAIN PLAN - explain access path to data LOCK TABLE - control concurrency

Dirty Reads

Dirty Reads A dirty read occurs when a transaction reads data that has not yet been committed. For example, suppose transaction 1 updates a row. Transaction 2 reads the updated row before transaction 1 commits the update. If transaction 1 rolls back the change, transaction 2 will have read data that is considered never to have existed.

Isolation

Isolation keeps transactions separated from each other until they're finished. enables transactions to operate independently of and transparent to each other.

Boyce Codd normal form (BCNF)

It is an advance version of 3NF that's why it is also referred as 3.5NF. BCNF is stricter than 3NF. A table complies with BCNF if it is in 3NF and for every functional dependency X->Y, X should be the super key of the table. Example: Suppose there is a company wherein employees work in more than one department. They store the data like this:

Local Temporary Tables

Local temporary tables are the tables stored in tempdb. Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session. They are specified with the prefix #, create table #table_name ( column_name varchar(20), column_no int )

Aggregate Functions

MIN returns the smallest value in a given column SUM returns the sum of the numeric values in a given column AVG returns the average value of a given column COUNT returns the total number of values in a given column COUNT(*) returns the number of rows in a table

What Are the Disadvantages of Denormalization?

Obviously, the biggest advantage of the denormalization process is increased performance. But we have to pay a price for it, and that price can consist of: 1. Disk space: This is expected, as we'll have duplicate data. 2. Data anomalies: We have to be very aware of the fact that data now can be changed in more than one place. We must adjust every piece of duplicate data accordingly. That also applies to computed values and reports. We can achieve this by using triggers, transactions and/or procedures for all operations that must be completed together. 3. Documentation: We must properly document every denormalization rule that we have applied. If we modify database design later, we'll have to look at all our exceptions and take them into consideration once again. Maybe we don't need them anymore because we've solved the issue. Or maybe we need to add to existing denormalization rules. (For example: We added a new attribute to the client table and we want to store its history val

Regular User Tables

Regular user table is the actually table which holds data of user for later on processing and reporting purpose. These are also called physical tables at they physically resides at hard drive until you DROP them intentionally. Regular tables are the most important tables. They contain data for later use. Regular tables are stored in the hard drive. If you want to delete these tables from hard drive, you can use a drop command CREATE TABLE [dbo].[TestTable] ( [TestTableID] [int] NOT NULL, [FirstCol] [varchar](200) NULL, [SecondCol] [int] NULL ) ON [PRIMARY] -- This part indicates, where (Database FileGroup) table will be created physically

Insert anomaly

Suppose a new employee joins the company, who is under training and currently not assigned to any department then we would not be able to insert the data into the table if emp_dept field doesn't allow nulls.

Delete anomaly

Suppose, if at a point of time the company closes the department D890 then deleting the rows that are having emp_dept as D890 would also delete the information of employee Maggie since she is assigned only to this department. To overcome these anomalies we need to normalize the data. In the next section we will discuss about normalization.

Temporary Tables

Temporary tables and created to hold temporary data regarding intermediate results of different quires. These tables will be drooped automatically once the store procedure is executed (if they are used in stored procedure) or once the session is over. But as good programming practice will must drop these tables once they are not required. CREATE TEMPORARY TABLE Temp_TestTable ( [TestTableID] [int] NOT NULL, [FirstCol] [varchar](200) NULL, [SecondCol] [int] NULL ) GO -- DROP TABLE #Temp_TestTable --(Drop temporary table when not required) GO

Transaction Control

The following commands are used to control transactions. COMMIT − to save the changes. ROLLBACK − to roll back the changes. SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK. SET TRANSACTION − Places a name on a transaction. Transactional Control Commands Transactional control commands are only used with the DML Commandssuch as - INSERT, UPDATE and DELETE only. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database.

Isolation Levels

Transaction isolation levels are a measure of the extent to which transaction isolation succeeds. In particular, transaction isolation levels are defined by the presence(mavjudligi) or absence(yo'qligi) of the following phenomena(fenomena-hodisalar):+ · Dirty Reads A dirty read occurs when a transaction reads data that has not yet been committed. For example, suppose transaction 1 updates a row. Transaction 2 reads the updated row before transaction 1 commits the update. If transaction 1 rolls back the change, transaction 2 will have read data that is considered never to have existed. · Nonrepeatable Reads A nonrepeatable read occurs when a transaction reads the same row twice but gets different data each time. For example, suppose transaction 1 reads a row. Transaction 2 updates or deletes that row and commits the update or delete. If transaction 1 rereads the row, it retrieves different row values or discovers that the row has been deleted. · Phantoms A phantom is a row that matc

What is ACID?

Transactions have the following four standard properties, usually referred to by the acronym ACID. ACID (an acronym for Atomicity, Consistency Isolation, Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all these four attributes should be achieved. Andreas Reuter and Theo Härder created the acronym ACID in the year 1983 however Jim Gray had already defined these properties in the late 1970s. SQL Server, Oracle, MySQL, PostgreSQL are some of the databases which follows ACID properties by default.

Icrease performance of database

What are ways to increase performance of database Optimize the logical design The logical level is about the structure of the query and tables themselves. Try to maximize this first. The goal is to access as few data as possible at the logical level. · Have the most efficient SQL queries · Design a logical schema that support the application's need (e.g. type of the columns, etc.) · Design trade-off to support some use case better than other · Relational constraints · Normalization Optimize the physical design The physical level deals with non-logical consideration, such as type of indexes, parameters of the tables, etc. Goal is to optimize the IO which is always the bottleneck. Tune each table to fit it's need. Small table can be loaded permanently loaded in the DBMS cache, table with low write rate can have different settings than table with high update rate to take less disk spaces, etc. Depending on the queries, different index can be used, etc. You can denormalized data trans

Does each SQL system implement the SQL standard in the same way?

http://troels.arvin.dk/db/rdbms/#legend Legend, definitions, and notes The following SQL standard and implementations have been examined, if not otherwise stated:

Index organization table

· A heap-organized table does not store rows in any particular order. The CREATE TABLE statement creates a heap-organized table by default. · An index-organized table orders rows according to the primary key values. For some applications, index-organized tables enhance performance and use disk space more efficiently. An index-organized table has a storage organization that is a variant of a primary B-tree. Unlike an ordinary (heap-organized) table whose data is stored as an unordered collection (heap), data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Each leaf block in the index structure stores both the key and nonkey columns. The structure of an index-organized table provides the following benefits: · Fast random access on the primary key because an index-only scan is sufficient. And, because there is no separate table storage area, changes to the table data (such as adding new rows, updating rows, or deleting rows) result on

Phantoms

· Phantoms A phantom is a row that matches the search criteria but is not initially seen. For example, suppose transaction 1 reads a set of rows that satisfy some search criteria. Transaction 2 generates a new row (through either an update or an insert) that matches the search criteria for transaction 1. If transaction 1 reexecutes the statement that reads the rows, it gets a different set of rows.

Unique Key

• Can be more than one unique key in one table • Unique key can have NULL values • It can be a candidate key • Unique key can be NULL and may not be unique

Primary Key

• There can only be one primary key in a table • In some DBMS it cannot be NULL - e.g. MySQL adds NOT NULL • Primary Key is a unique key identifier of the record