COMPLETE SQL Guide Topic Wise Functions, Study notes of Computer Science

Download COMPLETE SQL Guide Topic Wise Functions and more Study notes Computer Science in PDF only on Docsity!

COMPLETE

SQL

Guide

Topic Wise Functions

Basic SQL Syntax ▪ SQL keywords ▪ Data types ▪ Operators ▪ SQL statements ▪ SELECT ▪ INSERT ▪ UPDATE ▪ DELETE

3 Data Manipulation Language (DML) ▪ SELECT statement (SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING, JOINs) ▪ INSERT statement ▪ UPDATE statement ▪ DELETE statement

4 Aggregate Functions ▪ SUM, AVG, COUNT, MIN, MAX ▪ GROUP BY clause ▪ HAVING clause

Joins ▪ INNER JOIN ▪ LEFT JOIN ▪ RIGHT JOIN ▪ FULL OUTER JOIN ▪ SELF JOIN ▪ CROSS JOIN

Subqueries ▪ Types of subqueries (scalar, column, row, table) ▪ Nested subqueries ▪ Correlated subqueries

9 Views ▪ Creating views ▪ Modifying views ▪ Dropping views

Indexes ▪ Creating indexes ▪ CREATE INDEX ▪ Using indexes for query optimization

Data Integrity and Security ▪ Data integrity constraint ▪ Referential integrity, entity integrity ▪ GRANT and REVOKE statements (granting and revoking permissions) ▪ Database security best practices

Stored Procedures and Functions ▪ Creating stored procedures ▪ CREATE PROCEDURE ▪ Executing stored procedures ▪ EXEC ▪ Creating functions ▪ Using functions in queries

Advanced SQL Concepts ▪ Recursive queries ▪ Pivot and unpivot operations ▪ Window functions: ▪ Row_number, rank, dense_rank, lead & lag ▪ CTEs (Common Table Expressions) ▪ Dynamic SQL

Schema Management ▪ Creating schemas ▪ CREATE SCHEMA ▪ Altering schemas ▪ ALTER SCHEMA ▪ Dropping schemas ▪ DROP SCHEMA

Triggers ▪ Creating triggers ▪ CREATE TRIGGER ▪ Using triggers

Error Handling ▪ TRY-CATCH blocks ▪ Raising custom exceptions