SQL Notes

Blog post description.

5/20/202412 min read

What is Sql and its uses?

SQL, which stands for Structured Query Language, is a standard programming language specifically designed for managing and manipulating databases. It is widely used in relational database management systems (RDBMS), where data is stored in tables that relate to each other. Here are some of the key uses and applications of SQL:

  1. Data Querying: SQL is primarily used for querying data from a database. This means you can retrieve specific data by writing SQL queries, which are instructions to the database to return data that matches given criteria.

  2. Database Management: SQL allows users to create, modify, and manage databases. This includes creating new databases, modifying the structure of existing databases, and setting up database environments.

  3. Data Manipulation: It includes adding, updating, or deleting records in a database. SQL commands like INSERT, UPDATE, and DELETE are used for these purposes.

  4. Data Definition: SQL is used to define data structures in a database. This involves creating tables, defining fields in the tables, and setting data types for each field. It also includes creating indexes to improve data retrieval speeds.

  5. Data Access Control: SQL can be used to control access to data in a database. This includes granting and revoking permissions and roles, ensuring that only authorized users can access or manipulate data.

  6. Transactional Control: SQL supports transactions, which are sequences of operations performed as a single unit. This ensures data integrity, especially in multi-user environments, by allowing operations to be rolled back if something goes wrong.

  7. Data Integration: SQL allows for the integration of data from multiple sources. This can be useful in data warehousing and online analytical processing (OLAP), where data is collected from various sources for in-depth analysis.

  8. Reporting: SQL queries can be used to generate reports from data, providing insights and supporting decision-making in an organization.

Types of SQL commands

SQL commands can be broadly categorized into several types, each serving different purposes in the management and manipulation of databases. Here are the primary types of SQL commands:

  • Data Definition Language (DDL): These commands are used for defining and modifying the database structure or schema. Some of the key DDL commands include:

    • CREATE: Used to create new databases, tables, views, indexes, etc.

    • ALTER: Modifies the structure of an existing database object, like adding or dropping columns in a table.

    • DROP: Deletes objects from the database, such as tables, views, or databases.

    • TRUNCATE: Removes all records from a table, including all spaces allocated for the records are removed.

    • COMMENT: Adds comments to the data dictionary.

    • RENAME: Renames an existing database object.

  • Data Manipulation Language (DML): These commands deal with the manipulation of data present in the database. Common DML commands include:

    • SELECT: Retrieves data from a database table.

    • INSERT: Inserts new data into a table.

    • UPDATE: Modifies existing data in a table.

    • DELETE: Removes data from a table.

  • Data Control Language (DCL): DCL commands are used to control access to data in the database. These include:

    • GRANT: Gives users access privileges to the database.

    • REVOKE: Withdraws access privileges given by the GRANT command.

  • Transaction Control Language (TCL): These commands are used to manage transactions in the database. They are crucial for maintaining the integrity of data within SQL databases. Key TCL commands are:

    • COMMIT: Saves all changes made in the transaction.

    • ROLLBACK: Restores the database to the last committed state.

    • SAVEPOINT: Sets a savepoint within a transaction, which you can roll back to without affecting the entire transaction.

    • SET TRANSACTION: Specifies characteristics for the transaction.

  • Data Query Language (DQL): Technically, DQL is not recognized as a separate category by all database systems, but it's often used to refer specifically to the SELECT command, which is used to query data from the database.

1. Numeric Types

  • INT: Used to store integer values (whole numbers). The range is -2,147,483,648 to 2,147,483,647 for signed, and 0 to 4,294,967,295 for unsigned.

    • Example: age INT

  • FLOAT: A floating-point number with approximate precision. Suitable for fractional numbers.

    • Example: price FLOAT(7, 2) (total of 7 digits, 2 after the decimal point)

  • DECIMAL: A fixed-point number with exact precision. Used for storing exact numeric data values.

    • Example: total DECIMAL(10, 2) (total of 10 digits, 2 after the decimal point)

2. String Types

  • VARCHAR: A variable-length string between 1 and 65,535 characters. You must define a length when creating a VARCHAR field.

    • Example: name VARCHAR(50) (string with a maximum length of 50 characters)

  • CHAR: A fixed-length string between 1 and 255 characters. The length is set when the table is created, and any unused characters are padded with spaces.

    • Example: initials CHAR(5) (string with a fixed length of 5 characters)

  • TEXT: A variable-length string with a maximum length of 65,535 characters. TEXT fields are used when you need to store large amounts of text.

    • Example: description TEXT

