Makindo Medical Notes.com

Overview of SQL Programming

SQL (Structured Query Language) is a standardized programming language used for managing relational databases and performing various operations on the data stored in them. SQL is used to query, insert, update, and delete data, as well as to manage database schema and control access to data.

Basic SQL Commands

• SELECT:
  • Used to retrieve data from one or more tables.

  SELECT column1, column2
  FROM table_name
  WHERE condition;

  • Example:

  SELECT first_name, last_name
  FROM employees
  WHERE department = 'Sales';

• INSERT:
  • Used to insert new rows into a table.

  INSERT INTO table_name (column1, column2, column3)
  VALUES (value1, value2, value3);

  • Example:

  INSERT INTO employees (first_name, last_name, department)
  VALUES ('John', 'Doe', 'Sales');

• UPDATE:
  • Used to modify existing rows in a table.

  UPDATE table_name
  SET column1 = value1, column2 = value2
  WHERE condition;

  • Example:

  UPDATE employees
  SET department = 'Marketing'
  WHERE last_name = 'Doe';

• DELETE:
  • Used to delete rows from a table.

  DELETE FROM table_name
  WHERE condition;

  • Example:

  DELETE FROM employees
  WHERE last_name = 'Doe';

Advanced SQL Commands

• JOIN:
  • Used to combine rows from two or more tables based on a related column.

  SELECT column1, column2
  FROM table1
  JOIN table2
  ON table1.common_column = table2.common_column;

  • Example:

  SELECT employees.first_name, employees.last_name, departments.department_name
  FROM employees
  JOIN departments
  ON employees.department_id = departments.department_id;

• GROUP BY:
  • Used to group rows that have the same values in specified columns into summary rows.

  SELECT column1, COUNT(*)
  FROM table_name
  GROUP BY column1;

  • Example:

  SELECT department, COUNT(*)
  FROM employees
  GROUP BY department;

• ORDER BY:
  • Used to sort the result set of a query by one or more columns.

  SELECT column1, column2
  FROM table_name
  ORDER BY column1 [ASC
  |DESC];

  • Example:

  SELECT first_name, last_name
  FROM employees
  ORDER BY last_name ASC;

• HAVING:
  • Used to filter rows after the GROUP BY clause is applied.

  SELECT column1, COUNT(*)
  FROM table_name
  GROUP BY column1
  HAVING COUNT(*) > value;

  • Example:

  SELECT department, COUNT(*)
  FROM employees
  GROUP BY department
  HAVING COUNT(*) > 5;

SQL Functions

• Aggregate Functions:
  • Perform a calculation on a set of values and return a single value.

  SELECT AVG(column_name)
  FROM table_name;
  SELECT COUNT(column_name)
  FROM table_name;
  SELECT SUM(column_name)
  FROM table_name;

  • Examples:

  SELECT AVG(salary)
  FROM employees;
  SELECT COUNT(*)
  FROM employees
  WHERE department = 'Sales';
  SELECT SUM(salary)
  FROM employees
  WHERE department = 'Sales';

• String Functions:
  • Manipulate string values.

  SELECT LENGTH(column_name)
  FROM table_name;
  SELECT UPPER(column_name)
  FROM table_name;
  SELECT LOWER(column_name)
  FROM table_name;

  • Examples:

  SELECT LENGTH(first_name)
  FROM employees;
  SELECT UPPER(first_name)
  FROM employees;
  SELECT LOWER(last_name)
  FROM employees;

• Date Functions:
  • Perform operations on date values.

  SELECT CURRENT_DATE;
  SELECT EXTRACT(YEAR FROM date_column)
  FROM table_name;
  SELECT DATEADD(year, 1, date_column)
  FROM table_name;

  • Examples:

  SELECT CURRENT_DATE;
  SELECT EXTRACT(YEAR FROM hire_date)
  FROM employees;
  SELECT DATEADD(year, 1, hire_date)
  FROM employees;

Subqueries

• Definition:
  • A subquery is a query nested inside another query.

  SELECT column1
  FROM table_name
  WHERE column2 = (SELECT column2
   FROM table_name
   WHERE condition);

  • Example:

  SELECT first_name, last_name
  FROM employees
  WHERE department_id = (SELECT department_id
   FROM departments
   WHERE department_name = 'Sales');

Views

• Definition:
  • A view is a virtual table based on the result set of an SQL query.

  CREATE VIEW view_name AS
  SELECT column1, column2
  FROM table_name
  WHERE condition;

  • Example:

  CREATE VIEW SalesEmployees AS
  SELECT first_name, last_name
  FROM employees
  WHERE department = 'Sales';

Indexes

• Definition:
  • An index is a database object that improves the speed of data retrieval.

  CREATE INDEX index_name
  ON table_name (column1, column2);

  • Example:

  CREATE INDEX idx_last_name
  ON employees (last_name);

Transactions

• Definition:
  • A transaction is a sequence of one or more SQL operations treated as a single unit of work.
  • Transactions ensure data integrity and consistency.

  START TRANSACTION;
  UPDATE employees
  SET salary = salary * 1.10
  WHERE department = 'Sales';
  DELETE FROM employees
  WHERE last_name = 'Doe';
  COMMIT;

  • Example:

  START TRANSACTION;
  UPDATE employees
  SET salary = salary * 1.10
  WHERE department = 'Sales';
  COMMIT;

Summary

SQL is a powerful language for managing and manipulating relational databases. It provides a wide range of commands and functions for querying, inserting, updating, and deleting data. Advanced features such as joins, subqueries, views, indexes, and transactions enable complex data operations and ensure data integrity and performance. Mastering SQL is essential for database management and development.