Database

1. INNER JOIN and NATURAL JOIN are not the same**. NATURAL JOIN is an implicit INNER JOIN based on matching column names. INNER JOIN is explicit and safer, so it is preferred in practice. 👉 Always use INNER JOIN INNER JOIN → You tell SQL how to join NATURAL JOIN → SQL guesses how to join 2. Key SQL terminology and core concepts Aliases Aliases rename columns or tables in a query to make results clearer or queries shorter. Column alias: SELECT COUNT(*) AS total_students (uses AS). Table alias: FROM students s [no AS (for Oracle) for the table in many dialects] (best avoid AS). Relationship types One‑to‑one: one row relates to exactly one row in another table [e.g., one teacher (teaches)→ one subject]. One‑to‑many: one row relates to many rows [e.g., one teacher (teaches) → many students]. Many‑to‑many: many rows on each side relate to many on the other [e.g., many students (enrolled in) → many courses]. UNION vs UNION ALL UNION: Combines (stacks) results from multiple SELECT statements. Requires same number of columns, compatible data types, and same column order. Removes duplicate rows (implicit DISTINCT). May be slower due to duplicate removal (sorting/hash) UNION ALL: Same structural requirements as UNION. Keeps duplicates; generally faster [than UNION (no duplicate check)]. Extra Note: INTERSECT: Common rows MINUS / EXCEPT: Rows in first query not in second 1 2 3 4 5 6 7 8 9 -- Using UNION (duplicates removed) SELECT city FROM customers UNION SELECT city FROM suppliers; -- Using UNION ALL (duplicates kept) SELECT city FROM customers UNION ALL SELECT city FROM suppliers; Main clauses in a SQL query Common main clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Typical logical order you write: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …. Only SELECT and FROM are mandatory Actual order of execution Logical execution order: FROM – choose tables / joins. [where data is coming from] WHERE – row‑level filtering. [what is returned] GROUP BY – create groups. [how data is grouped] HAVING – filter groups (often on aggregates). [which characteristics] SELECT – project columns / expressions. [what data is returned] ORDER BY – sort result. Constraints A constraint controls what data is allowed in a table. Common constraints: primary key, foreign key, UNIQUE, NOT NULL. Aggregate functions (Used with GROUP BY, Aggregate functions are allowed only in HAVING.) SUM() – returns total. AVG() – average. MIN() – smallest value or oldest date. MAX() – largest value or newest date. COUNT() – number of values (or rows when using COUNT(*)). Categories of SQL commands DDL (Data Definition Language): defines/changes table structure and objects such as databases, tables, views, and indexes (CREATE, ALTER, DROP. TRUNCATE). DDL commands usually auto-commit. Exmple: CREATE DATABASE, CREATE TABLE. DML (Data Manipulation Language): changes data (INSERT, UPDATE, DELETE). DML commands are transaction-controlled (can be committed or rolled back). Example: INSERT INTO. DCL (Data Control Language): Controls access and permissions on database objects (GRANT, REVOKE). Typically handled by DBAs. TCL (Transaction Control Language): manages transactions (BEGIN / START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT). DQL (Data Query Language): querying data (primarily SELECT, along with clauses like WHERE, ORDER BY, GROUP BY, HAVING, and LIMIT). 3. Creating, seeding, and modifying tables Creating a table Think through: table name, columns, data types, NULL vs NOT NULL, constraints (PK / FK), identity/auto‑increment. Example pattern (SQL Server style): 1 2 3 4 5 6 CREATE TABLE employee_info ( id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, first_name NVARCHAR(75), last_name NVARCHAR(75), email NVARCHAR(125) ); Use comments (--) to describe purpose of each block; this helps in written interviews. Inserting (seeding) data Pattern: 1 2 INSERT INTO employee_info (first_name, last_name, email) VALUES ('John', 'Arthur', 'john.arthur@example.com'); When the id column is identity/auto‑increment, you omit it from the column list. Verify with: SELECT * FROM employee_info;. ALTER – changing table structure Purpose: alter structure/relationships, including adding, dropping, or changing columns. Drop a column: 1 2 ALTER TABLE employee_info DROP COLUMN email; Add columns (can add multiple at once): 1 2 ALTER TABLE employee_info ADD city NVARCHAR(100), department NVARCHAR(100); Change data type of a column: 1 2 ALTER TABLE some_table ALTER COLUMN country NVARCHAR(100); 4. TRUNCATE vs DROP (and constraints) TRUNCATE TRUNCATE TABLE table_name removes all data but keeps the table structure (columns and their constraints remain). Often faster than DELETE without a WHERE clause. Cannot truncate a table that is referenced by a foreign key constraint; you must drop or disable the constraint first. Handling foreign key constraints If TRUNCATE fails due to a foreign key, you: ALTER TABLE child_table DROP CONSTRAINT fk_name; Then TRUNCATE TABLE parent_table;. You typically find fk_name in the database’s key/constraint metadata. DROP DROP TABLE table_name removes the table structure and all data. ALTER TABLE table_name DROP COLUMN column_name removes just one column. Think of TRUNCATE as clearing rows, and DROP as demolishing the table (or column). 5. Logical/comparison operators and wildcards Core keyword operators BETWEEN – values in a range (inclusive). IN – values in a specific list. LIKE – pattern matching (commonly with wildcards). IS NULL / IS NOT NULL – test for nulls. EXISTS – checks if a subquery returns any rows. Wildcards (used with LIKE) % – any number of characters (including zero). Example: WHERE name LIKE '%AMES' returns anything ending in “AMES”. _ – exactly one character per underscore. Example: WHERE code LIKE '_2_6' matches patterns like A2B6. [] – one of the characters inside the brackets. Example: WHERE name LIKE 'Wi[lk]%' matches “Will…”, “Wik…”, etc. Practice patterns with LIKE Return emails with any characters before 6 and ending in @gmail.com: 1 2 3 SELECT first_name, email FROM some_table WHERE email LIKE '%6@gmail.com'; Exclude first names with exactly one character followed by “ar” then anything: 1 WHERE first_name NOT LIKE '_ar%'; First name starting with B followed by I/R/O and any characters, and city with no “UL” anywhere: 1 2 WHERE first_name LIKE 'B[IRO]%' AND city NOT LIKE '%UL%'; 6. IN and BETWEEN in practice IN – simplifies multiple equality checks: 1 2 WHERE shirt_size IN ('M', 'L', 'XL', 'XXL') ORDER BY shirt_size ASC; BETWEEN – range on numbers, dates, etc. (inclusive of endpoints): 1 WHERE dress_size BETWEEN 2 AND 16; Combine with functions like DATENAME to extract parts of dates: Birth month: DATENAME(MONTH, birthday) AS birthmonth. Birth year: DATENAME(YEAR, birthday) AS birthyear. Example: people with dress size 2–16 and pant size containing L, ordered by birth month: 1 2 3 4 5 6 7 SELECT first_name, pant_size, DATENAME(MONTH, birthday) AS birthmonth FROM friends WHERE dress_size BETWEEN 2 AND 16 AND pant_size LIKE '%L%' ORDER BY birthmonth; 7. COALESCE and its relation to CASE COALESCE COALESCE(expr1, expr2, …) returns the first non‑null expression; if all are null, returns null (or the last specified default). Classic use: replace nulls with a default value in a result set. 1 2 3 4 SELECT first_name, favorite_color, COALESCE(shirt_size, '4XL') AS shirt_size_filled FROM company_shirts; Existing non‑null values are preserved; only nulls get the default replacement. Equivalent CASE pattern The same logic can be expressed with a CASE expression: 1 2 3 4 5 6 7 SELECT first_name, favorite_color, CASE WHEN shirt_size IS NULL THEN '4XL' ELSE shirt_size END AS shirt_size_filled FROM company_shirts; CASE is more human‑readable and flexible but more verbose; COALESCE is succinct but slightly less explicit. 8. Quick revision checklist for interview Use this as a rapid checklist when revising: ...

