-
Notifications
You must be signed in to change notification settings - Fork 0
Module 3
SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. In order to work with SQL, you'll need to know some basic concepts:
- Tables: A table is a collection of data stored in rows and columns. Each column represents a specific attribute, while each row represents a specific instance of that attribute.
- Queries: A query is a request for data from a database. You can use SQL to write queries that retrieve, insert, update, or delete data from a table.
- Statements: SQL statements are used to perform specific actions, such as creating a table or inserting data into a table.
- Clauses: SQL clauses are used to specify conditions for a query or statement. Examples include the WHERE clause (which filters data based on specific conditions) and the ORDER BY clause (which orders data by a specific column).
SQL supports several data types, which are used to define the type of data that can be stored in a column of a table. Some common data types include:
- Numeric: Numeric data types are used to store numeric values, such as integers, decimals, or floating-point numbers.
- String: String data types are used to store text data, such as strings or individual characters.
- Date/Time: Date and time data types are used to store date and time values, such as timestamps or dates.
- Boolean: Boolean data types are used to store true/false values.
DDL changes the table structure like creating a table, deleting a table, altering a table, etc. All the command of DDL is auto-committed which means it permanently saves all the changes in the database. Commands that come under DDL:
- CREATE
- ALTER
- DROP
- TRUNCATE
DML commands are used to modify the database. It is responsible for all forms of changes in the database. The command of DML is not auto-committed which means it can't permanently save all the changes in the database. They can be rollback. Commands that come under DML:
- INSERT
- UPDATE
- DELETE
DCL commands are used to grant and take back authority from any database user.
Here are some commands that come under DCL:
- Grant
- Revoke
Transaction Control Language (TCL) is a set of SQL commands used to manage transactions in a relational database. TCL commands are used to define the beginning and end of a transaction, as well as to commit or rollback changes made during a transaction. The three main TCL commands are:
- Commit
- Rollback
- Savepoint
A database is used to store the collection of records in an organized form. It allows us to hold the data into tables, rows, columns, and indexes to find the relevant information frequently. We can access and manage the records through the database very easily.
mysql> CREATE DATABASE office;
mysql> SHOW CREATE DATABASE office;
mysql> SHOW DATABASES;
mysql> USE office;
SELECT Database is used in MySQL to select a particular database to work with. This query is used when multiple databases are available with MySQL Server.
USE database_name;
USE customers;
When we work with the MySQL server, it is a common task to show or list the databases, displaying the table from a particular database, and information of user accounts and their privileges that reside on the server. In this article, we are going to focus on how to list databases in the MySQL server.
We can list all the databases available on the MySQL server host using the following command, as shown below:
mysql> SHOW DATABASES;
We can drop an existing database in MySQL by using the DROP DATABASE statement with the below syntax:
DROP DATABASE [IF EXISTS] database_name;
In MySQL, we can also use the below syntax for deleting the database. It is because the schema is the synonym for the database, so we can use them interchangeably.
DROP SCHEMA [IF EXISTS] database_name;
Tables are a fundamental component of MySQL databases. A table comprises rows and columns, with each column representing a specific type of data, and each row representing a record or instance of that data. MySQL tables can be created, modified, and deleted using SQL queries.
To create a table in MySQL, the CREATE TABLE statement is used. This statement specifies the columns and data types for the table, as well as any constraints or indexes that should be applied.
Example of CREATE TABLE:
CREATE TABLE emp (
id INT NOT NULL AUTO_INCREMENT,
Address VARCHAR(50) NOT NULL,
full_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
);
In MySQL, the ALTER TABLE statement is used to modify an existing table. It can be used to add or remove columns, change column data types, add or remove indexes, and more.
Example of Alter Table:
ALTER TABLE employees
ADD COLUMN phone_number VARCHAR(20) AFTER email;
Modify the table
ALTER TABLE employees
MODIFY COLUMN hire_date DATETIME;
Rename The table
ALTER TABLE employees
RENAME TO employees_backup;
Dropping the table
ALTER TABLE employees
DROP COLUMN phone_number;
MYSQL provides a SHOW TABLES command to list all the tables in a database. This command is particularly useful when you want to know what tables are available to you, or if you want to verify that a table you created actually exists.
SHOW TABLES;
SHOW TABLES LIKE <pattern>;
SHOW TABLES LIKE 'office';
Descibe Table Describe table command is used to get the structure of a table.
DESCRIBE <tablename>;
DESCRIBE emp;
Drop Table The DROP TABLE statement is used to delete a table from a MySQL database. The syntax for using the DROP TABLE statement is as follows:
DROP TABLE [IF EXISTS] <tablename>;
DROP TABLE emp;
The statement must be executed by a user with appropriate privileges to delete the table. Once the statement is executed, the table and all its contents are permanently deleted, and cannot be recovered.
MYSQL provides a temporary table feature that allows you to create a temporary table that is only accessible to the current session. This is useful for storing intermediate results from a query, or for performing complex operations on a table without affecting the original table.
Create Temporary Table To create a temporary table in MySQL, the CREATE TEMPORARY TABLE statement is used. This statement specifies the columns and data types for the table, as well as any constraints or indexes that should be applied.
CREATE TEMPORARY TABLE temp_table (
id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
);
To drop a temporary table in MySQL, the DROP TEMPORARY TABLE statement is used. This statement specifies the name of the table to be dropped.
DROP TEMPORARY TABLE temp_table;
The SHOW COLUMNS command is used to list all the columns in a table. This command is particularly useful when you want to know what columns are available to you, or if you want to verify that a column you created actually exists.
SHOW COLUMNS FROM <tablename>;
SHOW COLUMNS FROM office;
SELECT INFORMATION_SCHEMA.COLUMNS FROM office;
Retrieves data from one or more tables
Example: To retrieve all data from a table named "employees" with columns "id", "name", and "salary".
SELECT id, name, salary
FROM employees;
Adds new data to a table
Example: To add a new employee with ID 123, name "John Smith", and salary 50000 to the "employees" table.
INSERT INTO employees (id, name, salary)
VALUES (123, 'John Smith', 50000);
Modifies existing data in a table
Example: To update the salary of employee with ID 123 to 60000 in the "employees" table
UPDATE employees
SET salary = 60000
WHERE id = 123;
Removes data from a table.
Example: To delete all data from the "employees" table:
DELETE FROM employees;
Used in SELECT, UPDATE, and DELETE statements to specify conditions for selecting, updating, or deleting data
Example: To retrieve all employees from the "employees" table whose salary is greater than or equal to 50000:
SELECT *
FROM employees
WHERE salary >= 50000;
Used in SELECT statements to group data by one or more columns
Example: To count the number of employees in each department in the "employees" table:
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
Used in SELECT statements with GROUP BY to specify conditions for filtering grouped data
Example: To count the number of employees in each department in the "employees" table, but only include departments with more than 10 employees:
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;
Used in SELECT statements to sort the result set by one or more columns
Example: To retrieve all employees from the "employees" table sorted by salary in descending order:
SELECT *
FROM employees
ORDER BY salary DESC;
Note: The ORDER BY clause can be used with one or more columns, and can sort in either ascending (ASC) or descending (DESC) order.
A join is a fundamental SQL operation that combines rows from two or more tables based on a related column between them. The join operation allows you to retrieve data from multiple tables that are related to each other, and combine them into a single result set.
There are different types of joins in SQL, including:
- Inner join
- Left join
- Right join
- Full join
Returns only the matching rows from both tables
Example: To retrieve all orders with customer information from the "orders" table and "customers" table, where there is a match between the "customer_id" column in both tables:
SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
INNER JOIN customers c
ON o.customer_id = c.customer_id;
Returns all the rows from the left table and the matching rows from the right table, and NULL values for the non-matching rows in the right table.
Example: To retrieve all customers and their orders from the "customers" table and "orders" table, including those customers who have not placed any orders.
SELECT c.customer_id, c.customer_name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;
Returns all the rows from the right table and the matching rows from the left table, and NULL values for the non-matching rows in the left table.
Example: To retrieve all orders and their corresponding customer information from the "orders" table and "customers" table, including those orders that do not have any matching customer information:
SELECT o.order_id, o.order_date, c.customer_id, c.customer_name
FROM orders o
RIGHT JOIN customers c
ON o.customer_id = c.customer_id;
Returns all the rows from both tables, and NULL values for the non-matching rows in both tables
Example: To retrieve all customers and orders from the "customers" table and "orders" table, including those customers and orders that do not have any matching information:
SELECT c.customer_id, c.customer_name, o.order_id, o.order_date
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id;
Primary Key: A unique identifier for each row in a table that ensures no two rows are the same. A primary key cannot contain null values, and there can only be one primary key per table.
Example:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50),
customer_email VARCHAR(50)
);
A column or set of columns in a table refers to the primary key of another table. A foreign key creates a relationship between two tables, allowing data to be joined and referenced across tables.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
A key that ensures that no two rows have the same value in the specified column(s). Unlike primary keys, unique keys can contain null values, but only one unique key can be defined per column.
Example:
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(50),
product_sku VARCHAR(20) UNIQUE,
product_price DECIMAL(10, 2)
);
A key that is made up of two or more columns, is used to uniquely identify a row in a table.
Example:
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
Note: It's important to choose the right type of key based on the requirements of your database and the relationships between tables. The choice of the key can affect performance, data integrity, and the ability to query and join data across tables.
In SQL, a view is a virtual table that is created based on the result of a SELECT statement. Views allow you to define complex queries that combine data from multiple tables, and then use that query as a table for future queries. Here are some key points to keep in mind about views:
-
A view is a stored SELECT statement that behaves like a table.
-
Views can be used to simplify complex queries by predefining a SELECT statement.
-
Views are not physical tables, but instead represent a virtual table that is created on the fly based on the SELECT statement used to define it.
-
Views can be used to control access to data by allowing users to see only the data they need, without granting them access to the underlying tables.
-
Views can also be used to encapsulate complex logic or calculations, making it easier to reuse that logic across multiple queries.
-
Views can be created, altered, and dropped using SQL statements. Example:
CREATE VIEW customer_orders AS
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;
Conditional statements in SQL are used to control the flow of execution based on certain conditions. These statements allow you to specify certain conditions that must be met before a specific action can be taken.
The IF statement is used to execute a certain block of code based on a condition. It takes a boolean expression as an argument and executes the statements inside the IF block if the condition is true. Otherwise, it executes the statements inside the ELSE block. Here's an example:
IF (1 > 0) THEN
SELECT '1 is greater than 0';
ELSE
SELECT '1 is less than or equal to 0';
END IF;
The NULLIF function is used to compare two expressions and return NULL if they are equal. This function takes two arguments and returns NULL if the two arguments are equal, otherwise, it returns the first argument. Here's an example:
SELECT NULLIF(5, 5);
The IFNULL function is used to check if a value is NULL and return a replacement value if it is. It takes two arguments, the first argument is the value to be checked, and the second argument is the replacement value. Here's an example:
SELECT IFNULL(NULL, 'replacement');
The CASE statement is used to perform conditional logic in SQL. It allows you to specify multiple conditions and the corresponding actions to be taken. Here's an example:
SELECT
CASE
WHEN score > 90 THEN 'A'
WHEN score > 80 THEN 'B'
WHEN score > 70 THEN 'C'
ELSE 'F'
END
FROM students;
This will return a letter grade based on the student's score. If the score is greater than 90, it will return 'A', if it's between 80 and 90, it will return 'B', and so on. If the score is less than or equal to 70, it will return 'F'.
Normalization is the process of organizing data in a database to eliminate redundancy and improve data integrity. It involves dividing large tables into smaller ones and defining relationships between them. It is a technique used to eliminate data anomalies and improve database efficiency. Most commonly used levels of normalization are:
- 1NF (First Normal Form)
- A table is said to be in 1NF if it contains only atomic values and has no repeating groups.
- This means that each column in the table contains only one value, and there are no duplicate rows.
- 2NF (Second Normal Form)
- A table is said to be in 2NF if it is in 1NF and all non-key columns are fully dependent on the primary key.
- This means that each non-key column should depend on the entire primary key, not just a part of it.
- 3NF (Third Normal Form)
- A table is said to be in 3NF if it is in 2NF and all non-key columns are independent of each other.
- This means that each non-key column should depend only on the primary key, not on any other non-key columns.
- BCNF (Boyce-Codd Normal Form)
- A table is in BCNF if and only if every determinant in the table is a candidate key and it is already in 3NF. In other words, every non-trivial functional dependency in the table is a dependency on a candidate key.
-
Locks in SQL are used to ensure data consistency and prevent data conflicts that may arise when multiple transactions try to access the same data simultaneously.
-
A lock can be placed on a table, a row, or a specific set of rows to prevent other transactions from modifying or reading the data until the lock is released.
-
There are different types of locks in SQL, such as shared locks, exclusive locks, and update locks. Shared locks allow multiple transactions to read the same data, while exclusive locks prevent any other transactions from accessing the data.
-
Locks can be obtained automatically by the database management system or explicitly requested by the transaction.
-
Deadlocks can occur when two or more transactions hold locks on resources that the other needs to complete their operation, resulting in a situation where neither transaction can proceed.
- Write locks are used to prevent other processes from modifying a shared resource while a process is writing to it.
- A write lock ensures exclusive access to the resource, meaning no other process can read or write to it until the write lock is released.
- For example, if a process is writing data to a file, it will acquire a write lock on that file to prevent other processes from modifying the file while it's being written.
- Read locks are used to allow multiple processes to read from a shared resource simultaneously while preventing any process from modifying the resource.
- A read lock allows multiple processes to acquire shared access to the resource, meaning they can all read from it at the same time, but they cannot modify it.
- For example, if multiple processes need to read data from a file simultaneously, they will acquire a read lock on that file to prevent any process from modifying it while they're reading from it.
- Shared locks are similar to read locks, but they allow multiple processes to simultaneously acquire shared access to a resource and read from it.
- However, unlike read locks, shared locks also allow a single process to acquire an exclusive lock and modify the resource.
- for example, if multiple processes need to read data from a database table, they will acquire a shared lock on that table, but if one of the processes needs to modify the table, it will acquire an exclusive lock to prevent other processes from accessing the table while it's being modified.
- Exclusive locks are used to prevent any other process from accessing a resource while a process is modifying it.
- An exclusive lock ensures exclusive access to the resource, meaning no other process can read or write to it until the exclusive lock is released.
- For example, if a process needs to modify data in a database table, it will acquire an exclusive lock on that table to prevent other processes from accessing the table while it's being modified. Syntax:
LOCK TABLES table_name [READ | WRITE];