<jupyter_start><jupyter_text>Tables1. What is a dual table and why do we need it?The DUAL table is a special one row table present by default in all Oracle database installations- It is suitable for use in selecting a psuedo columns such as SYSDATE or USER.- I can be used to perform calculations.- It can be used as a sequence generator.---2. How many columns anr rows are present in the DUAL table? OR What is the output of the SQL statement:```sqlSELECT COUNT(1) from dual;```- 1 Row and 1 Column---3. Can we insert rows into the DUAL table?- Yes---4. Can we create a table based on a exsting table? OR What is the easiest way to backup a table?- Yes, using CTAS (Create Table AS)```sqlCREATE TABLE SALES1 AS SELECT * FROM SALES;SELECT * FROM SALES1;```---5. Can we drop all the columns from a table?- No, we get the following error:```ORA-12983: cannot drop all columns in a table```---6. What are psuedo columns- A psuedo column behaves like a table column, but is not actually stored in the table.- You can select from psuedo columns, but you cannot insert, update, or delete their values.- Psuedo columns include:    - SYSDATE    - ROWNUM    - ROWID    - OBJECT_ID    - OBJECT_VALUE    - ORA_ROWSCN    - XMLDATA    - CURRVAL    - NEXTVAL    - LEVEL    - etc.---7. What is the difference between:```sqlCOUNT(1)```and```sqlCOUNT(*)```- Nothing, They are the same    - They incur the same amount of work    - They do the same thing    - They take the same amount of resources---Filters1. What is the difference between IN and = operator?- = operator accepts only 1 value- IN operator can accept more than 1 value```sql-- Using = operatorSELECT employee_id, department_id, salaryFROM employeesWHERE DEPARTMENT_ID = 90-- Using IN operatorSELECT employee_id, department_id, salaryFROM employeesWHERE DEPARTMENT_ID IN (100, 90, 200)```---2. How many maxinum number of values can an IN operator take?- 999 values---3. What is the difference between EXIST and IN when using sub queries?- The difference between the IN and EXISTS predicate in a subquery is the way the query gets executed- **IN**    - The inner query is **executed first** and the list of values obtained as its result is used by the outer query. The inner query is executed for only one.    ```sql    SELECT employee_id, department_id, salary    FROM employees    WHERE DEPARTMENT_ID IN (select department_id FROM department)    ```- **EXISTS**    - The **first row from the outer query is selected**, then the inner query is executed and, the outer query output uses this result for checking.    - This process of inner query execution repeats as many number of times as there are outer query rows.    - That is, if there are ten rows that can result from the outer query, the inner query is executed that many number of times.    ```sql    SELECT *     FROM customers cs    WHERE EXISTS (SELECT * FROM  order_details ord)    ```---4. What is the difference between HAVING and WHERE clause?- WHERE is used to filter the data **before** the aggregation takes place- HAVING is used to filter out the data **after** the aggregation takes place```sqlSELECT department_id, SUM(salary) AS salaryFROM employeesWHERE DEPARTMENT_ID IN (100, 90, 200)GROUP BY department_idHAVING SUM(salary) > 10000```--- DELETE and TRUNCATE1. What is the difference between DELETE and TRUNCATE? Which is faster?- **DELETE**    - DELETE command is used to selectively remove rows from the table using a WHERE clause. If no WHERE condition is specified, all rows will be removed.    - After performing a DELETE operation you need to commit or ROLLBACK the transaction to make the change permanent(COMMIT) or to undo(ROLLBACK) it.    - DELETE triggers on the table are fired if there are any triggers.- **TRUNCATE**    - TRUNCATE removes **all the rows** from a table. You can not roll nack the data.    - Id does not affect any of the table's indexes, triggers, or dependencies.    - Since no UNDO log is maintained and has no dependencies TRUNCATE is faster.---2. Can we use WHERE condition with TRUNCATE?- NO- TRUNCATE is used to delete all the data from the table.---3. Can we delete a record from more than one table simultaneously?- YES```sqlDELETE t1, t2FROM t1 INNER JOIN t2WHERE t1.id = t2.idAND t2.id = t3.id;```---4. How do we delete duplicate records?- Use a psuedo column- Then SELECT the minimum of 2 rows where the row id is equal and DELETE one of them- rowid(PRIMARY KEY) is always unique- Sometimes the interviewer will ask you to write this statement out```sqlDELETE FROM department d1WHERE d1.rowid >(    SELECT MIN(d2.rowid) from department d2    WHERE d1.department_id = d2.department_id        AND d1.department_name = d2.department_name        AND d1.branch = d2.branch)```--- JOINS1. Can your briefly explain the various kinds of JOINS?- INNER JOIN    - INNER JOIN returns all rows from multiple tables where the JOIN condition is met- LEFT OUTER JOIN    - This type of JOIN returns all rows from the LEFT hand table specified in the ON condition and **only** those rows from the other table where the joined fields are equal (join condition is met)- RIGHT OUTER JOIN    - This type of JOIN returns all rows from the **righ hand table** specified in the ON condition and **only** those rows from the other table where the joined fields are equal (join condition is met)- FULL OUTER JOIN    - FUL OUTER JOIN returns all rows from the **LEFT hand** table and the **RIGHT hand** table with NULLS in place where the join condition is not met. ---2. What is a self join and can you explain with an example?- A self join is a join in which a table is joined with itsself```sql-- Getting details about employee and his managerSELECT e1.ename AS employee_name,         e2.ename as manager_name    FROM emp e1, emp e2    WHERE e1.mgr = e2.empno```---3. What happens when a JOIN condition is missed?- It performs a CARTESION Join (also called a CROSS join)- Each row in the first table is paired with all the rows in the second table. (So if the first table has 10 rows and second table has 5 rows, total would be 10*5=50 rows are displayed)```sqlSELECT *    FROM sales s, customer c```- In the above example since not ON parameter is defined the two tables will perfrom all possible joins so all possibilities are shown. If sales has 10 rows and customer has 5 rows then the resulting amount of rows will be 50 (10*5)---4. Which is faster among the LEFT OUTER JOIN and RIGHT OUTER JOIN?- There is no difference. It should not really matter to the database engine.--- Data Types1. Can you briefly eplain the various Datatypes present in Oracle?- There are many data types- **Character Datatypes**    - CHAR    - VARCHAR    - VARCHAR2    - NCHAR and NVARCHAR2    - LONG- **Numeric Datatypes**    - NUMBER    - BINARY_FLOAT    - BINARY_DOUBLE- **Date Datatypes**    - DATE    - TIMESTAMP    - TIMESTAMP WITH TIME ZONE    - TIMESTAMP WITH LOCAL TIMEZONE- **Large Objects**    - CLOB (Charater large object)    - NCLOB (N character large object)    - BFILE (Binary File)- **XML Datatypes**    - XMLType---2. What is the difference between CHAR, VARCHAR and VARCHAR2?- VARCHAR is reserved by Oracle for future releases and is advised not to use it.- CHAR variables store the data in fixed length including the trailing space (It will pad your entry with sapces.)- VARCHAR2 variables stores only the data (Variable length text)---3. Can you reate a column with Boolean datatype in Oracle Databse?- No, oracle does not provide a boolean data type.---4. What is the difference between DATE and TIMESTAMP datatype?- DATE can store month, day, year, centurry, hours, minutes, and seconds.- TIMESTAMP is much more fine grained as a measurement of time than DATE is (millisenconds vs seconds)- Date math with DATE is decimal math up to 5 places deep- TIMSTAMP can optionally store the Time zone as well---5. What is the difference between CHAR and NCHAR datatype?- NCHAR is used to store fixed length Unicode data. It is often used to store data in different languages.CHAR is used to store fixed length character data- When data is stored using CHAR, it takes n bytes while NCHAR takes 2n bytes---6. Explain composite data type, tell me the syntax.- A composite data type could be a record, table, nested table or a varray. This is so because all of them are composed of multiple data types.```sqlTYPE student_data IS RECORD(    first_name, VARCHAR(20),    id NUMBER(2));```--- Functions1. What does INSTR() and SUBSTR() return? What is the difference between INSTR() and SUBSTR()- SUBSTR function is used to **extract a substring** from a string.    ```sql    SUBSTR(source_string, start_position, [length])    ```- INSTR function is used to **return the location** of a substring in a string    ```sql    INSTR(string, substring [, start_position [, th_appearance]])    ```---2. What are conversion functions?- Conversion functions are useful to typecast data from one datatype to other```sqlSELECT first_name,    TO_CHAR (hire_date, 'MONTH, DD, YYYY') HIRE_DATE,    TO_CHAR (salary, '99999.99') SalaryFROM employeesSELECT TO_NUMBER('1210.73', '9999.99') FROM DUAL;SELECT TO_DATE('January 15, 2016', 'Month dd, YYYY') FROM DUAL;```---3. What is the usage of LPAD() and RPAD() functions?- LPAD (left pad) and RPAD (right pad) are used to add padding characters to the left or right side of a string up to a given length-The default padding character is a space- If the strings length is greater than the required length, it will be trimmed (excess characters will be removed).---4. What is the difference between a single row function and multi row function?-Single row functions return a single result row for every row of a queried table or view.```sqlSELECT empno, ename, TO_CHAR(sal, '9,999.99') FROM emp;```- Multiple row functions return a single result row **based on groups of rows**, rather than on a single row.```sqlSELECT deptno, SUM(sal) FROM empGROUP BY deptno;```---5. What are the various date functions you have used? (Common question)- MONTHS_BETWEEN- LAST_DAY- NEXT_DAY- ADD_MONTHS- EXTRACT    - EXTRACT is used to grab specific types of the date, like month, day, year, quarter, etc.---6. How do I add 1 month to SYSDATE?```sqlSELECT ADD_MONTHS(SYSDDATE, 1) FROM dual;```- To substract a date just do -1 ---7. What is the simplest way to add one hour to SYSDATE?```sqlSELECT SYSDATE + (1/24)FROM dual```---8. What is the difference between CASE and DECODE- DECODE AND CASE both provide IF-THEN-ELSE functionality in Oracle SQL.- DECODE is a simple switch comparing each argument against the first such that..    ```sql    DECOODE    (        'if this'        ,'matches this','then this result'        ,'matches this','then this result'        ...        ,'else alternative result'    )    ```- Whereas CASE allow you to do different tests with each WHEN clause    ```sql    CASE    WHEN A = B THEN C    WHEN D != F THEN E    ELSE Q    ```--- NULL's1. What is the output of the below SQL statement?```sqlSELECT 1 + NULL FROM dual;SELECT 1 * NULL FROM dual;SELECT 1 / NULL FROM dual;```    - The answer is NULL.    - Any arithmatic operation on NULL is NULL2. Explain BRiefly NVL, NVL2, NULL IF functions? What is the difference between NVL, NVL2, NULL IF functions? Can you explain about a few NULL funtions?- **NVL**    - The NVL function helps to replace NULL values with a default value        - If the value in the first parameter is NULL, the function returns the value in the second parameter.         - If the first parameter is any value other than NULL, it returns the first parameter value.- **NVL2**    - The NVL2 function accepts three parameters        - If the first parameter value **is NOT NULL** it returns the value in the second parameter.        - If the first parameter value **is NULL**, it returns the third parameter.- **NULLIF**    - It accepts two parameters and returns NULL if both parameters are equal    - If they are not equal, the first parameter value is returned.-**Example:**    ```sql    -- Since the first parameter is NUll it returns the second parameter 1    SELECT NVL(NULL, 1) FROM dual;    -- This will return the first parameter sinze the vlaue is NOT NULL    SELECT NVL('a', 1) FROM dual;    -- If the first parameter is NULL it returns the second parameter 1. If the first parameter is NOT NULL it returns the second parameter 2.    SELECT NV12(NULL, 1,2) FROM dual;    -- If both the parameter values are the same it returns the NULL    SELECT NULLIF('a', 'a') FROM dual;    -- If bothe te parameters are not the same it returns the first value    SELECT NULLIF('a', 'b') FROM dual;    ```---3. Do you know about the COALESCE function?- Returns the first NON NULL value in a list- If all parameters contain NULL values, it returns NULL4. Can you modify a NULL column to a NOT NULL column, if the table has some data?- YES, IF the column does not have NULL values- NO, If the column has null values---5. What is the output of the below SQL statement```sqlSELECT * FROM test1WHERE customer_phone = NULLSELECT *FROM test1WHERE customer_phone IN (NULL)```- Zero rows will be returned    - You cannot use '=' or 'IN' with NULL.    - You have to use 'IS' or 'NOT IS'---6. What is the output of the below SQL statement?```sqlSELECT 'tom' || NULL || 'joe'FROM dual;```- Answer: tomjoe- It will not concatenate with NULL- Most commonly asked returned example---7. What datatype does not allow 'NULL' values?- None. All datatypes can be NULL. You can however set the column on a table to NOT NULL.--- Integrity Constraints Part 1.1 What is a constraint? What types of constraints are available? What is integrity Constraints?- Constraints is a way of enforcing rules in the database, and it maintains the integrity of the database.- Integrity Constraints are used to prevent entry of invalid information into tables- Below are the integrity constraints available in Oracle:    - NOT NULL        - A NOT NULL constraint prohibits a database value from being NULL    - PRIMARY KEY        - UNIQUE + NOT NULL    - FOREIGN KEY        - A foreign key constraint requires values in one table to match values in another table.    - CHECK        - A check constraint requires a value in the database to comply with a specified condition    - UNIQUE        - A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be NULL    - REF        - A REF column by definition refrences an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object refrences.---2. Can a table have 2 primary keys?- NO---3. Can we create a primary key constraint on one column and a foreign key on other column?- Yes---4. How do you disable a constriants?```sqlALTER TABLE testDISABLE CONSTRAINT constraint_name;```---5. Can you use SYSDATE un check constraints? If no, why?- NO!!! SYSSDATE, CURRVAL, NEXTVAL, LEVEL, ROWID, UID, USER or USERENV cannot be refrenced with Oracle check constraint- This is because, the values are dynamic in nature!!---6. If you create a primary key, by default which index is created?- Yes, When you create a primary key, by default a unique key is created---7. What are CASCADE CONSTRAINTS- CASCADE CONSTRAINTS clause is used to drop all referential integrity constraints that refer to primary and unique keys in the dropped table.- If you omit this clause, and if the table has a referential integrity constraints, then the database returns an error and does not drop the table.---8. What is ON DELETE CASCADE?- A foreign key with CASCADE DELETE means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted---9. Which will fire first, a constraint or a trigger- Constraints are fired first, if AFTER trigger exists- But if there are any BEFORE triggers on the table, they are fired first.---10. I have two tables and they ahve foreign keys refrencing each other with different columns. How can I inset the data into the tables- You would have to diable the foreign key to do so.- They souldnt have designed the tables with circular refrences- Thats typically a big no no.--- Sequences1. What is a sequence and what is the need of it- A sequence is a databse object that auto generates continuous unique numbers- It is used whenever we need to poulate a column with unique values- This is a very common interview question2. Will the below sequence be created?```sqlCREATE SEQUENCE sequence_name;```- Yes it will be created, Its start value will be 1 and its increment will be by 1```sql CREATE SEQUENCE sequence_name    -- These are all optional parameters    MINVALUE value    MAXVALUE value    START WITH value    INCREMENT BY value;```---3. Can you reset the sequence to start at a different number?- YES- To reset the value use the ALTER SEQUENCE statement--- Synonyms's1. Can you create a synonym with the same name of the object?- No, if you are creating in the **same schema**- Yes, if you are creating in a **different schema**---2. What is a synonym and what is a use of a synonym?- A synony is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.- Synonyms are used if you want to **grant access to an object from another schema** and you dont want users to worry about knowing which schema owns the object.- It can also be used to **switch your code between two schemas** with the same structure by redefining the synonyms---3. What are public synonyms- Public synonyms are synonyms which are accessible to all users- However, each user must have appropriate privileges on the underlying object in order to use the synonym.--- SET1. What are the various SET operators available?- UNION    - It returns rows of first query + rows of second query minus duplicate rows- UNION ALL    - It returns rows from both the queries including duplicate rows- MINUS    - Rows that unique for the first query will be retrieved- INTERSECT    - Common rows from both the queries will be retireved.---2. What is the difference between JOINS and SET operators- SET operators are used to combine **similar types of data** from two or more tables.- JOINS is used to select columns from two or more tables based on logical relationships between the tables---3. What is the difference between UNION and UNON ALL?- UNION    - The UNION set operator **returns all distinct rows** selected from the queries, That means any duplicate rows will be removed- UNION ALL    - The UNION ALL set operator **returns all rows** selected from the queries, even the duplicate rows are returned---4. Which is faster between UNION ALL and UNION- UNION    - The UNION set operator **returns all distinct rows** selected from queries, That means any duplicate rows will be removed    - The removal of duplicates requires extra processing, hence takes some time- UNION ALL    - The UNION ALL set operator **returns all riws** selected from the queries, even the duplicate rows are returned    - UNION AL merely appends the results of teh second fetch to follow right after the first- So, UNION ALL is faster!---5. Should the number of columns be the same when performing SET operations?- YES---6. Should the datatypes of the columns be the same when performing SET operations?- YES--- Sub Queries1. What is a Nested Sub Query?- A query which appears in teh WHERE clause is called a nested sub query```sqlSELECT * FROM salesWHERE customer_id IN ( SELECT customer_id                        FROM customer                        WHERE region = 'SOUTH')```---2. What is an Inline View?- A subquery which appears in the FROM clause is called as Inline View.```sqlSELECT *FROM sales s, (    SELECT customer_Id    FROM customer    WHERE region = 'SOUTH') cWHERE s.customer_id = c.customer_id```---3. What is a scalar sub query?- A subquery which appears in the SELECT clause is called as a Scalar sub query.- It can return only 1 column and 1 row```sqlSELECT s.customer_id, s.total_amount, (    SELECT first_name    FROM customer c    WHERE c.customer_id = s.customer_id) first_nameFROM sales s```---4. What is the advantage of WITH clause?- Using a WITH clause, we can execute the sub query once and can reference it multiple times in the main query- The data is saved in a temporary table, thus improving the performance---5. Write a correlated sub query and explain how it works.- A correlated subquery is a subquery that uses values from the outer query, requiring the inner query to **execute once for each outer query**```sqlSELECT empno, fname, sal, deptidFROM emp e WHERE sal = (    SELECT AVG(sal)    FROM emp    WHERE deptid=e.deptid)``` Views1. What is a view and what are the advantages of it?- View is a virtual table baased on the result set of an SQL statement- When you execute the view, data is pulled from the database and shown to the user- **Advantages**    - We can use views to hide data complexity. Instead of forcing users to learn the complicated JOINS and business rules, you can provide a view that runs a commonly requested SQL statement    - We can use views to protect the data from some users. (We can mask the columns like account number, social security numbers..etc)    - We can use views to limit acces to only part of the table---2. Can we create a synonym on view?- Yes---3. What are key preserved tables.- A table is key preserved if **every key of thet able** can also be a key of the result of the JOIN. So, a key=preserved table has its keys preserved though a JOIN.- The concept dictates the restrictions on modifying JOIN views.```sqlCREATE VIEW customer_sales ASSELECT c.customer_id, c.first_name, c.last_name,c.region, s.sales_date, s.order_id, s.product_id, s.total_amountFROM customer c, sales sWHERE c.customer_id = s.customer_id```- Here CUSTOMER table has a PRIMARY KEY on the column CUSTOMER_ID and it is present in the view of customer_sales. So **CUSTOMER table is our key preserved table** in this view.---4. Can we delete a record in a view?- Yes- Rows from a join view can be deleted as long as there is exactly one key-preserved table in the JOIN- The key preserved table can be repeated in the FROM clause- If the view is defined with the WITH CHECK OPTION clause and the key preserved table is repeated, the the rows cannot be deleted from the view.---5. Can we update a view?- Yes- All updatable columns of a JOIN view must map to columns of a key preserved table.- If the view is define with the WITH CHECK OPTION clause, then all JOINcolumns and all columns of repeated tables are not updatable.---6 Can we insert data into a view?- An INSERT statement must not explicitly or implicitly refer to the columns of a non key presered table.- If the JOIN view is defined with the WITH CHECK OPTION clause, INSERT statements are not permitted---7. If I have created views from two tables by taking all the columns, Is it possible to update that view?- NO- If you have to compulsory update that view, how do you do that?-By using instead of triggers```sqlCREATE OR REPLACE TRIGGER V_MYTRIGGERINSTEAD OF UPDATEON VIEW_NAMEFOR EACH ROW```---8. If a table is dropped, what happens to the views?- All the views which are refrencing the dropped table **will become invalid**---9. Whatis the FORCE option on a View?- You can create a view with errors by using the FORCE option, that means even if the SQL statement is not valid, you can force racle to create it.```sqlCREATE FORCE VIEW customerview ASSELECT * FROMtable_waiting;```--- Database Concepts1. What is a Database?- A database is a collection of information that is organized so that it can easily be accessed, managed, and updated---2. What is the difference between DBMS and RDBMS- DBMS refers to Database Management System and data is stored in files-Later based on the relational model, RDBMS (Relational Database Management System) was instroduced- In RDBMS, data is stored in files, represented in the form of tables and you establish relationships betweenmultiple tables of the database using Primary Keys and Foriegn Keys---3. What is SQL - Structured Query Language (SQL) is a language designed specifically for communicating with databases - SQL is an ANSI (AMerican National Standas INstitute) standard. ---4. What are the advantages of SQL?- SQL is not a proprietary language used by specific database vendors. Almost every DBMS supports SQL, so learning this one language will enable programmers to interact with any database like ORACLE, SQL, MYSQL, etc.- SQL is easy to learn, The statements are all made up of descriptive English words, and there arn't that many of them- SQL is actually a very powerful language and by using its language elements you can perform very complex and sophisticated database operations---5. What is a FIELD/RECORD/TABLE?- A FIELD is an area within a record reserved for a specific piece of data    - Examples        - Employee Name, Employee ID, etc.- A RECORD is the collection of values/fields of a specific entity.    - Examples        - Employee, Salary- A TABLE is a collection of records of a specific type    - Examples        - employee table, salary table.---6. What is a Transaction?- A transaction is a unit of work    - If a transaction is successful, all  of the data modifications made during the transaction are committed and become a permanent part of the database    - If a transaction enounters errors and must be cancelled or rolled back, then all of the data modifcations are erased- Database transactions take the databse from one consistent state to another.---7. What are the properties of a transaction?- **Atomicity**    - A transaction consists of many steps. When **all the steps in a transaction get completed**, it will get reflected in a Database or if any step fails, all the transactions are rolled back.- **Consistency**    - The database wiwll move from one consistent state to another if the transation succeeds and remains in the original state if the transaction fails- **Isolation**    - Every transaction should operate as if it is the only transaction in the system- **Durability**    - Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis.---8. What is a database lock?- A database lock (read lock or write lock), is used when multiple users need to access a database concurrently- This prevents data from being corrupted or invalidated while multiple users try to read while others write to the Database---9. What are the types of Locks?- **Shared Lock**    - When a shared lock is applied on a data item, other transactions can only read the item, but cant write into it.- **Exclusive Lock**    - When an exclusive lock is applied on a data item, other transactions can't read or write into the data item.---10. What is SGA- The SGA (System Global Area) is an area of memory (RAM) allocated when an Oracle Instance starts up- SGA is used to store shared information across all database users- The common components are:    - Library Cache- It is used to store Oracle Statemnts    - Data Dictionary Cache - It contains the definition of Database objects and privileges granted to users    - Data Base buffer cache - It holds copies od data blocks which are frequently accessed so that they can be retrieved faster for any futute requests    - Redo log buffer cache - It records all changes made to teh data files---11. What is PGA- The PGA (Program or Process Global Area) is a non shared memory created by Oracle Database when a server process is started- Access to the PGA is exclusive to the server process- The is one PGA for each server---12. What is a tAblespace?- Tablespaces are the logical storage units which comprises of one or more datafiles- Each tablespace in an Oracle Database is comprised of one or more operating system files called data files.---13. What are data blocks?- A data block is the smallest unit of data used by a database- Oracle stores the data in the data files and manages them in units called data blocks---14. What is a data dictionary?- A data dictionary is a set of read only tables that provides information (metadata) about the database. A data dictionary contains:    - The definitions of all schemas objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)    - How much space has been allocated for, and is currently used by, the schema objects    - Default values for colummns    - Integrity constraint information    - The names of Oracle users    - Privileges and roles each user has been granted    - Auditing information, such as who has accessed or updated various schema objects.    - ther general database information- The data dictionary is presented to us in the form of DBA, ALL or USER view.---15. What is an Oracle Instance?- When a database is started, all the required software is loaded in the memory (PGA). The software and PGA is used to manipulate the data in the database. This is referred to as an Instance.---16. What is a SAVEPOINT- A SAVEPOINT is a marker within a transaction that allows for partial rollback- As changes are made in a transaction, we can create SAVEPOINTs to mark different points within the transaction- If we encounter an error, we can rollback to a SAVEPOINT or all the way back to the beginning of the transaction.---17. What is Table Fragmentation?- When we delete data from a table, space is freed up and is not immediately re-used- This leaves behind holes in table which result in table fragmentation- It leads to    - Slower response time    - High number of chained (actually migrated) rows.    - Table has grown many folds and the old space is not getting reused.---18. What is Data Modeling- Data Modeling is the first step in database design- It starts from a conceptual model of how data items relate to each other- Then it progresses from conceptual model to logical model to physical schema- **Conceptual Data modeling** - Identifies the highest level relationships between different entities.- **Logical Data Modeling** - Illustrates the specific entities, attributes and relationships involved in a business function. Serves as the basis for the creation of the physical data model.- **Physical Data Modeling** - Represents an application and database-specific implementation of a logical data model19. What is ER Diagram- An Entity Relationship (ER) Diagram is a graphical representation of the relationship between people, objects, places, concepts, or events within that system- In the database world, An Entity-Relationship (ER) diagram, is agraphical representation between database entities.---20. What is the difference between logical and physical data model?- The logical data model represents database in terms of logical objects, such as entities and relationships- The physical data model represents database in terms of physical objects, as tables and constraints and columns---21. What are dead locks?- A deadlock occurs when two or more sessions are waiting for data locked by each other. resulting in all teh session being locked- Oracle automatically detects and resolves deadlocks by rolling back the statement associated with teh transaction that detects the deadlock---22. If you run the SQL statement below:```sqlSELECT * FROM emp;``` What are the steps the SQL engine will follow to display the output?1. SQL Statement2. Parsing    - Syntax Check    - Semantic Check    - Shared Pool Check3. Optimization    - Generation of multiple execution plans4. Row Source Generation    - Generation of query plan5. Execution<jupyter_code><jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.06108483128350678, 'fraction_numerical': 0.008420056764427626, 'mean_word_length': 3.7379351561332737, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '365281', 'license': 'MIT', 'n_tokens_mistral': 8057, 'n_tokens_neox': 7656, 'n_words': 4929, 'path': 'nbs/sql_questions.ipynb', 'repo_name': 'mpHarm88/hireheroes', 'size': '31710'}