What is DBMS? DBMS (Database Management System) is software that manages and organizes data in databases, providing an interface for users and applications to interact with and manipulate data efficiently. What are the use cases? Data Storage and Management: Efficiently stores and manages large volumes of d Data Retrieval: Provides quick and easy access to data through queries. Transaction Management: Ensures data consistency and integrity during transactions Data Security: Controls access and protects data from unauthorized access, Data Backup and Recovery: Facilitates regular backups and restores data in case of loss. Data Integrity: Maintains accuracy and consistency of data through constraints and validation. Multi-User Access: Supports concurrent access by multiple users while managing conflicts. Reporting and Analysis: Generates reports and performs data analysis for business insightsWhat are the different keys? Can you name some? Primary Key: Uniquely identifies each record in a table. Foreign Key: Establishes a relationship between records in two tables. Unique Key: Ensures that all values in a column or a set of columns are unique across the table, but unlike a primary key, it can accept NULL values. Composite Key: A combination of multiple columns used to uniquely identify a record. Candidate Key: A columa or set of columns that can uniquely identify a record in a table. There can be multiple candidate keys, but only one is chosen as the primary key.What are the different keys? Can you name some? Primary Key: Uniquely identifies each record in a table. Foreign Key: Establishes a relationship between records in two tables. Unique Key: Ensures that all values in a column or a set of columns are unique across the table, but unlike @ primary key, it can accept NULL values. Composite Key: A combination of multiple columns used to uniquely ident y a record, Candidate Key: A columa or set of columns that can uniquely identify a record in a table. There can be multiple candidate keys, but only one is chosen as the primary key. (TRICKY QUESTION) What happens if you try to insert a value in a foreign key column that doesn’t exist in the referenced primary key column? It will result in a referential integrity constraint violation, and the insert operation wil filDetnion Primary Key VS Foreign Key Primary Key VS Unique KeyEvery primary key is a super key, but not every super key key. Why? a primary Every primary key is a super key because it uniquely identifies each record. However, not every super key isa primary key because: Primary Key: A specific super key chosen to uniquely identify records, must be unique and not, NULL ‘Super Key: Any set of columns that uniquely identifies records; it may include extra columns beyond what is necessary Example: Primary Key: (Employee!O} uniquely identifies each record ‘Super Key: {EmployeelD, Email) is a super key because it also uniquely ident includes extra columns. les records butNormalisation (IMPORTANT: Only definition) First Normal Form (1NF): Ensures that each column contains only atomic (indivisible) values and each record is unique Second Normal Form (2NF): Achieved when the table is in 1NF and all non-key attributes are fully functionally dependent on the entire primary key. Third Normal Form (3NF): Achieved when the table is in 2NF and alll attributes are functionally dependent only on the primary key, with no transitive dependencies Boyce-Codd Normal Form (BCNF): A stronger version of 3NF where every determinant is a candidate key.(Very Important - Most Asked) ACID Properties Inother words, it ensures that no transaction occurs partially ‘Consistency ~ This property ensures that integrity constraints are maintained, Inother words, it ensures that the database remains consistent before and after the transaction ' trlation = This property ensures that multiple transactions can occur simultaneously without causing any The resultant state ofthe system after executing all the transactions isthe same asthe state that ‘would be achieved if the transactions were executed serially one ater the other, ‘© Durability This property ensures that ll the changes made by a transaction after its successful execution are that these changes exst ently and are never lost even if there occurs 8What are the different types of SQL commands? 1. Data Definition tanguage (001): 00% n of Data Defirtion Language, which deals with database schema2. Data Manipulation Language (OML: OM s short name of Data Manipulation Language wich dal with data2. ata Manipulation Language (OML}: OM s short name of Data Manipulation Language which deals with data manipulation ad includes most common SOL statements such SELECT, INSERT, UPDATE, DELETE. ee. and tis wed DELETE FROM Employees WHERE EmployeelO «1 3. Data Control Language (DCL) OCLs short name of Data Control Language which includes commands uch 85 GRANT and mostly concerned with rights, permissions and other controls of the database system, GRANT: Gives user access privileges to the database (GRANT SELECT, INSERT ON Employees TO UserNam REVOKE: Removes user access privileges REVOKE SELECT, INSERT ON Employees FROM UserName;ROLLBACK: Undoes changes made during th SAVEPOINT Sets savepont with(Very Important - Most Asked) DROP VS DELETE VS TRUNCATE fee ry sc = cate: | rater toneAggregate function in SQL ‘Aggreese functions in Sk peror calculations on set of voles and vet J value. t's mostly wed with Group by + LeoUT: Returns the number of rows that match the specifederteri. 2. SUMI}: Returns the total sum of hen 3. AVGO) Returns the average value ofa numeric ol + san: Returns he smal value in as IMAXD: Returns the largest vlJOINS Types of join: «INNER JOIN: Returns rows with matching values in both table LEFT JOIN (LEFT OUTER JOIN}: Returns all rows from the left NULLS for unmatched rows from the righ RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the, table; NULLs for unmatched rows from the lft. ht table and matching rows from the left + FULL OUTER/OIN: Returns all rows with matches in ether table; NULL table + ROSS JOIN: Returns the Cartesian product of Both tables, combining every row from the frst table withUNION VS UNION ALL Union - Removes duplicate rows Union All Includes al rows, including duplicates JOIN VS UNION (VERY IMPORTANT) are ur ectyhowio inthe [Dos tear peermace [ese orice combring rte | May be oneJOINS Types of join: INNER JOIN: Returns rows with matching values in both table LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table NULLS for unmatched rows from the right RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left table; NULLS for unmatched rows from the lft. FULL OUTER JOIN: Returns al rows with matches in ether table; NULLS for unmatched row tables (CROSS JOIN: Returns the Cartesian product of both tables, combining every row from the first table with ‘every row from the second table SELECT column name(s) FROM tab YOIN_TVPE ON tablet.column_name = table2.column_name