Structured Query Language (SQL)

In this Chapter

• Introduction: The chapter provides an overview of SQL (Structured Query Language), emphasizing its importance in managing databases within RDBMS (Relational Database Management Systems) such as MySQL, Oracle, and PostgreSQL. SQL is user-friendly, allowing users to specify what data they want rather than how to retrieve it.

9.1 Introduction to SQL

SQL is designed to facilitate data management. Unlike file systems where specific programs must be written to access data, SQL provides a standardized way to manage and manipulate databases. The chapter focuses on how to:

• Create and define databases and tables.
• Populate databases with data.
• Query databases to retrieve and manipulate data.

9.2 SQL Language

• Case Sensitivity: SQL commands are not case-sensitive. For example, SELECT and select are identical.
• Termination of commands: Every SQL statement should end with a semicolon (;). For multiline commands, the statement continues until the semicolon is encountered.

9.3 Data Types and Constraints in MySQL

• Data Types: Defines the kinds of data that a column can hold. Common data types include:

  • CHAR(n): Fixed-length character string.
  • VARCHAR(n): Variable-length character string.
  • INT: Integer.
  • FLOAT: Floating-point number.
  • DATE: Date value formatted as 'YYYY-MM-DD'.

• Constraints: Rules that restrict the values that can be placed in a column. Common constraints include:

  • NOT NULL: Ensures the column cannot have NULL values.
  • UNIQUE: Ensures all values in a column are distinct.
  • DEFAULT: Sets a default value if no value is provided.
  • PRIMARY KEY: Uniquely identifies each record in a table.
  • FOREIGN KEY: Refers to a primary key in another table.

9.4 SQL for Data Definition Language (DDL)

DDL commands are used to define data structures:

• CREATE DATABASE: To create a new database.
• CREATE TABLE: To define tables with specified data types and constraints.
• ALTER TABLE: To modify existing tables (adding/removing columns, changing data types, etc.).
• DROP: To delete a table or database.

9.5 SQL for Data Manipulation Language (DML)

DML commands are used to manipulate data:

• INSERT INTO: To add new records to a table.
• UPDATE: To modify existing data in a table.
• DELETE: To remove records.

9.6 SQL for Data Query

The primary command for retrieving data is SELECT. It can be enhanced with:

• WHERE: To specify conditions.
• ORDER BY: To sort results.
• GROUP BY: To aggregate data.
• HAVING: To filter groups.

9.7 Functions in SQL

SQL supports both single-row functions (like ROUND, UPPER, and date-related functions) and aggregate functions (like SUM, AVG, COUNT, MAX, and MIN).

9.8 GROUP BY and JOIN Operations

• GROUP BY: Groups rows sharing a property so aggregate functions can be applied.
• JOIN: Combines rows from two or more tables based on related columns. Types include INNER JOIN, LEFT JOIN, and RIGHT JOIN.

9.9 Operations on Relations

Common operations include UNION, INTERSECT, and MINUS. They enable combining data from multiple tables while adhering to conditions regarding data types and attributes.

• UNION: Combines results from two queries.
• INTERSECT: Retrieves common records.
• MINUS: Retrieves records from the first query that are not in the second.

Key Concepts

• DDL includes commands for defining database structures.
• DML includes commands for manipulating and querying data.
• SQL is a powerful tool for managing relational databases efficiently, with a range of functions for handling various data operations. Understanding data types, constraints, and functions is fundamental in utilizing SQL effectively in database management.'

1. SQL is a widely used language for managing Relational Databases.
2. Data Types such as CHAR, VARCHAR, INT, and FLOAT define what kind of data can be stored in database columns.
3. Constraints like PRIMARY KEY, FOREIGN KEY, and UNIQUE help maintain data integrity,
4. DDL commands like CREATE, ALTER, and DROP are used to define table structure.
5. DML commands such as INSERT, UPDATE, and DELETE are used for data manipulation.
6. The SELECT statement is fundamental for querying data from database tables.
7. GROUP BY and HAVING clauses are crucial for data aggregation.
8. SQL supports both single-row functions (e.g., UPPER, LOWER, ROUND) and aggregate functions (e.g., SUM, AVG, COUNT).
9. JOIN operations allow you to combine rows from different tables based on common attributes.
10. Operations such as UNION, INTERSECT, and MINUS are essential for combining results from multiple tables.