Sql Interview

• DBMS (Relational, Hierarchical Network, Graph, Object-Oriented) - allow updating, wrangling, insertion, removing data.
• Conjunction table (used for example on M:M relation) - table that's maid of foreign key only that point other table.
• Type of subquery - scalar or correlated. In other case - Single row, return one row or nothing, Multirow - return more than 1 one rows, Multicolumn - return at least 2 column, Correlated - subquery related to other query, Nested subquery - subquery with inside another query
• Join - (inner) Left, Right, Outer (full)
• SQL Schema - table, procedure, triggers, indexes, relationship, functions.
• SQL global variables and like @@TranCount And @@Version - can be used anywhere, Local variables only inside one user context and one connection.

• Local and Global Tables - Local Table variables (Declare @T Table), Local Temporary Tables (Create Table #T) ,Global Temporary Tables (Create Table ##T), Tempdb Permanent Tables (Use Tempdb Create Table T) - More on StackOverflow
• @@TranCount - Number Of Opened User TransactionMore from MS documentation
• Type of constraints - Default, Unique, Not NULL, Primary key, Foreign key.
• Type of Index - Unique, Clustered, nonclustered.
• Clause - Where, Limit, Having, Like, And, Or, Order by, Over.
• For clause to use result to XML/JSON format More on Microsoft
• Distinct More on TutorialsPoint
• Property of Transaction - ACID. Atomicity (data save or not save), Consistency (transaction can not commit without data journal), Isolation (on concurrent transactions don't interfere to another), Durability (transaction can not be lost), More on CodeProject
• DBMS normalization is a process to exclude duplicate data (1NF - all record mus be unique, 2NF - should have primary key, 3NF - must not have transitive dependencies, BCNF - only a key) More on Wikipedia
• Redundancy - combine data to one single table, Denationalization - increase performance and and redundancy.
• Type of relationship - 1:1; 1:M, M:1, M:M, M:Fixed.
• DDL – Data Definition Language (Create, Alter, Drop), DQL – Data Query Language (Select), DML – Data Manipulation Language ( Insert, Delete, Update), DCL – Data Control Language (Grant, Revoke), TCL – Transaction Control Language (Commit, Rollback), More on GeeksForGeeks
• Transaction Isolation Level - Read Uncommitted, Read Committed, Repeatable Read, Snapshot (full remove deadlock but workload is grow), Serializable, More on Microsoft, More on SqlHack
• Sql Profiler.
• Sql Trigger (insert, Update, Delete)
• Union vs Join - Join combine tables, Union combine Rows.
• Union vs Union All - Union remove duplication rows, Union all not remove.
• Self Join - create two instance of the same table.
• Transaction checkpoint causes the transaction log to be truncated.
• View type - from simple table and complex view from many tables. Row can delete from View if View based on simple table.
• Materialized View with SchemaBinding - More on MsSqlTips
• Recompiling SQL procedure
• Lock modes (Database, Table, Page, Row) More on SqlHack
• Nolock improve performance, Nolock can allow access to commited and uncommited data. Nolock can return dirty with data integrity issues.
• Analyzing various SQL server metric like PageSplit during server monitoring - Blog
• DBCC (CheckAlloc, CheckCatalog, CheckConstraints, CheckDb, CheckFileGroup, CheckIdent, CheckTable, CleanTable, CloneDatabase, DbReindex, DllName (Free), DropCleanBuffers, FreeProcCache, FreeSessionCache, FreeSystemCache, Help, IndexDefrag, InputBuffer, OpenTran, OutputBuffer, ProcCache, Show_Statistics, ShowContig, ShrinkDatabase, ShrinkFile, SqlPerf, TraceOff, TraceOn - Trace Flags, TraceOn, TraceStatus, UpdateUsage, UserOptions) - on Microsoft
• Update statistic - More from Microsoft
• Index Reorganization is not update statistic.
• Index Rebiuld vs Index Reorganization - More from SqlOverflow
• Truncating and Shrinking the Log File - More from SysToolsGroup
• Shrink Database vs Shrink File - More from MS
• TruncateOnly vs DBCC ShrinkFile - more from dba.stackexchange
• Auto_Shrink and AutoGrow More from MS
• Constraints is a limit of data - Not Null, Unique, Primary key, (Table) Check, Foreign key, Informational, Default More from IBM
• Level of Constraints - colon and table level
• Primary key is a set of attributes to unique identity every tuples
• Identity is automatic generating numeric value
• Covered index is non-clustered index that contains all columns of query
• Filtered index is an optimized nonclustered index.
• Foreign key refers to integrity by endorsing link between data
• Relation is a link
• Null mean unknown, any comparison with Null yield Null
• Cursor allow row-by-row processing result, in program cursor need to create asynchronously rather than whole population to be completed.
• Cursor can be Static, Dynamic, Forward Only, Keyset, Dynamic More from MS, More from DotNetTrick
• Associated entities is table with relation between M:M association table.
• Attributes in DBMS(Simple, Single Valued, Multi Valued, Derived, Complex, Key, Stored) - More
• User definition function - Scalar, Inline, Table value functions, Multistatement.
• Scalar functions (Configuration, Conversion, Cursor, Date and Time Data Types, Graph, JSON, Logical, Mathematical, Metadata, Security, String, System, System Statistical, Text and Image) More from MS
• SQL Operators types (Arithmetic, bitwise, comparison, compound, logical, string) More from DataQuest
• Aggregate functions (Approx_Count_Distinct, Avg, Checksum_Agg, Count, Count_Big, Grouping, Grouping_Id, Max, Min, Stdev, Stdevp, String_Agg, Sum, Var, Varp) More from MS
• Multi-statement table-valued functions (MSTVF) More from SqlServerTutorial
• DDL triggers and trigger functions (Columns_Updated, Eventdata, Trigger_Nestlevel, Update())
• Join combine records what have matching views in both tables, Join is a methods to combine rows
• MS SQL Recovery models - Simple, Full, Bulk Logged
• Dynamic SQL can be executed by Exec, sp_ExecuteSQL
• Collation can data can be sort
• Alias name can be given to any data or column
• Execution plan - Overview from MS, More from SqlHack
• Temporary object use to reduce plan complexity, temporary table materialized intermediate result.
• Order clause is invalid in View
• Trigger fires
• Permission setting on Operation system and SQL layer
• Group function return one record for group
• AutoIncrement can create unique number
• SQL Language Control flow statement (Begin...End, Return, Break, Throw, Continue, Try...Catch, Goto Label, Waitfor, If...Else, While) - More from MS
• Select clause control flow (If, IsNUll, Case, NullIF) - More from SqlHack
• CTE - Common table expression, work with virtual tables, created dirty execution and eliminate after execution. CTE can use to recursion, it we can use for Nested CTE. More from MS
• OLTP vs OLAP - Online analytical processing (OLAP) and online transaction processing (OLTP) are data processing systems that help you store and analyze business data. More
• Modeling step - Logical (design), Physical (Design, Implementation, Maintenance)
• Change data capture - which records activity on a database when tables and rows have been modified More from Wikipedia, More from MS

---

Sql context:

< THANKS ME>