Data Management & SQL Concepts - Unit 4

Course Information

• Course: M.Sc. IT Data Science and Analytics (5 Years Integrated)
• Subject: Data Management and Relational Data Modelling
• Unit: 4
• Topic: SQL Concepts and Queries

Objectives

• SQL Introduction, Syntax, and Data Types - DDL, DML, DCL, TCL, DQL Commands
• SQL Clauses - Having, Where, Order By, Group By
• SQL Joins and Sub Queries
• SQL Operators, Expressions, and Comments
• SQL Views
• SQL Stored Procedures, Cursors
• SQL Functions

Contents

• SQL Introduction, Syntax, and Data Types (DDL, DML, DCL, TCL, DQL Commands)
• SQL Clauses (Having, Where, Order By, Group By)
• SQL Joins and Sub Queries
• SQL Operators, Expressions, and Comments (Transactions, Indexes, and Views)
• SQL Constraints and Functions
• SQL Stored Procedures, Cursors, and Triggers

SQL Statements

• SQL statements are used for database operations
• SQL statements are keywords that are easy to understand.
• An example SQL statement is used to retrieve data from a "Customers" table
• Tables in SQL databases usually contain records (rows) of data identified by names.

SQL Data Types

• SQL supports various data types (int, char, varchar, number, date, Boolean).
• Data types like char and varchar are commonly used in SQL queries for various data.

SQL Commands

• SELECT: Extracting data from a database
• UPDATE: Updating data in a database
• DELETE: Deleting data from a database
• INSERT INTO: Inserting new data into a database
• CREATE DATABASE: Creating a new database
• ALTER DATABASE: Modifying a database
• CREATE TABLE: Creating a new table
• ALTER TABLE: Modifying a table
• DROP TABLE: Deleting a table
• CREATE INDEX: Creating an index (search key)
• DROP INDEX: Deleting an index

String Data Types

• CHAR(size): Fixed-length string
• VARCHAR(size): Variable-length string
• BINARY(size): Fixed-length binary string
• VARBINARY(size): Variable-length binary string
• TINYBLOB/TINYTEXT: Small binary/text data
• TEXT(size): Larger text data
• BLOB(size): Large binary data
• MEDIUMTEXT/MEDIUMBLOB: Medium-sized text/binary data
• LONGTEXT/LONGBLOB: Very large text/binary data

Numeric Data Types

• BIT(size): Bit value
• TINYINT(size): Small integer
• BOOL/BOOLEAN: Boolean value
• SMALLINT(size): Small integer
• MEDIUMINT(size): Medium integer
• INT/INTEGER(size): Integer
• BIGINT(size): Large integer
• FLOAT(size, d),FLOAT(p): Floating-point numbers
• DOUBLE(size, d),DOUBLE PRECISION (size, d): Double-precision numbers
• DECIMAL(size, d), DEC(size, d): Decimals

SQL Clauses

• WHERE: Filters records based on specific conditions
• HAVING: Filters groups of records after a GROUP BY clause
• ORDER BY:Sorts records based on specified criteria

SQL Joins

• CROSS JOIN/Cartesian Product: Returns all possible combinations of rows from two tables.
• INNER JOIN: Returns rows where the join condition is met in both tables
• LEFT JOIN: Returns all rows from the left table (and matching rows from the right table, if any). If there's no match, the right table columns have NULL values in the result
• RIGHT JOIN: Returns all rows from the right table (and matching rows from the left table, if any). If there's no match, the left table columns have NULL values in the result
• FULL OUTER JOIN: Returns all rows from both tables, and matching rows from both, if any. If there's no match in one or the other table, one or the other table's columns will have NULL values

Sub Queries

• Subqueries are nested queries
• Rules for subqueries include limits on columns used, no ORDER BY in subqueries but ORDER BY in the main query.
• Subqueries can be used with comparison operators like IN or multiple values
• Subqueries can't be immediately contained within set functions or use the BETWEEN operator.

Stored Procedures

• Stored procedures are SQL code that is stored and can be reused multiple times, thus reducing redundancy in code.
• Parameters can be passed to stored procedures, so they can be customized with dynamic data

Cursors

• Cursors are pointers to data that can be used to process one row at a time.
• Cursors can be used with SELECT statements to retrieve data one record at a time and process efficiently.

SQL Functions

• Aggregate functions (e.g., AVG(), COUNT(), MIN(), MAX(), FIRST(), LAST(), SUM()) perform operations on groups of data and return a single value.
• Scalar functions (e.g., UCASE(), LCASE(), MID(), LEN(), ROUND(), NOW()) operate on individual values and return a single value.

Constraints and Functions

• Aggregate functions (AVG, COUNT, MIN, MAX, FIRST, LAST, SUM) are used in SQL to operate on groups of values and return single values for data summarization