

Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community for help and clear up your study doubts
Discover the best universities in your country according to Docsity users
Free resources
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
A comprehensive overview of sql (structured query language) and database design principles. It covers fundamental concepts such as sql data types, commands, and basic queries, as well as advanced features like views, indexing, joins, and triggers. The document also explores nested queries, embedded sql, dynamic sql, and the entity-relationship (er) model, highlighting its importance in database design. It concludes by emphasizing the significance of sql and database design principles for efficient data management and scalability.
Typology: Summaries
1 / 3
This page cannot be seen from the preview
Don't miss anything!
1. SQL Fundamentals SQL (Structured Query Language) is used for managing and manipulating relational databases. The key components of SQL are: a. SQL Data Types Numeric: INT, FLOAT, DECIMAL Character: CHAR, VARCHAR, TEXT Date/Time: DATE, TIME, TIMESTAMP Boolean: TRUE, FALSE b. SQL Commands DDL (Data Definition Language): CREATE, ALTER, DROP DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE DCL (Data Control Language): GRANT, REVOKE TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT c. Basic SQL Queries SELECT column1, column2 FROM table_name WHERE condition; INSERT INTO table_name (column1, column2) VALUES (value1, value2); UPDATE table_name SET column1 = value WHERE condition; DELETE FROM table_name WHERE condition; 2. Advanced SQL Features a. Views Views are virtual tables representing the result of a SQL query. CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition; b. Indexing Indexes speed up data retrieval operations. CREATE INDEX index_name ON table_name(column_name); c. Joins in SQL INNER JOIN: Returns records with matching values in both tables.
LEFT JOIN: Returns all records from the left table and matched records from the right table. RIGHT JOIN: Returns all records from the right table and matched records from the left table. FULL JOIN: Returns all records when there is a match in either table. SELECT A.column1, B.column2 FROM TableA A INNER JOIN TableB B ON A.id = B.id;
3. Triggers A trigger is a stored procedure that is automatically executed when a specific event occurs. a. Creating a Trigger CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW BEGIN -- SQL statements END; b. Example of a Trigger CREATE TRIGGER update_salary BEFORE UPDATE ON Employees FOR EACH ROW BEGIN IF NEW.salary < 5000 THEN SET NEW.salary = 5000; END IF; END; 4. Nested Queries and Embedded SQL a. Nested Queries A nested query is a query inside another query. SELECT name FROM Employees WHERE dept_id IN ( SELECT dept_id FROM Departments WHERE location = 'New York' ); b. Embedded SQL Embedded SQL is used within programming languages such as C, Java, or Python.