Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

SQL Fundamentals and Database Design: A Comprehensive Guide, Summaries of Database Management Systems (DBMS)

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

2024/2025

Available from 03/15/2025

sanika-sree-ak
sanika-sree-ak 🇮🇳

4 documents

1 / 3

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
UNIT II: BASICS OF SQL AND DATABASE DESIGN
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.
pf3

Partial preview of the text

Download SQL Fundamentals and Database Design: A Comprehensive Guide and more Summaries Database Management Systems (DBMS) in PDF only on Docsity!

UNIT II: BASICS OF SQL AND DATABASE DESIGN

1. SQL Fundamentals SQL (Structured Query Language) is used for managing and manipulating relational databases. The key components of SQL are: a. SQL Data TypesNumeric: INT, FLOAT, DECIMAL  Character: CHAR, VARCHAR, TEXT  Date/Time: DATE, TIME, TIMESTAMP  Boolean: TRUE, FALSE b. SQL CommandsDDL (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 SQLINNER 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.