SQL and databases

This repository contains some basic sql files pushed from local repository. Basic concepts of databases and sql usecases.

Topics covered

Basics about database

Data, Database , Server, Host, DBMS, RDBMS and N-RDBMS

Schema and tables

ER diagram and its components

Starting with SQL

Types of SQL commands

1. Data Defination Language (C-DAT)

• Data types.
• Primary key and foreign key.
• Constrains - column and table constrains.
• Default and Check constrains.
• CREATE, ALTER, DROP, TRUNCATE. (C-DAT)
• Differences between DELETE,TRUNCATE, DROP.

2. Data Manipulation Language (IUDM)

• INSERT
• UPDATE
• DELETE
• MERGE

3. Data Query language and common SQL Commands

3.1 Data Query language

• SELECT : SELECT * FROM table_name;

3.2 Order of execution of SQL commands

Find a job where given highest salary and opportunities to limitless grow. (F-J-W-G-H-S-O-L)

FROM > JOIN > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT

3.3 JOINS: Types of joins

3.4 Self JOIN and UNION

3.5 CROSS JOIN

3.6 UNION

3.7 WHERE (WHERE -AND, OR, NOT, LIKE,ILIKE)

3.8 GROUP BY

3.9 HAVING

3.10 ORDER BY

3.11 OFFSET m

3.12 LIMIT

3.13 Aggregation functions

3.14 Aliases

4. Advanced Concepts and Functions

4.1 Sub query

4.2 WITH CTE as (Select ----)

4.3 Window functions

• Aggregate window functions vs standard aggregate function.
• Ranking window functions-ROW_NUMBER(), RANK(), DENSE_RANK()
• More ranking functions- NTILE(), CUME_DIST(), PERCENT_RANK()
• Value functions - LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()

4.4 String functions and operations

4.5 Timestamp and Extract functions

4.6 Mathematical functions.

4.7 Conditional statements

• CASE WHEN THEN ELSE END
• COALESCE
• CAST
• NULLIF

4.8 VIEWS

Procedural SQL (pl-sql)

1. Introduction

2. Variables

2.1 Variable declaration

2.2 Rules for variable naming

3 Conditional statements

• IF-ELSE

4 Control statements -LOOP

4.1 Basic loop and Exit loop

4.2 WHILE LOOP

4.3 FOR LOOP

WHILE LOOP vs FOR LOOP

Continue

5. Procedures

How to create a procedure

Types of parameter

Procedures in PostgreSQL

6. Functions

Difference between functions and procedures

7. Cursors

7.1 Types of Cursors

7.2 Process to create explicit cursors

Cursors with parameters to reduce if-else blocks

8. Ecception handling

Handling built in exceptions

Handling user defined exceptions

9. Triggers

Create trigger action function and then trigger

Drop a trigger

Alter a trigger

Common Interview topics

Differences

• WHERE vs HAVING
• IN vs EXISTS
• JOIN vs SubQuery
• JOIN vs UNION
• UNION vs UNION ALL
• GROUP BY vs ORDER by