1️⃣ “Tell us about a time you disagreed with a supervisor.” Situation: On a previous project, my supervisor wanted to release a feature quickly without full regression testing. Task: My responsibility was to ensure the release would not introduce production defects. Action: I respectfully shared my concerns, backed them with data from past incidents, and proposed a compromise—running a focused regression on high-risk modules instead of full testing. Result: We delayed the release by one day, avoided a potential production issue, and the supervisor later appreciated the risk-based approach. 👉 Key signal: Professional disagreement + data + teamwork 2️⃣ “Describe a time you had to solve a problem you didn’t immediately know the answer to.” Situation: During integration testing, an API intermittently failed with inconsistent error messages. Task: I needed to identify the root cause and ensure system stability before deployment. Action: I reviewed logs, reproduced the issue in a lower environment, collaborated with developers, and researched similar issues. I isolated the problem to a timeout configuration mismatch. Result: After fixing the configuration, the failures stopped, and the system passed integration testing. 👉 Key signal: Problem-solving + learning mindset 3️⃣ “How do you handle multi-tasking and competing priorities?” Situation: In one role, I was supporting testing, handling production issues, and assisting developers simultaneously. Task: I had to make sure critical issues were addressed without missing deadlines. Action: I prioritized tasks based on business impact, used a task-tracking system, communicated timelines clearly, and focused on high-risk items first. Result: All critical issues were resolved on time, and stakeholders were satisfied with the transparency and delivery. 👉 Key signal: Organization + calm under pressure 4️⃣ “Why do you want to work for the Commonwealth / PSP?” (This is motivation + values, not strict STAR—but structured works) ...