3. Date and Time Types

  • DATE: Stores a date in the format 'YYYY-MM-DD'.

    • Example: birth_date DATE

  • DATETIME: Stores a date and time value in the format 'YYYY-MM-DD HH:MM:SS'.

    • Example: created_at DATETIME

  • TIMESTAMP: Stores a timestamp value, which is a date and time value represented as the number of seconds since '1970-01-01 00:00:00' UTC. Automatically updated to the current timestamp when the row is created or updated.

    • Example: last_modified TIMESTAMP

4. Special Types

  • ENUM: An enumeration, which is a string object that can have only one value, chosen from a list of possible values. Can have up to 65,535 distinct elements.

    • Example: gender ENUM('Male', 'Female', 'Other')

  • SET: A string object that can have zero or more values, each of which must be chosen from a list of possible values. Can have up to 64 distinct members.

    • Example: skills SET('HTML', 'CSS', 'JavaScript')

Mysql Commands

1. CREATE DATABASE: Creating a New Database

Used to create a new database.

  • Syntax: CREATE DATABASE database_name;

  • Example: CREATE DATABASE students;

  • After execution, a new database named "students" will be created.

2. USE: Selecting a Database

Used to select a specific database to work with.

  • Syntax: USE database_name;

  • Example: USE students;

  • After execution, all subsequent commands will be executed in the "students" database.

3. SHOW DATABASES: Listing All Databases

Used to list all databases on the MySQL server.

  • Syntax: SHOW DATABASES;

  • Displays a list of all databases.

4. CREATE TABLE: Creating a New Table

Used to create a new table in the database.

  • Syntax: CREATE TABLE table_name (column1 datatype, column2 datatype, ...);

  • Example: CREATE TABLE employees (

    id INT PRIMARY KEY,

    name VARCHAR(100),

    department VARCHAR(50),

    salary INT,

    address VARCHAR(200),

    age INT

);

5. DESCRIBE: Viewing Table Structure

Used to view the structure of a table.

  • Syntax: DESCRIBE table_name;

  • Example: DESCRIBE student;

  • Displays the columns, types, and other properties of the "student" table.

6. INSERT INTO: Adding Data to a Table

Used to insert data into a table.

  • Syntax: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

  • Example: INSERT INTO student (id, name, age) VALUES (1, 'John Doe', 21);

  • After execution, a new row will be added to the "student" table.

INSERT INTO employees (id, name, department, salary, address, age) VALUES

(1, 'John Doe', 'HR', 55000, '123 Elm St', 30),

(2, 'Jane Smith', 'Marketing', 60000, '456 Oak St', 35),

(3, 'Michael Brown', 'Sales', 45000, '789 Pine St', 28),

(4, 'Emily Davis', 'HR', 52000, '101 Maple St', 42),

(5, 'David Wilson', 'Marketing', 58000, '202 Birch St', 29),

(6, 'Sarah Johnson', 'Sales', 47000, '303 Cedar St', 31),

(7, 'Chris Lee', 'IT', 63000, '404 Walnut St', 25),

(8, 'Jessica Taylor', 'HR', 54000, NULL, 26),

(9, 'Daniel Harris', 'Marketing', 61000, '606 Chestnut St', 40),

(10, 'Laura Clark', 'Sales', 46000, '707 Spruce St', 33);

7. SELECT: Retrieving Data from a Table

Used to retrieve data from a table.

  • Syntax: SELECT column1, column2, ... FROM table_name WHERE condition;

  • Example: SELECT * FROM employees;

  • Displays all rows from the "student" table. The asterisk (*) can be used to select all columns.

8. UPDATE: Updating Existing Data

Used to update data in a table.

  • Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

  • Example: UPDATE employees SET age = 22 WHERE id = 1;

  • After execution, the age of the student with id 1 will be updated to 22.

9. DELETE: Deleting Data

Used to delete data from a table.

  • Syntax: DELETE FROM table_name WHERE condition;

  • Example: DELETE FROM employees WHERE id = 1;

  • After execution, the student with id 1 will be deleted from the "student" table.

