Download D677 SQL & Database Fundamentals – Guaranteed Pass Study Guide 2026 update and more Summaries Database Programming in PDF only on Docsity!
D677 SQL & Database Fundamentals Study Guide 2026 update – Guaranteed Pass
1️⃣ Database Fundamentals (Core Concepts) A database^ What Is a Database? is an organized collection of data stored electronically.
- DBMS (Database Management System) examples: o MySQL o o Microsoft SQL ServerOracle Database o PostgreSQL -^ Relational Database Concepts Table = Rows + Columns
- • Primary Key (PK)Foreign Key (FK) = Links tables= Unique identifier
- • SchemaIndex = Improves search speed = Database structure
- • ViewConstraint = Virtual table = Rule enforced on data
2️⃣ Entity Relationship (ER) Modeling
-^ Cardinality One-to-One (1:1)
- • OneMany-to-to-Many (1:M)-Many (M:N) → Requires junction table (Most common in exams) Normalization (HIGHLY TESTED) Normal Form Rule 1NF No repeating groups
Normal Form Rule 2NF No partial dependency 3NF No transitive dependency Most exam questions focus on identifying violations of 2NF and 3NF. 3️⃣ SQL Fundamentals (Core Commands) SELECT^ SELECT Statement (MOST TESTED) column1, column FROM WHERE table_name condition ORDER BY column; Filtering Operators
- • =, >, <, >=, <=BETWEEN
- • INLIKE
Example:^ •^ IS NULL SELECT WHERE * grade FROM >= students 80 ;
4️⃣ Aggregate Functions
Join Returns RIGHT JOIN All right + matches FULL OUTER JOIN All records both sides Example: SELECT s.name, c.course_name FROM INNER students sJOIN courses c ON s.course_id = c.course_id; Exam Tip: If question says “all records from left table,” answer is LEFT JOIN. 7️⃣ SELECT Subqueries name FROM WHERE employees salary > ( SELECT FROM employees AVG(salary) ); Types:
- • SingleMulti--rowrow
- Correlated subquery 8️⃣ Data Manipulation Language (DML)
Command Purpose INSERT Add data UPDATE Modify data DELETE Remove data Example: UPDATE students SET WHERE grade id == (^905) ;
9️⃣ Data Definition Language (DDL) Command Purpose CREATE Create table ALTER Modify table DROP Delete table Example: CREATE TABLE students ( id name INT VARCHARPRIMARY (^) (KEY 50 ),,
);^ grade^ INT
-^ Constraints (Frequently Tested) PRIMARY KEY
✔ ✔ JoinsGROUP BY + HAVING ✔ ✔ NormalizationKeys (PK vs FK) ✔ ✔ SubqueriesConstraints ✔ ✔ ACID propertiesSQL syntax errors
1.^ Practice Exam Which join returns all rows from left table? - Style Questions
- What normal form removes transitive dependency?What does HAVING filter?
- Which constraint ensures uniqueness?What does ROLLBACK do? 7 - Day Crash Study Plan Day Focus 1 Database basics + ERD 2 Normalization 3 SELECT + WHERE 4 JOINS 5 GROUP BY + Aggregates
Day Focus 6 Subqueries + Constraints 7 Full Practice Tests
-^ Final Exam Strategy Read SQL queries carefully
- • Identify what is being filtered (rows vs groups)Look for keywords: ALL, MATCHING, AVERAGE
- • Eliminate obviously wrong JOIN typesCheck for syntax order mistakes