1. How are you? “I’m great, thank you. How are you?” 2. Why do you want to work here? “I’ve outgrown my current role and I’m looking for a new challenge. Based on what I’ve seen about this team’s goals, I know I can help you solve [specific problem this team is facing].” 3. Where do you see yourself in 5 years? “I see myself here, becoming an expert in this area and a valuable part of this team. This role aligns with where I want to go because [connect directly to the responsibilities of this role].” ...

Got it — below is your document cleaned up, with all links removed, consistent formatting, and properly numbered and titled for future revision as a Database Administrator prep guide. Database Administrator – Interview Preparation & Revision Guide Section 1 1. Tell Me About Yourself (Easy Version) Here’s a version that matches your background and sounds natural: “I have been working in IT for several years, mainly as an IT Analyst. I’ve supported applications and users, and over time I started working more with databases like SQL Server and Oracle. I have created tables, written queries, and helped improve performance and solve issues when applications were slow. I enjoy this kind of work and want to move into a dedicated database administrator role, especially in a stable public-service environment like the Department of Corrections.” ...

SQL Joins Complete Guide Inner, Left, Right, Full Outer, Cross, Self & Natural Joins — clearly explained with examples Master SQL joins using a single, consistent scenario. This guide removes repetition, keeps every important detail, and presents joins in a compact, practical, and reusable format. What is a JOIN? A JOIN combines rows from two or more tables based on a related column. Indexes on join columns significantly affect performance. Common join families ...

Security Best Practices Prefer Windows Authentication over SQL Authentication. Avoid using sa for daily operations. Regularly review and remove unused or orphaned users. Enforce strong passwords and role-based access. Enable auditing, encryption, and backups. Document user and login changes for compliance. Encryption: SQL Server supports multiple types of encryption: Transparent Data Encryption (TDE) for encrypting the whole database at rest, column-level encryption for sensitive columns, Always Encrypted(SQL Server 2016+) for client-side protection so the database never sees plaintext, protecting sensitive data from DBAs, with keys stored locally or in Azure Key Vault, and backup encryption. Proper key management and regular backups of encryption keys are critical for maintaining access and security. ...