10. DROP TABLE/DROP DATABASE: Deleting Tables and Databases

  • DROP TABLE: Used to delete a table and all of its data.

    • Syntax: DROP TABLE table_name;

    • Example: DROP TABLE student;

    • After execution, the "student" table will be deleted.

  • DROP DATABASE: Used to delete a database and all of its tables and data.

    • Syntax: DROP DATABASE database_name;

    • Example: DROP DATABASE students;

    • After execution, the "students" database will be deleted.

11. WHERE Clause

Used to filter records based on specified conditions.

  • Syntax: SELECT column1, column2, ... FROM table_name WHERE condition;

  • Example: SELECT * FROM employees WHERE department = 'HR';

12. AND Operator

Combines multiple conditions, and retrieves records when all conditions are met.

  • Syntax: SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND ...;

  • Example: SELECT * FROM employees WHERE department = 'HR' AND salary > 54000;

13. OR Operator

Retrieves records when at least one of the conditions is met.

  • Syntax: SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR ...;

  • Example: SELECT * FROM employees WHERE department = 'HR' OR department = 'Marketing';

14. LIKE Operator

Used for pattern matching with wildcard characters (% for zero or more characters, _ for a single character).

  • Syntax: SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;

  • Example: SELECT * FROM employees WHERE name LIKE 'J%'; (selects all employees whose name starts with 'J')

15. BETWEEN Operator

Used to filter the result set within a certain range.

  • Syntax: SELECT column1, column2, ... FROM table_name WHERE column_name BETWEEN value1 AND value2;

  • Example: SELECT * FROM employees WHERE age BETWEEN 20 AND 30;

16. IN Operator

Used to filter the result set based on a list of discrete values.

  • Syntax: SELECT column1, column2, ... FROM table_name WHERE column_name IN (value1, value2, ...);

  • Example: SELECT * FROM employees WHERE department IN ('HR', 'Marketing', 'Sales');

17. NOT Operator

Used to exclude records that match the condition.

  • Syntax: SELECT column1, column2, ... FROM table_name WHERE NOT condition;

  • Example: SELECT * FROM employees WHERE NOT department = 'HR';

18. IS NULL/IS NOT NULL

Used to check if a value is null or not.

  • Syntax: SELECT column1, column2, ... FROM table_name WHERE column_name IS NULL;

  • Example: SELECT * FROM employees WHERE address IS NULL;

  • Syntax for non-null: SELECT column1, column2, ... FROM table_name WHERE column_name IS NOT NULL;

19. Combining AND, OR, and NOT

You can combine these operators to create complex conditions.

  • Example: SELECT * FROM employees WHERE (department = 'HR' AND salary > 50000) OR (department = 'Marketing' AND salary > 60000);

20. MIN

The MIN function is used to find the smallest value in a selected column.

  • Syntax: SELECT MIN(column_name) FROM table_name;

  • Example: SELECT MIN(salary) FROM ordersemployees;

  • This query returns the smallest salary from the employees table.

21. MAX

The MAX function is used to find the largest value in a selected column.

  • Syntax: SELECT MAX(column_name) FROM table_name;

  • Example: SELECT MAX(salary) FROM employees;

  • This query returns the largest salary from the employees table.

21. SUM

The SUM function is used to calculate the total sum of a numeric column.

  • Syntax: SELECT SUM(column_name) FROM table_name;

  • Example: sql  SELECT SUM(salary) FROM employees;

  • This query calculates the total sum of salaries for all employees.

22. AVG (Average)

The AVG function is used to calculate the average value of a numeric column.

  • Syntax: SELECT AVG(column_name) FROM table_name;

  • Example: SELECT AVG(salary) FROM employees;

  • This query calculates the average salary of the employees.

23. COUNT

The COUNT function is used to count the number of rows that match a specified criterion.

  • Syntax: SELECT COUNT(column_name) FROM table_name;

  • Example:  SELECT COUNT(*) FROM employees;

  • This query counts the number of rows (employees) in the employees table.

  • SELECT COUNT(DISTINCT column_name) FROM table_name;

  • This variation of the COUNT function counts the number of distinct

24. ORDER BY Clause

Sorts the result set in ascending or descending order.

  • Syntax:

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

  • Example:

  • SELECT * FROM employees ORDER BY last_name ASC, first_name DESC;

  • This sorts employees first by last name in ascending order, then by first name in descending order.

25. GROUP BY Clause

Groups rows with the same values in specified columns.